How constraints on the source table affect replication of load operations

Replication of load operations at the source table requires special consideration when constraints are defined on the source table.

After the source table is loaded, READ access that is required by the DB2 LOAD utility to load the data into the target table might be restricted:

z/OS®

On z/OS, access might be restricted if constraints are defined on the source table and the LOAD utility is invoked with the REPLACE or RESUME ENFORCE NO option. This situation prompts the LOAD utility to put the table into CHECK PENDING state, and Q Apply stops the Q subscription. To correct the problem you must run CHECK DATA on the source table to make the table space accessible, and then start the Q subscription.

Linux, UNIX, Windows

On Linux, UNIX, and Windows, access might be restricted if constraints are defined on the source table and the LOAD utility is invoked with the REPLACE or INSERT NO ACCESS option. This situation prompts the LOAD utility to put the table into SET INTEGRITY CHECK PENDING state, and Q Apply stops the Q subscription. To correct the problem, you must run SET INTEGRITY on the source table to make the table space accessible, and then start the Q subscription.

Attention: The INSERT ALLOW READ ACCESS option of the LOAD utility is not recommended if you set the option to replication source table loads. If the utility is invoked with this option and constraints are defined on the source table, the table is put into SET INTEGRITY PENDING state and READ ACCESS state. In these two states, the newly loaded data is not accessible. If you expect to use this option, use one of the following methods to prevent the source and target tables from losing synchronization:

• Before the load commences, stop the Q subscription, load the source table, and then start the Q subscription. This procedure triggers a complete new load of the target table (full refresh).
• Set the CAPTURE_LOAD option of the Q subscription to W and load the source table while the Q subscription is active. None of the newly loaded data is replicated, and you can then do one of the following things:
  • Use the asntdiff and asntrep commands to find and resolve differences between the source and target tables.
  • Stop and start the Q subscription, triggering an automatic load of the target.