As promised, the replay of the “Introducing [InfoSphere Optim] Query Capture and Replay” (QCR) webinar delivered on October 16, 2012 is on ChannelDB2.  The presentation materials for the webinar are here: Query Capture and Replay Webinar 2012-10-16.

For assistance implementing QCR: Infosphere Optim Query Capture and Replay

As much as I dislike the relentless repetition that’s part of the nature of the world of blogs and twitter and listserves and email blasts, here I go with a shameless pitch for a TFG special services offering I emailed to several DB2 users earlier today.  I am doing this because, at the end of the day, the rate disparity in today’s DB2 services market baffles me.  Are the ridiculously high rates being charged eroding product adoption?  I am afraid so, which is why we are offering a summer services special. 

Read More…

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.

The second LUW track meeting is scheduled for this Friday, March 6, 2009, at the IBM TEC in McLean, Virginia.  Petrus Chan from the Toronto Lab will be presenting. 

I’ve spent a good portion of the past couple weeks getting individual invitations out to local DB2 for LUW users but if you are out there and I have not personally invited you, please consider yourself welcome – and try to join us.  Details on the presentations are below.   There is no charge for the sessions (they are *free* thanks to Tak Lee and the local IBM team) and pizza will be provided by The Fillmore Group. Read More…