Checking the referential integrity relationships

After you locate the DBD in the dump, the referential integrity (RI) relationships can be analyzed for inconsistencies.

To check the RI relationships:

1. Checking the records illustrates the structure of relationship fan set chains. Refer to that figure to perform the following checks. These checks apply to informational referential constraints as well as enforced referential constraints.
   1. For each relationship fan set OBD (OBDFS):
      • If OBDDCCHR is non-zero, then the dependent in the relationship resides in a different database than the parent. The DBID of the other database is in the OBDDCCHD field of this relationship fan set.

        Make a note that when you check the relationship chains for database OBDDCCHD, you should ensure that the OBDDCPAR field contains the OBID of this relationship fan set, and that OBDDCPAD contains the DBID of this database.

      • Check that OBDFSPAR is equal to the OBID of the parent record type OBD (OBDREC). The OBID of OBDREC is stored in OBDRECID.
      • If OBDRDEF is equal to binary B'0' OBDREC, then OBDDCIP (in OBDFS) contains the OBID of the index fan set for the primary index. Verify the existence of that index fan set.

        If OBDRDEF is equal to binary B'1' in OBDREC, then the definition of OBDREC is incomplete (there is no primary index on the primary key) and OBDDCIP is meaningless.

      • Cross-check the OBD with the catalog. Issue the following SQL statement, replacing DBID with the DBID of the DBD being checked, and OBID with the OBID of the relationship.

        

        SELECT R.RELNAME, R.REFTBCREATOR, R.REFTBNAME
          FROM SYSIBM.SYSRELS R,
               SYSIBM.SYSTABLES T
          WHERE R.REFTBCREATOR = T.CREATOR
            AND R.REFTBNAME = T.NAME
            AND T.DBID = dbid
            AND R.RELOBID1 = obid

        

        This returns the name of the relationship with respect to the parent table.

   2. Relationship chain in which record is a dependent
      For each relationship fan set OBD (OBDFS):

      • If OBDDCPAR is non-zero, then the parent in the relationship resides in a different database than the dependent. The dbid of the other database is in the OBDDCPAD field of this relationship fan set.

        Make a note that when you check the relationship chains for database OBDDCPAD, you should ensure that the OBDDCCHR field contains the obid of this relationship fan set, and that OBDDCCHD contains the dbid of this database.

      • Check that OBDFSCHI is equal to the OBID of the dependent record type OBD (OBDREC). The OBID of OBDREC is stored in OBDRECID.
      • If OBDDCIF is not equal to zero, then it contains the OBID of the index fan set on the foreign key. Verify the existence of that index fan set.

        If OBDDCIF is equal to zero, then there is no index on the foreign key.

      • Cross-check the OBD with the catalog. Issue the following SQL statement, replacing dbid with the DBID of the DBD being checked, and OBID with the OBID of the relationship.

        

        SELECT R.RELNAME R.CREATOR, R.TBNAME
          FROM SYSIBM.SYSRELS R,
               SYSIBM.SYSTABLES T
          WHERE R.CREATOR = T.CREATOR
            AND R.TBNAME = T.NAME
            AND T.DBID = dbid
            AND R.RELOBID1 = obid

        

        This returns the name of the relationship with respect to the dependent table.

2. To check the number of foreign keys, issue the following SQL statement:

   

   SELECT COUNT(*) FROM SYSIBM.SYSFOREIGNKEYS
    WHERE CREATOR = creator
      AND RELNAME = relname

   

   To check the number of primary keys, issue:

   

   SELECT COUNT(*)
     FROM SYSIBM.SYSINDEXES I,
          SYSIBM.SYSKEYS K
    WHERE I.UNIQUERULE = 'P'
      AND I.TBCREATOR = reftbcreator
      AND I.TBNAME = reftbname
      AND I.CREATOR = K.IXCREATOR

   

3. Return to the procedure that referred you here.