IBM i Field Notes

IBM i High System ASP Usage: QRECOVERY Journal Receivers and an Open Transaction

A real IBM i troubleshooting case study showing how SQL identified unusually high QRECOVERY usage, how an unresolved transaction retained journal receivers, and how the storage was safely recovered.

An IBM i system reached almost 96% system ASP utilization. SQL helped identify QRECOVERY as one of the largest libraries, but the real cause was an unresolved commitment-control transaction that prevented IBM i from deleting recovery journal receivers.

I recently worked through an IBM i storage incident where the system ASP had reached a critical level.

The system showed:

Total capacity:       186.41 GB
Used capacity:        178.56 GB
Free capacity:          7.85 GB
System ASP used:       95.78%
Configured threshold:  90%

At that level, the immediate concern is understandable:

What can be deleted to recover storage?

But deleting objects should not be the first step.

The first step should be:

Where is the storage actually being used, and why?

In this incident, SQL services quickly helped identify the largest libraries on the system.

One result stood out:

QRECOVERY  44.54 GB

The investigation eventually showed that numerous journal receivers associated with the system journal QRECOVERY/QSQTTJRN were being retained because a job still had an unresolved commitment-control transaction.

Once the responsible job was identified and ended, IBM i resolved the transaction, automatically removed the receivers that were no longer needed, and released the occupied ASP storage.

The initial symptom

The system ASP had grown to approximately 96% utilization.

Using WRKSYSSTS, we could see that very little auxiliary storage remained available.

WRKSYSSTS

Important values to review include:

• System ASP
• Percentage of system ASP used
• Total auxiliary storage
• Current temporary storage used
• Available auxiliary storage

The first challenge was determining whether the growth came from:

• application libraries
• journal receivers
• save files
• spooled files
• performance data
• temporary objects
• system recovery objects
• another unexpected source

Rather than guessing, we began by measuring.

Step 1: Check ASP utilization with SQL

The system ASP can also be reviewed using the QSYS2.ASP_INFO SQL service.

SELECT
    ASP_NUMBER,
    ASP_TYPE,
    ASP_STATE,
    DECIMAL(
        TOTAL_CAPACITY / 1024.0,
        15,
        2
    ) AS TOTAL_GB,
    DECIMAL(
        (TOTAL_CAPACITY - TOTAL_CAPACITY_AVAILABLE) / 1024.0,
        15,
        2
    ) AS USED_GB,
    DECIMAL(
        TOTAL_CAPACITY_AVAILABLE / 1024.0,
        15,
        2
    ) AS FREE_GB,
    DECIMAL(
        (TOTAL_CAPACITY - TOTAL_CAPACITY_AVAILABLE)
        * 100.0
        / NULLIF(TOTAL_CAPACITY, 0),
        7,
        2
    ) AS PERCENT_USED,
    STORAGE_THRESHOLD_PERCENTAGE
FROM QSYS2.ASP_INFO
ORDER BY PERCENT_USED DESC;

This gives a queryable view of:

• total capacity
• used capacity
• available capacity
• percentage used
• configured storage threshold

The advantage of SQL is that the information can be sorted, exported, scheduled, or incorporated into a monitoring utility.

Step 2: Find the largest libraries

The next question was:

Which libraries are consuming the most storage?

The following query uses QSYS2.OBJECT_STATISTICS to retrieve the system libraries and then calls QSYS2.LIBRARY_INFO for each library.

WITH LIBRARIES AS
(
    SELECT OBJNAME AS LIBRARY_NAME
    FROM TABLE(
        QSYS2.OBJECT_STATISTICS('*ALLSIMPLE', '*LIB')
    ) AS LIB
)
SELECT
    L.LIBRARY_NAME,
    I.OBJECT_COUNT,
    DECIMAL(
        I.LIBRARY_SIZE / 1000000000.0,
        15,
        2
    ) AS SIZE_GB,
    I.LIBRARY_SIZE_COMPLETE,
    I.LIBRARY_TYPE,
    I.TEXT_DESCRIPTION
FROM LIBRARIES AS L,
     LATERAL
     (
         SELECT *
         FROM TABLE(
             QSYS2.LIBRARY_INFO(
                 LIBRARY_NAME  => L.LIBRARY_NAME,
                 IGNORE_ERRORS => 'YES',
                 DETAILED_INFO => 'LIBRARY_SIZE'
             )
         )
     ) AS I
WHERE I.IASP_NUMBER = 1
  AND L.LIBRARY_NAME <> 'QHASM'
ORDER BY I.LIBRARY_SIZE DESC
FETCH FIRST 30 ROWS ONLY;

The largest libraries found during this investigation included:

QPFRDATA    50.12 GB
QRECOVERY   44.54 GB
QGPL         8.43 GB
QRPLOBJ      6.35 GB

QPFRDATA also required a separate retention review.

But QRECOVERY was unusually large and became the immediate focus of the investigation.

Step 3: Find the largest objects in QRECOVERY

Once QRECOVERY was identified, the next step was to determine which objects were consuming the storage.

SELECT
    OBJNAME,
    OBJTYPE,
    OBJATTRIBUTE,
    OBJOWNER,
    DECIMAL(
        OBJSIZE / 1000000000.0,
        15,
        2
    ) AS SIZE_GB,
    OBJCREATED,
    CHANGE_TIMESTAMP,
    LAST_USED_TIMESTAMP,
    OBJTEXT
FROM TABLE(
    QSYS2.OBJECT_STATISTICS(
        'QRECOVERY',
        '*ALL'
    )
) AS O
WHERE COALESCE(OBJSIZE, 0) > 0
ORDER BY OBJSIZE DESC
FETCH FIRST 30 ROWS ONLY;

The result showed numerous *JRNRCV objects associated with QSQTTJRN.

Examples included:

QSQTTJ0001  *JRNRCV  approximately 0.51 GB
QSQTTJ0002  *JRNRCV  approximately 0.51 GB
QSQTTJ0003  *JRNRCV  approximately 0.51 GB

There were enough of these receivers to account for most of the unexpected growth in QRECOVERY.

At this point, it might be tempting to delete the old receivers.

That would have been the wrong action.

Step 4: Review the journal receiver chain

The journal and its receivers were reviewed using:

WRKJRNA JRN(QRECOVERY/QSQTTJRN)

The receiver chain could also be reviewed with:

WRKJRNRCV JRNRCV(QRECOVERY/*ALL)

The receivers appeared online and could not be deleted.

Attempts to delete them returned:

CPF7024 — Receiver not deleted
Reason code 2

Reason code 2 was the most important clue.

It indicated that the receiver contained entries for changes that had not yet been committed or rolled back.

IBM i still required those entries for IPL recovery.

In other words:

The journal receivers were not being retained accidentally. IBM i was protecting an unresolved transaction.

Step 5: Find the commitment definition

The next task was to identify which job still had the unresolved transaction.

From:

WRKJRNA JRN(QRECOVERY/QSQTTJRN)

the following options were used:

F19
Option 6 — Commitment definitions
Option 12 — Work with associated job

This displayed the job associated with the open commitment-control transaction.

The job had performed a large amount of SQL temporary-table activity and had not committed or rolled back the transaction.

Because the transaction remained open, IBM i could not discard the journal entries needed to recover it.

As more journal activity occurred, additional receivers accumulated.

Step 6: Stop the responsible job safely

Before ending the job, confirm that it is safe to stop.

Where possible, stop the application or process that is submitting the workload first.

A controlled end should generally be attempted before an immediate end.

ENDJOB JOB(job-number/user/job-name) OPTION(*CNTRLD) DELAY(60)

If the job does not end normally, and an immediate end is appropriate for the environment, it may be considered:

ENDJOB JOB(job-number/user/job-name) OPTION(*IMMED)

Use OPTION(*IMMED) cautiously.

An immediate end can trigger rollback and recovery processing, which may itself require time and available storage.

In a production environment, ending a job should always be coordinated with the application owner or operations team.

What happened after the job ended

After the responsible job was ended:

• the open transaction was resolved
• IBM i completed the required rollback or recovery processing
• the no-longer-required QSQTTJRN receivers were automatically removed
• the storage occupied by those receivers was released

We did not need to manually force-delete the receivers.

IBM i removed them when they were no longer required for recovery.

That is an important distinction.

The resolution was not deleting the receivers. The resolution was fixing the condition that required IBM i to retain them.

Step 7: Confirm storage recovery

After the transaction was resolved, the system ASP was checked again using:

WRKSYSSTS

The following values should be reviewed:

• percentage of system ASP used
• available auxiliary storage
• current temporary storage
• configured storage threshold

The library-size SQL can also be rerun to confirm that QRECOVERY has returned to a normal size.

WITH LIBRARIES AS
(
    SELECT OBJNAME AS LIBRARY_NAME
    FROM TABLE(
        QSYS2.OBJECT_STATISTICS('*ALLSIMPLE', '*LIB')
    ) AS LIB
)
SELECT
    L.LIBRARY_NAME,
    I.OBJECT_COUNT,
    DECIMAL(
        I.LIBRARY_SIZE / 1000000000.0,
        15,
        2
    ) AS SIZE_GB,
    I.LIBRARY_SIZE_COMPLETE,
    I.LIBRARY_TYPE,
    I.TEXT_DESCRIPTION
FROM LIBRARIES AS L,
     LATERAL
     (
         SELECT *
         FROM TABLE(
             QSYS2.LIBRARY_INFO(
                 LIBRARY_NAME  => L.LIBRARY_NAME,
                 IGNORE_ERRORS => 'YES',
                 DETAILED_INFO => 'LIBRARY_SIZE'
             )
         )
     ) AS I
WHERE I.IASP_NUMBER = 1
  AND L.LIBRARY_NAME <> 'QHASM'
ORDER BY I.LIBRARY_SIZE DESC
FETCH FIRST 30 ROWS ONLY;

Root cause

The root cause was a job using SQL temporary tables while an unresolved commitment-control transaction remained open.

That transaction produced a large amount of journal activity in:

QRECOVERY/QSQTTJRN

Because the transaction had not been committed or rolled back, IBM i could not remove the older journal receivers.

The receivers continued to accumulate until:

QRECOVERY size:       approximately 44.54 GB
System ASP utilized: approximately 95.78%

Resolution

The responsible job was identified through the journal’s commitment definitions.

The job was then ended in a controlled manner.

Once the transaction was resolved, IBM i automatically deleted the receivers that were no longer required for recovery and released the associated disk storage.

What not to do

During this type of incident, do not:

• delete the QRECOVERY library
• clear the QRECOVERY library
• delete QRECOVERY/QSQTTJRN
• force-delete receivers protected by CPF7024 reason code 2
• delete the currently attached journal receiver
• assume that an old-looking receiver is safe to remove
• end a production job without understanding its business impact

A protected receiver is usually a symptom.

The unresolved transaction is the underlying problem.

Preventive recommendations

There are several ways to reduce the chance of this happening again.

Monitor system ASP utilization

Create alerts before system ASP usage reaches critical levels.

Reasonable warning levels may include:

85% — early warning
90% — critical warning

The exact thresholds should be based on the environment and the amount of storage required for:

• rollback
• IPL recovery
• saves
• database maintenance
• index creation
• object restoration
• temporary processing

Monitor QRECOVERY

Monitor:

• total size of QRECOVERY
• number of QSQTTJRN receivers
• total size of the receiver chain
• rate of growth
• age of the oldest retained receiver

Unexpected growth in QRECOVERY should be investigated promptly.

Review long-running commitment definitions

Look for jobs that:

• remain under commitment control for an unusually long time
• create large or repeated SQL temporary tables
• perform large transactions without intermediate commits
• remain inactive while holding an open transaction
• do not issue an appropriate COMMIT or ROLLBACK

Review application transaction design

Applications using commitment control should have a clear transaction strategy.

That includes:

• defined commit boundaries
• rollback handling
• exception handling
• cleanup during abnormal termination
• monitoring for long-running transactions

Schedule a storage report

The library-size query can be scheduled daily and stored in a history table.

That makes it possible to identify:

• rapidly growing libraries
• unexpected system-library growth
• long-term storage trends
• unusual changes following deployments or batch processing

Review QPFRDATA separately

During this incident, QPFRDATA was also approximately 50 GB.

That was not the cause of the QRECOVERY issue, but it was another significant storage consumer.

Performance data retention should be reviewed separately to confirm that only the required history is being retained.

The SQL advantage

Traditional IBM i commands remain essential.

Commands such as:

WRKSYSSTS
WRKJRNA
WRKJRNRCV
ENDJOB

were required to understand and resolve this incident.

But SQL services made the initial investigation much faster.

SQL helped us:

• rank libraries by size
• identify the storage outlier
• find the largest objects inside the library
• compare results
• rerun the checks after recovery
• create queries that can later be automated

This was not SQL replacing native IBM i tools.

It was SQL helping us reach the right native tools faster.

Troubleshooting checklist

When system ASP usage grows unexpectedly:

1. Check WRKSYSSTS.
2. Query QSYS2.ASP_INFO.
3. Rank libraries by size.
4. Investigate any unusual system-library growth.
5. Rank objects inside the large library.
6. Review journal and receiver chains where applicable.
7. Read the full CPF message and reason code.
8. Check journal commitment definitions.
9. Identify the responsible job.
10. Resolve the transaction or job safely.
11. Allow IBM i to remove recovery objects naturally.
12. Confirm that storage has been released.
13. Add monitoring to prevent recurrence.

Final takeaway

The system ASP problem appeared to be caused by a large number of journal receivers.

But the receivers were not the root cause.

They were being retained because IBM i still needed them to recover an unresolved transaction.

The real resolution was:

Find the open transaction.
Identify the responsible job.
Resolve the job safely.
Let IBM i release the receivers.
Verify the recovered storage.

The biggest lesson from this incident is:

Do not begin an IBM i storage cleanup by deleting what looks old. Measure where the storage is being used, understand why IBM i is retaining it, and resolve the underlying condition first.