IBM i: The SQL Way · #22

Inspect IBM i Objects with QSYS2.OBJECT_STATISTICS

Use QSYS2.OBJECT_STATISTICS to query native IBM i objects, connect long SQL names to system object names, filter by object type, and troubleshoot objects that are difficult to locate through SQL catalogs alone.

Saurabh Arora

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

Published August 25, 20265 min read

Related native optionWRKOBJ, DSPOBJD, and DSPFD

QSYS2.OBJECT_STATISTICS makes the IBM i object model queryable through SQL. It can list objects in a library, filter by native object type, return the 10-character system name, expose a long SQL name when one exists, and provide object attributes that are normally inspected through commands such as WRKOBJ or DSPOBJD.

The table function is:

QSYS2.OBJECT_STATISTICS

It is especially useful when the question is broader than:

What table does Db2 know about?

and becomes:

What object actually exists in this IBM i library?

Basic query

List all objects in one library:

SELECT *
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*ALL'
    )
);

For most troubleshooting, selecting only the useful columns is easier to read:

SELECT
    OBJNAME,
    OBJLONGNAME,
    OBJTYPE,
    OBJATTRIBUTE,
    OBJOWNER,
    OBJCREATED,
    OBJSIZE,
    OBJTEXT,
    SQL_OBJECT_TYPE
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*ALL'
    )
)
ORDER BY
    OBJTYPE,
    OBJNAME;

This gives a SQL inventory of native IBM i objects in the library.

Filter by IBM i object type

The second parameter accepts IBM i object types.

To list only files:

SELECT
    OBJNAME,
    OBJLONGNAME,
    OBJATTRIBUTE,
    SQL_OBJECT_TYPE,
    OBJTEXT
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*FILE'
    )
)
ORDER BY OBJNAME;

To list programs:

SELECT
    OBJNAME,
    OBJTYPE,
    OBJATTRIBUTE,
    OBJOWNER,
    OBJCREATED,
    OBJTEXT
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*PGM'
    )
)
ORDER BY OBJNAME;

The function supports many native object types, which makes it useful well beyond database files.

Find one object by system name

Use the optional third parameter to avoid scanning every object in the library:

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

For a targeted lookup, this is preferable to returning the entire library and filtering afterward.

Find a database file by its long SQL name

For files and libraries, IBM documents that the object-name parameter can use either the long SQL name or the short system name.

That means this can locate a table created with a long SQL name:

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

A result can look conceptually like:

OBJNAME      OBJLONGNAME                    SQL_OBJECT_TYPE
-----------  -----------------------------  ---------------
CUSTOM00001  CUSTOMER_TRANSACTION_HISTORY   TABLE

The exact generated system name can vary.

This is one of the most useful reasons to keep OBJECT_STATISTICS available during DDL troubleshooting.

OBJNAME versus OBJLONGNAME

The distinction is fundamental on IBM i:

OBJNAME      Native IBM i system object name
OBJLONGNAME  SQL name when a long SQL name is available

For many traditional IBM i objects, OBJLONGNAME may be null or may not add anything useful.

For SQL-created database objects, it can provide the bridge between what a developer sees in DDL and what an administrator sees through native object commands.

SQL_OBJECT_TYPE adds database context

For *FILE objects, SQL_OBJECT_TYPE can help distinguish the SQL role of the object.

For example, values can identify objects such as:

TABLE
VIEW
INDEX

This matters because multiple database constructs can be represented as IBM i *FILE objects even though they have different meanings in SQL.

A compact database-file inventory is:

SELECT
    OBJNAME AS SYSTEM_NAME,
    OBJLONGNAME AS SQL_NAME,
    OBJATTRIBUTE,
    SQL_OBJECT_TYPE,
    OBJSIZE,
    OBJTEXT
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*FILE'
    )
)
ORDER BY
    SQL_OBJECT_TYPE,
    SQL_NAME,
    SYSTEM_NAME;

Find objects by a generic system name

The third parameter can also accept a generic name ending in *:

SELECT
    OBJNAME,
    OBJTYPE,
    OBJATTRIBUTE,
    OBJTEXT
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*ALL',
        'ORD*'
    )
);

There is an important limitation.

When a generic object name is used, IBM documents that long SQL names are not included in the generic-name search.

So:

'ORD*'

is a generic search against native system object names, not a wildcard search across every long SQL name.

If you need pattern matching on OBJLONGNAME, return the relevant object type and filter the result:

SELECT
    OBJNAME,
    OBJLONGNAME,
    SQL_OBJECT_TYPE
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*FILE'
    )
)
WHERE UPPER(OBJLONGNAME) LIKE 'CUSTOMER%';

Use *ALLSIMPLE when you only need library names

*ALLSIMPLE is a special value for the object-schema parameter when the requested object type is *LIB. It is optimized for retrieving library names; it is not a shortcut for listing every object inside one library.

For example:

SELECT
    OBJNAME,
    OBJLONGNAME,
    OBJTYPE,
    OBJLIB,
    OBJLONGSCHEMA,
    IASP_NUMBER,
    IASP_NAME
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        '*ALLSIMPLE',
        '*LIB'
    )
);

IBM documents that this form returns only a limited set of columns; other detail columns are null.

Use it when the question is:

Which libraries are available, and what are their system and long names?

For an inventory of objects inside one application library, continue to use the normal library name with the required object type, such as:

SELECT
    OBJNAME,
    OBJLONGNAME,
    OBJTYPE
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*ALL'
    )
);

Find the largest objects in a library

OBJECT_STATISTICS also makes size analysis straightforward:

SELECT
    OBJNAME,
    OBJLONGNAME,
    OBJTYPE,
    OBJATTRIBUTE,
    DECIMAL(
        OBJSIZE / 1024.0 / 1024.0,
        15,
        2
    ) AS SIZE_MB,
    OBJTEXT
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*ALL'
    )
)
ORDER BY OBJSIZE DESC
FETCH FIRST 20 ROWS ONLY;

This is useful for quick library investigations, although specialized services may be better for detailed storage analysis by library, IASP, or object category.

Find objects by creation time

SELECT
    OBJNAME,
    OBJLONGNAME,
    OBJTYPE,
    OBJATTRIBUTE,
    OBJCREATED,
    OBJOWNER
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'MYLIB',
        '*ALL'
    )
)
WHERE OBJCREATED >= CURRENT TIMESTAMP - 1 DAY
ORDER BY OBJCREATED DESC;

This can help after a deployment when you want to identify objects created recently in a target library.

Authority matters

OBJECT_STATISTICS returns information according to the caller’s authority.

For non-user-profile objects, the caller needs access to the library and sufficient authority to the objects for full details. Depending on the release and authority configuration, partial information can be returned with warning SQLSTATE 01548 instead of every requested attribute.

So an empty or incomplete result should always be interpreted in context:

Does the object not exist?

or

Can this profile not see it?

That distinction matters in support tooling and automated inventories.

OBJECT_STATISTICS versus SYSTABLES

These two services overlap for database objects, but they are not the same tool.

Use QSYS2.SYSTABLES when you need SQL database metadata such as:

TABLE_TYPE
TABLE_SCHEMA
TABLE_NAME
SYSTEM_TABLE_NAME
base table information
SQL table properties

Use QSYS2.OBJECT_STATISTICS when you need native IBM i object information such as:

OBJTYPE
OBJATTRIBUTE
OBJOWNER
OBJCREATED
OBJSIZE
OBJTEXT
native object inventory

A useful troubleshooting sequence is:

SYSTABLES
    |
    v
OBJECT_STATISTICS

The first asks what Db2 knows about the SQL object.

The second asks what IBM i object exists underneath or alongside it.

Native commands versus SQL

Use WRKOBJ when:

you need an interactive object list
you are already on a green-screen session
you want option-based object actions

Use DSPOBJD when:

you need detailed native object-description information
you are investigating one known object

Use DSPFD when:

the object is a database file
record formats, members, access paths, or file-level details are required

Use OBJECT_STATISTICS when:

results need to be filtered or sorted
long and system names need to be compared
data must be saved, joined, or reported
the check will become part of automation
multiple objects need to be analyzed consistently

The strongest IBM i troubleshooting workflows use both interfaces.

When OBJECT_STATISTICS still does not explain the problem

Suppose a DDL statement says a long table name already exists, but:

QSYS2.SYSTABLES          -> no matching row
QSYS2.SYSINDEXES         -> no matching row
QSYS2.OBJECT_STATISTICS  -> no normal matching object

At that point, inspect the database cross-reference layer:

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

For the catalog layer first, see:

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

The real incident connecting all three layers is:

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

Final takeaway

QSYS2.OBJECT_STATISTICS is one of the most broadly useful IBM i Services because it turns native object information into relational data.

Remember the core pattern:

SELECT ...
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'LIBRARY',
        '*OBJECT_TYPE',
        'OPTIONAL_OBJECT_NAME'
    )
);

For database troubleshooting, pay particular attention to:

OBJNAME          native system name
OBJLONGNAME      long SQL name
OBJTYPE          IBM i object type
OBJATTRIBUTE     object attribute
SQL_OBJECT_TYPE  SQL role for applicable objects

That combination makes it much easier to move between modern SQL DDL and the native IBM i object model underneath it.

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.