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.