Referential constraints and informational referential constraints

In all cases, referential constraints are checked between parent and child tables only when you are loading the child table.

Referential constraints and SQLAPPLY load jobs

The High-speed Apply architecture always preserves referential integrity relationships during an SQLAPPLY load job. Therefore, when you specify the ENFORCE option with an SQLAPPLY load job, LOADPLUS ignores that option.

When referential integrity is involved, BMC recommends that you specify ORDER YES, causing LOADPLUS to sort your data by table. This sort order causes LOADPLUS to load parent tables before their child tables.

If you attempt to load tables that are self-referencing or that have circular relationships and you specify ORDER YES, LOADPLUS cannot determine the correct order for an SQLAPPLY load. In this case, LOADPLUS issues warning message BMC51582W but continues processing. When processing passes to Db2, you might receive SQL -530 errors. To maintain referential integrity for this type of load, order the data before your load job, and specify ORDER NO with your LOAD command.

Referential constraints and DSNUTILB load jobs

When LOADPLUS invokes DSNUTILB, referential integrity relationships are handled by DSNUTILB based on the ENFORCE option that you specify.

However, the IBM Db2 LOAD utility ENFORCE options are CONSTRAINTS and NO. If you specify the LOADPLUS option ENFORCE RI or ENFORCE CONSTRAINTS INFORI YES, LOADPLUS cannot convert that option to an appropriate IBM option. In these cases, LOADPLUS issues message BMC50178E and terminates.

Enforcing referential constraints for other load types

For other load types, LOADPLUS enforces referential constraints based on the ENFORCE option that is in effect.

When enforcing referential constraints, LOADPLUS verifies the constraints and discards any violations.

To enforce referential constraints

1. Ensure that a currently supported version of XBM or SUF (as described in System-and-software-requirements) is installed and available to LOADPLUS.XBM and SUF require a started task.
2. Enable XBM objects:
   1. Create and activate the XBM management set that contains the appropriate snapshot object definition.
   2. Create and activate the XBM configuration that contains the appropriate cache attributes.For proper LOADPLUS operation, ensure that XBM or SUF is configured with sufficient cache for the number of concurrent snapshots and the level of Db2 update activity at your site. For specific cache configuration information, see the EXTENDED BUFFER MANAGER for DB2 documentation.
3. Specify the following options in your LOADPLUS installation options module or on your LOAD command:
   • (Optional) XBMID, if you want to use a specific XBM subsystem for snapshot processing
   • PRELOAD LOAD

   • Either ENFORCE RI (to check only referential constraints) or ENFORCE CONSTRAINTS (to check both referential and check constraints)

     You can optionally specify INFORI YES on either of these options to tell LOADPLUS to also enforce informational referential constraints. For more information about these options, see ENFORCE.

4. Ensure that you have READ access to the primary index of the parent table for the table that you are loading.
5. Ensure that your objects and the options on your LOAD command meet the requirements described in Restrictions on enforcing referential constraints.

Restrictions on enforcing referential constraints

When checking referential constraints during your load job, LOADPLUS has the following restrictions on your objects. When encountering any of these conditions, LOADPLUS issues an appropriate message and terminates.

• You cannot load the parent table and child table in the same load execution.
• You cannot load multiple tables concurrently if those tables use the same parent index.
• Primary indexes on the parent table of the table that you are loading cannot be in any restrictive status (for example, RBDP).
• Primary indexes on the parent table of the table that you are loading must already be defined. (In other words, if the index space was defined with DEFINE NO, it must be materialized before the load job.)
• Objects cannot be in LPL or WEPR status.
• Identity columns in the tables that you are loading cannot have check constraints defined on them.