How To
Summary
Temporary storage analysis tool ACTIVE_QUERY_INFO shows large amounts of temporary storage consumed in *DATABASE buckets 8, 9, 11, and 14.
Objective
Excessive temporary storage would be when the cumulative
Refer to the following document that explains how you can prevent individual jobs from allocating excessive temporary storage: Establish SQL temporary storage limits.
DATABASE components are beyond 10% of the system ASP.Refer to the following document that explains how you can prevent individual jobs from allocating excessive temporary storage: Establish SQL temporary storage limits.
Use this
If you need
TechNote for you own understanding and investigating.If you need
IBM i Global Support Center assistance use MustGather: Temporary Storage growth in *DATABASE buckets 8, 9, 11, and 14 instead to collect data to send in to IBM. Environment
This
TechNote applies to all support IBM i releases.Steps
Start with a quick check of what/where the temporary storage is being used - refer to another MustGather TechNote - QMGTOOLS: Temp Storage Menu :
GO MG
16. Internals
1. Temp Storage Menu
16. Internals
1. Temp Storage Menu
From this Temp Storage Menu
3. Temporary storage summary
or
4. List temp storage and SQL info for active jobs
or
4. List temp storage and SQL info for active jobs
Else:
select CURRENT_TEMPORARY_STORAGE, AVERAGE_TEMPORARY_STORAGE, QUALIFIED_JOB_NAME, JOB_NAME, JOB_USER,
JOB_NUMBER, QUERY_TYPE, PSEUDO_CLOSED, QRO_HASH, PLAN_IDENTIFIER, FULL_OPEN_TIMESTAMP,
LAST_PSEUDO_OPEN_TIMESTAMP, LIBRARY_NAME, FILE_NAME, NUMBER_OF_PSEUDO_CLOSES,
CURRENT_ROW_COUNT, CURRENT_RUNTIME, CURRENT_DATABASE_READS, CURRENT_PAGE_FAULTS, MTI_COUNT,
MTI_SIZE, AVERAGE_ROW_COUNT, AVERAGE_RUNTIME, AVERAGE_DATABASE_READS, AVERAGE_PAGE_FAULTS
from table (
QSYS2.ACTIVE_QUERY_INFO(JOB_NAME => 'QZDASOINIT')
)
order by CURRENT_TEMPORARY_STORAGE desc
limit 10;
JOB_NUMBER, QUERY_TYPE, PSEUDO_CLOSED, QRO_HASH, PLAN_IDENTIFIER, FULL_OPEN_TIMESTAMP,
LAST_PSEUDO_OPEN_TIMESTAMP, LIBRARY_NAME, FILE_NAME, NUMBER_OF_PSEUDO_CLOSES,
CURRENT_ROW_COUNT, CURRENT_RUNTIME, CURRENT_DATABASE_READS, CURRENT_PAGE_FAULTS, MTI_COUNT,
MTI_SIZE, AVERAGE_ROW_COUNT, AVERAGE_RUNTIME, AVERAGE_DATABASE_READS, AVERAGE_PAGE_FAULTS
from table (
QSYS2.ACTIVE_QUERY_INFO(JOB_NAME => 'QZDASOINIT')
)
order by CURRENT_TEMPORARY_STORAGE desc
limit 10;
This example is subsetting for all QZDASOINIT jobs.
Job 135353/QUSER/QZDASOINIT has the most current temporary storage.
Use Access Client Solutions (ACS) to access the
SQL Performance Center.
Select
SQL Details for Jobs 
Use
Filters to find the job. Right click on the job and select Show Details.
When you have the
SQL statement and details right click on the area around the SQL statement and select Visual Explain.
For this query we see several icons that use temporary storage.
You can start with IBM DB2 for i indexing methods and strategies to start tuning your queries.
IBM Technology Expert Labs - Db2 for i Offerings is another option.
Related Information
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001goBAAQ","label":"IBM i Db2-\u003ETemp Storage"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
15 November 2024
UID
ibm17142070