You can define storage pools that are not recoverable. Changes made to user data in nonrecoverable storage pools are not logged, which eliminates much of the overhead required for recovery operations described earlier in this chapter. Recovery is the responsibility of the user.
For some applications, the benefit derived from the reduced overhead far outweighs the effort of having to do your own recovery. The applications that benefit the most are those that do massive updating of a specific set of tables in the database. Such applications include:
If normal recovery procedures were in place, these applications would generate many log records. These not only cause processing overhead, but require a larger log, because the log must be large enough to hold all the records generated during the long-running LUW (along with the records of all other concurrent LUWs). Further, if you use archiving, the increased log activity causes more frequent archives.
For applications that cause excessive logging or archiving, you have two alternatives:
The first of these methods is usually preferable. For example, suppose you have an application that loads thousands of new records into an existing table. These records are the names and addresses of subscribers to a new monthly service that your company is offering. The data for new subscribers is loaded into the tables once a month. Between runs, users perform updates on the table using ISQL (for example, changing the address of an existing subscriber).
Now suppose you decide to run the application in single user mode with LOGMODE=N. The advantage is that after the application runs successfully and you create a database archive, the ISQL users have the benefit of full database recovery. The disadvantages are:
Consider, though, the alternative of placing the data in a nonrecoverable storage pool. By doing so, you avoid having to create the archives, and you can run the application in multiple user mode and so avoid interrupting other users. However, the data is nonrecoverable. The decision depends on whether your ISQL or DBSU utility users can work without recovery. If the answer is no, or if you are not certain you can foresee all possible recovery situations, use LOGMODE=N instead.
The following discussion provides the basis for you to determine whether it is feasible to store the data for a given application in a dbspace in a nonrecoverable storage pool, and what recovery procedures you will need for such data.
There is one situation where nonrecoverable and recoverable dbspaces have the same characteristics: when the database manager is running in single user mode with LOGMODE=N. In this situation, for both types of dbspaces, if there is a failure, all updates that were committed at the time of the failure are in the database; all those that were not committed are not. This applies to any ISQL or DBS utility command that updates the database. Note that commitment includes both an explicit COMMIT command and any implicit commitment (as described earlier in this chapter).
In any mode other than LOGMODE=N, the following characteristics apply to nonrecoverable dbspaces:
When you take a database archive, nonrecoverable dbspaces are archived the same way as the recoverable ones. Logging is performed differently, however, because changes to user data in nonrecoverable dbspaces are not logged.
Same as for recoverable dbspaces.
DB2 Server for VM preprocessors never update data in nonrecoverable dbspaces.
Not supported. For more information, see the discussion on the SQL statements that affect multiple rows on page (ATOM).
The database manager forces a checkpoint whenever there is an implicit or explicit COMMIT of a LUW that updated data in a nonrecoverable dbspace, to ensure that all updates in that LUW are really in the database. The checkpoint will only occur if data is modified, such as by an INSERT, UPDATE, or DELETE statement. It will not occur for LUWs that do not update data, or for data administration operations such as creating or dropping indexes or altering tables. These operations are logged and are thus recoverable.
Thus, except when restoring from an archive (see below), a user can be sure that committed updates are in the database, and will survive a system failure or an application failure. They do not, however, survive a DASD failure unless you archive the database after the updates are made.
| Note: | Checkpoints cause significant system overhead and increase response time for interactive users. Thus, avoid a high frequency of LUWs that update data in nonrecoverable dbspaces. Also, a checkpoint that occurs during a database or log archive causes the database manager to end all concurrent activity until the archive is completed, so users must wait. Plan your updates to nonrecoverable dbspaces so that they do not coincide with an archive operation. |
When an LUW is rolled back (either implicitly or explicitly), the database manager does not undo successful SQL INSERT, PUT, DELETE, and UPDATE statements. Instead, it forces a checkpoint (after it rolls back any changes made to recoverable data during that LUW). This means that the nonrecoverable data appears just as though the LUW had been committed at the point when the rollback occurred.
If you want to return the data to the state it was in before the LUW, you must undo the INSERTs, PUTs, DELETEs, and UPDATEs manually. Until you do, other users can see the uncommitted updates.
The database manager does a checkpoint to ensure that you know what changes were made (so that you can undo them). If the checkpoint was not done, and the database manager failed before the next checkpoint, it would be difficult to tell what changes (if any) were made to the database. The checkpoint is done to make it easier for you to undo the changes.
There are two situations where the database manager does not force a checkpoint for rollbacks of LUWs that update nonrecoverable data:
The database manager uses the log to determine the LUWs that were in progress at the time of the failure. These LUWs are normally rolled back. Changes to nonrecoverable data are not rolled back, because they were never recorded in the log in the first place.
There is no forced checkpoint because when the system fails, all changes made since the last checkpoint are lost. (They are not in the database.) For nonrecoverable data, in this situation, there is nothing to record at a checkpoint. For more information, see the discussion on recovering from processing failures, below.
Here again, the updates are not in the log, so there is nothing to record at a checkpoint. In fact, all changes to nonrecoverable data made after the archive are lost. For more information, see the discussion on restoring from an archive, below.
The following consideration applies only if you have VSE guest sharing:
Usually the EXEC CICS ROLLBACK rolls back updates made to multiple resources, but the CICS transactions that use the two-phase syncpoint (TPSP) protocol cannot rely on this when nonrecoverable data is involved. You must make other provisions for such transactions.
Logical units of work that are in-process when a system failure occurs lose the automatic rollback that normally is done the next time the application server is started. In this situation, the state of these updates depends on when the last checkpoint occurred before the failure. Updates that were completed before the checkpoint occurred are in the database; those done after the checkpoint are not. You must undo only the updates made by an in-process LUW that occurred before the last checkpoint. This procedure resets the data to its state before the LUW that was interrupted by the system failure.
This process applies only to nonrecoverable data. If you are also updating recoverable data in that same LUW, the normal recovery rules apply for that data.
If you are restoring the database from an archive copy, all data updates to nonrecoverable dbspaces done after that archive was taken are lost. You must redo all updates since the archive to bring those dbspaces to the current level.
Because row updates (INSERT, PUT, DELETE, UPDATE) are not recorded in the log, the filtered log recovery ROLLBACK COMMITTED WORK command does not apply. It does apply, however, for recoverable SQL statements and for the DBS utility command REORGANIZE INDEX (see below), because they are logged. For information about filtered log recovery, see the discussion on starting the application server to recover from a DBSS error in the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
The following SQL statements are always recoverable, even if they involve nonrecoverable dbspaces:
The DBS utility command REORGANIZE INDEX is also recoverable.
The reason these are recoverable is that the database manager does not suppress logging for them. They are logged to ensure the integrity of the database catalog tables, which always refer only to objects that exist.
If an LUW fails to commit (implicitly or explicitly) after successfully doing any of the above statements or the command, the recovery procedures will automatically undo the statement or command. For example, suppose the following actions are in a LUW:
If this LUW fails to be committed, the table, all its rows, and its indexes are automatically dropped from the database. Because the above statements are logged, if an LUW is committed after successfully processing the statements, they can be restored from the archive.
Except for long strings, the problem never occurs of a single row being only partially updated (inserted, deleted, or modified). The database manager always ensures that either all processing for updating a row is in the database, or that none is. (You can get partial row updates for long strings because more than one update is needed internally for each row update you request.)
An SQL statement that causes multiple rows to be inserted, deleted, or updated can fail between row modifications, due to an error condition or a system failure. Whatever the cause, because the dbspace is nonrecoverable, some of the rows are modified in the database, and some are not.
When you are considering placing application data in a nonrecoverable storage pool, you must determine whether the user will be able to recover it in a reasonable and relatively simple manner. If so, then the table is a candidate for a nonrecoverable storage pool.
Some examples of such data follow, along with descriptions of how to recover it, based on the rules in the previous section.
Some applications use data that is retrieved from a source outside the database, such as data from another DB2 Server for VM or non-DB2 Server for VM database, or data from sequential files (including CMS files and virtual reader files). Such tables are candidates for nonrecoverable storage pools if the following are true:
If the application that loads the data into the database tables fails (does not COMMIT) for any reason, you can recover in either of the following ways:
If, after successfully loading the data, you restore the database from a database archive that was created before the data was loaded, the rows you loaded no longer exist in the database. You can recover as follows:
These steps are not necessary because the database manager always records DROP and CREATE table statements in the log, even for nonrecoverable dbspaces.
You must redo the data manipulation statements (in this situation, INSERT and PUT) because they are not recorded in the log. (The restore defined the tables in the database, but did not insert any data.)
These recovery rules apply only to data that is imported and loaded once and is discarded when no longer needed. Each time the data is loaded, it completely replaces the previous version.
The key point is that the source data must exist so that it can be used to recover the read-only database version.
Data that is retrieved from an outside source and added to existing data can also be stored in a nonrecoverable dbspace.
The data can be from any of the sources described in Example 1 above. To add it to an existing table, you could use the DBS utility DATALOAD command or an application program to perform a mass INSERT operation.
You can recover the data if each batch of added rows has a unique value in a column that identifies rows of the batch. You would need an application program that generates a unique batch identifier and places it into each record (or into each row, if the application loads the rows into a table).
If the application that loads the data fails (does not commit the work) for any reason, you can recover as follows:
| Note: | Although it is tempting to commit work frequently during loading to avoid potential recovery problems, keep in mind that the commit operations cause checkpoints, which can adversely affect overall performance. |
If you restore the database using a database archive that was created before one or more of the load operations, all rows loaded since that archive no longer exist in the database.
To recover those lost rows, either:
Alternatively:
Both methods of recovery assume that the loaded data still exists somewhere outside the database, and that each batch of rows has a unique identifier.
Read-only data that is created by one or more INSERT via subselect statements can also be stored in a nonrecoverable dbspace. For recovery to be possible, the data must be inserted into empty tables.
If the loading of the table fails to be committed, you can recover the data as follows:
If you restore the database from a database archive that was created before the data was loaded, the data that was loaded is not in the database. The table is not dropped, however, even if it was created after the archive, because CREATE TABLE statements are always logged. To restore the data that was eliminated by the database restore operation:
Avoid loading (or otherwise updating) nonrecoverable dbspaces if an online database archive could occur at the same time, because such archives typically contain changes made by incomplete LUWs. For recoverable data, this is not a problem because the log contains the rest of the changes, so when you do a restore, the archive and the log are used together to reconstruct a consistent copy of the database. For nonrecoverable data however, changes are not recorded in the log, so data can be incomplete or inconsistent because no log records are available to complete the restoration of the database.
You should also not update nonrecoverable data when an online log archive can occur, because the database manager waits until all LUWs end before creating the log archive. Because LUWs that update nonrecoverable data are usually long-running, the log archive is forced to wait. If the log fills to the SLOGCUSH point, log overflow processing will be started: this involves rolling back the longest-running LUW, which is usually the one that is updating nonrecoverable data. (For a description of the SLOGCUSH parameter, see SLOGCUSH.)
Any data that would be difficult or impossible for a user to recover should not be put in nonrecoverable dbspaces. Some examples are:
This includes data whose source is destroyed after the data is loaded, and data that is manually entered into tables (with the ISQL INPUT command, for example).
If the owner of the table keeps an audit trail of the updates made, you can put this kind of data in a nonrecoverable dbspace, and have the owner use the audit trail to do recovery. However, this is practical only if the number of updates made is small.
Here, recovery is not a problem. Rather, there is just not enough logging done for the data to justify the added complexity of user recovery. Let the database manager do the logging and recovery.
Here again, there is not enough logging to justify user recovery.
If you want the data for a particular application to reside in a nonrecoverable storage pool, do the following:
Attention: Once a storage pool is defined, either by adding dbextents to it or by POOL(NOLOG), you must not change it from recoverable to nonrecoverable, or the reverse.
Remember to perform your recovery procedures whenever there is a LUW failure or when you must restore the database from an archive.
To determine whether a storage pool is nonrecoverable, issue the SHOW DBEXTENT operator command. The POOL NO. column shows the number of the pool. If it is positive, the storage pool is recoverable; if negative, it is nonrecoverable. For example, if the number displayed is -32, storage pool 32 is nonrecoverable; if it is 32, this storage pool is recoverable.
To determine what dbspaces are in nonrecoverable storage pools, look at the POOL column in the SYSTEM.SYSDBSPACES catalog table. If this value is positive, the pool where the dbspace is assigned is recoverable; if it is negative, the pool is nonrecoverable. Again, the absolute value of the number is the storage pool number.
Following are some sample queries you can use to determine the status of nonrecoverable storage pools and dbspaces:
SELECT DISTINCT POOL -
FROM SYSTEM.SYSDBSPACES -
WHERE POOL > 999
Because the data type of the POOL column is DBAHW, you specify POOL > 999 instead of POOL < 0 to retrieve the nonrecoverable (that is, negative) storage pools. The DBAHW fields do not sort the same way that SMALLINT fields do. (See the DB2 Server for VSE & VM SQL Reference manual for description of data types.)
SELECT NPAGES FROM SYSTEM.SYSDBSPACES -
WHERE DBSPACETYPE=1 AND POOL=-7 AND OWNER=' '
The blank OWNER column indicates that the dbspace is not yet acquired.
To find the same information for private dbspaces, change the DBSPACETYPE value in the statement from 1 to 2.
Next, issue SHOW DBEXTENT to determine the number of storage pools that are in use. Storage pools are in use only if dbextents are assigned to them. The difference between this number and MAXPOOLS is the number of pools that remain to be defined. You can define storage pools by adding extents to new pool numbers until you reach the MAXPOOLS limit.
SELECT DBSPACENO FROM SYSTEM.SYSCATALOG -
WHERE TNAME=table_name AND CREATOR=userid
If the DBSPACENO value is 0, the table is actually a view, and you have to query the SYSTEM.SYSVIEWS catalog table to obtain the name of the underlying table. If the DBSPACENO value is not 0, use the value in this SELECT statement:
SELECT POOL FROM SYSTEM.SYSDBSPACES WHERE DBSPACENO=n
If the returned POOL value is negative, the dbspace is nonrecoverable; if it is positive, the dbspace is recoverable.