William Holenstein
Sr. Manager
Shadowbase Product Support
The Shadowbase Audit Reader (S.A.R.) is a useful utility in the Shadowbase product suite for accurately analyzing the database change results of an application. When used during the development of code, for example, one can learn the exact order or sequence in which the application applies changes to a database. These I/O executions can be analyzed to make sure the code is performing results based upon the correct implementation of the intended design algorithm or business rule.
Another use for the Shadowbase Audit Reader is to analyze the TMF audit trail contents to determine where application problems may have occurred and assist in the remediation of the database when these problems are discovered. It is particularly useful for vendor-supplied software (i.e., for cases where you do not have access to the source code) so that you can see exactly how the application is changing the database for each transaction it performs.
Using the SQLCI-like interface to S.A.R., one can write a simple query against the audit trail for the timeframe and the Enscribe file or SQL table in question. The Shadowbase Audit Reader allows one to query the audit trail by timestamp, transaction id, or type of statement (insert, update, delete, transactional boundary-begin commit, etc., for local and network events). It permits the assignment of DDL for Enscribe I/O events, uses the SQL table schema for SQL events, and provides the ability to display the output data in a variety of views and formats showing both the before and after images of the change which was performed by the application.
Let us consider the following example:
Application inserts into a user table and subsequently updates the row due to additional required processing. End of day reconciliation reveals values in row do not balance. Why?
When I perform a SQLCI select against the database, my analysis reveals the target table shows the result of the initial insert (all columns have zeros in them), and not the subsequent update.
>>select * from wtest1;
AKEY ASTRING STRING2
----------- -------------------- ----------
0 0000 0000
--- 1 row(s) selected.
Using the Shadowbase Audit Reader, I performed a broad query against the audit trail to return all the activity for the table for a given timeframe:
Statement one (The output from the audit reader has been truncated for the purposes of this article):
Record Type : INSERT
MAT-File : \S1.$AUDIT.ZTMFAT.AA000025
MAT-Position : 25.993332
MAT-Timestamp : 2009-05-15 16:38:41.687064 (212109179921687064)
Home Node : 2 (\S1)
Transaction ID : \S1(0).1.25289 (216454258747834368)
Undo Flag : 0 (NONE)
Image Length : 38
Object Type : 0 (Not SQL/MX object)
Physical File : \S1.$DATA7.RJMSQL.WTEST1
Logical File : \S1.$DATA7.RJMSQL.WTEST1
File Type : SQL/MP table
COLUMN AFTER
------- --------------------
AKEY 0
ASTRING 0000
STRING2 0000
Statement two, an update, is a different transaction id than statement one:
Record Type : UPDATE
…
Transaction ID : \S1(0).1.25290 (216454258747899904)
…
Physical File : \S1.$DATA7.RJMSQL.WTEST1
File Type : SQL/MP table
COLUMN BEFORE AFTER
------- -------------------- --------------------
AKEY 0 0
ASTRING 0000 0000
STRING2 0000 1111
Statement three, an update has the same transaction id as statement two and it reverses the statement two update:
Record Type : UPDATE
…
Transaction ID : \S1(0).1.25290 (216454258747899904)
Undo Flag : 1 (TRANSACTION)
…
Physical File : \S1.$DATA7.RJMSQL.WTEST1
File Type : SQL/MP table
COLUMN BEFORE AFTER
------- -------------------- --------------------
AKEY 0 0
ASTRING 0000 0000
STRING2 1111 0000
As we can see, three events were returned, an INSERT, and two UPDATES when we did a select for the table in question. Note the transaction id for the insert is different than the transaction id for the two updates. In the second update, the UNDO flag is 1 (which means this event is a reversing event) therefore this transaction aborted. Why? If we use the transaction id of the second statement and perform another select, we can return all the activity associated with that transaction. When we do, we see another update, against table WTEST2, which turns out to be invalid when applied to the database, and resulted in the abort.
TP-SPY 24> add transaction \S1(0).1.25290
TP-SPY 29> run
Start AT-Location:
Start Time : 2009-05-15 17:18:36.681020
MAT-File : $AUDIT.ZTMFAT.AA000025
MAT-Position : 25.991588
MAT-Timestamp : 2009-05-15 16:41:48.978518 (212109180108978518)
Record Type : UPDATE
…
Transaction ID : \S1(0).1.25290 (216454258747899904)
…
Physical File : \S1.$DATA7.RJMSQL.WTEST2
File Type : SQL/MP table
COLUMN BEFORE AFTER
------- -------------------- --------------------
AKEY 1 1
ASTRING CCCC CCCC
STRING2 DDDD XXXX
Record Type : UPDATE
…
Transaction ID : \S1(0).1.25290 (216454258747899904)
Undo Flag : 0 (NONE)
…
Physical File : \S1.$DATA7.RJMSQL.WTEST1
File Type : SQL/MP table
COLUMN BEFORE AFTER
------- -------------------- --------------------
AKEY 0 0
ASTRING 0000 0000
STRING2 0000 1111
Record Type : UPDATE
…
Transaction ID : \S1(0).1.25290 (216454258747899904)
Undo Flag : 1 (TRANSACTION)
…
Physical File : \S1.$DATA7.RJMSQL.WTEST1
File Type : SQL/MP table
COLUMN BEFORE AFTER
------- -------------------- --------------------
AKEY 0 0
ASTRING 0000 0000
STRING2 1111 0000
Record Type : UPDATE
…
Transaction ID : \S1(0).1.25290 (216454258747899904)
Undo Flag : 1 (TRANSACTION)
…
Physical File : \S1.$DATA7.RJMSQL.WTEST1
File Type : SQL/MP table
COLUMN BEFORE AFTER
------- -------------------- --------------------
AKEY 1 1
ASTRING CCCC CCCC
STRING2 XXXX DDDD
Record Type : ABORT
MAT-File : \S1.$AUDIT.ZTMFAT.AA000025
MAT-Position : 25.994796
MAT-Timestamp : 2009-05-15 16:41:25.901381 (212109180085901381)
Home Node : 2 (\S1)
Transaction ID : \S1(0).1.25290 (216454258747899904)
Note the query returns the transactional boundaries (the ABORT, there is no such thing as a BEGIN TRANSACTION EVENT in the audit trail) and any/all events, and the UNDO events.
In summary, the Shadowbase Audit Reader is a powerful utility for displaying the transactional activity that has changed your database. It is particularly useful for understanding how and when the database changes occur, and for analyzing the before and after values of every insert, update, and delete that was applied against your data. Contact Shadowbase Support for more information or to perform a no-obligation trial of the Shadowbase Audit Reader.