IBM i Operations

When Db2 for i Says a Table Exists - But You Cannot Find It

A real IBM i troubleshooting path for a DDL name collision where Db2 says a long table name already exists, the normal SQL catalogs show nothing, and QSYS.QADBXREF reveals stale cross-reference information.

Saurabh Arora

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

Published August 25, 20267 min read

A DDL deployment through RUNSQLSTM failed because Db2 for i said a table with a long SQL name already existed. The obvious fix should have been to find the object and replace it. Instead, SYSTABLES, SYSINDEXES, and SYSVIEWS returned nothing, every DROP statement said the object did not exist, and CREATE OR REPLACE still could not create it. The answer was one layer deeper: QSYS.QADBXREF still contained cross-reference information for the name.

This was one of those IBM i problems where the individual messages appeared to contradict each other:

CREATE OR REPLACE TABLE  -> name already exists
DROP TABLE               -> table not found
DROP INDEX               -> index not found
DROP VIEW                -> view not found
SQL catalog queries       -> no matching object

The eventual fix was:

RCLDBXREF OPTION(*FIX) LIB(MYLIB)

After the library cross-reference information was rebuilt, the stale entry disappeared and the same DDL created the table successfully.

The interesting part is not the command by itself. It is the troubleshooting path that explains how Db2 for i can reach this state and where to look when the normal catalog does not tell the whole story.

The original failure

The deployment contained DDL similar to:

CREATE OR REPLACE TABLE MYLIB.MY_LONG_TABLE_NAME
(
    ID          BIGINT NOT NULL,
    STATUS      VARCHAR(20),
    CREATED_TS  TIMESTAMP NOT NULL
);

The statement was being executed through RUNSQLSTM.

Db2 reported that the long table name was already in use in the library.

Normally, CREATE OR REPLACE TABLE makes this kind of deployment easier. If Db2 recognizes an existing table with that SQL name, it can replace the table definition according to the rules of the statement.

But that assumes Db2 can identify a normal existing table to replace.

Here, it could not.

First check: the Db2 for i catalog

The first place to look is the SQL catalog.

For a table, view, alias, physical file, or logical file, start with:

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

SYSTABLES is especially useful on IBM i because it exposes both names:

TABLE_NAME          SQL name, up to 128 characters
SYSTEM_TABLE_NAME   IBM i system object name, up to 10 characters

That distinction matters when DDL uses long SQL names.

The query returned no row.

Next, check whether the name belongs to an SQL index:

SELECT
    INDEX_SCHEMA,
    INDEX_NAME,
    SYSTEM_INDEX_SCHEMA,
    SYSTEM_INDEX_NAME,
    TABLE_SCHEMA,
    TABLE_NAME
FROM QSYS2.SYSINDEXES
WHERE INDEX_SCHEMA = 'MYLIB'
  AND UPPER(INDEX_NAME) = UPPER('MY_LONG_TABLE_NAME');

Again, no row.

A view can also be checked directly:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    SYSTEM_VIEW_SCHEMA,
    SYSTEM_VIEW_NAME
FROM QSYS2.SYSVIEWS
WHERE TABLE_SCHEMA = 'MYLIB'
  AND UPPER(TABLE_NAME) = UPPER('MY_LONG_TABLE_NAME');

Still nothing.

SYSTABLES already contains views.

SYSVIEWS is useful when view-specific information such as the definition is needed. For a basic object-name collision, SYSTABLES plus SYSINDEXES is often the fastest SQL catalog check.

At this point the normal Db2 catalog did not contain the object that the create operation appeared to be colliding with.

Try the DROP statements anyway

The next step was to eliminate the possibility that the catalog queries were simply missing something obvious.

DROP TABLE MYLIB.MY_LONG_TABLE_NAME;

Db2 reported that the table did not exist.

Then:

DROP INDEX MYLIB.MY_LONG_TABLE_NAME;

Not found.

And:

DROP VIEW MYLIB.MY_LONG_TABLE_NAME;

Also not found.

That produced the key contradiction:

The create path believed the name was occupied.
The drop path could not resolve an SQL object with that name.

That is a strong signal to stop repeating DROP statements and inspect a different metadata layer.

Look at the IBM i object layer

Db2 for i is integrated into the IBM i object model. An SQL table is also represented as an IBM i *FILE object.

QSYS2.OBJECT_STATISTICS lets us inspect that object layer through SQL.

A targeted check is:

SELECT
    OBJNAME,
    OBJLONGNAME,
    OBJTYPE,
    OBJATTRIBUTE,
    SQL_OBJECT_TYPE,
    OBJLONGSCHEMA
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*FILE',
        'MY_LONG_TABLE_NAME'
    )
);

For database files, IBM documents that the object-name parameter can be the long SQL name or the system name.

You can also scan the files in the library and compare OBJLONGNAME:

SELECT
    OBJNAME,
    OBJLONGNAME,
    OBJTYPE,
    OBJATTRIBUTE,
    SQL_OBJECT_TYPE,
    OBJLONGSCHEMA
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*FILE'
    )
)
WHERE UPPER(OBJLONGNAME) =
      UPPER('MY_LONG_TABLE_NAME');

This is a useful bridge between SQL metadata and native IBM i objects.

But the breakthrough in this incident came from the database cross-reference itself.

The query that found the supposedly missing table name

The following query returned a row:

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

The normal SQL catalog did not contain the table, but QSYS.QADBXREF still contained cross-reference information associated with the long file name.

That changed the diagnosis completely.

What is QADBXREF?

QSYS.QADBXREF is part of the database-maintained cross-reference information on IBM i.

IBM Support documents the relationship between several familiar SYSTABLES columns and the underlying QADBXREF fields:

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

For a conventionally named library, DBXLIB and DBXLB2 will often look the same. They represent the system and SQL sides of the schema name relationship.

A catalog-verification query can therefore also be written as:

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

For ordinary, unquoted identifiers, use uppercase names. If the object was created with a delimited mixed-case SQL identifier, preserve the exact case when verifying catalog information.

What the mismatch told us

The system was effectively in a state like this:

Db2 SQL catalog
    |
    +-- MY_LONG_TABLE_NAME -> no row

IBM i object/catalog view
    |
    +-- no normal object resolved for the SQL name

Database cross-reference
    |
    +-- MY_LONG_TABLE_NAME -> row still present

That does not prove the original event that caused the inconsistency.

It does prove that the cross-reference information was not aligned with the database object information expected by the DDL operation.

That distinction is important.

It would be easy to say that an interrupted deployment, restore, failed object operation, or abnormal job definitely caused the problem. Without evidence from the time the inconsistency was introduced, those are only possibilities.

The useful conclusion is narrower and stronger:

The database cross-reference information for the library needed to be rebuilt.

Do not repair QADBXREF with DELETE

Once the row is visible, the tempting response is to delete it directly.

Do not treat QSYS.QADBXREF like an application table.

It is database-maintained cross-reference information. A manual DELETE can create a different catalog inconsistency instead of repairing the existing one.

IBM provides supported reclaim functions for this purpose.

Check the cross-reference state

Before repairing a library, the supported diagnostic command is:

RCLDBXREF OPTION(*CHECK)

IBM documents the following messages during the check:

CPD32A7   Diagnostic message for a library known to have inconsistent data
CPF32AC   Escape message when cross-reference problems are found
CPC32AC   Completion message when the cross-reference data appears correct

The job log is therefore part of the diagnosis.

Repair the affected library

For this incident, the command that resolved the problem was:

RCLDBXREF OPTION(*FIX) LIB(MYLIB)

IBM describes RCLDBXREF as a way to recover database cross-reference catalog data for a specific library without requiring the system to be in restricted state.

After the command completed:

the stale cross-reference entry was gone
the DDL was run again
CREATE OR REPLACE TABLE succeeded
the new table appeared normally in the SQL catalog

That closed the loop.

RCLDBXREF is not routine cleanup

The fact that RCLDBXREF fixed the problem does not mean it should become a scheduled maintenance command.

IBM explicitly positions it for database cross-reference problems.

When OPTION(*FIX) is used:

applications should not use or modify objects in the library being reclaimed
the reclaim should not be interrupted
the operation can take time depending on the number of database files and fields involved
some conditions may still require RCLSTG SELECT(*DBXREF)

RCLSTG SELECT(*DBXREF) is a broader operation and requires restricted state. It should not be the first reaction to one suspicious table name.

If cross-reference inconsistencies keep returning, the real task is to determine why the system is repeatedly entering that state.

A reusable troubleshooting workflow

The next time Db2 says an object exists but SQL cannot find it, this is the sequence I would use:

1. Capture the exact SQL and job-log messages.

2. Check QSYS2.SYSTABLES.

3. Check QSYS2.SYSINDEXES.

4. Use QSYS2.SYSVIEWS when view-specific detail is needed.

5. Try the appropriate DROP only after identifying the object type.

6. Check QSYS2.OBJECT_STATISTICS for the underlying IBM i object.

7. Compare the name with QSYS.QADBXREF.

8. Run RCLDBXREF OPTION(*CHECK).

9. If the evidence supports it, repair the affected library with
   RCLDBXREF OPTION(*FIX) LIB(MYLIB).

10. Re-query the catalogs before retrying the DDL.

The important principle is to move down the stack only when the previous layer does not explain the result.

Three layers worth remembering

This incident is a useful reminder that database troubleshooting on IBM i spans more than one interface.

SQL catalog
    QSYS2.SYSTABLES
    QSYS2.SYSINDEXES
    QSYS2.SYSVIEWS

IBM i object layer
    QSYS2.OBJECT_STATISTICS
    WRKOBJ / DSPFD / DSPOBJD

Database cross-reference
    QSYS.QADBXREF
    RCLDBXREF

Each layer answers a different question.

The SQL catalog answers:

What database object does Db2 expose under this SQL name?

The object layer answers:

What native IBM i object exists in the library?

The cross-reference answers:

What database name mapping does IBM i still maintain for the file?

When all three agree, nobody notices the distinction.

When they do not, the distinction becomes the investigation.

This troubleshooting path is broken into three reusable Era of i SQL Way entries:

Final takeaway

The strangest part of this incident was not the fix.

It was the apparently impossible combination:

CREATE -> already exists
DROP   -> does not exist

On IBM i, that can be a clue rather than a contradiction.

The normal SQL catalog may no longer resolve the object while database cross-reference information still contains the name. In this case, QSYS.QADBXREF exposed that mismatch and RCLDBXREF OPTION(*FIX) rebuilt the affected library’s cross-reference data.

The broader lesson is simple:

When Db2 for i metadata appears contradictory, identify which layer is answering the question before deciding which answer is wrong.

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.

When Db2 for i Says a Table Exists - But You Cannot Find It

The original failure

First check: the Db2 for i catalog

Try the DROP statements anyway

Look at the IBM i object layer

The query that found the supposedly missing table name

What is QADBXREF?

What the mismatch told us

Do not repair QADBXREF with DELETE

Check the cross-reference state

Repair the affected library

RCLDBXREF is not routine cleanup

A reusable troubleshooting workflow

Three layers worth remembering

Related SQL Way guides

Final takeaway

References

Comments