INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data Compare between UDB and DB2 Z\OS

Data Compare between UDB and DB2 Z\OS

(OP)
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?  

RE: Data Compare between UDB and DB2 Z\OS

(OP)
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?

RE: Data Compare between UDB and DB2 Z\OS

Have you tried using Federation?

ie, you define a nickname to access your remote zos table in your UDB database.
once you do this, then you can issue

select pkcol1,pkcol2 from zostable_nickname
except
select pkcol1,pkcol2 from luwtable

this should return 0 rows..

 

For db2 resoruces visit www.db2click.com
More DB2 questions answered at www.dbforums.com/f8 & http://www.idug.org/user/UserLogin.asp

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close