In the Db2 world during a BIND/REBIND, particularly while using the command EXPLAIN(ONLY), you may experience a real Halloween horror story. Data is disappearing and reappearing, which can cause LOCK problems in production.
As luck would have it, we have got the magic formula (see text with long SQL queries…) to get rid of the horrible ghosts, so that you can feel as clean as a ghostbuster.
The scary tale begun like this:
We were contacted by a customer who has been using our BindImpactExpert (BIX) for years to check that no access paths go horribly wrong during REBIND or BIND.
Now with a BIND you obviously have no choice – the program has been compiled and linked and they probably changed the SQL as well, so no chance of just changing the CONTOKEN in the load module, (That’s what another of our products does, ConditionalBind, by the way – it saves the BIND completely if the SQL has not changed.)
Anyway, they were simply doing a BIND … EXPLAIN(ONLY) and…
… got a LOCK problem in production.
RTFM Time
This was very odd as the EXPLAIN(ONLY) is documented thus:
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/comref/src/tpc/db2z_bindoptexplain.html
“Populates EXPLAIN tables but does not create or rebind a package. This option allows EXPLAIN to be run when the authorization ID of the bind or rebind process does not have the privilege to execute statements in the package. If the command that specifies EXPLAIN(ONLY) refers to a package and version that already exists, the existing package is not dropped or replaced, even if ACTION(REPLACE) is specified. “
This has not changed since DB2 10. Highlighting from me, by the way.
CHAPTER 1 : Horror scenes
Trust is good – Test is better
So we decided to do some tests and picked a little package with 38 SQL statements that was already bound to a collection. I generated a 100 BIND statements all looking like this:
BIND PACKAGE(IQA_COLLECTION_610) MEMBER(PDB2D900) QUALIFIER(IQA0610) -
OWNER(BOXWELL) EXPLAIN(ONLY)What you see is what you get
While these were running I did a DISPLAY LOCKS and saw this:
DSNT360I -QB1A ************************************** DSNT361I -QB1A * DISPLAY DATABASE SUMMARY * * GLOBAL LOCKS * DSNT360I -QB1A **************************************
DSNT362I -QB1A DATABASE = DSNDB01 STATUS = RW DBD LENGTH = 108200 DSNT397I -QB1A NAME TYPE PART STATUS CONNID CORRID LOCKINFO -------- ---- ----- -------------- ------- --------- --------- SPT01 TS 0001 RW BATCH BINDROY H-IX,P,C - AGENT TOKEN 568 SPT01 TS ********** DISPLAY OF DATABASE DSNDB01 ENDED ******************** DSN9022I -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION ***
So it is doing locks!
I Claim, I Saw, I Conquered
And this from a DISPLAY CLAIMERS:
DSNT360I -QB1A *********************************** DSNT361I -QB1A * DISPLAY DATABASE SUMMARY * * GLOBAL CLAIMERS * DSNT360I -QB1A ***********************************
DSNT362I -QB1A DATABASE = DSNDB01 STATUS = RW DBD LENGTH = 108200 DSNT397I -QB1A NAME TYPE PART STATUS CONNID CORRID CLAIMINFO -------- ---- ----- ----------------- -------- ----------- -------- SPT01 TS 0001 RW BATCH BINDROY (CS,C) - AGENT TOKEN 576 - MEMBER NAME SPT01 TS 0001 RW BATCH BINDROY (WR,C) - AGENT TOKEN 576 - MEMBER NAME SPT01 TS **************** DISPLAY OF DATABASE DSNDB01 ENDED ***************** DSN9022I -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION ***
And then after a few BINDs had completed:
DSNT360I -QB1A *********************************** DSNT361I -QB1A * DISPLAY DATABASE SUMMARY * * GLOBAL CLAIMERS * DSNT360I -QB1A *********************************** DSNT362I -QB1A DATABASE = DSNDB01 STATUS = RW DBD LENGTH = 108200 DSNT397I -QB1A NAME TYPE PART STATUS CONNID CORRID CLAIMINFO -------- ---- ----- ----------------- -------- ----------- --------- SPT01 TS 0001 RW,AREOR BATCH BINDROY (CS,C) - AGENT TOKEN 580 - MEMBER NAME SPT01 TS 0001 RW,AREOR BATCH BINDROY (WR,C) - AGENT TOKEN 580 - MEMBER NAME SPT01 TS ************** DISPLAY OF DATABASE DSNDB01 ENDED ************************ DSN9022I -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION ***
So the AGENT TOKEN is increasing for each bind which is OK, but that WR claim is not good!
Where’d he go? Where’d who go?
But the weirdest, most unbelievable thing, was a mass select I wrote against SYSIBM.SYSPACKAGE.
Here’s a cut of the output:
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+-------- 2018-10-05-07.22.43.532257 ---------+---------+---------+---------+-------- SELECT SUBSTR(NAME, 1, 8) AS NAME, HEX(CONTOKEN) FROM SYSIBM.SYSPACKAGE WHERE COLLID = 'IQA_COLLECTION_610' AND NAME = 'PDB2D900' WITH UR ; ---------+---------+---------+---------+-------- NAME ---------+---------+---------+---------+-------- PDB2D900 19A8269C16C11114 DSNE610I NUMBER OF ROWS DISPLAYED IS 1
Package is there, all is fine.
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+-------- 2018-10-05-07.22.43.548474 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 SELECT SUBSTR(NAME, 1, 8) AS NAME, HEX(CONTOKEN) FROM SYSIBM.SYSPACKAGE WHERE COLLID = 'IQA_COLLECTION_610' AND NAME = 'PDB2D900' WITH UR ; ---------+---------+---------+---------+-------- NAME ---------+---------+---------+---------+-------- DSNE610I NUMBER OF ROWS DISPLAYED IS 0
Package has *gone* !!!
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+-------- 2018-10-05-07.22.43.568380 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 SELECT SUBSTR(NAME, 1, 8) AS NAME, HEX(CONTOKEN) FROM SYSIBM.SYSPACKAGE WHERE COLLID = 'IQA_COLLECTION_610' AND NAME = 'PDB2D900' WITH UR ; ---------+---------+---------+---------+-------- NAME ---------+---------+---------+---------+-------- DSNE610I NUMBER OF ROWS DISPLAYED IS 0
Package is still *gone* !!!
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+-------- 2018-10-05-07.22.43.609963 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 SELECT SUBSTR(NAME, 1, 8) AS NAME, HEX(CONTOKEN) FROM SYSIBM.SYSPACKAGE WHERE COLLID = 'IQA_COLLECTION_610' AND NAME = 'PDB2D900' WITH UR ; ---------+---------+---------+---------+-------- NAME ---------+---------+---------+---------+-------- PDB2D900 199F572E0A8E6FF0 DSNE610I NUMBER OF ROWS DISPLAYED IS 1
Package is back from its little holiday by the sea and all is fine – well apart from the timeouts in production that is…
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+-------- 2018-10-05-07.22.43.628259 DSNE610I NUMBER OF ROWS DISPLAYED IS 1
You have got to be kidding me!
So you have seen that my package was *gone* for about 0.06 seconds.
It would appear that BIND … EXPLAIN(ONLY) “moves” the current *live* running package “out of the way” then does the EXPLAIN(ONLY) processing and then “moves” the package back… this is, to put it mildly, NOT GOOD!!!
CHAPTER 2 : The magic formula
We can Fix it!
Luckily, the “fix” is pretty easy – Use a dummy Collection and all is fine!
Here’s how it looks with a different COLLECTION ID:
BIND PACKAGE(ROYBOYECTION_610) MEMBER(PDB2D900) QUALIFIER(IQA0610) - OWNER(BOXWELL) EXPLAIN(ONLY)
Then DISPLAYs:
DSNT360I -QB1A ************************************* DSNT361I -QB1A * DISPLAY DATABASE SUMMARY * * GLOBAL LOCKS * DSNT360I -QB1A ************************************* DSNT362I -QB1A DATABASE = DSNDB01 STATUS = RW DBD LENGTH = 108200 DSNT397I -QB1 NAME TYPE PART STATUS CONNID CORRID LOCKINFO -------- ---- ----- ---------- -------- --------- -------- SPT01 TS 0001 R SPT01 TS *************** DISPLAY OF DATABASE DSNDB01 ENDED ************** DSN9022I -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION ***
No locks!
DSNT360I -QB1A **************************************** DSNT361I -QB1A * DISPLAY DATABASE SUMMARY * * GLOBAL CLAIMERS * DSNT360I -QB1A **************************************** DSNT362I -QB1A DATABASE = DSNDB01 STATUS = RW DBD LENGTH = 108200 DSNT397I -QB1A NAME TYPE PART STATUS CONNID CORRID CLAIMINFO -------- ---- ----- --------- ------- ------- --------- SPT01 TS 0001 RW BATCH BINDROY (CS,C) - AGENT TOKEN 700 - MEMBER NAME SPT01 TS ************** DISPLAY OF DATABASE DSNDB01 ENDED *********** DSN9022I -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION ***
Just a CS claim!
Be careful out there!
And no “disappearing” rows in SYSPACKAGE!
Hallelujah!! I think this is interesting information for most Db2 Users out there. I know more and more shops are doing EXPLAIN(ONLY) in the, mistaken, belief that it cannot harm production…
What about the Directory?
They wouldn’t do that to the DSNDB01.SPT01 SYSIBM.SPTR table as well, would they? Well guess what? They did!
I wrote another little SQL against the SYSIBM.SPTR table for the same package and re-ran the BINDs:
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+---------+--------+ 2018-10-05-11.15.51.928875 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION , SUBSTR(A.SPTNAME, 1, 8) AS PACKAGE , HEX(A.SPTCONID) AS CONTOKEN , HEX(A.SPTRESV) AS RESV , HEX(A.SPTSEC) AS SEC , HEX(A.SPTSEQ) AS SEQ , SUBSTR(A.SPTVER, 1, 8) AS VERSION , A.SPTLL AS LL FROM SYSIBM.SPTR A WHERE A.SPTLOCID = '' AND A.SPTCOLID = 'IQA_COLLECTION_610' AND A.SPTNAME = 'PDB2D900' ORDER BY 1 , 2 , 3 , 4 , 5 , 6 WITH UR ; ---------+---------+---------+---------+---------+---------+---------+--------+ COLLECTION PACKAGE CONTOKEN RESV SEC SEQ VERSION LL ---------+---------+---------+---------+---------+---------+---------+--------+ IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000001 0001 2.50 2147468080 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000B 0001 2.50 2147461080 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000C 0001 2.50 2147455728 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000D 0001 2.50 2147445836 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000E 0001 2.50 2147458316 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000F 0001 2.50 2147441860 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000010 0001 2.50 2147460296 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000011 0001 2.50 2147449216 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000012 0001 2.50 2147463256 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000013 0001 2.50 2147452416 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000014 0001 2.50 2147468032 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000015 0001 2.50 2147450804 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000016 0001 2.50 2147461896 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000017 0001 2.50 2147466700 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000018 0001 2.50 2147452760 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000019 0001 2.50 2147466152 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001A 0001 2.50 2147449352 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001B 0001 2.50 2147469548 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001C 0001 2.50 2147472392 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001D 0001 2.50 2147468924 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001E 0001 2.50 2147465912 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001F 0001 2.50 2147469084 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000020 0001 2.50 2147471688 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000021 0001 2.50 2147481020 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000022 0001 2.50-2147472632 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000023 0001 2.50 2147471160 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000024 0001 2.50 2147473128 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000025 0001 2.50 2147466564 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000026 0001 2.50 2147473972 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000027 0001 2.50 2147473692 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000028 0001 2.50 2147474100 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000029 0001 2.50 2147474532 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002A 0001 2.50 2147477556 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002B 0001 2.50 2147477988 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002C 0001 2.50 2147464100 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002D 0001 2.50 2147474904 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002E 0001 2.50 2147466772 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002F 0001 2.50 2147468564 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000030 0001 2.50 2147472764 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000031 0001 2.50 2147468820 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296 DSNE610I NUMBER OF ROWS DISPLAYED IS 41
Everything fine and dandy!
---------+---------+---------+---------+---------+---------+---------+--------+ SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+---------+--------+ 2018-10-05-11.15.51.944246 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION , SUBSTR(A.SPTNAME, 1, 8) AS PACKAGE , HEX(A.SPTCONID) AS CONTOKEN , HEX(A.SPTRESV) AS RESV , HEX(A.SPTSEC) AS SEC , HEX(A.SPTSEQ) AS SEQ , SUBSTR(A.SPTVER, 1, 8) AS VERSION , A.SPTLL AS LL FROM SYSIBM.SPTR A WHERE A.SPTLOCID = '' AND A.SPTCOLID = 'IQA_COLLECTION_610' AND A.SPTNAME = 'PDB2D900' ORDER BY 1 , 2 , 3 , 4 , 5 , 6 WITH UR ; ---------+---------+---------+---------+---------+---------+--------+--------+ COLLECTION PACKAGE CONTOKEN RESV SEC SEQ VERSION LL ---------+---------+---------+---------+---------+---------+--------+--------+ IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000C 0001 2.50 2147455728 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000D 0001 2.50 2147445836 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000E 0001 2.50 2147458316 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000F 0001 2.50 2147441860 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000010 0001 2.50 2147460296 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000011 0001 2.50 2147449216 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000012 0001 2.50 2147463256 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000013 0001 2.50 2147452416 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000014 0001 2.50 2147468032 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000015 0001 2.50 2147450804 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000016 0001 2.50 2147461896 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000017 0001 2.50 2147466700 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000018 0001 2.50 2147452760 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000019 0001 2.50 2147466152 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001A 0001 2.50 2147449352 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001B 0001 2.50 2147469548 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001C 0001 2.50 2147472392 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001D 0001 2.50 2147468924 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001E 0001 2.50 2147465912 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001F 0001 2.50 2147469084 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000020 0001 2.50 2147471688 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000021 0001 2.50 2147481020 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000022 0001 2.50 2147472632 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000023 0001 2.50 2147471160 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000024 0001 2.50 2147473128 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000025 0001 2.50 2147466564 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000026 0001 2.50 2147473972 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000027 0001 2.50 2147473692 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000028 0001 2.50 2147474100 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000029 0001 2.50 2147474532 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002A 0001 2.50 2147477556 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002B 0001 2.50 2147477988 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002C 0001 2.50 2147464100 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002D 0001 2.50 2147474904 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002E 0001 2.50 2147466772 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002F 0001 2.50 2147468564 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000030 0001 2.50 2147472764 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000031 0001 2.50 2147468820 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296 DSNE610I NUMBER OF ROWS DISPLAYED IS 39
My mind is going… I can feel it Dave…
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+---------+--------+ 2018-10-05-11.15.51.972933 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION , SUBSTR(A.SPTNAME, 1, 8) AS PACKAGE , HEX(A.SPTCONID) AS CONTOKEN , HEX(A.SPTRESV) AS RESV , HEX(A.SPTSEC) AS SEC , HEX(A.SPTSEQ) AS SEQ , SUBSTR(A.SPTVER, 1, 8) AS VERSION , A.SPTLL AS LL FROM SYSIBM.SPTR A WHERE A.SPTLOCID = '' AND A.SPTCOLID = 'IQA_COLLECTION_610' AND A.SPTNAME = 'PDB2D900' ORDER BY 1 , 2 , 3 , 4 , 5 , 6 WITH UR ; ---------+---------+---------+---------+---------+---------+---------+--------+ COLLECTION PACKAGE CONTOKEN RESV SEC SEQ VERSION LL ---------+---------+---------+---------+---------+---------+---------+--------+ IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002C 0001 2.50 2147464100 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002D 0001 2.50 2147474904 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002E 0001 2.50 2147466772 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002F 0001 2.50 2147468564 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000030 0001 2.50 2147472764 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000031 0001 2.50 2147468820 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296 DSNE610I NUMBER OF ROWS DISPLAYED IS 7
Dave, what are you doing Dave?
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+---------+---------+---------+
2018-10-05-11.15.51.986345
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+
SELECT SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION
, SUBSTR(A.SPTNAME, 1, 8) AS PACKAGE
, HEX(A.SPTCONID) AS CONTOKEN
, HEX(A.SPTRESV) AS RESV
, HEX(A.SPTSEC) AS SEC
, HEX(A.SPTSEQ) AS SEQ
, SUBSTR(A.SPTVER, 1, 8) AS VERSION
, A.SPTLL AS LL
FROM SYSIBM.SPTR A
WHERE A.SPTLOCID = ''
AND A.SPTCOLID = 'IQA_COLLECTION_610'
AND A.SPTNAME = 'PDB2D900'
ORDER BY 1 , 2 , 3 , 4 , 5 , 6
WITH UR ;
---------+---------+---------+---------+---------+---------+---------+--------+
COLLECTION PACKAGE CONTOKEN RESV SEC SEQ VERSION LL
---------+---------+---------+---------+---------+---------+---------+--------+
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296
DSNE610I NUMBER OF ROWS DISPLAYED IS 1Dave, stop. Stop, will you?
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+---------+--------+ 2018-10-05-11.15.51.997994 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION , SUBSTR(A.SPTNAME, 1, 8) AS PACKAGE , HEX(A.SPTCONID) AS CONTOKEN , HEX(A.SPTRESV) AS RESV , HEX(A.SPTSEC) AS SEC , HEX(A.SPTSEQ) AS SEQ , SUBSTR(A.SPTVER, 1, 8) AS VERSION , A.SPTLL AS LL FROM SYSIBM.SPTR A WHERE A.SPTLOCID = '' AND A.SPTCOLID = 'IQA_COLLECTION_610' AND A.SPTNAME = 'PDB2D900' ORDER BY 1 , 2 , 3 , 4 , 5 , 6 WITH UR ; ---------+---------+---------+---------+---------+---------+---------+--------+ COLLECTION PACKAGE CONTOKEN RESV SEC SEQ VERSION LL ---------+---------+---------+---------+---------+---------+---------+--------+ DSNE610I NUMBER OF ROWS DISPLAYED IS 0
Flat line or “Daisy, Daisy…”
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+---------+--------+ 2018-10-05-11.15.52.010002 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION , SUBSTR(A.SPTNAME, 1, 8) AS PACKAGE , HEX(A.SPTCONID) AS CONTOKEN , HEX(A.SPTRESV) AS RESV , HEX(A.SPTSEC) AS SEC , HEX(A.SPTSEQ) AS SEQ , SUBSTR(A.SPTVER, 1, 8) AS VERSION , A.SPTLL AS LL FROM SYSIBM.SPTR A WHERE A.SPTLOCID = '' AND A.SPTCOLID = 'IQA_COLLECTION_610' AND A.SPTNAME = 'PDB2D900' ORDER BY 1 , 2 , 3 , 4 , 5 , 6 WITH UR ; ---------+---------+---------+---------+---------+---------+---------+--------+ COLLECTION PACKAGE CONTOKEN RESV SEC SEQ VERSION LL ---------+---------+---------+---------+---------+---------+---------+--------+ DSNE610I NUMBER OF ROWS DISPLAYED IS 0
Still gone…
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+---------+--------+ ---------+---------+---------+---------+---------+---------+---------+--------+ 2018-10-05-11.15.52.159740 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+--------+ SELECT SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION , SUBSTR(A.SPTNAME, 1, 8) AS PACKAGE , HEX(A.SPTCONID) AS CONTOKEN , HEX(A.SPTRESV) AS RESV , HEX(A.SPTSEC) AS SEC , HEX(A.SPTSEQ) AS SEQ , SUBSTR(A.SPTVER, 1, 8) AS VERSION , A.SPTLL AS LL FROM SYSIBM.SPTR A WHERE A.SPTLOCID = '' AND A.SPTCOLID = 'IQA_COLLECTION_610' AND A.SPTNAME = 'PDB2D900' ORDER BY 1 , 2 , 3 , 4 , 5 , 6 WITH UR ; ---------+---------+---------+---------+---------+---------+---------+--------+ COLLECTION PACKAGE CONTOKEN RESV SEC SEQ VERSION LL ---------+---------+---------+---------+---------+---------+---------+--------+ IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000001 0001 2.50 2147468080 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000B 0001 2.50 2147461080 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000C 0001 2.50 2147455728 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000D 0001 2.50 2147445836 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000E 0001 2.50 2147458316 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000F 0001 2.50 2147441860 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000010 0001 2.50 2147460296 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000011 0001 2.50 2147449216 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000012 0001 2.50 2147463256 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000013 0001 2.50 2147452416 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000014 0001 2.50 2147468032 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000015 0001 2.50 2147450804 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000016 0001 2.50 2147461896 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000017 0001 2.50 2147466700 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000018 0001 2.50 2147452760 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000019 0001 2.50 2147466152 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001A 0001 2.50 2147449352 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001B 0001 2.50 2147469548 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001C 0001 2.50 2147472392 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001D 0001 2.50 2147468924 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001E 0001 2.50 2147465912 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001F 0001 2.50 2147469084 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000020 0001 2.50 2147471688 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000021 0001 2.50 2147481020 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000022 0001 2.50 2147472632 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000023 0001 2.50 2147471160 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000024 0001 2.50 2147473128 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000025 0001 2.50 2147466564 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000026 0001 2.50 2147473972 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000027 0001 2.50 2147473692 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000028 0001 2.50 2147474100 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000029 0001 2.50 2147474532 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002A 0001 2.50 2147477556 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002B 0001 2.50 2147477988 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002C 0001 2.50 2147464100 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002D 0001 2.50 2147474904 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002E 0001 2.50 2147466772 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002F 0001 2.50 2147468564 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000030 0001 2.50 2147472764 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000031 0001 2.50 2147468820 IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296 DSNE610I NUMBER OF ROWS DISPLAYED IS 41 SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+---------+--------+ 2018-10-05-11.15.52.278732 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
It is back!! HAL 9000 is online and the AE35 unit will still fail in 72 hours…
Feel free to send me your comments and ask questions. Also feel free to ask IBM why it does this and please forgive my usage of 2001 quotes.
TTFN,
Roy Boxwell
Senior Architect