Administrative API Reference

sqluroll - Rollforward Database

Recovers a database by applying transactions recorded in the database log files. Called after a database or a table space backup has been restored, or if any table spaces have been taken offline by the database due to a media error. The database must be recoverable (that is, either logretain, userexit, or both of these database configuration parameters must be set on) before the database can be recovered with roll-forward recovery.

Scope

In a multi-node environment, this API can only be called from the catalog node. A database or table space rollforward call specifying a point-in-time affects all nodes that are listed in the db2nodes.cfg file. A database or table space rollforward call specifying end of logs affects the nodes that are specified. If no nodes are specified, it affects all nodes that are listed in the db2nodes.cfg file; if no roll forward is needed on a particular node, that node is ignored.

Authorization

One of the following:

• sysadm
• sysctrl
• sysmaint

Required Connection

None. This API establishes a database connection.

Version

sqlutil.h

C API Syntax

/* File: sqlutil.h */ /* API: Rollforward Database */ /* ... */ SQL_API_RC SQL_API_FN sqluroll ( struct rfwd_input * pRfwdInput, struct rfwd_output * pRfwdOuput, struct sqlca * pSqlca); /* ... */

Generic API Syntax

/* File: sqlutil.h */ /* API: Rollforward Database */ /* ... */ SQL_API_RC SQL_API_RN sqlgroll ( struct grfwd_input * grfwdin, struct rfwd_output * rfwdout, struct sqlca * sqlca); SQL_STRUCTURE grfwd_input { unsigned short DbAliasLen, unsigned short StopTimeLen, unsigned short UserNameLen, unsigned short PasswordLen, unsigned short OverflowLogPathLen, unsigned short ReportFileLen, /* NOTE: This parameter is no longer used */ /* for the DB2 Data Links Manager. */ sqluint32 Version, char * pDbAlias, unsigned short CallerAction, char * pStopTime, char * pUserName, char * pPassword, char * pOverflowLogPath, unsigned short NumChngLgOvrflw, struct sqlurf_newlogpath * pChngLogOvrflw, unsigned short ConnectMode, struct sqlu_tablespace_bkrst_list * pTablespaceList, short AllNodeFlag, short NumNodes, SQL_PDB_NODE_TYPE * pNodeList, short NumNodeInfo, unsigned short DlMode, /* NOTE: This parameter is no longer used */ /* for the DB2 Data Links Manager. */ char * pReportFile, /* NOTE: This parameter is no longer used */ /* for the DB2 Data Links Manager. */ char * pDroppedTblID, char * pExportDir } /* ... */

API Parameters

pRfwdInput

Input. A pointer to the rfwd_input structure. For more information about this structure, see RFWD-INPUT.

pRfwdOutput

Output. A pointer to the rfwd_output structure. For more information about this structure, see RFWD-OUTPUT.

DbAliasLen

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

StopTimeLen

Input. A 2-byte unsigned integer representing the length in bytes of the stop time parameter. Set to zero if no stop time 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.

OverflowLogPathLen

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

ReportFileLen

Input. This parameter is not currently used, and should be set to zero.

Version

Input. The version ID of the rollforward parameters. It is defined as SQLUM_RFWD_VERSION.

pDbAlias

Input. A string containing the database alias. This is the alias that is cataloged in the system database directory.

CallerAction

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

SQLUM_ROLLFWD

Rollforward to the point in time specified by pPointInTime. For database rollforward, the database is left in rollforward-pending state. For table space rollforward to a point in time, the table spaces are left in rollforward-in-progress state.

SQLUM_STOP

End roll-forward recovery. No new log records are processed and uncommitted transactions are backed out. The rollforward-pending state of the database or table spaces is turned off. Synonym is SQLUM_COMPLETE.

SQLUM_ROLLFWD_STOP

Rollforward to the point in time specified by pPointInTime, and end roll-forward recovery. The rollforward-pending state of the database or table spaces is turned off. Synonym is SQLUM_ROLLFWD_COMPLETE.

SQLUM_QUERY

Query values for pNextArcFileName, pFirstDelArcFileName, pLastDelArcFileName, and pLastCommitTime. Return database status and a node number.

SQLUM_PARM_CHECK

Validate parameters without performing the roll forward.

SQLUM_CANCEL

Cancel the rollforward operation that is currently running. The database or table space are put in recovery pending state.

Note:

This option cannot be used while the rollforward is actually running. It can be used if the rollforward is paused (that is, waiting for a STOP), or if a system failure occurred during the rollforward. It should be used with caution.

Rolling databases forward may require a load recovery using tape devices. The rollforward API will return with a warning message if user intervention on a device is required. The API can be called again with one of the following three caller actions:

SQLUM_LOADREC_CONTINUE

Continue using the device that generated the warning message (for example, when a new tape has been mounted).

SQLUM_LOADREC_DEVICE_TERMINATE

Stop using the device that generated the warning message (for example, when there are no more tapes).

SQLUM_LOADREC_TERMINATE

Terminate all devices being used by load recovery.

pStopTime

Input. A character string containing a time stamp in ISO format. Database recovery will stop when this time stamp is exceeded. Specify SQLUM_INFINITY_TIMESTAMP to roll forward as far as possible. May be NULL for SQLUM_QUERY, SQLUM_PARM_CHECK, and any of the load recovery (SQLUM_LOADREC_xxx) caller actions.

pUserName

Input. A string containing the user name of the application. May be NULL.

pPassword

Input. A string containing the password of the supplied user name (if any). May be NULL.

pOverflowLogPath

Input. This parameter is used to specify an alternate log path to be used. In addition to the active log files, archived log files need to be moved (by the user) into the logpath (see sqlfxdb - Get Database Configuration) before they can be used by this utility. This can be a problem if the user does not have sufficient space in the logpath. The overflow log path is provided for this reason. During roll-forward recovery, the required log files are searched, first in the logpath, and then in the overflow log path. The log files needed for table space roll-forward recovery can be brought into either the logpath or the overflow log path. If the caller does not specify an overflow log path, the default value is the logpath. In a multi-node environment, the overflow log path must be a valid, fully qualified path; the default path is the default overflow log path for each node. In a single-node environment, the overflow log path can be relative if the server is local.

NumChngLgOvrflw

MPP only. The number of changed overflow log paths. These new log paths override the default overflow log path for the specified node only.

pChngLogOvrflw

MPP only. A pointer to a structure containing the fully qualified names of changed overflow log paths. These new log paths override the default overflow log path for the specified node only.

ConnectMode

Input. Valid values (defined in sqlutil) are:

SQLUM_OFFLINE

Offline roll forward. This value must be specified for database roll-forward recovery.

SQLUM_ONLINE

Online roll forward.

pTablespaceList

Input. A pointer to a structure containing the names of the table spaces to be rolled forward to the end-of-logs or to a specific point in time. If not specified, the table spaces needing rollforward will be selected.

AllNodeFlag

MPP only. Input. Indicates whether the rollforward operation is to be applied to all nodes defined in db2nodes.cfg. Valid values are:

SQLURF_NODE_LIST

Apply to nodes in a node list that is passed in pNodeList.

SQLURF_ALL_NODES

Apply to all nodes. pNodeList should be NULL. This is the default value.

SQLURF_ALL_EXCEPT

Apply to all nodes except those in a node list that is passed in pNodeList.

SQLURF_CAT_NODE_ONLY

Apply to the catalog node only. pNodeList should be NULL.

NumNodes

Input. Specifies the number of nodes in the pNodeList array.

pNodeList

Input. A pointer to an array of node numbers on which to perform the roll-forward recovery.

NumNodeInfo

Input. Defines the size of the output parameter pNodeInfo, which must be large enough to hold status information from each node that is being rolled forward. In a single-node environment, this parameter should be set to 1. The value of this parameter should be same as the number of nodes for which this API is being called.

DlMode

Input. This parameter is not currently used, and should be set to zero.

pReportFile

Input. This parameter is not currently used, and should be set to NULL.

pDroppedTblID

Input. A string containing the ID of the dropped table whose recovery is being attempted.

pExportDir

Input. The directory into which the dropped table data will be exported.

pSqlca

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

REXX API Syntax

ROLLFORWARD DATABASE database-alias [USING :value] [USER username USING password] [rollforward_action_clause | load_recovery_action_clause] where rollforward_action_clause stands for: { TO point-in-time [AND STOP] | { [TO END OF LOGS [AND STOP] | STOP | CANCEL | QUERY STATUS | PARM CHECK } [ON {:nodelist | ALL NODES [EXCEPT :nodelist]}] } } [TABLESPACE {ONLINE |:tablespacenames [ONLINE]} ] [OVERFLOW LOG PATH default-log-path [:logpaths]] and load_recovery_action_clause stands for: LOAD RECOVERY { CONTINUE | DEVICE_TERMINATE | TERMINATE }

REXX API Parameters

database-alias

Alias of the database to be rolled forward.

value

A compound REXX host variable containing the output values. In the following, XXX represents the host variable name:

XXX.0

Number of elements in the variable

XXX.1

The application ID

XXX.2

Number of replies received from nodes

XXX.2.1.1

First node number

XXX.2.1.2

First state information

XXX.2.1.3

First next archive file needed

XXX.2.1.4

First first archive file to be deleted

XXX.2.1.5

First last archive file to be deleted

XXX.2.1.6

First last commit time

XXX.2.2.1

Second node number

XXX.2.2.2

Second state information

XXX.2.2.3

Second next archive file needed

XXX.2.2.4

Second first archive file to be deleted

XXX.2.2.5

Second last archive file to be deleted

XXX.2.2.6

Second last commit time

XXX.2.3.x

and so on.

username

Identifies the user name under which the database is to be rolled forward.

password

The password used to authenticate the user name.

point-in-time

A time stamp in ISO format, yyyy-mm-dd-hh.mm.ss.nnnnnn (year, month, day, hour, minutes,seconds, microseconds), expressed in Coordinated Universal Time (UTC).

tablespacenames

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

XXX.0

Number of table spaces to be rolled forward

XXX.1

First table space name

XXX.2

Second table space name

XXX.x

and so on.

default-log-path

The default overflow log path to be searched for archived logs during recovery

logpaths

A compound REXX host variable containing a list of alternate log paths to be searched for archived logs during recovery. In the following, XXX is the name of the host variable:

XXX.0

Number of changed overflow log paths

XXX.1.1

First node

XXX.1.2

First overflow log path

XXX.2.1

Second node

XXX.2.2

Second overflow log path

XXX.3.1

Third node

XXX.3.2

Third overflow log path

XXX.x.1

and so on.

nodelist

A compound REXX host variable containing a list of nodes. In the following, XXX is the name of the host variable:

XXX.0

Number of nodes

XXX.1

First node

XXX.2

Second node

XXX.x

and so on.

Sample Programs

C

\sqllib\samples\c\backrest.c

COBOL

\sqllib\samples\cobol\backrest.cbl

Usage Notes

The database manager uses the information stored in the archived and the active log files to reconstruct the transactions performed on the database since its last backup.

The action performed when this API is called depends on the rollforward_pending flag of the database prior to the call. This can be queried using sqlfxdb - Get Database Configuration. The rollforward_pending flag is set to DATABASE if the database is in roll-forward pending state. It is set to TABLESPACE if one or more table spaces are in SQLB_ROLLFORWARD_PENDING or SQLB_ROLLFORWARD_IN_PROGRESS state. The rollforward_pending flag is set to NO if neither the database nor any of the table spaces needs to be rolled forward.

If the database is in roll-forward pending state when this API is called, the database will be rolled forward. Table spaces are returned to normal state after a successful database roll-forward, unless an abnormal state causes one or more table spaces to go offline. If the rollforward_pending flag is set to TABLESPACE, only those table spaces that are in roll-forward pending state, or those table spaces requested by name, will be rolled forward.

Note:

If table space rollforward terminates abnormally, table spaces that were being rolled forward will be put in SQLB_ROLLFORWARD_IN_PROGRESS state. In the next invocation of ROLLFORWARD DATABASE, only those table spaces in SQLB_ROLLFORWARD_IN_PROGRESS state will be processed. If the set of selected table space names does not include all table spaces that are in SQLB_ROLLFORWARD_IN_PROGRESS state, the table spaces that are not required will be put into SQLB_RESTORE_PENDING state.

If the database is not in roll-forward pending state and no point in time is specified, any table spaces that are in rollforward-in-progress state will be rolled forward to the end of logs. If no table spaces are in rollforward-in-progress state, any table spaces that are in rollforward pending state will be rolled forward to the end of logs.

This API reads the log files, beginning with the log file that is matched with the backup image. The name of this log file can be determined by calling this API with a caller action of SQLUM_QUERY before rolling forward any log files.

The transactions contained in the log files are reapplied to the database. The log is processed as far forward in time as information is available, or until the time specified by the stop time parameter.

Recovery stops when any one of the following events occurs:

• No more log files are found
• A time stamp in the log file exceeds the completion time stamp specified by the stop time parameter
• An error occurs while reading the log file.

Some transactions might not be recovered. The value returned in pLastCommitTime indicates the time stamp of the last committed transaction that was applied to the database.

If the need for database recovery was caused by application or human error, the user may want to provide a time stamp value in pStopTime, indicating that recovery should be stopped before the time of the error. This applies only to full database roll-forward recovery, and to table space rollforward to a point in time. It also permits recovery to be stopped before a log read error occurs, determined during an earlier failed attempt to recover.

When the rollforward_recovery flag is set to DATABASE, the database is not available for use until roll-forward recovery is terminated. Termination is accomplished by calling the API with a caller action of SQLUM_STOP or SQLUM_ROLLFORWARD_STOP to bring the database out of roll-forward pending state. If the rollforward_recovery flag is TABLESPACE, the database is available for use. However, the table spaces in SQLB_ROLLFORWARD_PENDING and SQLB_ROLLFORWARD_IN_PROGRESS states will not be available until the API is called to perform table space roll-forward recovery. If rolling forward table spaces to a point in time, the table spaces are placed in backup pending state after a successful rollforward.

Rolling databases forward may involve prerequisites and restrictions that are beyond the scope of this manual. For more detailed information, see the Administration Guide.

See Also

sqluload - Load

sqlurestore - Restore Database.