The following sections provide information that applies if you have tables that contain DATALINK columns. For a full description of DATALINK columns, refer to the CREATE TABLE statement in the SQL Reference.
When an application issues SQL requests involving Data Links servers running the DB2 Data Links Manager (using DATALINK columns with the FILE LINK CONTROL attribute), the database manager distributes the work to the Data Links servers. It also keeps track of which Data Links servers are involved in the transaction. When the application issues a COMMIT for a transaction, the database manager commits the transaction by using two-phase commit protocol. In the first phase, the database manager writes a PREPARE log record and distributes a PREPARE request to all Data Links servers. Each Data Links server then responds with one of the following:
The first phase is considered successful if all Data Links servers respond "YES".
The processing in the second phase depends on the outcome of the first phase. If at least one of the Data Links servers responded "NO", the database manager distributes ABORT requests to all the Data Links servers involved. The transaction is rolled back and the error message SQL0903N with reason code "03" is returned to the application. Otherwise, the database manager proceeds to commit the transaction as it usually does in the absence of involvement of Data Links servers. At the end of this processing, it distributes a COMMIT request to all the Data Links servers involved in the transaction.
If a failure occurs on a Data Links server leaving some transactions in the PREPARED state, these transactions are called indoubt transactions. The database manager is responsible for tracking the outcome of these transactions and eventually resolving them on the Data Links server. Whenever the database manager determines that a failure has potentially created indoubt transactions on a Data Links server, it marks the state of the Data Links server as needing crash recovery. It disallows any SQL requests involving the Data Links server while it is in this state. SQL0357N with reason code "03" is returned to the application which made the SQL request.
At the time of RESTART, ACTIVATE DATABASE, or first CONNECT processing, the database manager attempts to connect to each configured Data Links server and attempts to resolve its indoubt transactions by aborting or committing them. A Data Links server's state is marked as available if all of its indoubt transactions are resolved except those transactions that are also indoubt on the database manager. In the available state, SQL requests involving the Data Links server are allowed. At the end of this attempt to resolve indoubt transactions if the database manager determines that a Data Links server still potentially has indoubt transactions which need resolution, it marks the state of the Data Links server as needing crash recovery. This can happen, for instance, if a Data Links server is not available during RESTART, ACTIVATE DATABASE, or first CONNECT processing; or, the Data Links server encounters a failure during that processing.
While a Data Links server configured to a database is in a state needing crash recovery, the database manager disallows SQL requests involving that particular Data Links server. SQL requests involving other data in the database are still allowed. The database manager starts a process which asynchronously attempts to complete crash recovery on each Data Links server requiring recovery. When the process successfully completes the crash recovery, the state of the Data Links server is marked as available thereby allowing further SQL requests involving it.
DB2 ensures that by the time the backup utility completes, linked files at Data Links servers running the DB2 Data Links Manager 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 Data Links servers schedule them to be copied asynchronously to an archive server such as TSM, 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 Data Links servers that are specified in the DB2 configuration file are running. If a Data Links server has one or more linked files, it must be available until the backup operation completes. If a Data Links server becomes unavailable before the backup operation completes, the backup operation is declared as incomplete.
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. A successful backup operation can cause the Data Links servers to clean up the archived versions of files on the archive server (either disk or TSM). The num_db_backups database configuration parameter specifies the number of DB2 database backups before archived versions of the files (that were unlinked) are removed. See Chapter 32, Configuring DB2 for more information about this configuration parameter.
When unlinked files are removed, the information about the unlinked files is also removed from the Data Links server registration tables.
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 Datalink_Reconcile_Pending state at the end of the restore operation. See Reconciling Data Links for more information.
During restore operations, tables with DATALINK columns may be put into one of the following states.
When a table is in the Datalink_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 Datalink_Reconcile_Not_Possible State for details). You cannot issue INSERT and DELETE statements because they involve the DATALINK column.
When a table is in the Datalink_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:
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 Datalink_Reconcile_Not_Possible state after the restore operation (or the roll-forward operation, if used) completes.
If the backup is not recorded in the registration tables, it means that the backup file that is provided is earlier than the value for num_db_backups and has already been "garbage collected". This means that the archived files from this earlier backup have been removed and cannot be restored. All tables that have DATALINK columns are put into the Datalink_Not_Possible state.
The table 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 Datalink_Reconcile_Not_Possible state, you can fix the DATALINK column data in one of the ways suggested under Removing a Table from the Datalink_Reconcile_Not_Possible State.
When you restore a database or table space and you do specify the WITHOUT DATALINK option, and one or more of the Data Links servers containing the DATALINK data is not available, all table spaces containing tables with DATALINK values on the unavailable server(s) are placed in the RESTORE PENDING state.
| Note: | You can only restore without rolling forward at the database level, and not the table-space level. To restore a database without rolling forward, you could either restore a nonrecoverable database (that is, a database that uses circular logging), or you would specify the WITHOUT ROLLING FORWARD parameter for the restore utility. |
If you use the restore utility with the WITHOUT DATALINK option, all tables with DATALINK columns are placed in the Datalink_Reconcile_Pending state and no reconciliation is performed with the Data Links servers during the restore operation.
If you do not use the WITHOUT DATALINK option, and all the Data Links servers are available and all information about the DATALINK columns is fully recorded in the registration tables, the following occurs for each Data Links server recorded in the backup file:
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:
SET CONSTRAINTS FOR tablename TO DATALINK RECONCILE PENDING
This puts the table in Datalink_Reconcile_Pending state and check-pending state.
SET CONSTRAINTS FOR tablename IMMEDIATE CHECKED
This takes the table out of the check-pending state, but leaves it in the Datalink_Reconcile_Pending state. You must use the reconcile utility to take the table out of this state. For more information, see Reconciling Data Links.
When working with Data Links tables, you can roll-forward to the end of the logs or to a specified point in time.
Tables in table spaces that are rolled forward to a point in time are placed in the Datalink_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 Data Links.
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 Data Links 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. Data Links
servers running the DB2 Data Links Manager do 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 num_db_backups 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 Data Links 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. |
The following table shows the different types of recovery that you can
perform, the DB2 Data Links 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 Data Links Manager Processing during Restore | DB2 Data Links 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 Datalink_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 Datalink_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 Datalink_Reconcile _Pending state | Required |
| Database restore using WITHOUT DATALINK option and roll forward to a point in time | No action | Tables put in Datalink_Reconcile _Pending state | Required |
| Table space restore and roll forward to a point in time | No action | Tables put in Datalink_Reconcile _Pending state | Required |
| Table space restore using WITHOUT DATALINK option and roll forward to a point in time | No action | Tables put in Datalink_Reconcile _Pending state | Required |
| Database restore to a different database name, alias, hostname, or instance with no roll forward (see note ***) | Tables put in Datalink_Reconcile _Not_Possible state | N/A | Optional, but tables in Datalink_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 Datalink_Reconcile _Not_Possible state | Optional, but tables in Datalink_Reconcile _Not_Possible state must be manually fixed |
| Database restore from an unusable backup (image has been garbage-collected on the Data Links server) with no roll forward (see note ***) | Tables put in Datalink_Reconcile _Not_Possible state | N/A | Optional, but tables in Datalink_Reconcile _Not_Possible state must be manually fixed |
| Database restore from an unusable backup (image has been garbage-collected on the Data Links server) and roll forward | No action | Tables put in Datalink_Reconcile _Not_Possible state | Optional, but tables in Datalink_Reconcile _Not_Possible state must be manually fixed |
| Table space restore from an unusable backup (image has been garbage-collected on the Data Links server) and roll forward | No action | Tables put in Datalink_Reconcile _Not_Possible state | Optional, but tables in Datalink_Reconcile _Not_Possible state must be manually fixed |
| Database restore from an unusable backup (image has been garbage-collected on the Data Links server) using the WITHOUT DATALINK option and no roll forward (see note ***) | Tables put in Datalink_Reconcile _Pending state | N/A | Required |
| Database restore from an unusable backup (image has been garbage-collected on the Data Links server) using the WITHOUT DATALINK option and roll forward | No action | Tables put in Datalink_Reconcile _Not_Possible state | Optional, but tables in Datalink_Reconcile _Not_Possible state must be manually fixed |
| Table space restore from an unusable backup (image has been garbage-collected on the Data Links server) using the WITHOUT DATALINK option and roll forward | No action | Tables put in Datalink_Reconcile _Not_Possible state | Optional, but tables in Datalink_Reconcile _Not_Possible state must be manually fixed |
| Note: |
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). |
A restored table (or tables) with a DATALINK column is put into the Datalink_Reconcile_Not_Possible state:
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:
You then reset the Datalink_Reconcile_Not_Possible state by issuing the following command:
SET CONSTRAINTS FOR tablename DATALINK RECONCILE PENDING IMMEDIATE UNCHECKED
You use the reconcile utility to reconcile data links. The utility is initiated from DB2, and involves all the Data Links servers running the DB2 Data Links Manager that are referenced by the DATALINK column values. It validates that the referenced files either exist on the Data Links 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 Data Links server 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 an exception table (if specified), then modifies the offending rows. If the exception table is not specified, the DATALINK column values for which a file reference could not be re-established are copied to an exception report file along with a column-ID and reason. You can use the exception table (if specified) information or the report to update the rows to make the required corrections. The exception table used with the reconcile utility is identical to the exception table used by the Load utility. Refer to Data Movement Utilities Guide and Reference for more information on the Load utility. 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 FOR EXCEPTION excptab
This command reconciles the table called dept, and writes exceptions to the exception table excptab, which was created by the user. Information about files that were unlinked during reconciliation are written to the file report.ulk, which is created in the directory /u/scottba. If FOR EXCEPTION excptab is not specified, then the exception information is written to the file report.exp, which is created in the directory /u/scottba. Refer to the Command Reference for more information on the reconcile utility.
Following are some situations when you may need to run the reconcile utility:
In this situation, tables with DATALINK columns data will already be in the Datalink_Reconcile_Pending state. You should issue the reconcile utility for each of these tables.
In some situations, such as SQL UPDATEs and DELETEs, DB2 may be able to detect a problem with the metadata in a Data Links server. In these situations, DB2 would fail the SQL statement. You would put the table in the Datalink_Reconcile_Pending state by using the SET CONSTRAINTS statement, then run the reconcile utility on that table.
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 Datalink_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 table or in the exception report. You can then restore those files or issue SQL UPDATEs to fix the column.
If you need to reconcile data links because of point in time recovery or because Data Links servers running the DB2 Data Links Manager and DB2 control information do not match: