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.