IBM i: The SQL Way · #1

Find Active IBM i Jobs Consuming the Most Temporary Storage

Use QSYS2.ACTIVE_JOB_INFO to find and rank active IBM i jobs by temporary-storage usage, then decide where the investigation should continue.

When IBM i temporary storage is increasing, one of the first questions should be: which active jobs are currently consuming the most?

IBM i provides several ways to investigate temporary storage.

A useful native starting point is:

WRKACTJOB SEQ(*TMPSTG)

This places the active jobs using the most temporary storage at the top of the display.

That is often exactly what is needed during an active incident.

But SQL gives us another useful view.

It lets us:

• select only the columns we need
• rank the jobs
• filter system jobs
• convert megabytes to gigabytes
• save or export the output
• join the result to other system information
• turn the query into a scheduled report or alert

The SQL query

Run this from ACS Run SQL Scripts:

SELECT
    JOB_NAME,
    AUTHORIZATION_NAME,
    JOB_TYPE,
    JOB_STATUS,
    SUBSYSTEM,
    FUNCTION_TYPE,
    FUNCTION AS CURRENT_FUNCTION,
    TEMPORARY_STORAGE AS TEMP_STORAGE_MB,
    DECIMAL(
        TEMPORARY_STORAGE / 1024.0,
        15,
        2
    ) AS TEMP_STORAGE_GB
FROM TABLE(
    QSYS2.ACTIVE_JOB_INFO(
        DETAILED_INFO => 'NONE'
    )
) AS J
WHERE JOB_TYPE <> 'SYS'
  AND COALESCE(TEMPORARY_STORAGE, 0) > 0
ORDER BY TEMPORARY_STORAGE DESC
FETCH FIRST 20 ROWS ONLY;

The result places the largest active temporary-storage consumers at the top.

What the columns tell us

JOB_NAME

The qualified job name:

job-number/job-user/job-name

This is the value needed when investigating the job through commands such as:

WRKJOB JOB(job-number/job-user/job-name)

AUTHORIZATION_NAME

The authorization identity currently associated with the job.

This may help identify the application, service account, or user connected to the work.

JOB_TYPE

The job type.

The query excludes SYS jobs so the result focuses on application and user workloads.

Remove that filter when system jobs also need to be reviewed.

JOB_STATUS

The current job status.

This can help distinguish a job that is actively running from one waiting for work, a message, a lock, or another resource.

SUBSYSTEM

The subsystem where the job is running.

This can quickly show whether the workload belongs to:

• batch processing
• interactive work
• database server jobs
• web or application servers
• another operational subsystem

FUNCTION_TYPE and CURRENT_FUNCTION

These columns provide information about what the job is currently doing when that information is available.

TEMP_STORAGE_MB and TEMP_STORAGE_GB

TEMPORARY_STORAGE is returned in megabytes.

The query keeps the original value and also converts it to gigabytes for easier reading.

Example result

JOB_NAME                  USER       STATUS   SUBSYSTEM   TEMP_MB   TEMP_GB
123456/APPUSER/BIGJOB     APPUSER    RUN      QBATCH      28672     28.00
123457/WEBUSER/QZDASOINIT WEBUSER    TIMW     QUSRWRK      8192      8.00
123458/REPORT/RPTJOB      REPORT     RUN      QBATCH       3072      3.00

The first job would immediately become the primary investigation target.

But the query does not prove that the job is defective.

It tells us where to investigate next.

What temporary storage can represent

Temporary storage can be allocated by:

• application processing
• SQL query processing
• sorts and temporary indexes
• internal database work
• temporary system objects
• large intermediate results
• other operating-system or licensed-program activity

A large value may be legitimate.

For example, a large report, extract, reorganization, or SQL query may temporarily require substantial working storage.

The important questions are:

Is the storage expected?
Is it still growing?
Is the job making progress?
Will the storage be released when the work completes?
Has this workload changed recently?

This is not the same as QTEMP size

Job temporary storage and the size of objects in the job’s QTEMP library should not be treated as the same value.

ACTIVE_JOB_INFO provides a separate QTEMP_SIZE column when called with:

DETAILED_INFO => 'QTEMP'

That level of detail requires appropriate job-control authority.

If the concern is specifically large objects in QTEMP, investigate that separately.

Add SQL statement information

When the high-storage job is running SQL, the next query can include SQL activity information:

SELECT
    JOB_NAME,
    AUTHORIZATION_NAME,
    JOB_STATUS,
    SUBSYSTEM,
    TEMPORARY_STORAGE AS TEMP_STORAGE_MB,
    DECIMAL(
        TEMPORARY_STORAGE / 1024.0,
        15,
        2
    ) AS TEMP_STORAGE_GB,
    SQL_STATEMENT_TEXT
FROM TABLE(
    QSYS2.ACTIVE_JOB_INFO(
        DETAILED_INFO => 'ALL'
    )
) AS J
WHERE JOB_TYPE <> 'SYS'
  AND COALESCE(TEMPORARY_STORAGE, 0) > 0
ORDER BY TEMPORARY_STORAGE DESC
FETCH FIRST 20 ROWS ONLY;

Access to SQL activity columns depends on the authority of the user running the query.

The SQL text may be unavailable when:

• the job is not performing SQL work
• the relevant SQL information is no longer available
• the caller does not have the required authority
• the job is performing non-SQL processing

Do not assume that a null SQL statement means the job is inactive.

Narrow the query to one subsystem

During a batch-processing incident, the query can be restricted to one subsystem:

SELECT
    JOB_NAME,
    AUTHORIZATION_NAME,
    JOB_STATUS,
    FUNCTION_TYPE,
    FUNCTION AS CURRENT_FUNCTION,
    TEMPORARY_STORAGE AS TEMP_STORAGE_MB,
    DECIMAL(
        TEMPORARY_STORAGE / 1024.0,
        15,
        2
    ) AS TEMP_STORAGE_GB
FROM TABLE(
    QSYS2.ACTIVE_JOB_INFO(
        SUBSYSTEM_LIST_FILTER => 'QBATCH',
        DETAILED_INFO => 'NONE'
    )
) AS J
WHERE COALESCE(TEMPORARY_STORAGE, 0) > 0
ORDER BY TEMPORARY_STORAGE DESC;

Replace QBATCH with the subsystem being investigated.

Find jobs above a threshold

To focus only on jobs using at least 1 GB:

SELECT
    JOB_NAME,
    AUTHORIZATION_NAME,
    JOB_STATUS,
    SUBSYSTEM,
    TEMPORARY_STORAGE AS TEMP_STORAGE_MB,
    DECIMAL(
        TEMPORARY_STORAGE / 1024.0,
        15,
        2
    ) AS TEMP_STORAGE_GB
FROM TABLE(
    QSYS2.ACTIVE_JOB_INFO(
        DETAILED_INFO => 'NONE'
    )
) AS J
WHERE JOB_TYPE <> 'SYS'
  AND TEMPORARY_STORAGE >= 1024
ORDER BY TEMPORARY_STORAGE DESC;

This threshold can be changed based on the size and normal workload of the system.

What to do after finding the job

Once a job stands out, investigate before taking action.

Useful next steps may include:

WRKJOB JOB(job-number/job-user/job-name)

Review:

• job status
• call stack
• job log
• open files
• locks
• current function
• SQL activity
• whether temporary storage is increasing
• whether the job is making progress

Also speak with the application owner when the job is part of a business process.

Native option versus SQL

The native command remains an excellent first response:

WRKACTJOB SEQ(*TMPSTG)

Use it when:

• you need an immediate interactive view
• you want to work directly with the job
• the system is under active operational pressure
• a green-screen interface is the fastest available option

Use SQL when:

• you want selected columns
• you want repeatable filters
• you want values converted or formatted
• you want to save a historical snapshot
• you want to export the result
• you want to schedule the check
• you want to join the data to other IBM i services

The two approaches complement each other.

Turning the query into monitoring

A scheduled process could store a snapshot when a job exceeds a defined threshold.

For example:

Capture time
Qualified job name
Authorization name
Subsystem
Job status
Temporary storage
Current function
Available SQL text

That historical data can help answer:

• Which jobs repeatedly consume large amounts of temporary storage?
• Does storage growth happen at a particular time?
• Did a deployment change the normal pattern?
• Is one user or subsystem repeatedly involved?
• Does the storage return to normal after the job ends?

This turns a one-time troubleshooting query into an operational control.

Final takeaway

When IBM i temporary storage is growing, begin with a simple question:

Which active jobs are currently consuming the most?

Use:

WRKACTJOB SEQ(*TMPSTG)

for the immediate native view.

Use QSYS2.ACTIVE_JOB_INFO when you need a result that can be filtered, formatted, saved, exported, or automated.

The query does not automatically identify the root cause.

It identifies where the investigation should begin.