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):
- 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.
- 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
dbnamewith the name of the database andtsnamewith the name of the base table space.SELECT COUNT(*) FROM SYSIBM.SYSTABLEPART WHERE DBNAME = 'dbname' AND TSNAME = 'tsname';The result is equal to the value in field OBDAPARTN of OBDRA. This field contains the number of partitions in the base table space. -
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:
- OBDAAT (OBID of the OBDREC for the auxiliary table)
- OBDAPS (OBID of the OBDPS for the LOB table space)
- OBDAFS (OBID of the OBDFS for the auxiliary index)
- OBDAFI (OBID of the OBDFILE for the LOB table space)
- OBDAIPS (OBID of the OBDPS for the auxiliary index)
- Use the following queries to cross-check the values in
the OBDAPART fields with the catalog.
- Replace
columnwith the value in field OBDACOLNO (the column number) in OBDRA. - Replace
obidwith the value in OBDAAT. - Replace
obid1, obid2, … obidnwith 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
columnwith the value in field OBDACOLNO (the column number) in OBDRA. - Replace
obidwith the value in OBDAAT. - Replace
obid1, obid2, … obidnwith the values in OBDAAT.
- For a nonpartitioned base table space, run this statement:
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';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:
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);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.
- Replace
- 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
columnwith the value in field OBDACOLNO (the column number) in OBDRA. - Replace
obidwith the value in OBDAPS. - Replace
obid1, obid2, … obidnwith the values in OBDAPS.
- For a nonpartitioned base table space, run this SQL statement:
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;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:
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);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.
- Replace
- To cross-check the value in OBDAFS with the catalog, run the following
SQL statements. In these SQL statements, make the following changes:
- Replace
columnwith the value in field OBDACOLNO (the column number) in OBDRA. - Replace
obidwith the value in OBDAFS. - Replace
obid1, obid2, … obidnwith the values in OBDAFS.
- For a nonpartitioned base table space, run this SQL statement:
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;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:
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);I.NAME should be the names of the auxiliary indexes on tables A.AUXTBNAME and associated with A.COLNAME in base table A.TBNAME.
- Replace
- 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
columnwith the value in field OBDACOLNO (the column number) in OBDRA. - Replace
obidwith the value in OBDAFI. - Replace
obid1, obid2, … obidnwith the values in OBDAFI.
- For a nonpartitioned base table space, run this SQL statement:
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;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:
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);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.
- Replace
- To cross-check the value in OBDAIPS with the catalog, run the
following SQL statements. In these SQL statements, make the following
changes:
- Replace
columnwith the value in field OBDACOLNO (the column number) in OBDRA. - Replace
obidwith the value in OBDAIPS. - Replace
obid1, obid2, … obidnwith the values in OBDAIPS.
- For a nonpartitioned base table space, run this SQL statement:
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;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:
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);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.
- Replace
- Replace