Problem with Data compare and different collating sequences

itsonlyme44 · #1 (**permalink**) 03-08-10, 09:01

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?

itsonlyme44 · #2 (**permalink**) 03-08-10, 13:45

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech