Parallel asntdiff Dramatically Improves Performance for Q Rep on z/OS

Posted by Frank Fillmore on February 11, 2013 under DB2 for z/OS, InfoSphere, Q-Replication. Tags: , , .

The asntdiff utility is used to compare the contents of two tables, primarily in the context of SQL and Q Replication.  The problem has been that very large tables can take a very long time to compare.  Moreover, in a dynamic replication environment, the data you are comparing might rapidly be changing.  There were attempts at fixing this problem, primarily by using the asntdiff -f parameter to provide a smaller range of rows to be compared.  Using the -f input file, SQL queries subset the data on a key value (e.g. account number) or transaction timestamp.  The bottom line is that many customers abandoned using asntdiff against production tables with millions or billions of rows.

Parallel asntdiff provides orders-of-magnitude improvements in performance.  The key differences between the processing of the legacy asntdiff and the parallel version are as follows:

  • Parallel processing: partitioning, difference detection, and reporting all in parallel
  • Compare multiple partitions in parallel (source and target table) and retrieve rows from each partition also in parallel from source and target
  • Minimize traffic (only send data needed)
  • Exploit computational capabilities of systems (checksum calculations, etc.)
  • Use a Stored Procedure (ASNTDSP) which runs at source and target systems
  • Avoid storing intermediate results

The results are dramatic.  A European bank killed a legacy asntdiff compare utility run after 17 hours; it might have run for 30.  Parallel asntdiff completed in 5 minutes.

There are some restrictions and advisories:

  1. The source and target tables must have the same code page and collation sequences.
  2. For optimal performance, increase the system resource limit for application threads and set NUMTHREADS to 21.  The minimal requirement is 6 threads.
  3. Since asntdiff can determine the parameter NUMBLOCKS automatically, grant the SELECT privilege on the following catalog tables: SYSIBM.SYSTABLES, SYSIBM.SYSKEYS and SYSIBM.SYSINDEXES.

There are others, but the topic is well worth exploring to speed up asntdiff – or to be able to use it at all.

Thanks to Jayanti Mahapatra and Xiao Li of IBM’s Silicon Valley Lab for some of the material presented here.