IBM i SQL Services for Everyday Administration

Who this is for: IBM i operators and administrators who already know WRKACTJOB, WRKPTFGRP, and WRKDSKSTS/WRKCFGSTS, and who want to run the same questions as SQL — scriptable, joinable, and schedulable — rather than reading a 5250 screen. This article does not repeat the query-performance SQL services (plan cache, STRDBMON, index advice) already covered in Db2 for i Query Optimization — it covers a distinct set: active job monitoring, PTF/group-PTF status, and ASP/storage capacity.

Verification level: Every view/function signature and column below is checked against current IBM Documentation or an IBM Support technote (see the References section near the end of this article) — IBM DOC CHECKED. A couple of release-timing details could only be confirmed from a secondary source rather than the SQL reference itself; those are called out individually, and you should confirm them with SELECT * FROM <view> FETCH FIRST 1 ROW ONLY or DESCRIBE on your own system before relying on them. No query below was run against a live system — this is a reference, not an execution log.

QSYS2.ACTIVE_JOB_INFO — active jobs as a table function

ACTIVE_JOB_INFO is a table function (not a plain view, because it takes parameters) that returns one row per active job. Confirmed against IBM’s current IBM i documentation, its description is that it returns information “similar to what the Work with Active Jobs (WRKACTJOB) command and the List Job (QUSLJOB) API” show.

Signature

QSYS2.ACTIVE_JOB_INFO(
  RESET_STATISTICS         => reset-statistics,
  SUBSYSTEM_LIST_FILTER    => subsystem-list-filter,
  JOB_NAME_FILTER          => job-name-filter,
  CURRENT_USER_LIST_FILTER => current-user-list-filter,
  DETAILED_INFO            => detailed-info
)

A few of these need more explanation than the table allows: RESET_STATISTICS => 'YES' resets the elapsed-statistics baseline for this connection (the first call always resets, as does changing the subsystem or job-name filter); JOB_NAME_FILTER => '*SYS' requires no subsystem filter; and each step up in DETAILED_INFO returns more columns at the cost of being slower to collect.

Example — jobs in MSGW status, no special authority needed

-- Which jobs are currently stuck waiting for a reply?
SELECT JOB_NAME, JOB_USER, JOB_STATUS, SUBSYSTEM, FUNCTION
FROM   TABLE(QSYS2.ACTIVE_JOB_INFO()) X
WHERE  JOB_STATUS = 'MSGW';

This pairs well with the runbook pattern in Troubleshooting an IBM i Job Stuck in MSGW — the SQL above finds every job in that state across the system in one query, instead of scanning WRKACTJOB screen by screen.

Key columns

Confirmed column groups (names are the long SQL column names; the documentation also lists shorter system column names for each):

• Identity: JOB_NAME, JOB_NAME_SHORT, JOB_USER, JOB_NUMBER, SUBSYSTEM, SUBSYSTEM_LIBRARY_NAME, AUTHORIZATION_NAME.
• State: JOB_TYPE, FUNCTION_TYPE, FUNCTION, JOB_STATUS (for example RUN, MSGW, DSPW).
• Resource use: CPU_TIME, TOTAL_DISK_IO_COUNT, TEMPORARY_STORAGE, PEAK_TEMPORARY_STORAGE.
• Elapsed (since last reset): ELAPSED_TIME, ELAPSED_CPU_PERCENTAGE, ELAPSED_TOTAL_DISK_IO_COUNT.
• SQL-specific (requires elevated authority, see below): SQL_STATEMENT_TEXT — the text of the job’s current or last SQL statement.
• QTEMP (requires DETAILED_INFO => 'QTEMP'): QTEMP_SIZE — size of the job’s QTEMP library in MB.

Authority

Confirmed: basic information and DETAILED_INFO of NONE or WORK need no special authority, and a caller can always see jobs running under their own user profile. Beyond that:

• DETAILED_INFO => 'QTEMP' requires *JOBCTL special authority.
• DETAILED_INFO => 'FULL' or 'ALL' exposes the SQL-activity columns (from SQL_STATEMENT_TEXT onward) only to a caller authorized to the QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage IDs, or with *JOBCTL special authority (which grants access to all detailed columns regardless of function usage).

Release note: per an IBM Support technote, the FULL value of DETAILED_INFO was added later than the function itself, specifically to let callers skip columns that are comparatively slow to collect; the exact PTF/TR level for that addition is not independently confirmed here. Run DESCRIBE TABLE(QSYS2.ACTIVE_JOB_INFO()) or check your release’s own documentation if DETAILED_INFO => 'FULL' behaves unexpectedly on an older TR level.

QSYS2.GROUP_PTF_INFO — group PTF status without parsing 5250 screens

Confirmed by IBM’s own support page for this enhancement: GROUP_PTF_INFO “contains information about the group PTFs for the server,” and the information it returns “is similar to the information available from the Work with PTF Groups (WRKPTFGRP) command.”

Authority

Confirmed, same IBM support page: “the user must have *USE authority to the Work with PTF Groups (WRKPTFGRP) command” to query the view.

Columns

Confirmed directly from the IBM Documentation column table for the GROUP_PTF_INFO view (schema QSYS2, system name GRPPTFINFO):

PTF_GROUP_STATUS takes one of nine documented values: UNKNOWN, NOT APPLICABLE, SUPPORTED ONLY, NOT INSTALLED, INSTALLED, ERROR, APPLY AT NEXT IPL, RELATED GROUP, or ON ORDER.

PTF_GROUP_APPLY_TIMESTAMP is null if any PTF in the group is not applied. It was added in IBM i 7.6 SF99960 Level 2 (also delivered as 7.6 TR1 / 7.5 TR7); on an older release this column may not exist.

Example

IBM’s own documentation example, which finds the level of the latest cumulative PTF group installed on the system:

SELECT MAX(PTF_GROUP_LEVEL) AS CUM_LEVEL
FROM   QSYS2.GROUP_PTF_INFO
WHERE  PTF_GROUP_NAME IN ('SF99610', 'SF99710')
  AND  PTF_GROUP_STATUS = 'INSTALLED';

SF99610 and SF99710 are the cumulative PTF group IDs for IBM i; other group PTFs (Db2 for i, HIPER, Technology Refresh, and similar) use their own IDs, which vary by release — confirm the current ID for your release from IBM’s Fix Central or your own WRKPTFGRP output rather than assuming any one ID shown here is current.

QSYS2.ASP_INFO — auxiliary storage pool capacity and status

Confirmed: ASP_INFO returns one row per auxiliary storage pool (system ASP, user ASPs, and independent ASPs), with values described as similar to what the WRKCFGSTS command and the QYASPOL (Open List of ASPs) API return.

Authority

Confirmed: the caller needs *IOSYSCFG special authority, or authorization to the QIBM_IOSYSCFG_VIEW function usage ID. Rows are returned only for device descriptions the caller has *USE authority to. A further subset of columns (the access-path-recovery columns, see below) additionally requires *JOBCTL special authority.

Example

-- Storage headroom per ASP, as a percentage used
SELECT ASP_NUMBER, ASP_TYPE,
       TOTAL_CAPACITY,
       TOTAL_CAPACITY - TOTAL_CAPACITY_AVAILABLE AS used_mb,
       DEC((TOTAL_CAPACITY - TOTAL_CAPACITY_AVAILABLE) * 100.0
           / NULLIF(TOTAL_CAPACITY, 0), 5, 2)     AS pct_used
FROM   QSYS2.ASP_INFO
ORDER BY ASP_NUMBER;

NULLIF(TOTAL_CAPACITY, 0) guards the division the same way described in SQL0802 on IBM i — if a row somehow reports zero total capacity, the percentage comes back null instead of raising a division-by-zero mapping error.

Key columns

Confirmed column list (selected columns most relevant to day-to-day capacity checks; the view has many more, mostly geographic-mirroring and access-path-recovery columns not reproduced here):

Release caveat: several of the geographic-mirroring columns documented for ASP_INFO (for example GEOGRAPHIC_MIRROR_ROLE, GEOGRAPHIC_MIRROR_MODE) are only meaningful on partitions using IBM’s geographic mirroring feature; they return null otherwise. This article does not cover those columns in detail — see IBM i High Availability: Geographic Mirroring for that topic.

Putting them together — a quick daily health query

A single script, combining all three services, suitable as the starting point for a scheduled monitoring job:

-- 1. Any job currently waiting for a message reply?
SELECT 'ACTIVE_JOB_INFO' AS source, JOB_NAME AS detail, JOB_STATUS AS status
FROM   TABLE(QSYS2.ACTIVE_JOB_INFO()) X
WHERE  JOB_STATUS = 'MSGW'

UNION ALL

-- 2. Any ASP above its configured storage threshold?
SELECT 'ASP_INFO', CHAR(ASP_NUMBER),
       DEC((TOTAL_CAPACITY - TOTAL_CAPACITY_AVAILABLE) * 100.0
           / NULLIF(TOTAL_CAPACITY, 0), 5, 2) CONCAT '% used'
FROM   QSYS2.ASP_INFO
WHERE  (TOTAL_CAPACITY - TOTAL_CAPACITY_AVAILABLE) * 100.0
         / NULLIF(TOTAL_CAPACITY, 0) >= STORAGE_THRESHOLD_PERCENTAGE

UNION ALL

-- 3. Is the Db2 for i group PTF installed at all? (illustrative group ID)
SELECT 'GROUP_PTF_INFO', PTF_GROUP_NAME, PTF_GROUP_STATUS
FROM   QSYS2.GROUP_PTF_INFO
WHERE  PTF_GROUP_NAME = 'SF99738'
  AND  PTF_GROUP_STATUS <> 'INSTALLED';

This is STATIC CHECKED, not executed — confirm the UNION ALL branches’ data types line up (the script casts everything to character for display) and substitute the correct group PTF ID for your release before scheduling it.

Common mistakes

• Assuming ACTIVE_JOB_INFO needs no authority at all levels. The base columns are open, but SQL_STATEMENT_TEXT and the other SQL-activity columns need QIBM_DB_SQLADM/QIBM_DB_SYSMON or *JOBCTL — a query that works for an administrator may return nulls or an authority error for an ordinary user.
• Querying GROUP_PTF_INFO without *USE authority to WRKPTFGRP and assuming an empty result means “no group PTFs installed” rather than “not authorized.”
• Treating -2 in a storage column as a real value. IBM documents -2 as a sentinel meaning “too large to return,” not an actual negative capacity — code that sums or compares these columns should guard against it.
• Dividing by TOTAL_CAPACITY without a NULLIF guard. A null or zero capacity value will raise a data mapping error (see SQL0802 on IBM i) instead of a clean result.
• Hard-coding a group PTF ID that was correct for one release and assuming it is correct for every release — group PTF numbers are release- and product-specific.

FAQ

Does ACTIVE_JOB_INFO replace WRKACTJOB?

Not for interactive triage — it is documented as returning comparable information, but as SQL it is far better suited to filtering, joining, and scheduled monitoring than to a human scanning a live screen.

Why would GROUP_PTF_INFO return zero rows for me?

Most commonly, missing *USE authority to the WRKPTFGRP command, which IBM’s support page states is required to query the view — or simply no group PTFs installed on the system.

What does -2 mean in an ASP_INFO capacity column?

IBM documents -2 as the sentinel for “value too large to return” in the capacity columns (TOTAL_CAPACITY, TOTAL_CAPACITY_AVAILABLE, and related columns) — treat it as “not available,” not as a literal number.

Does this article replace the STRDBMON/plan cache article?

No — this article intentionally covers job, PTF, and storage administration views; query performance via STRDBMON, the SQL plan cache, and Visual Explain is covered separately in Db2 for i Query Optimization.

References

• IBM i 7.5: ACTIVE_JOB_INFO table function — the full parameter list and defaults; the key output columns listed above; the authority rules for the WORK/QTEMP/FULL/ALL detail levels, including the QIBM_DB_SQLADM/QIBM_DB_SYSMON function-usage requirement and the *JOBCTL override.
• IBM i 7.6: GROUP_PTF_INFO view — the complete column list and data types reproduced above, the nine documented PTF_GROUP_STATUS values, the *USE authority requirement to WRKPTFGRP, and the worked MAX(PTF_GROUP_LEVEL) example reproduced verbatim above.
• IBM Support: GROUP_PTF_INFO enhancement history — corroborates the PTF_GROUP_APPLY_TIMESTAMP addition and its “IBM i 7.6 SF99960 Level 2” release label against the IBM Docs page above.
• IBM i: ASP_INFO view — the full column list reproduced in part above, including ASP_NUMBER ranges, the -2 sentinel on capacity columns, and the authority rules (*IOSYSCFG/QIBM_IOSYSCFG_VIEW, *USE on the device description, *JOBCTL for the access-path-recovery columns).

Two details are not independently confirmed beyond what’s cited above: the exact PTF/TR level at which DETAILED_INFO => 'FULL' was added to ACTIVE_JOB_INFO, and full reconciliation of the “7.6 SF99960 Level 2” versus “7.6 TR1 / 7.5 TR7” release labels for PTF_GROUP_APPLY_TIMESTAMP (IBM Support treats both labels as describing the same delivery, but this article did not independently verify that against IBM’s own release notes).

Internal links

• Db2 for i Query Optimization: Visual Explain, SQE vs CQE, Indexes, STRDBMON, Plan Cache
• Troubleshooting an IBM i Job Stuck in MSGW
• SQL0802 on IBM i: Data Conversion or Data Mapping Errors
• IBM i High Availability: Geographic Mirroring