View figure.A variety of problems can occur in a relational database management system, leading to inaccuracies or loss of data. A power failure can bring the computer to a halt; the disk used to store information could become damaged; users can make errors such as dropping the wrong table or dbspace. Database recovery refers to the processing needed to correct the data when something goes wrong.
The problems that can occur fall into the following categories. This chapter explains how to recover from those that fall into the first two categories. For information on how to recover from the other types, see the DB2 Server for VM System Administration or DB2 Server for VSE System Administration manual.
A single application (for example, an ISQL command or routine, or a DBS Utility command) fails to complete successfully.
The system or application does the requested function, but the request itself was in error: that is, the user (or application program) did not specify the correct function.
The operating system, CICS subsystem, or the database manager can end abnormally because of error conditions or a power failure.
The system may be unable to read data from or write it to the DASD device on which it is stored because the storage medium is unreadable or damaged. Such an error could occur on the log or the storage pool.
With VSE Guest Sharing, whereby users on VSE are accessing a DB2 Server for VM application server, any of the subsystems involved (the database manager, VM/ESA operating system, VSE, or the CICS subsystem) may end abnormally.
When a user or an application program has made a change or a group of related changes to the database, and if the application in question completed successfully, the user or program issues an SQL COMMIT WORK statement to the application server, to commit these changes to the database. If the application did not complete successfully, the user instead issues an SQL ROLLBACK WORK statement, which undoes all the changes made up to the point of the error since the last COMMIT WORK statement, or since the start of the program or session.
A group of SQL statements is called a logical unit of work (LUW). An LUW can be as small as one statement, or as large as an entire application execution (or ISQL session). All SQL statements are processed within an LUW. If no LUW exists when a statement is issued, then the database manager creates one.
VM users can take advantage of CMS work units which allow them to maintain more than one logical unit of work (LUW) at a time. With separate CMS work units, application programs can be independent of one another. For example, a user can run a program, and in the middle of an LUW, have that program call a second program which runs in a separate CMS work unit. When work is committed in the second program, it does not affect the active LUW in the first program.
| Note: | CMS work units require extra processing overhead, so should only be used when necessary. If an application does not need this support, set the WORKUNIT option of the SQLINIT command to NO. |
An operation is atomic if within a logical unit of work (LUW), it can succeed or fail on its own; that is, it does not affect other operations as long as they do not depend on it. The DB2 Server for VSE & VM database manager considers all operations are atomic except those that occur in dbspaces residing in nonrecoverable storage pools, and those that occur when LOGMODE=N (running with the no-log option).
Suppose you have an application program that performs the following operations within one LUW: a DELETE, an UPDATE, and an INSERT statement. Assume the DELETE statement will process successfully; then, the UPDATE statement will change the values in the table as specified. If, at the end of statement processing, any duplicates exist in the primary key, the UPDATE operation is rolled back. Because the failure of the UPDATE statement does not affect the DELETE statement (both operations are atomic), you can let the program proceed and perform the INSERT. Alternatively, you could COMMIT the successful DELETE or ROLLBACK the LUW.
For a further discussion of atomic operations, see Backouts Initiated by Application Programs.
This process reverses the changes made by a logical unit of work (LUW) that ends abnormally. It is performed while the system is online and processing other applications. It is supported for the following:
The DB2 Server for VSE dynamic application backout facilities are also coordinated with the Dynamic Transaction Backout facilities of the CICS subsystem. A backout initiated by the DB2 Server for VSE database manager initiates a CICS transaction backout for the affected transaction. Similarly, a transaction backout initiated by the CICS subsystem initiates a DB2 Server for VSE backout, if the transaction was doing any SQL processing.
If the system or the database manager ends abnormally, this process reverses any database changes made by applications that were in progress within an LUW at the time of the failure. It also ensures commitment of all changes made by those applications that completed successfully.
To take advantage of the DB2 Server for VSE & VM recovery support, applications should be designed so that all SQL requests that constitute one logical change to the database are properly grouped into logical units of work (LUWs). For example, if an application transfers funds from one account to another, which entails an update to two different rows in the database, the updates should be done in the same LUW. Thus, if the application should fail, the database would be left in one of two consistent states: either the transfer was done completely (both rows updated), or it was not done at all (neither row updated). If the updates were in different LUWs, an application failure could result in only half of the transfer being performed (only one row updated).
Designing an application properly requires an understanding of when an LUW begins and ends. When it ends, the changes made within the LUW are either committed to the database, or backed out. Figure 24 shows the general rules for DB2 Server for VSE LUWs. Table 24 shows the general rules for VM users and Table 25 shows the general rules for VSE guest users accessing an application server on a VM/ESA system. There are, however, variations and special considerations that depend on the application environment and application implementation techniques. These variations are discussed in the following sections.
Figure 24. General Rules for DB2 Server for VSE Logical Units of Work
| View figure. |
Notes to Figure 24:
Table 24. General Rules for DB2 Server for VM Logical Units of Work
| LOGICAL UNIT OF WORK | All Programs Under CMS | ISQL Sessions | DBS Utility | Pre- processors | ||
|---|---|---|---|---|---|---|
| AUTOCOMMIT ON | AUTOCOMMIT OFF | AUTOCOMMIT ON | AUTOCOMMIT OFF | |||
| BEGINS | First SQL statement | Each SQL statement entry | First SQL statement | Command entry | First command | Start of CMS command |
| ENDS COMMITTED |
COMMIT WORK Normal end of CMS command | After successful SQL statement processing, COMMIT WORK for multi-row updates | COMMIT WORK | After successful command processing |
COMMIT WORK Normal end of CMS command |
Normal end of CMS command Implicit rollback SQLHX |
| ENDS BACKED OUT |
ROLLBACK WORK Abnormal end of CMS command Implicit rollback SQLHX Statement error |
CANCEL Statement error ROLLBACK WORK Implicit rollback |
ROLLBACK WORK Abnormal end CANCEL Implicit rollback |
Abnormal end Command error SQLHX Implicit rollback |
Command error ROLLBACK WORK Abnormal end of CMS command Implicit rollback SQLHX | Abnormal end of CMS command |
Notes to Table 24:
Table 25. General Rules for DB2 Server for VM Logical Units of Work from VSE Guests
| LOGICAL UNIT OF WORK | PROGRAMS | Preprocessor Jobs | |
|---|---|---|---|
| CICS | Batch/ICCF | ||
| BEGINS | First SQL Statement | First SQL Statement | Start of job step |
| ENDS COMMITTED |
COMMIT WORK Normal end |
COMMIT WORK SYNCPOINT Normal end | Normal end of job step |
| ENDS BACKED OUT |
ROLLBACK WORK Abnormal end Implicit rollback Statement error |
ROLLBACK WORK SYNCPOINT ROLLBACK Abnormal end Implicit rollback | Abnormal end of job step |
An application is considered to have ended normally when it returns to CMS. In single user mode, an application ends normally when it returns to the DB2 Server for VM calling routine. All other types of termination (such as HX, CMS abend, program check, or any user machine termination) are considered abnormal.
| Note: | In single user mode, an application's Register 15 return code protocol is not part of the definition of termination, and is not used by the application server to determine whether it should proceed with normal or abnormal termination processing. The application server establishes a CMS ABNEXIT exit in the database machine. The exit attempts recovery and dumps important diagnostic information when the recovery attempt is not successful. If a single user mode application establishes an abnormal end exit (for example, by way of ABNEXIT, STAE, SPIE, STXIT), the DB2 Server for VM abend exit is overridden. |
Some compilers provide a mechanism that handles program interrupts during the execution of a program and before control returns to CMS. Consequently, the application server may not be aware that the program termination is abnormal, and will perform an implicit COMMIT rather than an implicit ROLLBACK. See the DB2 Server for VSE & VM Application Programming manual for more information about program interrupts.
Users should be aware of how CMS handles multiple abnormal end exits, and should clear any that have been set by the application program before returning to the DB2 Server for VM application server, or else unpredictable results may occur when later CMS commands are issued. Also, the user should reset the abnormal exit before returning to the CMS abnormal termination routine after handling an abnormal end condition.
When users switch from one program to another, the SQLRMEND EXEC enables application programs to free the storage used by the resource adapter code. This EXEC can also be used to perform COMMIT/ROLLBACK processing on outstanding work before running the next program.
For more information, see SQLRMEND EXEC.
If a batch application executing in multiple user mode ends without freeing its link to the DB2 Server for VSE & VM application server, the operating system informs the application server whether the application ended normally or abnormally. The indication is normal if the application ends with the EOJ macro and the high-order bit of general purpose register 15 is set to 0. Other conditions indicate an abnormal end. The database manager automatically commits updates if the termination is normal, or does a rollback if it is abnormal.
The DB2 Server for VSE application server establishes an STXIT AB exit in the database partition. The exit attempts recovery and dumps important diagnostic information if the recovery attempt is not successful. If an application is running with the TRAP(ON) run-time option of LE/VSE and it did not issue an STXIT AB MACRO, LE/VSE and DB2 Server for VSE will keep track of calls to and returns from DB2 Server for VSE. If an abend occurrs while the application is running, the LE/VSE condition manager is informed whether the problem occurred in the application or in DB2 Server for VSE. If the abend occurs in DB2 Server for VSE, the LE/VSE condition handler passes the condition back to DB2 Server for VSE. For information on condition handling with LE/VSE see the DB2 Server for VSE & VM Application Programming manual. Furthermore, if a single user mode application issues an STXIT AB macro, the DB2 Server for VSE abend exit is overridden.
Similarly, if the application issues an STXIT PC, then the DB2 Server for VSE abend exit is overridden for program check conditions. Other abend conditions are still processed by the application server.
DB2 Server for VSE & VM recovery from failures of online (CICS) transaction is coordinated with CICS recovery processing.
Consistency among multiple application servers is ensured at CICS synchronization points, when related data across multiple application servers is kept in a consistent state.
Synchronization points (syncpoints) are points, during the processing of a transaction, at which updates or modifications to the transaction's resources are logically complete and error-free. To take advantage of the CICS syncpoints, the database manager online support runs as a CICS resource adapter, using the CICS Application Program interface and User Exit interface. For more information, refer to the CICS/VSE Customization Guide.
Syncpoints occur during the execution of an application under any of the following circumstances:
Additionally, when the DB2 Server for VSE online support detects an internal ROLLBACK of a unit of work, it issues CICS SYNCPOINT (ROLLBACK). (Such an internal rollback could happen, for example, if the system operator entered the FORCE command to ROLLBACK an LUW).
As a performance note, it is more efficient for applications to use a CICS syncpoint. The SQL COMMIT or ROLLBACK calls are less efficient, because they result in a longer path. A CICS syncpoint is also easier to understand : when it is time to commit, the application program calls the global synchronization function (CICS SYNCPOINT [ROLLBACK]).
The assumptions are that individual application programs do not plan to do their own recovery, and that updates are not to be committed unless normal termination occurs or the application program explicitly requests a commit.
Notes:
An installation must explicitly request the CICS subsystem to start the syncpoint protocol by:
If an ISQL session ends abnormally, the database manager attempts to notify the user about the abnormal condition, and leaves the database in a consistent state. In VM, the database manager issues a ROLLBACK WORK and the session ends. Control returns to CMS. In VSE or in a VSE Guest Sharing environment, the CICS syncpoint manager issues a ROLLBACK WORK.
All CICS temporary storage for routines is deleted, and both the ISQL transaction and the CISQ transaction are terminated, if possible. If the CICS syncpoint manager is in control when the CISQ transaction abnormal termination occurs, the ISQL transaction abends with the abend code GCBE.
For more information on GCBE, see the DB2 Server for VSE Messages and Codes manual.
If the DBS Utility fails to complete the processing of all commands supplied in the command input, or if it terminates with a return code equal to or greater than 8, then before the Utility can be restarted the DBS message file listing must be analyzed to determine the commands that were processed and the error that occurred. If there are no error messages here that describe the reason for the failure, then the database machine console messages must be analyzed. After the error has been corrected, restart the Utility as described below:
just restart the Utility.
If the Utility ends with a return code of 4, this means that all the commands supplied in the command input were processed successfully but a DBS program termination error occurred. The Utility does not need to be rerun.
For full descriptions of DBS Utility return codes and error processing, see the DB2 Server for VSE & VM Database Services Utility manual.
If the preprocessor fails to complete the processing of all source statements supplied as input, or if it terminates with a return code equal to or greater than 8, then before running it again you must analyze the source statement listing produced to determine the errors that occurred. If there are no error messages there that describe the error condition(s), look at the console messages. After all source statements and any other errors are corrected, rerun the preprocessor from the beginning.
If the preprocessor ends with a return code of 1 while the program is being preprocessed with the BLOCK option, this means that one or more SQL statements are disqualified for blocking. For further information on blocking, refer to the DB2 Server for VSE & VM Performance Tuning Handbook manual.
If it ends with a return code of 4, then one or more preprocessor warning messages are contained in the source statement listing. The preprocessor does not have to be rerun; however, the source statement listing should be checked to insure that the warning conditions involve objects known to be nonexistent at the time the preprocessor was run.
If it ends with a return code of 0 and no package was created, then the source statements read by the preprocessor contained no SQL statements. Here, the preprocessor must be rerun if the incorrect input source statements were supplied as input.
User logic errors are those where the application server carries out the functions as requested, but the user (or program) determines that the change(s) requested should not have been made -- for example, the wrong table or dbspace may have been dropped.
Recovery from a user logic error depends on when the error is detected. If it is detected before the changes have been committed, the application server supports user (or program) invoked dynamic application backout. A user or program can take certain actions to back out these changes, depending on the way in which the application server is being used.
ISQL users accomplish this by issuing either the SQL ROLLBACK WORK statement or the ISQL CANCEL command, or by responding to ISQL prompts for CANCEL or ROLLBACK. The error handling logic in application programs can accomplish this by issuing a ROLLBACK WORK statement. In addition, in VM the invoker of the program can enter either the HX or SQLHX immediate command (HX causes a rollback and ends the CMS command; SQLHX causes a rollback, but does not end the CMS command.) If you have coded your own interactive program to process SQL statements dynamically, you can also code a cancel exit. This would allow a user of your program to perform a function similar to the ISQL CANCEL command.
For more information on cancel exits, refer to the DB2 Server for VM System Administration or DB2 Server for VSE System Administration manual. For user errors that are detected after changes have been committed, the user has three choices:
This involves issuing the INSERTs, PUTs, UPDATEs, and DELETEs necessary to cancel all changes. If the committed changes involved definitional change statements (CREATE, DROP, or ALTER), these too must be manually backed out, which can be quite a chore since definitional statements do not always have straightforward cancellation operations. For example, a DROP TABLE statement would have dropped views and authorizations along with the table; thus, to reverse its effects would include re-creating the views and regranting the authorizations.
If a back-up copy of the data exists, it may be simplest to just revert to this version and then reenter any valid changes made to the data since the copy was made. Reentering the valid changes can, of course, be as involved as the effort to back out invalid ones; however, it has the advantage in that it can be done by reexecuting applications.
The DBS Utility UNLOAD facilities can be used to create back-up copies of data, and the RELOAD facilities can be used to reset data to a previous state. The DB2 Server for VSE & VM database archiving support can also be used to create back-up copies of the entire database and reset it.
Filtered log recovery lets you rollback a committed logical unit of work (LUW). It sounds like an easy solution, but it must be exercised with extreme care. When you undo past errors, other database changes may be altered as well: rows that users thought they had deleted may unexpectedly reappear; the values in updated rows may change.
If you are using referential integrity, then on completion of the filtered log recovery you should deactivate and activate your primary and foreign keys to have the database manager automatically recheck the referential constraints. See Altering Referential and Unique Constraints.
Filtered log recovery can be used to bypass the operations recorded in the log. The smallest set of operations you can bypass is all the work done in a single LUW. You tell the application server which logical units of work to bypass by supplying EXTEND input file commands. Because you want to bypass work that has already been committed, you would use the ROLLBACK COMMITTED WORK command. All the EXTEND input file commands are described in the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
To dynamically recover from user errors, users should take advantage of the facilities that are provided for detecting error situations and for backing out changes that should not have been committed.
When using ISQL, there will be times when you will want to backout an invalid action: for example, if AUTOCOMMIT is OFF and you entered an SQL statement that resulted in a negative SQLCODE, or changes to a table that proved to be incorrect.
| Note: | You cannot backout changes in a nonrecoverable storage pool. |
If you detect an error before a change is committed, you can backout the change. How many changes you can backout depends on whether AUTOCOMMIT is ON or OFF.
If it is ON, every statement is its own logical unit of work (LUW), and ISQL will immediately issue a COMMIT WORK after processing the statement. The only exception is for INSERT, UPDATE, and DELETE statements that affect more than one row: in that case, ISQL displays a message that gives you the option of backing out. For all other statements, you can backout the changes before the statement completes its processing, by:
When using the INPUT command, you can enter the BACKOUT command after an invalid data row is entered. This deletes all data rows entered since INPUT was issued, or since the last SAVE command was entered.
If AUTOCOMMIT is OFF, you have control over what is an LUW and when changes are to be made. When you backout a change, this undoes all changes made since the beginning of the LUW. You can backout a change by any of these methods:
| Note: | In VM, when you enter a CANCEL command, ISQL does ROLLBACK WORK RELEASE processing. Any explicit connection you have made will be released. You should reissue the CONNECT statement if you want to explicitly connect to ISQL again. |
An application program may begin a backout if the application server shows that there is an error, or if the program detects something wrong internally. To detect and handle errors, the program should have the WHENEVER statement coded into it.
It can then determine whether to continue or to stop execution when an error occurs.
All operations against recoverable storage pools are atomic, except in SUM NOLOG mode. That is, either the operation will be completed successfully, or any changes made by the operation will be reversed automatically. Changes made by previous operations in the same LUW are not affected. The application is free to either continue working within the same LUW, to COMMIT the changes made so far, or to ROLLBACK the LUW. Some errors, such as deadlock, still require the entire LUW to be rolled back. The status of the LUW is indicated in SQLWARN6 in the SQLCA.
When running with LOGMODE=N, atomicity of operations is enforced by rolling back the current LUW to avoid partial completion of an operation. For operations on data in nonrecoverable storage pools, there is no support for atomicity of operations.
| Note: | When blocking, the database manager does not insert rows into the database until the block is full and it does not notify your program of an insert error until the PUT that fills a block is run. To determine when (or if) rows are actually inserted into the database, your program should examine SQLERRD(3) in the SQLCA when doing PUTs. |
To rollback work when an SQL error is encountered, code a ROLLBACK WORK statement in the program, and use a WHENEVER SQLERROR GO TO statement to cause a branch to the ROLLBACK statement when there is an SQL error. After the program issues a ROLLBACK WORK, it may continue processing more SQL statements without the previous error affecting their outcome.
If the application programmers do not wish to worry about setting up error-recovery logic in their programs, they can enable them to stop executing when an SQL error is detected. This is done by coding WHENEVER SQLERROR STOP (COBOL, COBOL II, PL/I) or WHENEVER SQLERROR GOTO. When this is coded, the database manager will issue either a CANCEL (in VSE) or a CMS DMSABN macro (in VM) for the application when any command results in a negative SQLCODE, which results in a ROLLBACK WORK for any outstanding LUW within the application program. Alternatively, the application programmer could code a WHENEVER SQLERROR GOTO and branch to a label or routine to perform the ROLLBACK WORK and end the program.
If the program detects an internal error and wishes to discontinue processing, it is probably best to issue a ROLLBACK WORK (if possible) before terminating it. This can be done by coding a ROLLBACK WORK statement in the application and branching to it when an internal program error is detected. After the ROLLBACK WORK statement is run, the program can stop, or continue if desired.
In VM, once a program is running, you can stop it by using the immediate commands HX or SQLHX, both of which cause a ROLLBACK WORK for the current LUW. You might want to do this if, for example, you start the program and then realize you have provided the wrong inputs. The difference between the two commands is that HX causes an end to the CMS command, while SQLHX does not. Thus, the choice of command is a matter of convenience. For example, issuing HX from ISQL both rolls back the current LUW and ends the ISQL session, so the user must reinitialize ISQL to continue processing; issuing SQLHX causes the LUW to be rolled back but the ISQL session continues.
| Note: | The ISQL CANCEL command and the more general SQLHX command have equivalent functions. The CANCEL command, however, does not work for user programs. In addition, CANCEL, SQLHX, and HX do not work if you have processed the SQLINIT command with the SYNCHRONOUS(YES) option. |
It is a good idea to maintain backup copies of specific tables or dbspaces, so that they can be reset in case of major errors.
Individual tables or entire dbspaces should be periodically unloaded to either a SAM tape or DASD file (in VSE), or to a tape or CMS minidisk file (in VM) with the DBS Utility UNLOAD command.
Multiple UNLOAD commands can be put in a single DBS SYSIPT (VSE), or SYSIN (VM), input file. You might establish one such job stream for periodic back-up of users' PRIVATE dbspaces, and others for periodic back-up of selected application production data. Different types of data would typically have different back-up schedules. For example, production data would probably be backed up more frequently than query user data. Some DB2 Server for VSE data, such as certain data extracted from DL/I, would not require back-up; that is, the DL/I copy of the data is sufficient back-up.
| Note: | You cannot use the DBS Utility UNLOAD facilities to back up data in the
system dbspaces (SYS000n).
The catalog tables and packages cannot be reset by DBS RELOAD processing. |
When data is backed up, you can recall the backup copy if necessary. Data that was backed up with the UNLOAD TABLE command is recalled with the RELOAD TABLE command; data that was backed up with the UNLOAD DBSPACE command can be recalled with either RELOAD DBSPACE (to reset the entire dbspace) or with RELOAD TABLE (to recall selected tables in the dbspace). Often, user data errors that have been introduced into the database are isolated to just a few tables; thus, even if the data had been unloaded with an UNLOAD DBSPACE command, you would use RELOAD TABLE to reset it.
When a table is RELOADed with the NEW option, a new table is created and data reloaded. None of the primary keys, indexes, unique constraints, referential constraints or field procedures are reproduced in the new table.
When you use the RELOAD command with the PURGE option to replace the contents of a table, the DBS Utility does the following to the table being replaced:
As a result, the CLUSTERING index will be preserved, as well as the primary key, foreign keys, unique constraints, and indexes existing on the table at the time of the RELOAD/PURGE command. If no CLUSTERING index exists, then the primary key becomes the CLUSTERING index. There is no requirement to order the reloading of tables, because all referential constraints are inactive while the data is inserted.
Consider running the DBS Utility in single user mode with LOGMODE=N when resetting data through RELOAD DBSPACE or RELOAD TABLE processing. This will eliminate any log overflow conditions that result from the table row deletes and inserts performed by RELOAD processing with the PURGE option. If you use log archiving, however, remember that switching the log mode disrupts the continuity of the log.
Running the DBS Utility with LOGMODE=N is shown in Figure 25 and Figure 26. If the data resides in a nonrecoverable storage pool, there is no need to use LOGMODE=N, because logging is automatically suppressed for nonrecoverable data.
Figure 25. Resetting a DB2 Server for VSE DBSPACE from a Back-up Copy
// JOB RESTORE DBSPACE // EXEC PROC=DBNAME01 // EXEC PROC=ARIS71PL // TLBL DUMPTAP,......... // ASSGN SYS004,......... // EXEC ARISQLDS,SIZE=AUTO,PARM='STARTUP=L,SYSMODE=S,LOGMODE=N,DUALLOG=Y' // EXEC ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,LOGMODE=N,PROGNAME=ARIDBS' CONNECT SQLDBA IDENTIFIED BY SQLDBAPW; RELOAD DBSPACE (SQLDBA.EXAMPLE) PURGE INFILE(DUMPTAP PDEV(TAPE) /& |
Notes:
For further information about switching log modes, see the DB2 Server for VSE System Administration manual.
Figure 26. Resetting a DB2 Server for VM Dbspace from a Back-up Copy
EXEC SQLLOG DB(dbname) FILEDEF DUMPTAP TAPn (RECFM VBS BLOCK 800 EXEC SQLDBSU DB(dbname) IN(TERM) LOGMODE(N) CONNECT SQLDBA IDENTIFIED BY SQLDBAPW; RELOAD DBSPACE (SQLDBA.EXAMPLE) PURGE INFILE(DUMPTAP) COMMIT WORK RELEASE; |
Notes:
To protect the entire database from user logic errors, use the archiving and COLDLOG facilities of the database manager. These facilities are required to protect the system catalog tables and the package dbspaces. Backup copies of the system dbspaces (SYS000n) made by DBS Utility UNLOAD command cannot be used to reset catalog tables or packages to a previous state.
The back-up copy of the database can be either a database archive or a database archive and subsequent log archives.
You can create the database archive by using a variety of facilities. You must, however, create the archive when no user is accessing the database. Create the archive by using either the SQLEND ARCHIVE or SQLEND UARCHIVE command. Because no user is accessing the database when the database archive is taken, no incomplete changes are recorded in the database archive.
If you use log archiving, you can think of the last back-up copy as being the last database archive plus all subsequent log archives. Log archives do not record changes by incomplete logical units of work.
You can create a proper back-up copy even if you have been running the database manager with LOGMODE=Y. However, if you create a database archive by using SQLEND parameters when LOGMODE=Y, you must follow the steps outlined in the DB2 Server for VM System Administration or DB2 Server for VSE System Administration manual to restart the database manager with LOGMODE=Y, because the log mode will automatically change to A when taking the database archive.
If you are restoring from a database archive without using subsequent log archives, you can reset the database to any previous database archive copy, not just the latest one.
To reset a DB2 Server for VSE database to a previous copy generated by an SQLEND command, run COLDLOG before restoring the database from the archive copy. This reformats the log so that changes since the archive was taken are not applied again.
To reset a DB2 Server for VM database, run the SQLLOG EXEC (omitting the LOG1 and LOG2 parameters) to reformat the log with a COLDLOG. Respond "NO" to message ARI0688D (for single logging) or ARI6129D (for dual logging). When you respond NO, the database manager reformats the log such that changes since the archive was taken are not applied again.
If you are restoring from a database archive and subsequent log archives, no COLDLOG is required. When the database is restored, the logs are restored in sequence. You are prompted to continue the log restore before processing each log archive. You can end the restore process at any log archive by responding "END RESTORE" to the appropriate prompt.
When resetting the database to a back-level copy, even if you are using subsequent log archives, you should be aware of the following:
Any dbextents added to the database (by an ADD DBEXTENT operation) after the database archive was created do not exist in the archive copy of the database. In VM, the CP LINK and CMS FILEDEF commands for these dbextents are present in the resid SQLFDEF file (on the DB2 Server for VM production minidisk) for the database. To redefine these dbextents in the DB2 Server for VM database, perform the following procedure:
| Note: | The SHOW DBEXTENT operator command tells you how many dbextents are defined in the database. |
FILEDEF SYSIN DISK fn ft fm
In VSE, do a COLDLOG by specifying STARTUP=L and the DUALLOG value that you want. For more information about DUALLOG, see the DB2 Server for VSE System Administration manual.
In VM, do a COLDLOG by running the SQLLOG EXEC without the LOG1 and LOG2 parameters. In this situation, the log minidisk is already reserved and formatted; only the directory needs to be updated. Respond "NO" to message ARI0688D (for single logging) or ARI6129D (for dual logging), which prompts you to FORMAT and RESERVE the log minidisk (or minidisks).
This final consideration applies when you restore a database archive without applying subsequent log archives:
A database restore (STARTUP=R) reformats the VSAM database data sets before the data is reloaded. Reformatting the data sets is necessary after a data set is replaced (for example, when restoring because of a media failure or database reconfiguration). Reformatting the data sets is not necessary when none of the database data sets is being replaced.
When restoring the database to a previous level to recover from a user logic error, you usually do not change the data sets. To save processing time, use STARTUP=F (fast restore) when you have not replaced any of the database data sets. The STARTUP=F processing does not format the VSAM data sets: it loads the data. Eliminating the formatting of the data sets significantly reduces the restore time.