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.

IBM Big Data, Integration and Governance Forum

Posted by Kim May on January 23, 2013 under Baltimore Washington DB2 Users Group, Big Data, DB2 Education, DB2 Gold Consultants, Frank Fillmore, IBM Information Management Software Sales, IBM Mid Market Customers, IBM Pure Systems, InfoSphere, Netezza, Q-Replication, TFG Blog. Tags: , , , , .

The Fillmore Group – and a special guest to-be-announced soon, will be be participating in the IBM Big Data, Integration and Governance Forum at the Sheraton Hotel in Reston, VA on March 5th.  The forum begins at 8am with a complimentary breakfast and sessions end at 1:15pm, followed by lunch provided by IBM.   There is no charge to attend the forum.

Great speakers are lined up to explain how best to leverage innovative techniques and gain a rock-solid understanding of how to:

  • Enhance the quality, availability and integrity of your data
  • Gain real-time insight into performance
  • Optimize and improve critical decision making
  • Reduce data management cost and complexity issues
  • Enforce data quality standards and stewardship policies
  • Effectively manage regulatory compliance demands
  • Leverage support for master data management (MDM) programs

Read More…

“Updating Netezza with Data from Q Replication”

Posted by Frank Fillmore on January 2, 2013 under DB2 for Linux Unix Windows, DB2 for z/OS, IDAA, InfoSphere, Netezza, Q-Replication.

In cooperation with IBM, The Fillmore Group performed a proof-of-concept for a large, well-known midwestern retailer in 2011 to demonstrate the use of IBM InfoSphere Data Replication (IIDR) Event Publisher (EP) feature to feed OLTP data into a Netezza appliance.  There is now a native changed data capture Apply component for Netezza built on technology IBM acquired from Data Mirror.  This provides for heterogeneous near-real-time OLTP replication to a Netezza appliance.  However – and this is a significant “however” – your shop might already be using Q Replication.  If you are replicating from a DB2 OLTP database, does it make sense to introduce a different replication technology just to keep your Netezza appliance current?

The answer is found in the developerworks post “Updating Netezza with Data from Q Replication“.  Built on the template developed by The Fillmore Group, IBM has published a java consumer that will provide mini-batches of comma delimited data files created by IIDR Event Publisher for ingestion into Netezza using native Netezza utilities.  Note that this approach works for both the new PureData System for Analytics as well as IBM DB2 Analytics Accelerator (IDAA) which are built on Netezza technology.

Kudos to IBMer Cecile Madsen of Silicon Valley Lab for her work on this technique.

First Half 2013 Classes Scheduled

Posted by Kim May on December 19, 2012 under Authorized Training Partner, DB2 Connect, DB2 Education, DB2 for Linux Unix Windows, DB2 for VSE&VM, DB2 for z/OS, InfoSphere, Q-Replication, TFG Blog. Tags: , , , , , .

The Fillmore Group has been delivering IBM Authorized Training classes in our Towson, Maryland classroom for the past several years and believe we offer some of the best DB2 training around – in a comfortable facility at an affordable travel location.  Our first half of 2013 schedule has recently been posted on the IBM website and a complete list of classes is available here.

In addition to the public, open enrollment classes we host on IBM’s behalf, we deliver an extended group of IBM classes directly to organizations as private classes.  The complete list of private classes available is attached here

If you or anyone on your team is looking to upgrade your DB2 skills in 2013 please let me know how I can assist.   Email me directly and I will help!

Unused 2012 Budget? Invest in DB2 Now!

Posted by Kim May on November 26, 2012 under Authorized Training Partner, DB2 Education, DB2 for Linux Unix Windows, DB2 for z/OS, DB2 Gold Consultants, DB2 Migrations, Frank Fillmore, IBM DB2 Services, InfoSphere, Netezza, Optim, Oracle, Q-Replication, TFG Blog. Tags: , , , , , .

Don’t wait – act now!  Invest in DB2 before year end.
 
With 2012 rapidly drawing to a close now is the time to review unused budget funds and invest in DB2 training and services.  The Fillmore Group has our DBA on Demand support option and IBM Authorized Training classes scheduled that can be paid for in advance and used to ensure your team is prepared for a successful 2013.
 
DBA on Demand offers expert consulting services, either onsite or remote, at a discounted hourly rate.  Our “150 for 150” offer delivers 150 hours of support at a discounted hourly rate of $150 per hour.
 
There are still seats available for the December 10th CL412/CL442 DB2 for LUW Performance Tuning and Monitoring Workshop to be delivered by DB2 Gold Consultant Frank C. Fillmore, Jr., in our Towson, Maryland classroom.  Additional TFG classes scheduled are available on the IBM Learning Services website.
 
Please feel free to contact me with any questions on how The Fillmore Group can help preserve your unused funds and help your team have a successful 2013 with DB2!

“Introducing Query Capture and Replay” Webinar Collateral

Posted by Frank Fillmore on October 17, 2012 under DB2 for Linux Unix Windows, InfoSphere, Netezza, Optim, Oracle, SQL Tuning. Tags: , , .

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

Get Ready for Q4 – Get a $50 iTunes Giftcard

Posted by Kim May on September 26, 2012 under Authorized Training Partner, DB2 for Linux Unix Windows, DB2 Migrations, IBM Information Management Software Sales, IBM Mid Market Customers, Informix, InfoSphere, Optim. Tags: , , , , , .

Next week marks the beginning of the final calendar quarter for IBM Information Management sales.  Along with Q4 push comes discounts and special deals to help save money – and encourage you to buy now!
 
As an IBM reseller The Fillmore Group wants you to be a smart shopper and to make sure you compare software quotes before buying during this hectic selling season.  This keeps your vendors honest and may help you avoid overlooking a critical component needed to make the solution a success, like implementation services and training.
 
And, of course, we would love to earn your business.
 
And as an extra incentive to customers looking to purchase IBM Information Management solutions between now and year end, The Fillmore Group will give a $50 iTunes gift card for any competitive Information Management software quotes we prepare for your organization.

Contact me at either 410-465-6335 or via email at kim.may@thefillmoregroup.com if you would like a TFG software quote. 

The fine print:  This offer is valid for IBM DB2, Informix, Optim and InfoSphere products purchased through the IBM Passport Advantage program only.  You must provide your organization’s Passport Advantage Account number to receive a quote.

 

 

InfoSphere Optim Query Capture and Replay – What’s the value?

Posted by Frank Fillmore on August 8, 2012 under DB2 for Linux Unix Windows, DB2 for z/OS, InfoSphere, Optim, Oracle. Tags: , .

Database recovery logs have been around for a long time.  They support rolling back transactions that abend – providing a logical unit of work (LUW).  The also provide roll-forward recovery so that if a database crashes, a full backup restored can be supplemented by all of the transactions, in chronological sequence, that have occured since the last backup was taken.

For many years now, clever engineers have been using this record of transactions for other purposes.  The most prominent is changed data capture: the technique of reading the database recovery log and replaying the transactions on another server to, say, feed a data warehouse or load-balance between two transaction servers.

IBM has packaged its Guardium technology to provide a new way to capture in-flight SQL statements: InfoSphere Optim Query Capture and Replay (IOQCR).  Not the most lyrical of names, but the idea is sound.  Rather than exploit the database recovery log, IOQCR imbeds a Guardium S-TAP in a database server to gather the SQL as it occurs.  This SQL can then be replayed in another environment.  The use case is to take SQL running in a production system and rerun it against Performance, QA, or Test/Dev database servers.

In May I participated in the IOQCR beta and installed it in a small DB2 9 for Windows server.  The purpose was to evaluate basic functionality and usability.  The IBM development team graciously assisted me with the installation and configuration.  It took a few tries – remember, this was an early beta – but I was able to successfully use all of the features.  SQL statements executing on a database server were captured and replayed flawlessly on another.

Rather than drive transaction load through scripting or third-party products, you can use IOQCR to validate database configuration changes or even the viability of running an SQL workload developed for one database server (we’re looking at you, Oracle) on a different vendor platform entirely.  This is a nifty way to test DB2 9 support of Oracle APIs using your own production PL/SQL code- especially dynamic SQL generated on the fly or by an IDE.  DB2, Informix, Netezza, Oracle, Teradata, SQL Server, Sybase, and MySQL are all supported in v1.1 announced on July 10th.

There’s a demonstration on developerWorks.  The Announcement Letter is also useful.

Greetings from IDUG – Lead Catching?

Posted by Kim May on May 16, 2012 under DB2 Education, DB2 for Linux Unix Windows, DB2 for z/OS, DB2 Migrations, Frank Fillmore, IBM DB2 Services, IBM Information Management Software Sales, IBM Mid Market Customers, IDAA, InfoSphere, International DB2 Users Group (IDUG), Netezza. Tags: , , , .

Since early this year IBM has been promoting efforts to better collaborate with their Business Partners by passing more IBM leads to partners.  I am aware of two campaigns in 2012 where IBM has pushed leads from direct IBM sellers to Business Partners.  This makes a lot of sense as it extends the IBM sales team and facilitates more Business Partner involvement with customers.

The talk hasn’t really had any real impact on our business, unfortunately, as the two big lead passing efforts haven’t resulted in anything of substance coming our way.

So, rather than passively waiting for IBM to pass leads the The Fillmore Group, I am trying to reach out to the IBM sellers.  Being a believer in the value of face-to-face meetings and knowing I would be at IDUG this week, I asked my colleague Andrew Stenger to invite as many IBM sellers as possible to join Frank and me for a drink at IDUG so that we can say hello, introduce the value TFG brings to IM customers, and perhaps begin working together. 

Andrew sent the email invitation to a group of IBM salespeople, some of whom we have worked with, and some who have been recommended to us.  I arrived at IDUG last night, am hoping to meet some sellers interested in working with us. 

If you sell IBM Information Management software and would be interested in teaming with TFG consider this an invitation.  We are a Premier Business Partner that is:  1) authorized to deliver IBM training, 2) has a history of successful Information Management product implementations (including DB2 migrations from Oracle), and 3) is certified and ready to sell DB2, InfoSphere, Netezza and IDAA.  Most importantly, if you are at IDUG, we work to support the DB2 community – Frank is a DB2 Gold Consultant and is presenting at the conference, and Frank and I are both Information Champions.  Please stop by and meet Frank Fillmore and me at Peak’s Lounge Thursday at 5:30pm Mountain time.

If you are an IBM customer attending IDUG, and would benefit from working with a Business Partner that will help you get the best deal on IBM software, implement it properly, and understand it well enough to feel confident taking supporting it long-term, please stop by Peak’s too.  It would be even better to meet you!

IDUG NA DB2 Tech Conference 2012 – Replication Topics

Posted by Frank Fillmore on May 9, 2012 under DB2 for Linux Unix Windows, DB2 Gold Consultants, DB2 Migrations, IBM Champion, InfoSphere, International DB2 Users Group (IDUG), Oracle, Q-Replication. Tags: , .

The International DB2 Users Group (IDUG) DB2 Tech Conference will be held in Denver, USA from May 14 – 18 (that’s next week at the time of this post).  There are two events in which I am involved that touch on Replication.

  • On Wednesday, May 16 from 4:00 p.m. to 5:00 p.m. a Replication Special Interest Group (SIG) will be held in Room 110.  Senthil Chandramohan of IBM will be co-leading the SIG with me.  Jenny Pang of Nationwide will be the moderator.  The purpose of the session:

“Meet industry experts and develop a better understanding of the best practices to support a replicated environment.  Are you faced with monitoring replication or determining the latency in replication?  Are your sources and targets in sync?  When problems occur, how do you resolve them?  Come and share your questions and experience with other professionals and other experts in the field of replication.”

  • On Thursday, May 17 from 8:30 a.m. to 9:30 a.m. I will deliver “Zero Outage Oracle to DB2 Migrations” (Session G09) in Room 112.  Replication figures prominently in reducing a migration outage window.  The abstract:

“IBM has enabled the Oracle PL/SQL API’s in DB2 for Linux, Unix, and Windows v9.7; 97% of Oracle application code will work unmodified. So, how best to migrate the Oracle data to DB2? The IBM Data Migration Tool (IDMT) fosters a batch migration which requires an application outage – backup, IDMT, restore, SET INTEGRITY – which can last for hours or days depending on the volume of data and complexity of the data model. What if my website, eCommerce, OLTP application can’t be down that long? Learn how a world-wide Top 50 financial institution migrated data from Oracle to DB2 with a minimal application outage using InfoSphere Replication Server’s heterogeneous replication from Oracle to DB2.”

I hope to see you in Denver.  Safe travel!