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
