Listing Last Used Date / Creation Date for all Objects in a Library

Troubleshooting

Problem

Listing the last used date and creation date for all objects in a library.

Resolving The Problem

To get this information, you can use the new QSYS2.OBJECT_STATISTICS SQL function using this SQL statement:

SELECT OBJNAME,
       OBJTYPE,
       OBJCREATED,
       LAST_USED_TIMESTAMP
    FROM TABLE (
            QSYS2.OBJECT_STATISTICS('LIBNAME', '*ALL')
        )

NOTE: You can use '*ALL' for LIBNAME to get all library object information, however, it may take a while for this to run.

If you want to know the last change (altered) date of all files on the system, you can run the following query:

SELECT table_name,
       table_schema
    FROM qsys2/systables
    WHERE alteredts > '2018-10-30-12.00.00.000000'

NOTE: The query will return all tables that have been altered in the last 2 years (example was written on 10/30/2018).

If you do not want to use the new SQL functions, these are the instructions to dump the library information to a outfile and then run a query over that outfile:

Dump all objects in selected library to an outfile:

DSPOBJD OBJ(TESTLIBRARY/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE) OUTFILE(QGPL/OBJDOUT)

Start SQL so we can select the necessary fields in the outfile:

STRSQL

Paste the following:

SELECT ODOBNM,
       ODLBNM,
       ODOBTP,
       ODUDAT,
       ODCDAT
    FROM qgpl/OBJDOUT
    ORDER BY ODUDAT DESC

This will create a display with objects creation date and last used date. You can also print to an outfile by using F13 on the Enter SQL Statements screen and changing the session attributes to print.

[{"Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHAAA2","label":"Operating System"}],"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Type":"MASTER"}]

Historical Number

511418647

Was this topic helpful?

Document Information

Modified date:
02 October 2024

UID

nas8N1013284

Tips