DRDA Performance for Q Replication ASNTDIFF Utility on DB2 for z/OS

Posted by Frank Fillmore on February 8, 2010 under DB2 for z/OS, InfoSphere, Q-Replication, SQL Tuning. Tags: , .

As you know, I work with IBM’s Q Replication technology – a lot.  Q Replication functionality is delivered in InfoSphere Replication Server.  The challenges are amplified when working on DB2 for z/OS with *really* large tables.  One financial institution at which I am working has tables with over 1 billion rows and hundreds of partitions.  Of course, DB2 for z/OS can manage tables of that size, but what about the tooling?

Q Replication comes with a utility called ASNTDIFF.  ASNTDIFF compares a checksum of rows in the source and target tables being replicated to validate that there are no discrepancies.  Challenge #1 is that when replicating between DB2 for z/OS subsystems, ASNTDIFF runs under Unix Systems Services (USS) that provides the Unix APIs enabled in z/OS.  There are considerations for USS applications that will form the basis for another post.

Challenge #2 is that ASNTDIFF retrieves the rows from the remote system (Application Server or AS) using a three-part-name query across a Distributed Relational Database Architecture (DRDA) connection.  For example:

SELECT * FROM <location>.<schema>.<tablename>

where location is found in the DRDA Communications Database (CDB) portion of the DB2 for z/OS Application Requestor (AR) catalog tables.  You typically run the ASNTDIFF utility on the replication target DB2 for z/OS server.  That’s because the CDB has probably already been configured to support cursor-based loading of the target tables.  Why is this a challenge?  Well, a three-part-name query across a DRDA pipe against a 1 billion row table ran for about 18 hours.  Ouch!

So the basic problem is: how can I get three-part-name queries running across a DRDA connection between DB2 for z/OS subsystems to run faster?  I asked a couple of IBMers and Jim Pickel pointed me in the direction of exploiting OPTIMZE FOR n ROWS.  There’s a good explanation of this in “Limiting the number of DRDA network transmissions”.  Right now the ASNTDIFF utility adds OPTIMIZE for 1000 ROWS and FOR READ ONLY to every ASNTDIFF query.  We’re experiementing with the recommendations in the “Limiting…” document to see if we need to override this hardcoded parameter.  I’ll keep you posted as to our findings.

2 Comments so far

  1. Nevil February 9, 2012 4:24 am

    Hi Frank,

    I tried to run following command on Linux server with DB2 9.1v.

    db2 asntdiff -f queries.asntdiff

    But it failed to run and thowred SQL10004N error means command not recognized.

    Is ASNTDIFF Utility limited to specific version of DB2 and if it’s available then how to use it.

    Regards,
    Neville

  2. Anonymous March 14, 2012 6:11 pm

    The command should be like,

    asntdiff -f queries.asntdiff

Leave a Comment

Name

Email

Website

Comments

More