What parameters in ZPARM needs to be modified to alleviate the parallel index processing being disabled to rectify the storage issue in IDB2?
book
Article ID: 126687
calendar_today
Updated On:
Products
Bind Analyzer for DB2 for z/OSSQL-Ease for DB2 for z/OSSYSVIEW Performance Management Option for DB2 for z/OSPlan Analyzer for DB2 for z/OSSubsystem 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?
Environment
Release: 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.