DB2 V11 zOS indexes for 16k pages

Melissa Case

DB2 V11 zOS indexes for 16k pages
Hello,
I have a puzzle regarding using BP16k pool for indexes.

I built a CA strategy to alter indexes to use BP16k1 to begin a process to change the 5 & 6 level indexes to use the larger pages. It seemed odd in the strategy that it never explicitly SAID BP16k1, but I ran 10 indexes through the strategy successfully. BTW – you can’t explicitly SAY alter index ABX.IX1 BPOOL BP16k1 - it won’t let it thru SPUFI. The indexes ‘altered & synced’, then rebuilt / reorged / runstated all TEN successfully in the same strategy using the exact same process.

EXCEPT there were only FOUR of the TEN that converted to the BP16k2 pool. They were all built exactly the same – the alter is blank

Strategy:
-- ABCPROD.IOUTP1 WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.
.SYNC 20 'ALTER INDEX ABCPROD.IOUTP1'
.SYNC 25 'ALTER INDEX DB2NIS.IXICAGR'
-- ABCPROD.XVO03226 WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.
.SYNC 30 'ALTER INDEX ABCPROD.XVO03226'
-- ABCPROD.XPA01124 WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.
.SYNC 35 'ALTER INDEX ABCPROD.XPA01124'
-- ABCPROD.XPA01510 WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.
.SYNC 40 'ALTER INDEX ABCPROD.XPA01510'

A sample of the output of the rebuild:

.SYNC 85 'RECOVER INDEX ABCPROD.XPA01124' <- even tho this index went thru the recover & alter – it NEVER hit BP16k1.
BPA0027I: SYNCPOINT TAKEN AND ALL WORK COMMITTED.
RETCODE = 0

.CALL UTIL RECOVER PARM(DB2N)
.DATA
REBUILD INDEX (ABCPROD.XPA01510) <- or this one
SORTDEVT SYSDA

.CALL UTIL RECOVER PARM(DB2N)
.DATA
REBUILD INDEX (ABCPROD.IOUTP1) <- but this one did…

Results:

DB2 Object ===> BP Option ===> I Where => N
Buffer Pool ===> BP16K% > Creator ===> * >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: DB2N ----------WIPBMC -- LINE 1 OF 5 >
CMD NAME CREATOR TABLE NAME TCREATOR PART
________ BP16K1
________ IEHDR1 ABCPROD EHDR ABCPROD 0000
________ IEVNT1 ABCPROD EVNT ABCPROD 0000
________ IOHDR1 ABCPROD OHDR ABCPROD 0000
________ IOUTP1 ABCPROD OUTP ABCPROD 0000


Does anyone have any idea what I can do to make my prod change this coming weekend succeed for ALL TEN of the indexes? Is this something you’ve seen before?

Melissa (Missy) Case
DB2 Logical DBA / Team Lead
Ensono
e: [login to unmask email]<mailto:[login to unmask email]>
www.ensono.com http://www.ensono.com
[cid:[login to unmask email]


© 2018 Ensono, LP. All rights reserved. Ensono is a trademark of Ensono, LP. The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
Attachments

  • image001.png (4.5k)

Melissa Case

DB2 V11 zOS indexes for 16k pages
(in response to Melissa Case)
I have altered the other 6 indexes bufferpool bp16k1 & recovered / rebuilt them manually. I will be opening a ticket with CA as this appears to be a tool issue. I thank you for any time you spent looking at this –

Thanks again!

Melissa (Missy) Case
DB2 Logical DBA / Team Lead
Ensono
e: [login to unmask email]<mailto:[login to unmask email]>
d: 605-892-9140
m: 701-440-1763
www.ensono.com http://www.ensono.com
[cid:[login to unmask email]

From: Melissa Case <[login to unmask email]>
Sent: Tuesday, May 28, 2019 8:58 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 V11 zOS indexes for 16k pages


*** ATTENTION! This message originated from outside of Ensono. Treat hyperlinks and attachments in this email with caution. ***


Hello,
I have a puzzle regarding using BP16k pool for indexes.

I built a CA strategy to alter indexes to use BP16k1 to begin a process to change the 5 & 6 level indexes to use the larger pages. It seemed odd in the strategy that it never explicitly SAID BP16k1, but I ran 10 indexes through the strategy successfully. BTW – you can’t explicitly SAY alter index ABX.IX1 BPOOL BP16k1 - it won’t let it thru SPUFI. The indexes ‘altered & synced’, then rebuilt / reorged / runstated all TEN successfully in the same strategy using the exact same process.

EXCEPT there were only FOUR of the TEN that converted to the BP16k2 pool. They were all built exactly the same – the alter is blank

Strategy:
-- ABCPROD.IOUTP1 WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.
.SYNC 20 'ALTER INDEX ABCPROD.IOUTP1'
.SYNC 25 'ALTER INDEX DB2NIS.IXICAGR'
-- ABCPROD.XVO03226 WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.
.SYNC 30 'ALTER INDEX ABCPROD.XVO03226'
-- ABCPROD.XPA01124 WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.
.SYNC 35 'ALTER INDEX ABCPROD.XPA01124'
-- ABCPROD.XPA01510 WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.
.SYNC 40 'ALTER INDEX ABCPROD.XPA01510'

A sample of the output of the rebuild:

.SYNC 85 'RECOVER INDEX ABCPROD.XPA01124' <- even tho this index went thru the recover & alter – it NEVER hit BP16k1.
BPA0027I: SYNCPOINT TAKEN AND ALL WORK COMMITTED.
RETCODE = 0

.CALL UTIL RECOVER PARM(DB2N)
.DATA
REBUILD INDEX (ABCPROD.XPA01510) <- or this one
SORTDEVT SYSDA

.CALL UTIL RECOVER PARM(DB2N)
.DATA
REBUILD INDEX (ABCPROD.IOUTP1) <- but this one did…

Results:

DB2 Object ===> BP Option ===> I Where => N
Buffer Pool ===> BP16K% > Creator ===> * >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: DB2N ----------WIPBMC -- LINE 1 OF 5 >
CMD NAME CREATOR TABLE NAME TCREATOR PART
________ BP16K1
________ IEHDR1 ABCPROD EHDR ABCPROD 0000
________ IEVNT1 ABCPROD EVNT ABCPROD 0000
________ IOHDR1 ABCPROD OHDR ABCPROD 0000
________ IOUTP1 ABCPROD OUTP ABCPROD 0000


Does anyone have any idea what I can do to make my prod change this coming weekend succeed for ALL TEN of the indexes? Is this something you’ve seen before?

Melissa (Missy) Case
DB2 Logical DBA / Team Lead
Ensono
e: [login to unmask email]<mailto:[login to unmask email]>
www.ensono.com http://www.ensono.com
[cid:[login to unmask email]


© 2018 Ensono, LP. All rights reserved. Ensono is a trademark of Ensono, LP. The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

-----End Original Message-----

© 2018 Ensono, LP. All rights reserved. Ensono is a trademark of Ensono, LP. The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
Attachments

  • image001.png (4.5k)