Hi,
I am getting different results when i execute queries in the following manner.
Query 1: Lists all the versions of the package BNESCLTR
Query 2: Lists Latest 2 packages based on collection.
Query 3: Here, iam creating a temporary table and INSERT using SELECT (same query as Query 2).
And select statement displays everything from the table.
Code:
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
-- QUERY 1 : LIST ALL THE PACKAGES IN DB2P
SELECT SUBSTR(A.COLLID,1,25) COLLECTION,
SUBSTR(A.NAME,1,10) PACKAGE,
SUBSTR(A.VERSION,1,26) VERSION,
HEX(A.CONTOKEN) CONTOKEN,
A.BINDTIME BINDTIME
FROM DBAMAINT.SYSPACKAGE_DB2P A
WHERE A.NAME LIKE 'BNESC%'
ORDER BY A.COLLID, A.NAME, A.BINDTIME DESC;
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
COLLECTION PACKAGE VERSION CONTOKEN BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INEB BNESCLTR 2010-04-05-11.50.22.516769 18B9186F15A78842 2010-04-08-15.54.55.179788
INEB BNESCLTR 189EB6471E4A22E0 2009-10-20-11.26.51.549114
INEB BNESCLTR EH00 18634E98179D8464 2008-10-06-11.17.05.800289
INEB BNESCLTR DK00 170C5F7113C0FA54 2002-11-02-11.33.53.258171
INEB BNESCLTR D500 16E2E2C813D1660E 2002-01-25-09.20.45.504939
INEB BNESCLTR B400 16DFBD9E1DBE3D0C 2002-01-05-08.52.30.084608
INEBRO BNESCLTR 2010-04-05-11.50.22.516769 18B9186F15A78842 2010-04-08-15.54.56.302960
INEBRO BNESCLTR 1864E13E038519ED 2009-10-09-14.50.46.448556
DSNE610I NUMBER OF ROWS DISPLAYED IS 8
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
-- QUERY 2 : LIST LATEST 2 PACKAGES IN DB2P
SELECT SUBSTR(A.COLLID,1,25) COLLECTION,
SUBSTR(A.NAME,1,10) PACKAGE,
SUBSTR(A.VERSION,1,26) VERSION,
HEX(A.CONTOKEN) CONTOKEN,
A.BINDTIME BINDTIME
FROM DBAMAINT.SYSPACKAGE_DB2P A
WHERE A.NAME LIKE 'BNESC%' AND
HEX(A.CONTOKEN)>=
COALESCE((SELECT MAX(HEX(B.CONTOKEN))
FROM DBAMAINT.SYSPACKAGE_DB2P B
WHERE B.LOCATION=A.LOCATION
AND B.COLLID=A.COLLID
AND B.NAME=A.NAME
AND HEX(B.CONTOKEN)< (SELECT MAX(HEX(C.CONTOKEN))
FROM DBAMAINT.SYSPACKAGE_DB2P C
WHERE C.LOCATION=A.LOCATION
AND C.COLLID=A.COLLID
AND C.NAME=A.NAME)
)
,'0000000000000000')
ORDER BY A.COLLID, A.NAME, A.BINDTIME DESC;
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
COLLECTION PACKAGE VERSION CONTOKEN BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INEB BNESCLTR 2010-04-05-11.50.22.516769 18B9186F15A78842 2010-04-08-15.54.55.179788
INEB BNESCLTR 189EB6471E4A22E0 2009-10-20-11.26.51.549114
INEBRO BNESCLTR 2010-04-05-11.50.22.516769 18B9186F15A78842 2010-04-08-15.54.56.302960
INEBRO BNESCLTR 1864E13E038519ED 2009-10-09-14.50.46.448556
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
-- QUERY 3 : LIST LATEST 2 PACKAGES IN DB2P
DECLARE GLOBAL TEMPORARY TABLE SESSION.DB2P_L2
(COLLID VARCHAR(128) NOT NULL
,NAME VARCHAR(128) NOT NULL
,VERSION VARCHAR(122) NOT NULL
,CONTOKEN CHAR(8) NOT NULL
,BINDTIME TIMESTAMP NOT NULL
)ON COMMIT DROP TABLE
;
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO SESSION.DB2P_L2
SELECT A.COLLID,A.NAME,A.VERSION,A.CONTOKEN,A.BINDTIME
FROM DBAMAINT.SYSPACKAGE_DB2P A
WHERE A.NAME LIKE 'BNESC%' AND
HEX(A.CONTOKEN)>=
COALESCE((SELECT MAX(HEX(B.CONTOKEN))
FROM DBAMAINT.SYSPACKAGE_DB2P B
WHERE B.LOCATION=A.LOCATION
AND B.COLLID=A.COLLID
AND B.NAME=A.NAME
AND HEX(B.CONTOKEN)< (SELECT MAX(HEX(C.CONTOKEN))
FROM DBAMAINT.SYSPACKAGE_DB2P C
WHERE C.LOCATION=A.LOCATION
AND C.COLLID=A.COLLID
AND C.NAME=A.NAME)
)
,'0000000000000000');
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
SELECT SUBSTR(A.COLLID,1,25) COLLECTION,
SUBSTR(A.NAME,1,10) PACKAGE,
SUBSTR(A.VERSION,1,26) VERSION,
HEX(A.CONTOKEN) CONTOKEN,
A.BINDTIME BINDTIME
FROM SESSION.DB2P_L2 A
ORDER BY A.COLLID, A.NAME, A.BINDTIME DESC;
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
COLLECTION PACKAGE VERSION CONTOKEN BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INEB BNESCLTR 189EB6471E4A22E0 2009-10-20-11.26.51.549114
INEB BNESCLTR EH00 18634E98179D8464 2008-10-06-11.17.05.800289
INEBRO BNESCLTR 2010-04-05-11.50.22.516769 18B9186F15A78842 2010-04-08-15.54.56.302960
INEBRO BNESCLTR 1864E13E038519ED 2009-10-09-14.50.46.448556
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
Following is the difference,
For Query 2, i got <--- Correct Result
DECLARE GLOBAL TEMPORARY TABLE SESSION.DB2P_L2
(COLLID VARCHAR(128) NOT NULL
,NAME VARCHAR(128) NOT NULL
,VERSION VARCHAR(122) NOT NULL
,CONTOKEN CHAR(8) NOT NULL
,BINDTIME TIMESTAMP NOT NULL
)ON COMMIT DROP TABLE
;
Select query uses a unicode table.
Code:
INSERT INTO SESSION.DB2P_L2
SELECT A.COLLID,A.NAME,A.VERSION,A.CONTOKEN,A.BINDTIME
FROM DBAMAINT.SYSPACKAGE_DB2P A
WHERE A.NAME LIKE 'BNESC%' AND
HEX(A.CONTOKEN)>=
COALESCE((SELECT MAX(HEX(B.CONTOKEN))
FROM DBAMAINT.SYSPACKAGE_DB2P B
WHERE B.LOCATION=A.LOCATION
AND B.COLLID=A.COLLID
AND B.NAME=A.NAME
AND HEX(B.CONTOKEN)< (SELECT MAX(HEX(C.CONTOKEN))
FROM DBAMAINT.SYSPACKAGE_DB2P C
WHERE C.LOCATION=A.LOCATION
AND C.COLLID=A.COLLID
AND C.NAME=A.NAME)
)
,'0000000000000000');
I thought, select query would get the data from unicode table and that data would be inserted into DGT EBCDIC table.
I never thought, the entire thing would happen in EBCDIC since destination table is in EBCDIC. That's new to me.....
