IBM i: The SQL Way · #21

Find Db2 for i Tables, Indexes, and Views with SQL

Use QSYS2.SYSTABLES, QSYS2.SYSINDEXES, and QSYS2.SYSVIEWS to identify database objects, map long SQL names to IBM i system names, and troubleshoot object-name collisions.

Saurabh Arora

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

Published August 25, 20264 min read

Related native optionWRKOBJ and DSPFD

When an IBM i database object is missing, duplicated, or blocking a DDL deployment, the Db2 for i catalog is usually the best place to start. QSYS2.SYSTABLES, QSYS2.SYSINDEXES, and QSYS2.SYSVIEWS make the SQL object model queryable and also expose the relationship between long SQL names and 10-character IBM i system names.

The three catalog views answer related but different questions:

QSYS2.SYSTABLES   What tables, views, aliases, physical files,
                  and logical files are known to Db2?

QSYS2.SYSINDEXES  What SQL indexes exist, and which tables
                  do they belong to?

QSYS2.SYSVIEWS    What SQL views exist, and what are their
                  view definitions and properties?

Start with SYSTABLES

To find one database object by SQL name:

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

SYSTABLES contains one row for each table, view, or alias in an SQL schema. On IBM i, it also exposes physical and logical files through TABLE_TYPE.

Common values include:

A   Alias
L   Logical file
M   Materialized query table
P   Physical file
T   SQL table
V   SQL view

That makes SYSTABLES a useful first query even when you are not yet sure what type of database object owns a name.

Long SQL name versus system name

One of the most useful IBM i-specific column pairs is:

TABLE_NAME          SQL name
SYSTEM_TABLE_NAME   IBM i system name

For example, a table might be created as:

CREATE TABLE MYLIB.CUSTOMER_TRANSACTION_HISTORY
(
    TRANSACTION_ID BIGINT NOT NULL
);

The catalog can show a relationship similar to:

TABLE_NAME                     SYSTEM_TABLE_NAME
-----------------------------  -----------------
CUSTOMER_TRANSACTION_HISTORY   CUSTOM00001

The exact generated system name can vary.

The important point is that the SQL name and the native IBM i object name do not have to be the same.

To see both names for every table-like object in a library:

SELECT
    TABLE_NAME,
    SYSTEM_TABLE_NAME,
    TABLE_TYPE,
    TABLE_TEXT
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = 'MYLIB'
ORDER BY
    TABLE_TYPE,
    TABLE_NAME;

This is especially useful when a developer knows the long SQL name while an administrator is looking at WRKOBJ, DSPFD, or another native interface that emphasizes the system name.

Search without knowing the exact case

For ordinary unquoted SQL identifiers, names are normally stored in uppercase.

A troubleshooting query can use:

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

However, delimited identifiers are case-sensitive.

For example:

CREATE TABLE MYLIB."CustomerHistory" ...

creates a different SQL identifier from:

CUSTOMERHISTORY

When investigating an object created with quoted identifiers, preserve the exact case.

Find an SQL index

SYSTABLES does not replace SYSINDEXES for index information.

To find an index by name:

SELECT
    INDEX_SCHEMA,
    INDEX_NAME,
    SYSTEM_INDEX_SCHEMA,
    SYSTEM_INDEX_NAME,
    TABLE_SCHEMA,
    TABLE_NAME,
    SYSTEM_TABLE_NAME,
    IS_UNIQUE,
    COLUMN_COUNT,
    CREATED
FROM QSYS2.SYSINDEXES
WHERE INDEX_SCHEMA = 'MYLIB'
  AND INDEX_NAME = 'MY_INDEX_NAME';

The important name mapping is similar to tables:

INDEX_NAME          SQL index name
SYSTEM_INDEX_NAME   IBM i system index name

The base table is also included:

TABLE_SCHEMA
TABLE_NAME
SYSTEM_TABLE_NAME

This is useful during DDL troubleshooting because a name may already be owned by an SQL index even when there is no table with that name.

List every SQL index for a table

SELECT
    INDEX_NAME,
    SYSTEM_INDEX_NAME,
    IS_UNIQUE,
    COLUMN_COUNT,
    CREATED
FROM QSYS2.SYSINDEXES
WHERE TABLE_SCHEMA = 'MYLIB'
  AND TABLE_NAME = 'CUSTOMER'
ORDER BY INDEX_NAME;

This gives a compact inventory of the SQL indexes defined over one table.

For access-path analysis, index statistics, or optimizer work, use the more specialized catalog and IBM i services designed for those tasks. SYSINDEXES is primarily object-definition metadata.

Find an SQL view

Because SYSTABLES contains views, this query is enough to determine whether a view owns a particular name:

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

Use SYSVIEWS when you need view-specific information:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    SYSTEM_VIEW_SCHEMA,
    SYSTEM_VIEW_NAME,
    IS_UPDATABLE,
    IS_INSERTABLE_INTO,
    VIEW_DEFINITION
FROM QSYS2.SYSVIEWS
WHERE TABLE_SCHEMA = 'MYLIB'
  AND TABLE_NAME = 'MY_VIEW_NAME';

VIEW_DEFINITION can help answer a second question after the object is found:

What query actually defines this view?

Search for a name collision across the main catalog views

When a DDL statement says a name already exists, use one query to check the common SQL object types:

SELECT
    'TABLE/VIEW/ALIAS' AS SOURCE,
    TABLE_SCHEMA AS OBJECT_SCHEMA,
    TABLE_NAME AS OBJECT_NAME,
    SYSTEM_TABLE_NAME AS SYSTEM_NAME,
    TABLE_TYPE AS DETAIL
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = 'MYLIB'
  AND UPPER(TABLE_NAME) = UPPER('MY_OBJECT_NAME')

UNION ALL

SELECT
    'INDEX',
    INDEX_SCHEMA,
    INDEX_NAME,
    SYSTEM_INDEX_NAME,
    CASE
        WHEN IS_UNIQUE IN ('U', 'V') THEN 'UNIQUE'
        ELSE 'INDEX'
    END
FROM QSYS2.SYSINDEXES
WHERE INDEX_SCHEMA = 'MYLIB'
  AND UPPER(INDEX_NAME) = UPPER('MY_OBJECT_NAME');

This answers:

Does Db2 currently know an SQL database object with this name?

If a row is returned, inspect the object type before deciding whether to alter, replace, or drop it.

Search a whole schema

A schema inventory can be useful before a deployment:

SELECT
    TABLE_NAME AS OBJECT_NAME,
    SYSTEM_TABLE_NAME AS SYSTEM_NAME,
    CASE TABLE_TYPE
        WHEN 'T' THEN 'TABLE'
        WHEN 'V' THEN 'VIEW'
        WHEN 'A' THEN 'ALIAS'
        WHEN 'P' THEN 'PHYSICAL FILE'
        WHEN 'L' THEN 'LOGICAL FILE'
        WHEN 'M' THEN 'MQT'
        ELSE TABLE_TYPE
    END AS OBJECT_TYPE,
    LAST_ALTERED_TIMESTAMP
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = 'MYLIB'
ORDER BY
    OBJECT_TYPE,
    OBJECT_NAME;

For indexes:

SELECT
    INDEX_NAME AS OBJECT_NAME,
    SYSTEM_INDEX_NAME AS SYSTEM_NAME,
    TABLE_NAME,
    IS_UNIQUE,
    CREATED
FROM QSYS2.SYSINDEXES
WHERE INDEX_SCHEMA = 'MYLIB'
ORDER BY INDEX_NAME;

Together, these queries provide a useful pre-deployment inventory without parsing command output.

SQL catalog versus native IBM i commands

Use native commands when:

you are interactively inspecting one object
you need file-description details from DSPFD
you are working from a green-screen support session
you need a quick WRKOBJ view of a library

Use the SQL catalog when:

long and system names must be compared
object metadata needs to be filtered or joined
a deployment check must be repeatable
results need to be saved or reported
you want one query across many objects or schemas

SQL complements the native interfaces. It does not make them obsolete.

What if all of these queries return nothing?

That result is useful too.

If Db2 reports that an object name already exists but:

SYSTABLES   -> no row
SYSINDEXES  -> no row
SYSVIEWS    -> no row

do not keep issuing random DROP statements.

Move to the next layer.

Check the actual IBM i object inventory with:

Inspect IBM i Objects with QSYS2.OBJECT_STATISTICS

If the SQL catalog and object layer still do not explain the name collision, inspect the database cross-reference:

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

The full real-world incident is documented here:

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

Final takeaway

For most Db2 for i object questions, remember these three catalog views:

SYSTABLES   tables, views, aliases, physical files, logical files
SYSINDEXES  SQL indexes and their base tables
SYSVIEWS    view-specific metadata and definitions

And on IBM i, always pay attention to both sides of the name:

SQL name       -> up to 128 characters
System name    -> native IBM i object name

That simple distinction can turn an apparently missing object into an object you can actually locate, inspect, and troubleshoot.

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.