What parameters in ZPARM need to be modified to alleviate the parallel index processing being disabled to rectify the storage issue in Sysview for Db2

Products

• SYSVIEW Performance Management Option for DB2 for z/OS

Issue/Introduction

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 need to be modified to alleviate the parallel index
processing being disabled to rectify the storage issue? How to verify what job, Dynamic SQL, or Static SQL is causing the critical alert? 

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.
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 IDB2 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 SYSVIEW for DB2 ssid sysid 02/11/19 10:25:45 20.0

6  Thread History

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 <= This will exclude any threads that did not have a RID list failure