Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Different result when DGTT table is used

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Apr 20, 2010 2:33 am    Post subject: Different result when DGTT table is used
Reply with quote

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
Code:
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


For Query 3, i got Wrong Result, wrong data is inserted into the table.
Code:
INEB                       BNESCLTR                                189EB6471E4A22E0  2009-10-20-11.26.51.549114
INEB                       BNESCLTR    EH00                        18634E98179D8464  2008-10-06-11.17.05.800289

Can you let me know the mistake i have made to get this inconsistent result.

Thank You in Advance,
Sushanth
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Apr 20, 2010 12:56 pm    Post subject:
Reply with quote

probably your default encoding scheme has changed to unicode.

EBCDIC : 18B9186F15A78842 < 189EB6471E4A22E0
unicode : 18B9186F15A78842 > 189EB6471E4A22E0

try

Code:
DECLARE GLOBAL TEMPORARY TABLE SESSION.DB2P_L2 CCSID EBCDIC

or
Code:
DECLARE GLOBAL TEMPORARY TABLE SESSION.DB2P_L2 CCSID UNICODE

and see the difference
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Apr 20, 2010 2:17 pm    Post subject:
Reply with quote

or do it correct and use
MAX(C.CONTOKEN) or max(pctimestamp)

and not max(hex(contoken))
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Apr 20, 2010 7:10 pm    Post subject:
Reply with quote

Thank You Very Much GuyC,

I will be using pctimestamp.

The thing that confused me is,

By default, this is EBCDIC....
Code:
  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.....

When i changed DGTT to unicode, it worked fine.

Thank You Again,
Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us