What parameters in ZPARM needs to be modified to alleviate the parallel index processing being disabled to rectify the storage issue in IDB2?
Article ID: 126687
Updated On:
Products
CA Bind Analyzer for DB2 for z/OS
CA Detector
CA SQL-Ease for DB2 for z/OS
CA Sysview Performance Management Option for DB2 for z/OS
CA Database Detector for DB2 for z/OS
CA Plan Analyzer for DB2 for z/OS
CA Subsystem Analyzer for DB2 for z/OS
Issue/Introduction
User has been experiencing High number of times multiple index processing was disabled due to not enough storage and High number of columns with invalid select procedures that were bypassed.
What parameters in ZPARM needs to be modified to alleviate the parallel index processing being disabled to rectify the storage issue?
How to verify what job or dynamic SQL, or static SQL is causing the critical alert?
What parameters in ZPARM needs to be modified to alleviate the parallel index processing being disabled to rectify the storage issue?
How to verify what job or dynamic SQL, or static SQL is causing the critical alert?
Environment
Release:
Component: CIDB
Component: CIDB
Resolution
The process for addressing this problem is likely to be iterative, and based on the specific times of day that you ran into the problem. You will need to study the workload that was active at the moment, and determine what action can be taken, whether it is reducing the maximum threads allowed, or re-sizing bufferpools, etc. If you want to identify specific threads that caused the condition, you can always go to thread history and search for them. If you update the following thread history panel, it should return to you the list of threads that encountered a list prefetch (multi-index processing) problem:
Menu Print Tools Help CA SYSVIEW for DB2 D12A CA31 02/11/19 10:25:45 20.0 D12APR20 KIETH01
1 Select by PF Key 2 List all Threads 3 Quick Summaries... THstSel4 Thread History Selection
Page 4 of 5 Specify selection criteria then List or Summary PF Key Sent SQL . . . .> Rows . . .> Trans . . . .> Received SQL . . . .> Rows . . .> Trans . . . .> Locks Lock Suspensions . . . . .> Max Page Locks . . . . . .> Deadlocks . . . . . . . . .> Shared Lock Escalation . .> Timeouts . . . . . . . . .> Exclusive Lock Escalation .> List Prefetch Used . . . . . . . . . . .> Failed . . . . . . . . . .> 0 <- that will exclude any threads that didn't have a RID list failure.
Menu Print Tools Help CA SYSVIEW for DB2 D12A CA31 02/11/19 10:25:45 20.0 D12APR20 KIETH01
1 Select by PF Key 2 List all Threads 3 Quick Summaries... THstSel4 Thread History Selection
Page 4 of 5 Specify selection criteria then List or Summary PF Key Sent SQL . . . .> Rows . . .> Trans . . . .> Received SQL . . . .> Rows . . .> Trans . . . .> Locks Lock Suspensions . . . . .> Max Page Locks . . . . . .> Deadlocks . . . . . . . . .> Shared Lock Escalation . .> Timeouts . . . . . . . . .> Exclusive Lock Escalation .> List Prefetch Used . . . . . . . . . . .> Failed . . . . . . . . . .> 0 <- that will exclude any threads that didn't have a RID list failure.
Feedback
Powered by
