Administration Guide

DB2 File Manager Considerations

The following sections provide information that applies if you are using the backup, restore, and rollforward utilities, and you have tables that contain DATALINK columns. For a full description of DATALINK columns, refer to the CREATE TABLE statement in the SQL Reference.

Backup Utility Considerations

DB2 ensures that by the time the backup utility completes, linked files at DB2 File Manager sites are also backed up. (The backup utility can run either online or offline, and the backup image can be of either a database or a table space.) The description that follows only applies to files that are linked by DATALINK columns that have the RECOVERY parameter set to YES. (Files that are referenced by DATALINK columns for which RECOVERY=NO is specified are not backed up.)

When files are linked, the DB2 File Managers schedule them to be copied asynchronously to an archive server such as ADSM, or to disk. When the backup utility runs, DB2 ensures that all files scheduled for copying have been copied. At the beginning of backup processing, DB2 also ensures that all DB2 File Managers that are specified in the DB2 configuration file are running. If a DB2 File Manager has one or more linked files, it must be available until the backup operation completes. If a DB2 File Manager becomes unavailable before the backup operation completes, the backup operation is declared as incomplete.

A successful backup operation can cause the DB2 File Manager to clean up the archived versions of files on the archive server (either disk or ADSM). When a file is unlinked, it is either deleted or returned to its previous permissions, depending on the value specified for the ON UNLINK parameter. The dl_num_backup database configuration parameter specifies the number of DB2 backups (either database or table space) before archived versions of the files are removed. For more information about this configuration parameter, see "DataLink Number of Backups (dl_num_backup)".

When unlinked files are removed, the information about the unlinked files is also removed from the DB2 File Manager registration tables.

Restore and Rollforward Utility Considerations

The information that follows applies if you have a DATALINK column (or columns) that is defined with RECOVERY=YES option for a table. If a table has a DATALINK column defined with the RECOVERY=NO option, the table is put in the reconcile_pending state at the end of the restore operation. See "Reconciling DB2 File Manager" for more information.

During restore operations, tables with DATALINK columns may be put into one of the following states.

• reconcile_not_possible

  When a table is in the reconcile_not_possible state, it is available for unrestricted manipulative actions for columns other than the DATALINK columns. When a DATALINK column is involved in a SELECT statement, a warning is issued. You can issue UPDATE calls to DATALINK columns (with some restrictions: see "Removing a Table from the Reconcile_Not_Possible State" for details). You cannot issue INSERT and DELETE statements because they involve the DATALINK column.

• reconcile_pending

  When a table is in the reconcile_pending state, it is available for unrestricted manipulative actions for columns other than the DATALINK columns. When a DATALINK column is involved in a SELECT statement, a warning is issued. You cannot issue any DML statements such as UPDATE, INSERT, or DELETE.

These states are reported in the db2diag.log file when the restore or rollforward utilities run. You can also use the db2dart command to obtain this information.

When you restore a database or table space and do not specify the WITHOUT DATALINK option, the following conditions must be satisfied for the restore operation to succeed:

• All DB2 File Managers that are recorded in the backup file must be available.

• Information about all DATALINK columns that are recorded in the backup file must exist in the appropriate DB2 File Manager registration tables.

  If all the information about the DATALINK columns is not recorded in the registration tables, the table with the missing DATALINK column information is put into the reconcile_not_possible state after the restore operation (or the roll-forward operation, if used) completes.

  DB2 also checks whether the backup file that is provided is earlier than the value for dl_num_backup from the most recent backup. If the backup file is earlier, meaning that the archived files from this earlier backup have been removed and cannot be restored, all tables that have the DATALINK columns are also put into the reconcile_not_possible state.

  In both situations, the table is not put in the check pending state. Instead, it remains available to users, but the values in the DATALINK columns may not reference the files accurately (for example, a file may not be found that matches a value for the DATALINK column).

  If you do not want this behavior, you can put the table into the check pending state by issuing the SET CONSTRAINTS for tablename TO DATALINK RECONCILE PENDING command.

  If, after a restore operation, you have a table in the reconcile_not_possible state, you can fix the DATALINK column data in one of the ways suggested under "Removing a Table from the Reconcile_Not_Possible State".

Restoring Databases from an Offline Backup without Rolling Forward

If you use the restore utility with the WITHOUT DATALINK option, all tables with DATALINK columns are placed in the reconcile_pending state and no reconciliation is performed with the DB2 File Manager during the restore operation.

If you do not use the WITHOUT DATALINK option, and all the DB2 File Managers are available and all information about the DATALINK columns is fully recorded in the registration tables, the following occurs for each DB2 File Manager recorded in the backup file:

• All files that were linked after the backup image that was used for the database restore are marked as unlinked (because they are not recorded in the backup image as being linked).

• All files that were unlinked after the backup image, but were linked before the backup image was taken, are marked as linked (because they are recorded in the backup image as being linked). If the file was subsequently linked to another table in another database, the restored table is put into the reconcile_pending state.

Restoring Databases and Table Spaces and Rolling Forward to the End of the Logs

If you restore then roll forward the database or table space to the end of the logs (meaning that all logs are provided), a reconciliation check is not required (regardless of whether the WITHOUT DATALINK parameter is specified). If you are not sure whether all the logs were provided for the roll-forward operation, or think that you may need to reconcile DATALINK values:

1. Issue the SQL statement for the table (or tables) involved:

      SET CONSTRAINTS FOR tablename TO DATALINK RECONCILE PENDING
   

   This puts the table in reconcile_pending state and check-pending state.

2. If you do not want a table in the check-pending state, issue the following SQL statement:

      SET CONSTRAINTS FOR tablename IMMEDIATE CHECKED
   

   This takes the table out of the check-pending state, but leaves it in the reconcile_pending state. You must use the reconcile utility to take the table out of this state. For more information, see "Reconciling DB2 File Manager".

Restoring Databases and Table Spaces and Rolling Forward to a Point in Time

The point in time specified for a roll-forward operation can be a quiesce point. The quiesce point is the timestamp that is associated with a QUIESCE TABLESPACES FOR TABLE tablename command. You can find this timestamp in the database history file.

Note:

This quiesce point only applies to the table spaces that have been quiesced.

You do not need to perform a reconcile check for the tables in a table space that is rolled forward to a quiesce point, as a fast reconcile is automatically performed for all the tables in that table space. Tables in table spaces that are not rolled forward to a quiesce point, however, will be in the reconcile_pending state at the end of the roll-forward operation. You should use the reconcile utility to remove them from this state. For more information, see "Reconciling DB2 File Manager".

Point-in-Time Roll-Forward Example

Following is a simple scenario showing the files that need to be retained in order to handle backup and recovery. The example shows changes to the value of a single row in column of type DATALINK together with the files that the DB2 File Manager needs to retain to support recovery. For this example, the assumption is made that there is no support for point-in-time recovery of these files earlier than the last backup. DB2 File Manager does not have such a restriction. Observe that fileA exists until time 3, at which time it is deleted because it was unlinked at time 2, and the policy for the database in this example is to keep the unlinked files until the next backup is run (that is, the dl_num_backup database configuration parameter is set to 1).

Time	1	2	3	4	5	6	7
Activity	Create	Update	Backup	Update	Update	Delete	Restore to 5
Column Value	valueA	valueB	valueB	valueC	valueD	-	valueD
Linked File	fileA	fileB	fileB	fileC	fileD	-	fileD
Extra Files Kept by File Manager		fileA		fileB	fileB, fileC	fileB, fileC, fileD	fileB, fileC

Note:

Recovery of linked files is always done in conjunction with the rest of the database.

DB2 File Manager and Recovery Interactions

The following table shows the different types of recovery that you can perform, the DB2 File Manager processing that occurs during restore and roll-forward processing, and whether you need to run the Reconcile utility after the recovery is complete:

Type of Recovery	DB2 File Manager Processing during Restore	DB2 File Manager Processing during Rollforward	Reconcile
Non-recoverable database (logretain=NO)
Database restore	Fast reconcile is performed	N/A	Can be optionally run if problem with file links is suspected
Database restore using WITHOUT DATALINK option	Tables put in reconcile_pending state	N/A	Required
Recoverable database (logretain=YES)
Database restore using WITHOUT ROLLING FORWARD option	Fast reconcile is performed	N/A	Optional
Database restore using WITHOUT ROLLING FORWARD and WITHOUT DATALINK options	Tables put in reconcile_pending state	N/A	Required
Database restore and roll forward to end of logs	No action	No action	Optional
Database restore using WITHOUT DATALINK option and roll forward to end of logs	No action	No action	Optional
Table space restore and roll forward to end of logs	No action	No action	Optional
Table space restore using WITHOUT DATALINK option and roll forward to end of logs	No action	No action	Optional
Database restore and roll forward to a point in time	No action	Tables put in reconcile_pending state	Required
Database restore using WITHOUT DATALINK option and roll forward to a point in time	No action	Tables put in reconcile_pending state	Required
Table space restore and roll forward to a point in time	No action	Tables put in reconcile_pending state	Required
Table space restore using WITHOUT DATALINK option and roll forward to a point in time	No action	Tables put in reconcile_pending state	Required
Database restore and roll forward to a quiesce point	No action	Fast reconcile is performed on tables in quiesced table spaces only; other tables are put in reconcile_pending state	Optional for tables in quiesced table spaces; required for all other tables
Database restore using WITHOUT DATALINK option and roll forward to a quiesce point	No action	Fast reconcile is performed on tables in quiesced table spaces only; other tables are put in reconcile_pending state	Optional for tables in quiesced table spaces; required for all other tables
Table space restore and roll forward to a quiesce point	No action	Fast reconcile is performed on tables in quiesced table spaces only; other tables are put in reconcile_pending state	Optional for tables in quiesced table spaces; required for all other tables
Table space restore using WITHOUT DATALINK option and roll forward to a quiesce point	No action	Fast reconcile is performed on tables in quiesced table spaces only; other tables are put in reconcile_pending state	Optional for tables in quiesced table spaces; required for all other tables
Database restore to a different database name, alias, hostname, or instance with no roll forward (see note (WQ4884))	Tables put in reconcile_not_possible state	N/A	Optional, but tables in reconcile_not_possible state must be manually fixed
Database restore to a different database name, alias, hostname or instance and roll forward	No action	Tables put in reconcile_not_possible state	Optional, but tables in reconcile_not_possible state must be manually fixed
Database restore from an unusable backup (image may be too old or was invalidated by the File Manager) with no roll forward (see note (WQ4884))	Tables put in reconcile_not_possible state	N/A	Optional, but tables in reconcile_not_possible state must be manually fixed
Database restore from an unusable backup (image may be too old or was invalidated by the File Manager) and roll forward	No action	Tables put in reconcile_not_possible state	Optional, but tables in reconcile_not_possiblestate must be manually fixed
Table space restore from an unusable backup (image may be too old or was invalidated by the File Manager) and roll forward	No action	Tables put in reconcile_not_possible state	Optional, but tables in reconcile_not_possible state must be manually fixed
Database restore from an unusable backup (image may be too old or was invalidated by the File Manager) using the WITHOUT DATALINK option and no roll forward (see note (WQ4884))	Tables put in reconcile_pending state	N/A	Required
Database restore from an unusable backup (image may be too old or was invalidated by the File Manager) using the WITHOUT DATALINK option and roll forward	No action	Tables put in reconcile_not_possible state	Optional, but tables in reconcile_not_possible state must be manually fixed
Table space restore from an unusable backup (image may be too old or was invalidated by the File Manager) using the WITHOUT DATALINK option and roll forward	No action	Tables put in reconcile_not_possible state	Optional, but tables in reconcile_not_possible state must be manually fixed

Notes:

1. If you roll forward with just the STOP option, you cannot use the DLREPORT option because reconcile processing is not performed. The DLREPORT option is only useful if you roll forward to a quiesce point, which performs a fast reconcile on tables in the quiesced table spaces.

2. (WQ4884) A restore using an offline backup and the WITHOUT ROLLING FORWARD option (logretain is on), or a restore using an offline backup (logretain is off).

Removing a Table from the Reconcile_Not_Possible State

A restored table (or tables) with a DATALINK column is put into the reconcile_not_possible state:

• If a table space is restored from a backup that is earlier than the value specified for the dl_num_backup database configuration parameter. For more information, see "DataLink Number of Backups (dl_num_backup)".

• If a table (or table space) was dropped and the time specified by the dl_time_drop database configuration parameter has passed. For more information, see "DataLink Time After Drop (dl_time_drop)".

DB2 still allows the table to be accessed, even though the DATALINK column values may not be valid. If you want to prevent access to a table with possibly inconsistent DATALINK column values, issue the SET CONSTRAINTS for tablename TO DATALINK RECONCILE PENDING command. You can update the DATALINK values as follows:

• Using the SQL UPDATE statement, set the data location part of a DATALINK column to a zero-length URL if the column is not nullable, or to NULL if the column is nullable. (You can, optionally, also update the comment part of the DATALINK column.) The UPDATE succeeds when the table is in the reconcile_not_possible state because DB2 does not interact with the file server to unlink the file.

• Restore the files on the appropriate file servers. Then run an application that issues SELECT statements to read the DATALINK column values, and issues UPDATE statements to update the DATALINK column with the same values. Note that the reconcile_not_possible state must be on while the DATALINK column values are being updated. After the update operation completes, the files will be marked as linked on the appropriate file servers.

You then reset the reconcile_not_possible state by issuing the following command:

   SET CONSTRAINTS FOR tablename DATALINK RECONCILE PENDING IMMEDIATE UNCHECKED

Reconciling DB2 File Manager

You use the reconcile utility to reconcile data links. The utility is initiated from DB2, and involves all the DB2 file servers that are referenced by the DATALINK column values. It validates that the referenced files either exist on the file server, or that links can be re-established. The following sections describe how DB2 detects whether you need to reconcile data links, and how to reconcile them.

If a DB2 File Manager file reference does not exist or cannot be re-established, the reconcile utility places a copy of the rows in error along with a reason for each into a report, then modifies the offending rows. You can use the report to update the rows to make the required corrections. The report uses the naming convention report.exp (the .exp extension is supplied by the reconcile utility). For example, you can invoke the reconcile utility with the following statement:

   db2 reconcile dept dlreport  /u/scottba/report

This command reconciles the table called dept, and writes exceptions to the exception report report.exp, which is created in the directory /u/scottba.

Detection of Situations That Require Reconciliation

Following are some situations when you may need to run the reconcile utility:

• The entire database is restored and rolled forward to a point in time. Because the entire database is rolled forward to a committed transaction, no tables will be in the check pending state (due to referential constraints or check constraints). All data in the database is brought to a consistent state. The DATALINK columns, however, may not be synchronized with the metadata in the DB2 File Manager, and reconciliation is required.

  In this situation, tables with DATALINK columns will already be in the reconcile_pending state. You should issue the reconcile utility for each of these tables.

• (AMNES) A particular DB2 File Manager loses track of its metadata. This can occur for different reasons. For example:
  • The DB2 File Manager was cold started.
  • The DB2 File Manager metadata was restored to a back-level state.

  In some situations, such as SQL UPDATEs and DELETEs, DB2 may be able to detect a problem with the metadata in a DB2 File Manager. In these situations, DB2 would fail the SQL statement with -2076. You would put the table in the reconcile_pending state by using the SET CONSTRAINTS statement, then run the reconcile utility on that table.

• A file system is not available (for example, because of a disk crash) and is not restored to the current state. In this situation, files may be missing.

  An error like this will typically be discovered by an application when it cannot access the file whose file reference it obtained from the database. You should put the table in the reconcile_pending state and run the reconcile utility on it. Some of the files may be restored from the archive server if their corresponding DATALINK columns had RECOVERY=YES. In any case, the reconcile utility will record the exceptions in the exception report. You can then restore those files or issue SQL UPDATEs to fix the column.

Summary of Procedure for Reconciliation

If you need to reconcile data links because of point-in-time recovery or because DB2 File Manager and DB2 control information do not match:

1. Put the table in the reconcile_pending state by issuing the SET CONSTRAINTS statement. (In some situations, DB2 will do this for you.)

2. Use the reconcile utility to resolve the links, and take the appropriate actions for the exceptions in the exception report.

[ DB2 List of Books | Search the DB2 Books ]