×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Data Compare between UDB and DB2 Z\OS

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!

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