INCLUDE RI example

The following figure shows an example of how INCLUDE RI works. In this example, jobs A, B, and C are the only sources of changes to these tables (no other jobs or simultaneous online transactions).

  • The primary key in EMPL_TB is a foreign key in PROJECT_TB (EMPL_TB is the parent).
  • The primary key in RESOURCE_TB is a foreign key in PROJECT_TB (RESOURCE_TB is the parent).
  • The delete rules for both RI constraints are defined as cascade.
  • Job A deletes rows in EMPL_TB that cascade to PROJECT_TB.

Related topic

Working-with-changes-resulting-from-referential-integrity

  • Job B deletes rows in RESOURCE_TB that cascade to PROJECT_TB.
  • Job C updates PROJECT_TB directly.

GUID-882F2AE2-096D-45F4-BF22-83ED6E6B5986-low.png

The following table shows the log records selected by different filters and different INCLUDE RI settings, based on this example.

Filter

INCLUDE RI setting

SQL statements that LBMC AMI Log Masterog Master generates

Notes

TABLE NAME = EMPL_TB

NO

All direct changes to EMPL_TB (Job A)

None

YES

All direct changes to EMPL_TB (Job A)

Only Job A updates EMPL_TB. If any cascading deletes in EMPL_TB that resulted from RI relationships with other tables existed, those deletes would be selected because INCLUDE RI is YES.

TABLE NAME IN (EMPL_TB, PROJECT_TB)

NO

  • All direct changes to EMPL_TB (Job A)
  • All direct changes to PROJECT_TB (Job C)

Because INCLUDE RI is NO, the product selects only direct (nonRI-related) changes to both tables.

YES

  • All direct changes to EMPL_TB (Job A)
  • All direct changes to PROJECT_TB (Job C)
  • All cascading deletes in PROJECT_TB (Job A and JOB B)

When INCLUDE RI is YES, the product selects all direct and all RI-related changes to both tables.

BMC AMI Log Master cannot distinguish between the cascading deletes from Job A and Job B without further criteria in the filter (see next entries).

TABLE NAME IN (EMPL_TB, PROJECT_TB) AND CORRID = JOBA

NO

All direct changes to EMPL_TB (Job A)

Job A updates only EMPL_TB.

Had Job A updated PROJECT_TB directly, those deletes would be selected because PROJECT_TB is included in the filter.

YES

  • All direct changes to EMPL_TB (Job A)
  • Selected cascading deletes in PROJECT_TB (Job A)

When INCLUDE RI is YES, the product selects all direct and all RI-related changes in both tables, and then selects only the changes made by Job A.


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*