Developer ToolsFree generatorPDF, Excel, Word exportOracle JD Edwards WorldAll JD Edwards World tools

Db2 for i SQL Services Query Generator (IBM i Health Queries)

Db2 for i includes SQL services in the QSYS2 schema that return system information as rows, so you can check object sizes, locks and jobs without writing CL. This generator builds the common queries for a library you name.

Fill in

Library (schema)

The data library to inspect.

Object type

Subsystem name

Subsystem to filter active jobs.

Rows to return

Generated code

Free, optional. Reopen it on any device.

Download:

Largest objects in the library

SELECT OBJNAME, OBJTYPE, OBJSIZE, LAST_USED_TIMESTAMP
FROM TABLE(QSYS2.OBJECT_STATISTICS('PRODDTA', '*FILE')) AS O
ORDER BY OBJSIZE DESC
FETCH FIRST 25 ROWS ONLY;

Tables with the most rows

SELECT TABLE_SCHEMA, TABLE_NAME, NUMBER_ROWS, DATA_SIZE
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'PRODDTA'
ORDER BY NUMBER_ROWS DESC
FETCH FIRST 25 ROWS ONLY;

Active jobs in a subsystem by CPU

SELECT JOB_NAME, AUTHORIZATION_NAME, SUBSYSTEM, JOB_STATUS, CPU_TIME
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(SUBSYSTEM_LIST_FILTER => 'QBATCH')) AS J
ORDER BY CPU_TIME DESC
FETCH FIRST 25 ROWS ONLY;

Locks on objects in the library

SELECT SYSTEM_OBJECT_SCHEMA, SYSTEM_OBJECT_NAME, OBJECT_TYPE, LOCK_STATE, JOB_NAME
FROM QSYS2.OBJECT_LOCK_INFO
WHERE SYSTEM_OBJECT_SCHEMA = 'PRODDTA';

Disabled user profiles

SELECT AUTHORIZATION_NAME, STATUS, PREVIOUS_SIGNON
FROM QSYS2.USER_INFO
WHERE STATUS = '*DISABLED'
ORDER BY PREVIOUS_SIGNON;

System status snapshot

SELECT *
FROM QSYS2.SYSTEM_STATUS_INFO;

These services need a supported IBM i release (most need 7.3 or later) with current Db2 PTF groups, and authority to the objects queried. Column lists differ slightly by release, so run a SELECT * once and adjust the column list if a column is missing.

Plain-English questions over your IBM i data

Optional: a read-only assistant that turns questions into queries like these, shows the SQL, and runs on your own server.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Enter the data library that holds your JD Edwards World files, pick an object type and a subsystem, then copy the queries into STRSQL, Run SQL Scripts in IBM i Access Client Solutions or any SQL client with a JDBC or ODBC connection to the partition. Run them one at a time and export the results to a spreadsheet when you need to share them. All six queries are read-only.

What each query tells you

OBJECT_STATISTICS shows size and last used date, which is how you find unused files and fast growing objects. SYSTABLESTAT shows row counts and data size from the catalog statistics. ACTIVE_JOB_INFO shows live jobs and CPU use. OBJECT_LOCK_INFO shows who holds locks, useful when a nightly job waits on a file. USER_INFO helps clean up profiles that are disabled or unused.

OBJECT_STATISTICS: size and last used
SYSTABLESTAT: rows and data size
ACTIVE_JOB_INFO: live job CPU
OBJECT_LOCK_INFO: lock holders
USER_INFO: profile status

Reading the results

A file with millions of rows and a last used date several years ago is a candidate for archive. A job in a batch subsystem with high CPU time may be looping. Locks held by an interactive job on a file a batch job needs explain many late nightly runs. NUMBER_ROWS counts include deleted rows in some cases, so reorganize files before treating counts as exact.

Common mistakes

Running table functions with a library name in the wrong case or with a missing quote returns an empty set rather than an error. Using system names instead of SQL names for long named tables returns surprises. Do not run these on a busy production system with very broad filters during peak hours, and test on a non-production partition before putting them in a scheduled job.

Frequently Asked Questions

Which IBM i release do I need?

Most of these services exist from IBM i 7.3 onward, and newer releases add more columns and services. If a view or function is missing, check your Db2 for i PTF group level against the IBM documentation before assuming it is unsupported.

Do these queries change any data?

No. All six are SELECT statements over system views and table functions. They only need read authority to the objects, although some views show more rows to users with special authorities like all-object.

Why are my row counts different from COUNT(*)?

SYSTABLESTAT values come from catalog statistics and can include deleted records until a reorganization. Use SELECT COUNT(*) FROM your table when you need an exact figure, especially for reconciliation.

Can I use these with JD Edwards World files?

Yes. World files are Db2 for i physical files, so the same services work. Use the data library name such as your production data library. JD Edwards file names with short system names may need quoting.

If you want these checks combined with ERP data into one question and answer interface that stays on your hardware, talk to us about a private build.

Talk to an Expert Explore More Free Tools