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

> Source: https://netray.co/tools/db2-for-i-sql-services-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in a library, object type and subsystem and get a set of QSYS2 SQL services queries for sizes, row counts, jobs, locks and user profiles.

## Category

developer

## Erp

Oracle JD Edwards World

## Audience

- IBM i administrator
- JDE World DBA
- Developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Library (schema)

**Key:** library

**Type:** text

**Default:** PRODDTA

**Help:** The data library to inspect.

#### Object type

**Key:** object_type

**Type:** select

**Options:**

- *FILE
- *PGM
- *DTAARA
- *DTAQ
- *JRNRCV

**Default:** *FILE

#### Subsystem name

**Key:** subsystem

**Type:** text

**Default:** QBATCH

**Help:** Subsystem to filter active jobs.

#### Rows to return

**Key:** top_n

**Type:** number

**Default:** 25

**Blocks:**

#### Largest objects in the library

**Body:** SELECT OBJNAME, OBJTYPE, OBJSIZE, LAST_USED_TIMESTAMP
FROM TABLE(QSYS2.OBJECT_STATISTICS('{{library}}', '{{object_type}}')) AS O
ORDER BY OBJSIZE DESC
FETCH FIRST {{top_n}} ROWS ONLY;

#### Tables with the most rows

**Body:** SELECT TABLE_SCHEMA, TABLE_NAME, NUMBER_ROWS, DATA_SIZE
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = '{{library}}'
ORDER BY NUMBER_ROWS DESC
FETCH FIRST {{top_n}} ROWS ONLY;

#### Active jobs in a subsystem by CPU

**Body:** SELECT JOB_NAME, AUTHORIZATION_NAME, SUBSYSTEM, JOB_STATUS, CPU_TIME
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(SUBSYSTEM_LIST_FILTER => '{{subsystem}}')) AS J
ORDER BY CPU_TIME DESC
FETCH FIRST {{top_n}} ROWS ONLY;

#### Locks on objects in the library

**Body:** SELECT SYSTEM_OBJECT_SCHEMA, SYSTEM_OBJECT_NAME, OBJECT_TYPE, LOCK_STATE, JOB_NAME
FROM QSYS2.OBJECT_LOCK_INFO
WHERE SYSTEM_OBJECT_SCHEMA = '{{library}}';

#### Disabled user profiles

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

#### System status snapshot

**Body:** SELECT *
FROM QSYS2.SYSTEM_STATUS_INFO;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Which IBM i release do I need?

**Answer:** 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?

**Answer:** 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(*)?

**Answer:** 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?

**Answer:** 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.

## Search terms

db2 for i sql services, QSYS2 OBJECT_STATISTICS, ACTIVE_JOB_INFO sql, ibm i sql query active jobs, SYSTABLESTAT number_rows, ibm i sql health check, as400 sql find locked objects, QSYS2 USER_INFO disabled

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json