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
CA Bind Analyzer for DB2 for z/OSCA SQL-Ease for DB2 for z/OSCA Sysview Performance Management Option for DB2 for z/OSCA Plan Analyzer for DB2 for z/OSCA Subsystem Analyzer for DB2 for z/OS
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?
Release: Component: CIDB
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.