Checking the auxiliary relationships

After you locate the DBD in the dump, the auxiliary relationships can be analyzed for inconsistencies.

Procedure

For each auxiliary relationship OBD (OBDRA):

  1. Check that the OBD located is an OBDRA. For an OBDRA, the first 3 bits of OBDFLAGS are B'010' and OBDATYP3 is B'1'. If these fields do not contain the correct values, return to the procedure that referred you here.
  2. Check that the OBDABT contains the OBID of the record OBD for the base table. If the base table space is partitioned, run the following SQL statement, replacing dbname with the name of the database and tsname with the name of the base table space.

    Begin general-use programming interface information.

    SELECT COUNT(*) FROM SYSIBM.SYSTABLEPART WHERE
      DBNAME = 'dbname' AND TSNAME = 'tsname';

    End general-use programming interface information.

    The result is equal to the value in field OBDAPARTN of OBDRA. This field contains the number of partitions in the base table space.
  3. If the base table space is partitioned, there is an OBDAPART section for each partition. Element i in the OBDAPART array corresponds to partition i of the base table space. If the base table space is not partitioned, there is a single OBDAPART section.
    Each OBDAPART section contains:
    1. OBDAAT (OBID of the OBDREC for the auxiliary table)
    2. OBDAPS (OBID of the OBDPS for the LOB table space)
    3. OBDAFS (OBID of the OBDFS for the auxiliary index)
    4. OBDAFI (OBID of the OBDFILE for the LOB table space)
    5. OBDAIPS (OBID of the OBDPS for the auxiliary index)
  4. Use the following queries to cross-check the values in the OBDAPART fields with the catalog.
    • Replace column with the value in field OBDACOLNO (the column number) in OBDRA.
    • Replace obid with the value in OBDAAT.
    • Replace obid1, obid2, … obidn with the values in OBDAAT.
    • To cross-check the value in OBDAAT with the catalog, run one of the following SQL statements. In these SQL statements, make the following changes:
      • Replace column with the value in field OBDACOLNO (the column number) in OBDRA.
      • Replace obid with the value in OBDAAT.
      • Replace obid1, obid2, … obidn with the values in OBDAAT.
      • For a nonpartitioned base table space, run this statement:

        Begin program-specific programming interface information.

        SELECT A.AUXTBNAME, A.COLNAME, A.TBNAME FROM
          SYSIBM.SYSCOLUMNS C,
          SYSIBM.SYSAUXRELS A,
          SYSIBM.SYSTABLES  T
          WHERE C.NAME  = A.COLNAME
            AND C.COLNO = 'column'
            AND T.NAME  = A.AUXTBNAME
            AND T.OBID  = 'obid';

        End program-specific programming interface information.

        The value of A.AUXTBNAME should be the name of the auxiliary table that is created for column A.COLNAME in base table A.TBNAME.

      • For a partitioned base table space, run the following query:

        Begin program-specific programming interface information.

        SELECT A.AUXTBNAME, A.COLNAME, A.TBNAME FROM
          SYSIBM.SYSCOLUMNS C,
          SYSIBM.SYSAUXRELS A,
          SYSIBM.SYSTABLES  T
          WHERE C.NAME  = A.COLNAME
            AND C.COLNO = 'column'
            AND T.NAME  = A.AUXTBNAME
            AND T.OBID IN (obid1, obid2, …, obidn);

        End program-specific programming interface information.

        The values of A.AUXTBNAME should be the names of the auxiliary tables that are created for column A.COLNAME in base table A.TBNAME.

    • To cross-check the value in OBDAPS with the catalog, run one of the following SQL statements. In these SQL statements, make the following changes:
      • Replace column with the value in field OBDACOLNO (the column number) in OBDRA.
      • Replace obid with the value in OBDAPS.
      • Replace obid1, obid2, … obidn with the values in OBDAPS.
      • For a nonpartitioned base table space, run this SQL statement:

        Begin program-specific programming interface information.

              SELECT S.NAME, A.COLNAME, A.TBNAME FROM
                SYSIBM.SYSCOLUMNS C,
                SYSIBM.SYSAUXRELS A,
                SYSIBM.SYSTABLES  T,
                SYSIBM.SYSTABLESPACE S
                WHERE C.NAME = A.COLNAME
                  AND C.COLNO = column
                  AND T.NAME = A.AUXTBNAME
                  AND T.TSNAME = S.NAME
                  AND S.PSID = obid;

        End program-specific programming interface information.

        The value of S.NAME should be the name of the LOB table space that is associated with A.COLNAME in base table A.TBNAME.

      • For a partitioned base table space, run this SQL statement:

        Begin program-specific programming interface information.

        SELECT S.NAME, A.COLNAME, A.TBNAME FROM
          SYSIBM.SYSCOLUMNS C,
          SYSIBM.SYSAUXRELS A,
          SYSIBM.SYSTABLES  T,
          SYSIBM.SYSTABLESPACE S
          WHERE C.NAME = A.COLNAME
            AND C.COLNO = column
            AND T.NAME = A.AUXTBNAME
            AND T.TSNAME = S.NAME
            AND S.PSID IN
              (obid1, obid2, ... , obidn);

        End program-specific programming interface information.

        The values of S.NAME should be the names of the LOB table spaces that are associated with A.COLNAME in base table A.TBNAME.

    • To cross-check the value in OBDAFS with the catalog, run the following SQL statements. In these SQL statements, make the following changes:
      • Replace column with the value in field OBDACOLNO (the column number) in OBDRA.
      • Replace obid with the value in OBDAFS.
      • Replace obid1, obid2, … obidn with the values in OBDAFS.
      • For a nonpartitioned base table space, run this SQL statement:

        Begin program-specific programming interface information.

        SELECT I.NAME, A.AUXTBNAME,
          A.COLNAME, A.TBNAME FROM
          SYSIBM.SYSCOLUMNS C,
          SYSIBM.SYSAUXRELS A,
          SYSIBM.SYSTABLES  T,
          SYSIBM.SYSINDEXES I
          WHERE C.NAME = A.COLNAME
            AND C.COLNO = column
            AND T.NAME = A.AUXTBNAME
            AND T.NAME = I.TBNAME
            AND I.OBID = obid;

        End program-specific programming interface information.

        The value of I.NAME should be the name of the auxiliary index on table A.AUXTBNAME associated with A.COLNAME in base table A.TBNAME.

      • For a partitioned base table space, run this SQL statement:

        Begin program-specific programming interface information.

        SELECT I.NAME, A.AUXTBNAME,
          A.COLNAME, A.TBNAME FROM
          SYSIBM.SYSCOLUMNS C,
          SYSIBM.SYSAUXRELS A,
          SYSIBM.SYSTABLES  T,
          SYSIBM.SYSINDEXES I
          WHERE C.NAME = A.COLNAME
            AND C.COLNO = column
            AND T.NAME = A.AUXTBNAME
            AND T.NAME = I.TBNAME
            AND I.OBID IN
              (obid1, obid2, ... , obidn);

        End program-specific programming interface information.

        I.NAME should be the names of the auxiliary indexes on tables A.AUXTBNAME and associated with A.COLNAME in base table A.TBNAME.

    • To cross-check the value in OBDAFI with the catalog, run one of the following SQL statements. In these SQL statements, make the following changes:
      • Replace column with the value in field OBDACOLNO (the column number) in OBDRA.
      • Replace obid with the value in OBDAFI.
      • Replace obid1, obid2, … obidn with the values in OBDAFI.
      • For a nonpartitioned base table space, run this SQL statement:

        Begin program-specific programming interface information.

        SELECT S.NAME, A.COLNAME, A.TBNAME FROM
          SYSIBM.SYSCOLUMNS C,
          SYSIBM.SYSAUXRELS A,
          SYSIBM.SYSTABLES  T,
          SYSIBM.SYSTABLESPACE S
          WHERE C.NAME = A.COLNAME
            AND C.COLNO = column
            AND T.NAME = A.AUXTBNAME
            AND T.TSNAME = S.NAME
            AND S.OBID = obid;

        End program-specific programming interface information.

        The value of S.NAME should be the name of the LOB table space that is associated with A.COLNAME in base table A.TBNAME.

      • For a partitioned table space, run this SQL statement:

        Begin program-specific programming interface information.

        SELECT S.NAME, A.COLNAME, A.TBNAME FROM
          SYSIBM.SYSCOLUMNS C,
          SYSIBM.SYSAUXRELS A,
          SYSIBM.SYSTABLES  T,
          SYSIBM.SYSTABLESPACE S
          WHERE C.NAME = A.COLNAME
            AND C.COLNO = column
            AND T.NAME = A.AUXTBNAME
            AND T.TSNAME = S.NAME
            AND S.OBID IN
              (obid1, obid2, ... , obidn);

        End program-specific programming interface information.

        The values of S.NAME should be the names of the LOB table spaces that are associated with A.COLNAME in base table A.TBNAME.

    • To cross-check the value in OBDAIPS with the catalog, run the following SQL statements. In these SQL statements, make the following changes:
      • Replace column with the value in field OBDACOLNO (the column number) in OBDRA.
      • Replace obid with the value in OBDAIPS.
      • Replace obid1, obid2, … obidn with the values in OBDAIPS.
      • For a nonpartitioned base table space, run this SQL statement:

        Begin program-specific programming interface information.

        SELECT A.COLNAME, I.NAME, A.AUXTBNAME, T.NAME FROM
          SYSIBM.SYSCOLUMNS C,
          SYSIBM.SYSAUXRELS A,
          SYSIBM.SYSTABLES  T,
          SYSIBM.SYSINDEXES I
          WHERE C.NAME = A.COLNAME
            AND C.COLNO = column
            AND T.NAME = A.AUXTBNAME
            AND T.NAME = I.TBNAME
            AND I.ISOBID = obid;

        End program-specific programming interface information.

        The value of I.NAME should be the name of the auxiliary index on table A.AUXTBNAME associated with A.COLNAME in base table T.NAME.

      • For a partitioned base table space, run this SQL statement:

        Begin program-specific programming interface information.

        SELECT I.NAME, A.AUXTBNAME, A.COLNAME, A.TBNAME FROM
          SYSIBM.SYSCOLUMNS C,
          SYSIBM.SYSAUXRELS A,
          SYSIBM.SYSTABLES  T,
          SYSIBM.SYSINDEXES I
          WHERE C.NAME = A.COLNAME
            AND C.COLNO = column
            AND T.NAME = A.AUXTBNAME
            AND T.NAME = I.TBNAME
            AND I.ISOBID IN (obid1, obid2, ... , obidn);

        End program-specific programming interface information.

        The values of I.NAME should be the names of the auxiliary indexes on tables A.AUXTBNAME associated with A.COLNAME in base table A.TBNAME.