Validates the references to files for the DATALINK data of a table. The rows for which the references to files cannot be established are copied to the exception table (if specified), and modified in the input table.
Authorization
One of the following:
Required Connection
Database
Command Syntax
>>-RECONCILE--table-name---DLREPORT--filename------------------->
>-----+----------------------------+---------------------------><
'-FOR EXCEPTION--table-name--'
Command Parameters
Examples
The following command reconciles the table DEPT, and writes exceptions to the exception table EXCPTAB, which was created by the user. Information about files that were unlinked during reconciliation is written into the file report.ulk, which is created in the directory /u/johnh. If FOR EXCEPTION excptab had not been specified, the exception information would have been written to the file report.exp, created in the /u/johnh directory.
db2 reconcile dept dlreport /u/johnh/report for exception excptab
Usage Notes
During reconciliation, attempts are made to link files which exist according to table data, but which do not exist according to Data Links File Manager metadata, if no other conflict exists.
Reconciliation is performed with respect to all DATALINK data in the table. If file references cannot be re-established, the violating rows are inserted into the exception table (if specified). These rows are not deleted from the input table. To ensure file reference integrity, the offending DATALINK values are nulled. If the column is defined as not nullable, the DATALINK values are replaced by a zero length URL.
If exception table is not specified, the DATALINK column values for which file references could not be re-established are copied to an exception report file (<filename>.exp), along with the column ID and a comment.
At the end of the reconciliation process, the table is taken out of datalink reconcile pending (DRP) state.
The exception table, if specified, should be created before the reconcile utility is run. The exception table used with the reconcile utility is identical to the exception table used by the load utility.
The exception table mimics the definition of the table being reconciled. It can have one or two optional columns following the data columns. The first optional column is the TIMESTAMP column. It will contain the time stamp for when the reconcile operation was started. The second optional column should be of type CLOB (32KB or larger). It will contain the IDs of columns with link failures, and the reasons for those failures. The DATALINK columns in the exception table should specify NO LINK CONTROL. This ensures that a file is not linked when a row (with a DATALINK column) is inserted, and that an access token is not generated when rows are selected from the exception table.
Information in the MESSAGE column is organized according to the following structure:
-----------------------------------------------------------------
Field
number Content Size Comments
-----------------------------------------------------------------
1 Number of violations 5 characters Right justified
padded with '0'
-----------------------------------------------------------------
2 Type of violation 1 character 'L' - DATALINK
violation
-----------------------------------------------------------------
3 Length of violation 5 characters Right justified
padded with '0'
-----------------------------------------------------------------
4 Number of violating 4 characters Right justified
DATALINK columns padded with '0'
-----------------------------------------------------------------
5 DATALINK column number 4 characters Right justified
of the first violating padded with '0'
column
-----------------------------------------------------------------
6 Reason for violation 5 characters Right justified
padded with '0'
-----------------------------------------------------------------
Repeat Fields 5
and 6 for each
violating column
-----------------------------------------------------------------
The following is a list of possible violations:
00001-File could not be found by DB2 Data Links Manager.
00002-File already linked.
00003-File in modified state.
00004-Prefix name not registered.
00005-File could not be retrieved.
00006-File entry missing. This will happen for
recovery = no, partial control 1 DATALINK
columns. Use update to relink the file.
00007-File is in unlink state.
00999-File could not be linked.
Example:
00001L000120002000400002000500001
00001 - Specifies that the number of violations is 1.
L - Specifies that the type of violation is 'DATALINK violation'.
00012 - Specifies that the length of the violation is 12 bytes.
0002 - Specifies that there are 2 columns in the row which
encountered link failures.
0004,00002
0005,00001 - Specifies the column ID and the reason for the violation.
If the message column is present, the time stamp column must also be present. For more information about exception tables, see the SQL Reference.