Replaying past database activity is possible if all required data, logs and information is available. This reference topic shows how a SECADM might replay past database activity via example.
At some point, company auditors might want to analyze the activities of a particular user that occurred in the past. The SECADM can use the backup database images, coupled with the backup logs, and audit logs to reconstitute the database in question and replay the activity the auditors want to analyze. Suppose the activities of a particular user that occurred on April 19, 2006 are in question, the following example shows the flow of how a SECADM would help the auditors carry out their analysis.
SELECT FILE FROM TABLE(SYSPROC.AUDIT_LIST_LOGS('/auditarchive'))
AS T WHERE FILE LIKE 'db2audit.db.sample.log.0.200604%'
FILENAME
---------------------------------------
...
db2audit.db.sample.log.0.20060418235612
db2audit.db.sample.log.0.20060419234937
db2audit.db.sample.log.0.20060420235128
CALL SYSPROC.AUDIT_DELIM_EXTRACT( '', '', '/auditarchive',
'db2audit.db.sample.log.0.20060419234937',
'category execute' )
db2 CONNECT TO sample
db2 SET CURRENT SCHEMA AUDITDATA
db2 -tvf sqllib/misc/db2audit.ddl
db2 LOAD FROM FILE execute.del OF DEL MODIFIED BY LOBSINFILE
INSERT INTO AUDITDATA.EXECUTE
timestamp=2006-04-10-13.20.51.029203;
category=EXECUTE;
audit event=STATEMENT;
event correlator=1;
event status=0;
database=SAMPLE;
userid=smith;
authid=SMITH;
session authid=SMITH;
application id=*LOCAL.prodrig.060410172044;
application name=myapp;
package schema=NULLID;
package name=SQLC2F0A;
package section=201;
uow id=2;
activity id=3;
statement invocation id=0;
statement nesting level=0;
statement text=SELECT * FROM DEPARTMENT WHERE DEPTNO = ? AND DEPTNAME = ?;
statement isolation level=CS;
compilation environment=
isolation level=CS
query optimization=5
min_dec_div_3=NO
degree=1
sqlrules=DB2
refresh age=+00000000000000.000000
schema=SMITH
maintained table type=SYSTEM
resolution timestamp=2006-04-10-13.20.51.000000
federated asynchrony=0;
value index=0;
value type=CHAR;
value data=C01;
value index=1;
value type=VARCHAR;
value index=INFORMATION CENTER;
local_start_time=2006-04-10-13.20.51.021507;
The rollforward
statement would look like this:ROLLFORWARD DATABASE sample
TO 2006-04-10-13.20.51.021507
USING LOCAL TIME AND COMPLETE
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS BLOB(1M) hv_blob;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE c1 CURSOR FOR SELECT COMPENVDESC
FROM AUDITDATA.EXECUTE TIMESAMP= '2006-04-10-13.20.51.029203';
EXEC SQL DECLARE c2 CURSOR FOR SELECT *
FROM DEPARTMENT
WHERE DEPTNO = 'C01'
AND DEPTNAME = 'INFORMATION CENTER';
EXEC SQL OPEN c1;
EXEC SQL FETCH c1 INTO :hv_blob;
EXEC SQL SET COMPILATION ENVIRONMENT :hv_blob;
EXEC SQL OPEN c2;
....
EXEC SQL CLOSE c1;
EXEC SQL CLOSE c2;