Cross-Invalidation due to pcloset/p-lock negotiation avoidance

Problem Statement / Pain points :

• Whenever there is no R/W activity and PCLOSET threshold is reached, pagesets SWITCH OUT of GBP dependency and the pages in our Bufferpools are cross-invalidated . Thereafter when R/W starts again, pagesets SWITCH IN to GBP dependency and the pages in Bufferpools are cross-invalidated "again".

• This repeated invalidation behavior because of going in and out of GBP-dependency is painful for our business critical applications. These applications depends on HUGE indexes which are expected to be 50-80% 'in memory' . This invalidation behavior due to PCLOSET or PLOCK negotiations defeats the purpose of having HUGE bufferpools and drastically increases SYNCREADs impacting online workload performance.

Workaround considered :

• Increased ZPARM PCLOSET value (which comes with extra logging and 'recovery & restart time' implications )

• Requested application team to schedule dummy SQL (partition-level inserts that rollbacks) every x minutes less than PCLOSET to keep a particular object in a desired GBP-dep state all through the day. Application team are concerned and hesitant to implement dummy SQLs against production data.

Proposed Solutions to IBM :

• PCLOSET option to be made available at Tablespace and Indexspace level .

• If PGSTEAL(NONE) - VSEQT(100) like behavior is available for PGSTEAL(LRU/FIFO) it will solve our issues. We understand PGSTEAL(NONE) - VSEQT(100) keeps the bufferpool pages "refreshed / implicitly prefetched " even after getting cross-invalidated because of PCLOSET or PLOCK negotiations.

• More options to customize PGSTEAL(NONE) which will make it flexible to accommodate table/indexes which cannot fit entirely in the BP. Currently we understand PGSTEAL(NONE) is useful only for objects that fit entirely in the Bufferpool.