Administrative API Reference

sqlurestore - Restore Database

Rebuilds a damaged or corrupted database that has been backed up using sqlubkp - Backup Database. The restored database is in the same state it was in when the backup copy was made. This utility can also restore to a database with a name different from the database name in the backup image (in addition to being able to restore to a new database).

This utility can also be used to restore DB2 databases created in the two previous releases.

This utility can also restore from a table space level backup.
Note:This API supersedes sqlurst (DB2 Version 5.0), and should be used with DB2 Data Links Manager. If DB2 Data Links Manager function is not required, sqlurst can be used.

Scope

This API only affects the node from which it is called.

Authorization

To restore to an existing database, one of the following:

To restore to a new database, one of the following:

Required Connection

Database, to restore to an existing database. This API automatically establishes a connection to the specified database.

Instance and database, to restore to a new database. The instance attachment is required to create the database.

To restore to a new database at an instance different from the current instance (as defined by the value of the DB2INSTANCE environment variable), it is necessary to first attach to the instance where the new database will reside.

Version

sqlutil.h

C API Syntax 



/* File: sqlutil.h */
/* API: Restore Database */
/* ... */
SQL_API_RC SQL_API_FN
  sqlurestore (
    char * pSourceDbAlias,
    char * pTargetDbAlias,
    sqluint32 BufferSize,
    sqluint32 RollforwardMode,
    sqluint32 DatalinkMode,
    sqluint32 RestoreType,
    sqluint32 RestoreMode,
    sqluint32 CallerAction,
    char * pApplicationId,
    char * pTimestamp,
    char * pTargetPath,
    sqluint32 NumBuffers,
    char * pReportFile,
    struct sqlu_tablespace_bkrst_list * pTablespaceList,
    struct sqlu_media_list * pMediaSourceList,
    char * pUserName,
    char * pPassword,
    void * pReserved2,
    sqluint32 VendorOptionsSize,
    void * pVendorOptions,
    sqluint32 Parallelism,
    void * pRestoreInfo,
    void * pContainerPageList,
    void * pReserved3,
    struct sqlca * pSqlca);
/* ... */

Generic API Syntax 



/* File: sqlutil.h */
/* API: Restore Database */
/* ... */
SQL_API_RC SQL_API_FN
  sqlgrestore (
   unsigned short SourceDbAliasLen,
   unsigned short TargetDbAliasLen,
   unsigned short TimestampLen,
   unsigned short TargetPathLen,
   unsigned short UserNameLen,
   unsigned short PasswordLen,
   unsigned short ReportFileLen,
   unsigned short Reserved2Len,
   char * pSourceDbAlias,
   char * pTargetDbAlias,
   sqluint32 BufferSize,
   sqluint32 RollforwardMode,
   sqluint32 DatalinkMode,
   sqluint32 RestoreType,
   sqluint32 RestoreMode,
   sqluint32 CallerAction,
   char * pApplicationId,
   char * pTimestamp,
   char * pTargetPath,
   sqluint32 NumBuffers,
   char * pReportFile,
   struct sqlu_tablespace_bkrst_list * pTablespaceList,
   struct sqlu_media_list * pMediaSourceList,
   char * pUserName,
   char * pPassword,
   void * pReserved2,
   sqluint32 VendorOptionsSize,
   void * pVendorOptions,
   sqluint32 Parallelism,
   unsigned short RestoreInfoSize,
   void * pRestoreInfo,
   unsigned short ContainerPageListSize,
   void * pContainerPageList,
   void * pReserved3,
   struct sqlca * pSqlca);
/* ... */

API Parameters

SourceDbAliasLen
Input. A 2-byte unsigned integer representing the length in bytes of the source database alias.

TargetDbAliasLen
Input. A 2-byte unsigned integer representing the length in bytes of the target database alias. Set to zero if no target database alias is specified.

TimestampLen
Input. A 2-byte unsigned integer representing the length in bytes of the time stamp. Set to zero if no time stamp is provided.

TargetPathLen
Input. A 2-byte unsigned integer representing the length in bytes of the target directory. Set to zero if no target path is provided.

UserNameLen
Input. A 2-byte unsigned integer representing the length in bytes of the user name. Set to zero if no user name is provided.

PasswordLen
Input. A 2-byte unsigned integer representing the length in bytes of the password. Set to zero if no password is provided.

ReportFileLen
Input. A 2-byte unsigned integer representing the length in bytes of the report file name. Set to zero if no report file name is provided.

Reserved2Len
Input. A 2-byte unsigned integer representing the length in bytes of the reserved area. Set to zero.

pSourceDbAlias
Input. A string containing the database alias of the source database backup image.

pTargetDbAlias
Input. A string containing the target database alias. If this parameter is null, the pSourceDbAlias alias is used.

BufferSize
Input. Backup buffer size in 4KB allocation units (pages). Minimum is 8 units. The default is 1024 units.
Note:The buffer size entered for a restore must be equal to or an integer multiple of the buffer size used to produce the backup image.

RollforwardMode
Input. Indicates whether or not to place the database in rollforward pending state at the end of the restore. Valid values (defined in sqlutil) are:

SQLUD_ROLLFWD
Place the database in roll-forward pending state after it has been successfully restored.

SQLUD_NOROLLFWD
Do not place the database in roll-forward pending state after it has been successfully restored.

If, following a successful restore, the database is in roll-forward pending state, sqluroll - Rollforward Database must be executed before the database can be used.

DatalinkMode
Input. Specifies whether any tables with DATALINK columns are to be placed in DataLink_Reconcile_Pending (DRP) state, and whether reconciliation of linked files is to be performed. Valid values (defined in sqlutil) are:

SQLUD_DATALINK
Perform reconciliation operations. Tables with a defined DATALINK column must have the RECOVERY YES option specified.

SQLUD_NODATALINK
Do not perform reconciliation operations. Tables with DATALINK columns are placed in DataLink_Reconcile_Pending (DRP) state. Tables with a defined DATALINK column must have the RECOVERY YES option specified.

RestoreType
Input. Specifies the type of restore. Valid values (defined in sqlutil) are:

SQLUD_FULL
Restore everything from the backup image. This will be run offline.

SQLUD_ONLINE_TABLESPACE
Restore only the table space level backups. This will be run online.

SQLUD_HISTORY
Restore only the recovery history file.

RestoreMode
Input. Specifies whether the restore is to be performed offline or online. Valid values (defined in sqlutil) are:

SQLUD_OFFLINE
Perform an offline restore operation.

SQLUD_ONLINE
Perform an online restore operation.

CallerAction
Input. Specifies the type of action to be taken. Valid values (defined in sqlutil) are:

SQLUD_RESTORE
Start the restore.

SQLUD_NOINTERRUPT
Start the restore. Specifies that the restore will run unattended, and that scenarios which normally require user intervention will either be attempted without first returning to the caller, or will generate an error. Use this caller action, for example, when all of the media required for the restore are known to have been mounted, and utility prompts are not desired.

SQLUD_CONTINUE
Continue the restore after the user has performed some action requested by the utility (mount a new tape, for example).

SQLUD_TERMINATE
Terminate the restore after the user has failed to perform some action requested by the utility.

SQLUD_DEVICE_TERMINATE
Remove a particular device from the list of devices used by the restore utility. When a particular device has exhausted its input, restore will return a warning to the caller. Call restore again with this caller action, and the device which generated the warning will be removed from the list of devices being used.

SQLUD_PARM_CHECK
Validate parameters without performing the restore.

SQLUD_RESTORE_STORDEF
Initial call. Table space container redefinition requested.

CallerAction must be set to SQLUD_RESTORE, SQLUD_NOINTERRUPT, SQLUD_RESTORE_STORDEF, or SQLUD_PARM_CHECK on the first call.

pApplicationId
Output. Supply a buffer of length SQLU_APPLID_LEN+1 (defined in sqlutil). Restore will return a string identifying the agent servicing the application. Can be used with the database system monitor APIs to monitor some aspects of the application.

pTimestamp
Input. A string representing the time stamp of the backup image. This field is optional if there is only one backup image in the source specified.

pTargetPath
Input. A string containing the relative or fully qualified name of the target database directory. Used if a new database is to be created for the restored backup.

NumBuffers
Input. The number of buffers to be used for the restore.

pReportFile
The file name, if specified, must be fully qualified. The files which become unlinked during restore (as a result of a fast reconcile) will be reported.

pTablespaceList
Specifies one or more table spaces to be restored. Used when restoring a subset of the backup image or a table space from a table space backup image.

The following restrictions apply:

Note:When restoring a table space that has been renamed since it was backed up, the new table space name must be used in the restore command. If the old table space name is used, it will not be found.

pMediaSourceList
Input. Source media for the backup image. See structure SQLU-MEDIA-LIST. The information the caller needs to provide in this structure is dependent upon the value of the media_type field. Valid values for this field (defined in sqlutil) are:

SQLU_LOCAL_MEDIA
Local devices (a combination of tapes, disks, or diskettes). Provide a list of sqlu_media_entry structures. On OS/2 or the Windows operating system, the entries can be directory paths only, not tape device names.

SQLU_TSM_MEDIA
TSM. No additional input is required, and the TSM shared library provided with DB2 is used. If a different version of TSM is desired, use SQLU_OTHER_MEDIA and provide the shared library name.

SQLU_OTHER_MEDIA
Vendor product. Provide the shared library name in an sqlu_vendor structure.

SQLU_USER_EXIT
User exit. No additional input is required (available on OS/2 only).

For more information, see the Administration Guide.

pUserName
Input. A string containing the user name to be used for a connection.

pPassword
Input. A string containing the password to be used with the user name for a connection.

pReserved2
Reserved for future use.

VendorOptionsSize
Input. The length of the vendor options field which cannot exceed 65535 bytes.

pVendorOptions
Input. To be used by the vendor to pass information from the application to the vendor functions. This data structure must be flat; that is, no level of indirection is supported. Note that byte-reversal is not done, and the code page for this data is not checked.

Parallelism
Input. Degree of intra-partition parallelism (number of buffer manipulators).

RestoreInfoSize
Reserved for future use.

pRestoreInfo
Reserved for future use.

ContainerPageListSize
Reserved for future use.

pContainerPageList
Reserved for future use.

pReserved3
Reserved for future use.

pSqlca
Output. A pointer to the sqlca structure. For more information about this structure, see SQLCA.

REXX API Syntax 



RESTORE DATABASE source-database-alias [USING :value] [USER username USING password]
[TABLESPACE :tablespacenames] [ONLINE | HISTORY FILE ]
[LOAD shared-library [OPTIONS vendor-options] [OPEN num-sessions SESSIONS] |
 FROM :source-area | USE TSM [OPEN num-sessions SESSIONS] | USER_EXIT]
[TAKEN AT timestamp] [TO target-directory] [INTO target-database-alias] 
[ACTION caller-action] [WITH num-buffers BUFFERS] [BUFFERSIZE buffer-size]
[WITHOUT ROLLING FORWARD] [PARALLELISM parallelism-degree]

REXX API Parameters

source-database-alias
Alias of the source database from which the database backup image was taken.

value
A compound REXX host variable to which the database restore information is returned. In the following, XXX represents the host variable name:

XXX.0
Number of elements in the variable (always 1)

XXX.1
An application ID that identifies the agent that serves the application.

username
Identifies the user name to be used for connection.

password
The password used to authenticate the user name.

tablespacenames
A compound REXX host variable containing a list of table spaces to be restored. In the following, XXX is the name of the host variable:

XXX.0
Number of table spaces to be restored

XXX.1
First table space name

XXX.2
Second table space name

XXX.3
and so on.

HISTORY FILE
Specifies to restore the history file from the backup.

shared-library
The name of the shared library (DLL on OS/2 or the Windows operating system) containing the vendor restore I/O functions to be used. It may contain the full path. If the full path is not given, defaults to the path on which the user exit program resides.

vendor-options
Information required by the vendor functions.

num-sessions
The number of I/O sessions to be used with TSM or the vendor product.

source-area
A compound REXX host variable that indicates on which directory or device the backup image resides. The default value is the current directory. On OS/2 or the Windows operating system, the entries can be directory paths only, not tape device names.

timestamp
The time stamp of the database backup.

target-directory
The directory of the target database.

target-database-alias
Alias of the target database. If the target database does not exist, it will be created.

caller-action
Specifies action to be taken. Valid values are:

SQLUD_RESTORE
Start the restore.

SQLUD_NOINTERRUPT
Start the restore. Specifies that the restore will run unattended, and that scenarios which normally require user intervention will either be attempted without first returning to the caller, or will generate an error. Use this caller action, for example, when all of the media required for the restore are known to have been mounted, and utility prompts are not desired.

SQLUD_CONTINUE
Continue the restore after the user has performed some action requested by the utility (mount a new tape, for example).

SQLUD_TERMINATE
Terminate the restore after the user has failed to perform some action requested by the utility.

SQLUD_DEVICE_TERMINATE
Remove a particular device from the list of devices used by the restore utility. When a particular device has exhausted its input, restore will return a warning to the caller. Call restore again with this caller action, and the device which generated the warning will be removed from the list of devices being used.

SQLUD_PARM_CHECK
Validate parameters without performing the restore.

SQLUD_RESTORE_STORDEF
Initial call. Table space container redefinition requested.

num-buffers
Number of backup buffers to be used.

buffer-size
Backup buffer size in allocation units of 4KB. Minimum is 16 units.

parallelism-degree
Number of buffer manipulators.

Sample Programs

C
\sqllib\samples\c\backrest.c

COBOL
\sqllib\samples\cobol\backrest.cbl

Usage Notes

For offline restore, this utility connects to the database in exclusive mode. The utility fails if any application, including the calling application, is already connected to the database that is being restored. In addition, the request will fail if the operating system restore utility is being used to perform the restore, and any application, including the calling application, is already connected to any database on the same workstation. If the connect is successful, the API locks out other applications until the restore is completed.

The current database configuration file will not be replaced by the backup copy unless it is unusable. If the file is replaced, a warning message is returned.

The database or table space must have been backed up using sqlubkp - Backup Database.

If the caller action is SQLUD_NOINTERRUPT, the restore continues without prompting the application. If the caller action is SQLUD_RESTORE, and the utility is restoring to an existing database, the utility returns control to the application with a message requesting some user interaction. After handling the user interaction, the application calls RESTORE DATABASE again, with the caller action set to indicate whether processing is to continue (SQLUD_CONTINUE) or terminate (SQLUD_TERMINATE) on the subsequent call. The utility finishes processing, and returns an SQLCODE in the sqlca.

To close a device when finished, set the caller action to SQLUD_DEVICE_TERMINATE. If, for example, a user is restoring from 3 tape volumes using 2 tape devices, and one of the tapes has been restored, the application obtains control from the API with an SQLCODE indicating end of tape. The application can prompt the user to mount another tape, and if the user indicates "no more", return to the API with caller action SQLUD_DEVICE_TERMINATE to signal end of the media device. The device driver will be terminated, but the rest of the devices involved in the restore will continue to have their input processed until all segments of the restore set have been restored (the number of segments in the restore set is placed on the last media device during the backup process). This caller action can be used with devices other than tape (vendor supported devices).

To perform a parameter check before returning to the application, set caller action to SQLUD_PARM_CHECK.

Set caller action to SQLUD_RESTORE_STORDEF when performing a redirected restore; used in conjunction with sqlbstsc - Set Tablespace Containers. For more information, see the Administration Guide.

If an error occurs, the utility terminates and returns the error in the sqlca structure.

If a system failure occurs during a critical stage of restoring a database, the user will not be able to successfully connect to the database until a successful restore is performed. This condition will be detected when the connection is attempted, and an error message is returned. If the backed-up database is not configured for roll-forward recovery, and there is a usable current configuration file with either of these parameters enabled, following the restore, the user will be required to either take a new backup of the database, or disable the log retain and user exit parameters before connecting to the database.

Although the restored database will not be dropped (unless restoring to a nonexistent database), if the restore fails, it will not be usable.

If the restore type specifies that the recovery history file on the backup is to be restored, it will be restored over the existing recovery history file for the database, effectively erasing any changes made to the history file after the backup that is being restored. If this is undesirable, restore the history file to a new or test database so that its contents can be viewed without destroying any updates that have taken place.

If, at the time of the backup operation, the database was enabled for roll forward recovery, the database can be brought to the state it was in prior to the occurrence of the damage or corruption by issuing sqluroll after successful execution of sqlurestore. If the database is recoverable, it will default to roll forward pending state after the completion of the restore.

If the database backup image is taken offline, and the caller does not want to roll forward the database after the restore, the RollforwardMode parameter can be set to SQLUD_NOROLLFWD. This results in the database being useable immediately after the restore. If the backup image is taken online, the caller must roll forward through the corresponding log records at the completion of the restore.

See Also

sqlbstsc - Set Tablespace Containers

sqlemgdb - Migrate Database

sqlfxdb - Get Database Configuration

sqlubkp - Backup Database

sqluroll - Rollforward Database.

[ Top of Page | Previous Page | Next Page ]