Administration Guide

Archive and Retrieve Considerations

The following considerations apply to calling a user exit program for archiving and retrieving log files:

• The database configuration file parameter userexit specifies whether the database manager invokes a user exit program to archive files or to retrieve log files during roll-forward recovery of databases. A request to retrieve a log file is made when the roll-forward database recovery utility needs a log file that is not found in the log path directory.

  Notes:

  1. On Windows NT, you cannot use a REXX user exit to archive logs.
• When archiving, a log file is passed to the user exit when it is full, even if the log file is still active and is needed for normal processing. This allows copies of the data to be moved away from volatile media as quickly as possible. The log file passed to the user exit is retained in the log path directory until it is no longer needed for normal processing. At this point, the disk space is reused.
• DB2 opens a file in read mode when it starts a user exit to archive a log file. Therefore, the user exit should not be able to delete the file while the file is still active. DB2 closes the file when it becomes inactive. If the user exit finishes when the file is inactive, the log file can be deleted but there is a performance cost for doing so.
• When a log file has been archived, and it is inactive, DB2 does not delete the file but renames it as the next log file when such a file is needed. This results in a performance gain since when creating a new log file (instead of renaming the file), all pages must be written out to guarantee the disk space. It is better to re-use than to free up and then re-acquire the necessary pages on disk.
• DB2 will NOT invoke the user exit to retrieve the log file in either crash recovery nor rollback.
• A user exit program does not guarantee roll-forward recovery to the point of failure, but only attempts to make the failure window smaller. As log files fill, they are queued for the user exit routine. Should the disk containing the log fail before a log file is filled, the data in that log file is lost. Also, since the files are queued for archiving, the disk can fail before all the files are copied. Any log files in the queue are lost.
• The configured size of each individual log file has a direct bearing on the user exit. If each log file is very large, a large amount of data can be lost if a disk fails. A log file configured with small log files causes the data to be passed to the user exit routine more often.

  However, if you are moving the data to a slower device such as tape, you might want to have larger log files to prevent the queue from building up. If the queue becomes full, archive and retrieve requests will not be processed. Processing will resume when there is room on the queue. Any requests not processed will not be automatically re-queued.

• An archive request to the user exit program occurs only when userexit is configured and each time an active log file is filled. It is possible that an active log file is not full when the last disconnection from the database occurs and the user exit program is also called for a partially filled active log file.

  Note:

  To free unused log space, the log file is truncated before it is archived.

• A copy of the log should be made to another physical device so that the offline log file can be used by roll-forward recovery if the device containing the log file has a media failure. This should not be the same device containing the database data files.
• In some cases, if a database is closed before a positive response has been received from a user exit program for an archive request, the database manager will send another request when the database is opened. Thus, a log file may be archived more than once. If you do not want this multiple archiving to occur, the user exit program must not allow the subsequent requests for archiving the same file.
• If a user exit program receives a request to archive a file that does not exist (because there were multiple requests to archive and the file was deleted after the first successful archiving), or to retrieve a file that does not exist (because it is located in another directory or the end of the logs has been reached), it should ignore this request and return a successful return code.
• A user exit may be interrupted if a remote client loses its connection to the DB2 server. That is, while handling the archiving of logs through a user exit, one of the other SNA-connected clients dies or powers off resulting in a signal (SIGUSR1) being sent to the server. The server passes the signal to the user exit causing an interrupt. The user exit program can be modified to check for an interrupt and then continue.
• The user exit program should allow for the existence of different log files with the same name after a point in time recovery; it should be written to preserve both log files and to associate those log files with the correct recovery path. (See "Considerations for Managing Log Files".)
• If two or more databases are using a device at the same time, and one of the operations involves a roll-forward operation, a log file needed for roll-forward recovery may not exist on the medium currently in the drive. Two conditions can occur:
  • If the user exit program passes a zero (successful) return code back to the database manager and the requested log file has not been retrieved, the database manager assumes the roll-forward operation is complete to the end of the logs, and the roll-forward operation stops. However, roll-forward processing may not have gone to the end of the logs.
  • If a non-zero return code is returned, the database will be in a roll-forward pending state, and you must either resume or stop roll-forward processing.

  To prevent either situation from occurring, you can ensure that no other databases on the node that calls the user exit program are open during the roll-forward operation, or write a user exit program to handle this situation.

Backup and Restore Considerations (DB2 for OS/2 only)

The following considerations apply if you are writing a user exit program which is called from the BACKUP DATABASE and RESTORE DATABASE utilities:

• A non-zero return code returned by a user exit program causes the utility to fail, and no retry is attempted.
• A wild card must be supported in the file name of a fully qualified file name. For example, C:\SQL00001\*.* and C:\*.MH* are both acceptable search criteria.
• The user exit program must handle the response file format of one fully qualified file name per line with each line terminated by a carriage return and line feed. There is no end-of-file character in the file.
• If multiple backups of the same database are placed on one media, the user exit program should be designed so that the correct version of the backup will be selected during the restore operation. (See the db2uexit.ex2 sample, as described in Sample User Exit Programs for OS/2.)
• Two concurrently running backup processes that are sharing one backup device must be serialized.
• If a backup image is spanned over more than one media, the prompting for the media must be handled by the user exit program or an application it may call. To support this feature, BACKUP DATABASE and RESTORE DATABASE open an operating system foreground session to call the user exit program.
• The user exit program must not back up any subdirectory within the database directory.
• When restoring a database using a user exit program, RESTORE DATABASE requires complete control over that database. However, the workstation can have active connections to databases other than the one being restored.
• If a database is being backed up or restored with a user exit program and another operation is using the same tape device, the backup or restore operation could fail. The backup or restore operation will have to be restarted. To avoid this situation, you can ensure that no other databases on the workstation that call the user exit program for logging are in use while a backup or restore operation is in progress, or you can ensure that the user exit program retries the backup or restore operation at a later time if a device is not ready.
• During the restore operation, the drive letter and the path can be different from those specified during the backup operation. For example, if file dbname.MH1 is backed up from C:\SQLUTIL, you can restore it into d:\xxx.