Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Problem with Data compare and different collating sequences

    I am running DB2 z\OS (mainframe) V9 and DB2 (UDB) on Windows V9.5

    I am using an in-house query tool which has a DataCompare feature to compare a table on the mainframe which was recentely copied down to UDB.

    Here is the DDL for the table:

    Code:
    --------------------------------------------------
    -- Create Table KCINGPRC.TDTOK
    --------------------------------------------------
    Create table KCINGPRC.TDTOK (
        CO_ID                          CHAR(2)             NOT NULL    ,
        DOC_ID                         CHAR(30)            NOT NULL    ,
        DBSRL_ID                       CHAR(4)             NOT NULL    ,
        TOKEN_ID                       CHAR(30)            NOT NULL    ,
        PREV_UPDT_USER_ID              CHAR(8)             NOT NULL   With Default  ,
        PREV_UPDT_TS                   TIMESTAMP                       ,
        DOCSTC_ID                      CHAR(8)             NOT NULL   With Default  ) 
    in DBINGPRC.ZDTOK   ;
    
    --------------------------------------------------
    -- Create Index KCINGPRC.X01DTOK
    --------------------------------------------------
    CREATE UNIQUE INDEX KCINGPRC.X01DTOK 
    	ON KCINGPRC.TDTOK 
    	(CO_ID, DOC_ID, DBSRL_ID, TOKEN_ID) 
    	USING STOGROUP SGPRC011
    	PRIQTY 2160
    	SECQTY 720
    	CLUSTER 
    	BUFFERPOOL BP2;
    
    --------------------------------------------------
    -- Create Index KCINGPRC.X02DTOK
    --------------------------------------------------
    CREATE UNIQUE INDEX KCINGPRC.X02DTOK 
    	ON KCINGPRC.TDTOK 
    	(TOKEN_ID, DOC_ID, DBSRL_ID, CO_ID) 
    	USING STOGROUP SGPRC012
    	PRIQTY 2160
    	SECQTY 720 
    	BUFFERPOOL BP2;
    
    --------------------------------------------------
    -- Create Index KCINGPRC.X03DTOK
    --------------------------------------------------
    CREATE  INDEX KCINGPRC.X03DTOK 
    	ON KCINGPRC.TDTOK 
    	(DOCSTC_ID, DBSRL_ID) 
    	USING STOGROUP SGPRC013
    	PRIQTY 2160
    	SECQTY 720 
    	BUFFERPOOL BP2;
    
    --------------------------------------------------
    -- Create Index KCINGPRC.X04DTOK
    --------------------------------------------------
    CREATE  INDEX KCINGPRC.X04DTOK 
    	ON KCINGPRC.TDTOK 
    	(DBSRL_ID) 
    	USING STOGROUP SGPRC014
    	PRIQTY 2160
    	SECQTY 720 
    	BUFFERPOOL BP2;
    
    --------------------------------------------------
    -- Create Primary Key
    --------------------------------------------------
    alter table KCINGPRC.TDTOK add primary key (CO_ID, DOC_ID, DBSRL_ID, TOKEN_ID);
    
    --------------------------------------------------
    -- Create Foreign Key F01DOCM
    --------------------------------------------------
    alter table KCINGPRC.TDTOK 
    	add foreign key F01DOCM (CO_ID, DOC_ID) 
    	references KCINGPRC.TDOCM (CO_ID, DOC_ID) 
    	On Delete Cascade;
    
    --------------------------------------------------
    -- Create Foreign Key F01DOCS
    --------------------------------------------------
    alter table KCINGPRC.TDTOK 
    	add foreign key F01DOCS (DOCSTC_ID, DBSRL_ID) 
    	references KCINGPRC.TDOCS (DOCSTC_ID, DBSRL_ID) 
    	On Delete Restrict;
    
    --------------------------------------------------
    -- Create Foreign Key F01TOKN
    --------------------------------------------------
    alter table KCINGPRC.TDTOK 
    	add foreign key F01TOKN (TOKEN_ID) 
    	references KCINGPRC.TTOKN (TOKEN_ID) 
    	On Delete Restrict;
    
    --------------------------------------------------
    -- Create Foreign Key F02DBRL
    --------------------------------------------------
    alter table KCINGPRC.TDTOK 
    	add foreign key F02DBRL (DBSRL_ID) 
    	references KCINGPRC.TDBRL (DBSRL_ID) 
    	On Delete Restrict;

    The problem I am having is that because the two databases have a difference in collating order, my results for the compare are showing differences that appear to be only the rows being displayed in a different order than each other.

    To do the compare, my query tool runs a query such as "select *
    from table order by pk-cols". When the databases have different collating
    sequences, the two databases will return the rows in different orders. This
    messes up the merge-match processing used by the compare.

    The vendor says the fix is this:

    Code:
    DB2 for z/OS
    For DB2 for z/OS, you can return the data in a particular collating sequence by two methods. In both of these cases you need to specify that you are comparing a Query rather than a Table.
    
    You the CAST function to cast your primary-key columns into a different collation sequence. Example:
    select cast(pkcol as varchar(20) CCSID ASCII) as pkcol1, col2, col3 etc from table order by 1
    
    If you are running DB2 for z/OS v9 or later you can use the COLLATION_KEY function on the ORDER BY. Example:
    select * from table order by COLLATION_KEY(pkcol, 'UCA400R1_LEN_S3')
    
    DB2/UDB
    Similar to the above:
    
    you can use the CAST method for returning rows in a particular order.
    for DB2/UDB v9.5 or higher, there is a COLLATION_KEY_BIT function. This is similar to the COLLATION_KEY function in the above example.
    My problem is, I do not know how to query the database(s) to find out the collation sequence of each (DB2\z\OS and UDB)

    Can anyone help?

  2. #2
    Join Date
    Dec 2007
    Posts
    288
    I found the db2 configuration file for my UDB database and the Collation_Sequence is UNIQUE


    On the mainframe I ran this:
    SELECT ENCODING_SCHEME
    FROM SYSIBM.SYSTABLES
    WHERE NAME = 'mytable'

    Returns 'E' which is EBCDIC.

    Now I'd like to query a table and return the rows in a certain 'sort order'

    I thought my mainframe query would be this:

    select * from KCINGPRC.TDTOK order by COLLATION_KEY(CO_ID, DOC_ID 'EBCDIC')

    and my UDB query would be this:

    select * from KCINGPRC.TDTOK order by COLLATION_KEY_BIT (CO_ID, DOC_ID 'UNIQUE')

    but neither work. I'm thinking it's just a syntax error - can anyone help?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •