IBM i: The SQL Way · #23

Diagnose Db2 for i Cross-Reference Problems with QSYS.QADBXREF

Use QSYS.QADBXREF to compare Db2 for i long and system file names, identify suspicious cross-reference entries, and use RCLDBXREF safely when catalog metadata becomes inconsistent.

Saurabh Arora

IBM i Solution Architect | Technical Lead | Modernization & Integration Specialist

Published August 25, 20265 min read

Related native optionRCLDBXREF OPTION(*CHECK)

QSYS.QADBXREF is not a table most IBM i developers query during normal application work. It becomes valuable when the database catalog and the object name being reported by Db2 no longer appear to agree.

A classic symptom is:

CREATE TABLE  -> object already exists
DROP TABLE    -> object not found
SYSTABLES     -> no matching row

In that situation, QADBXREF can reveal database cross-reference information that is not obvious from the normal SQL catalog query.

The important rule is:

Query QADBXREF for diagnosis. Do not manually maintain it as application data.

What QADBXREF represents

IBM i maintains database cross-reference information that connects database files, SQL names, schemas, and other catalog metadata.

IBM Support documents the relationship between familiar QSYS2.SYSTABLES columns and fields in QSYS.QADBXREF:

SYSTABLES                  QADBXREF
------------------------   --------
TABLE_NAME                 DBXLFI
TABLE_SCHEMA               DBXLB2
SYSTEM_TABLE_NAME          DBXFIL
SYSTEM_TABLE_SCHEMA        DBXLIB

The fields are useful because they expose both sides of the IBM i database naming model:

DBXFIL   System file name
DBXLIB   System library name
DBXLFI   Long SQL file/table name
DBXLB2   SQL schema/library name

For conventionally named libraries, DBXLIB and DBXLB2 may contain the same visible name.

Find one long table name

A direct lookup is:

SELECT
    DBXFIL,
    DBXLIB,
    DBXLFI,
    DBXLB2,
    DBXTXT
FROM QSYS.QADBXREF
WHERE DBXLB2 = 'MYLIB'
  AND DBXLFI = 'MY_LONG_TABLE_NAME';

For an ordinary unquoted SQL identifier, use uppercase.

If a delimited identifier was used, preserve its exact case.

During a recent real-world troubleshooting case, this type of query returned a row even though the corresponding long table name could not be found through the normal SQL catalog checks.

That was the clue that changed the investigation.

Search by system library name

A library can also be checked through the system-side field:

SELECT
    DBXFIL,
    DBXLIB,
    DBXLFI,
    DBXLB2,
    DBXTXT
FROM QSYS.QADBXREF
WHERE DBXLIB = 'MYLIB'
  AND UPPER(DBXLFI) =
      UPPER('MY_LONG_TABLE_NAME');

This can be convenient when the SQL schema name and native library name are the same.

For general catalog verification, understanding the distinction between DBXLIB and DBXLB2 is better than assuming they always mean exactly the same thing.

Compare SYSTABLES with QADBXREF

A useful diagnostic is to query both sources separately with the same name.

First, the SQL catalog:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    SYSTEM_TABLE_SCHEMA,
    SYSTEM_TABLE_NAME,
    TABLE_TYPE
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = 'MYLIB'
  AND TABLE_NAME = 'MY_LONG_TABLE_NAME';

Then the cross-reference:

SELECT
    DBXLB2 AS TABLE_SCHEMA,
    DBXLFI AS TABLE_NAME,
    DBXLIB AS SYSTEM_TABLE_SCHEMA,
    DBXFIL AS SYSTEM_TABLE_NAME
FROM QSYS.QADBXREF
WHERE DBXLB2 = 'MYLIB'
  AND DBXLFI = 'MY_LONG_TABLE_NAME';

Interpret the results carefully:

SYSTABLES row   QADBXREF row   Initial interpretation
-------------   ------------   -------------------------------
Yes             Yes            Normal catalog relationship
No              Yes            Suspicious cross-reference state
Yes             No             Suspicious catalog relationship
No              No             Look elsewhere for the collision

This table is a troubleshooting guide, not proof of corruption by itself.

Authority, ASP context, quoted names, aliases, object type, damaged objects, and other system conditions can affect what you see.

A practical comparison query

For a focused comparison, use two common table expressions:

WITH SQL_CATALOG AS
(
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        SYSTEM_TABLE_SCHEMA,
        SYSTEM_TABLE_NAME,
        TABLE_TYPE
    FROM QSYS2.SYSTABLES
    WHERE TABLE_SCHEMA = 'MYLIB'
      AND TABLE_NAME = 'MY_LONG_TABLE_NAME'
),
XREF AS
(
    SELECT
        DBXLB2 AS TABLE_SCHEMA,
        DBXLFI AS TABLE_NAME,
        DBXLIB AS SYSTEM_TABLE_SCHEMA,
        DBXFIL AS SYSTEM_TABLE_NAME
    FROM QSYS.QADBXREF
    WHERE DBXLB2 = 'MYLIB'
      AND DBXLFI = 'MY_LONG_TABLE_NAME'
)
SELECT
    'SQL_CATALOG' AS SOURCE,
    TABLE_SCHEMA,
    TABLE_NAME,
    SYSTEM_TABLE_SCHEMA,
    SYSTEM_TABLE_NAME,
    TABLE_TYPE
FROM SQL_CATALOG

UNION ALL

SELECT
    'QADBXREF',
    TABLE_SCHEMA,
    TABLE_NAME,
    SYSTEM_TABLE_SCHEMA,
    SYSTEM_TABLE_NAME,
    CAST(NULL AS CHAR(1))
FROM XREF;

The result makes a mismatch visible without modifying anything.

Why this matters with long SQL names

IBM i database objects can have both:

Long SQL name
10-character system name

For example:

SQL name:     CUSTOMER_TRANSACTION_HISTORY
System name:  CUSTOM00001

Most of the time, Db2 and IBM i maintain this relationship transparently.

When cross-reference information becomes inconsistent, the long name can become the key clue because one layer may still know about the mapping while another no longer resolves the object normally.

The symptom that should make you suspicious

Consider this sequence:

CREATE OR REPLACE TABLE MYLIB.MY_LONG_TABLE_NAME
(
    ID BIGINT NOT NULL
);

Db2 says the name already exists.

Then:

DROP TABLE MYLIB.MY_LONG_TABLE_NAME;

Db2 says the table does not exist.

Then:

SELECT *
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = 'MYLIB'
  AND TABLE_NAME = 'MY_LONG_TABLE_NAME';

returns nothing.

But:

SELECT
    DBXFIL,
    DBXLIB,
    DBXLFI,
    DBXLB2
FROM QSYS.QADBXREF
WHERE DBXLB2 = 'MYLIB'
  AND DBXLFI = 'MY_LONG_TABLE_NAME';

returns a row.

At that point, stop treating the problem as an ordinary DROP-and-recreate issue.

The evidence is pointing toward database cross-reference inconsistency.

Do not DELETE the QADBXREF row

This deserves a separate warning.

Do not respond with:

DELETE FROM QSYS.QADBXREF
WHERE ...;

QADBXREF is database-maintained cross-reference information.

Removing one row manually does not prove that the other related cross-reference information is correct. It can turn one inconsistency into several.

Use IBM i’s supported reclaim functions.

Check cross-reference consistency

Run:

RCLDBXREF OPTION(*CHECK)

IBM documents this option as a check of the database cross-reference catalogs.

Useful messages include:

CPD32A7   A diagnostic message for each library known to
           have inconsistent data in the catalog being checked

CPF32AC   Cross-reference problems were found

CPC32AC   Cross-reference data appears correct

Review the job log after the command.

The check does not repair the library.

Repair one affected library

When the evidence supports a cross-reference repair, use:

RCLDBXREF OPTION(*FIX) LIB(MYLIB)

IBM documents RCLDBXREF as a subset of the database cross-reference recovery capability available through RCLSTG SELECT(*DBXREF), with an important operational advantage:

RCLDBXREF can target a specific library and does not require
restricted state.

That makes it a much more focused response to a known library-level cross-reference problem.

Operational cautions for *FIX

RCLDBXREF OPTION(*FIX) is not a casual command.

IBM warns that while the library is being reclaimed:

applications must not use or modify objects in the library
the reclaim process should not be interrupted
a failed reclaim can leave additional inconsistencies
some situations may still require RCLSTG SELECT(*DBXREF)

The operation can also take time depending on the number of files and fields involved.

Plan it like a database repair operation, not like a simple metadata refresh.

Why RCLSTG is not the first step

RCLSTG SELECT(*DBXREF) rebuilds cross-reference information more broadly and requires restricted state.

For one library with a clearly identified cross-reference problem, that is usually a much larger operational action than necessary as a first response.

A sensible escalation path is:

RCLDBXREF OPTION(*CHECK)
        |
        v
RCLDBXREF OPTION(*FIX) LIB(MYLIB)
        |
        v
If the problem cannot be recovered through RCLDBXREF,
review IBM guidance and consider RCLSTG SELECT(*DBXREF)
under the required system conditions.

Recheck after the repair

Do not assume the command worked only because it completed.

Repeat the same query:

SELECT
    DBXFIL,
    DBXLIB,
    DBXLFI,
    DBXLB2,
    DBXTXT
FROM QSYS.QADBXREF
WHERE DBXLB2 = 'MYLIB'
  AND DBXLFI = 'MY_LONG_TABLE_NAME';

Then check the SQL catalog again:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    SYSTEM_TABLE_NAME,
    TABLE_TYPE
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = 'MYLIB'
  AND TABLE_NAME = 'MY_LONG_TABLE_NAME';

Only after the metadata state makes sense should the original DDL be retried.

What if the inconsistency returns?

A one-time repair answers:

How do we restore the cross-reference information?

It does not automatically answer:

Why did the cross-reference become inconsistent?

Do not invent a root cause after the fact.

Possible events around the time of failure may be worth investigating, but recurring inconsistencies should be treated as a system problem rather than solved repeatedly with a reclaim command.

Useful evidence can include:

job logs around the first failure
deployment history
save and restore activity
object creation or deletion activity
IASP state changes
system messages
PTF level
cross-reference server health
repeat frequency

IBM’s ANALYZE_CATALOG service can also report categories related to catalog reclaim and DBXREF server conditions on supported releases and PTF levels.

If the same library repeatedly needs cross-reference recovery, investigate the cause and involve IBM Support where appropriate.

QADBXREF versus the normal SQL catalog

Use QSYS2.SYSTABLES for normal application and database metadata work.

Use QSYS.QADBXREF when:

catalog verification is required
long and system names need to be compared at the cross-reference level
Db2 reports a name collision the normal catalog cannot explain
IBM Support guidance specifically calls for cross-reference verification

QADBXREF should not replace the normal catalog in everyday SQL tooling.

It is a deeper diagnostic source.

Recommended troubleshooting sequence

1. Capture the exact SQL and job-log error.
2. Check SYSTABLES and SYSINDEXES.
3. Check the underlying object with OBJECT_STATISTICS.
4. Query QADBXREF for the same long name.
5. Compare SQL and system names.
6. Run RCLDBXREF OPTION(*CHECK).
7. Repair only when the evidence supports it.
8. Re-query the catalog and cross-reference.
9. Retry the DDL.
10. Investigate recurrence instead of normalizing the repair.

Related guides:

Final takeaway

QSYS.QADBXREF is valuable precisely because it sits below the normal SQL catalog view most developers use every day.

When this combination appears:

CREATE -> already exists
DROP   -> not found
catalog query -> no row
QADBXREF -> row exists

there is enough evidence to investigate database cross-reference consistency instead of continuing to manipulate an object that SQL cannot resolve.

Use QADBXREF to diagnose the relationship.

Use RCLDBXREF to check and, where appropriate, rebuild it.

And if the inconsistency keeps returning, treat the recurrence as the problem that still needs to be solved.

References

IBM documentation and support references used for this entry.

Comments

Share your thoughts, questions, or real-world IBM i experiences related to this article.