One of our customers recently wanted to extract data using db2audit, but the documentation on this topic is very limited and scarce. Moreover, the commands have changed after V9.7 and finding documentation or following it could be tricky. After some testing, we were able to provide the customer with the exact syntax to use to extract data. Here’s how:
Make sure db2audit is on
$db2audit start $db2audit flush
This forces any pending audit records to be written to the audit log. Also, the audit state is reset from “unable to log” to a state of “ready to log” if the audit facility is in an error state.
$ pwd /home/db2inst1/sqllib/security/auditdata $ ls -ltr total 404204 -rw------- 1 db2inst1 db2iadm1 9122 Feb 14 2014 db2audit.instance.log.0.20140214184332 -rw-rw-rw- 1 db2inst1 db2iadm1 0 Feb 14 2014 auditlobs -rw------- 1 db2inst1 db2iadm1 403732695 Dec 9 22:10 db2audit.instance.log.0.20141209221020 -rw------- 1 db2inst1 db2iadm1 3636219 Dec 9 23:32 db2audit.db.WCST01.log.0.20141209233216 -rw-rw-rw- 1 db2inst1 db2iadm1 0 Dec 9 23:40 audit.del -rw------- 1 db2inst1 db2iadm1 5750894 Dec 9 23:42 db2audit.db.DBINST1.log.0 -rw------- 1 db2inst1 db2iadm1 333282 Dec 9 23:42 db2audit.instance.log.0 $ db2audit extract delasc delimiter ! category validate from files /home/db2inst1/sqllib/security/auditdata/db2audit.db.WCST01.log.0.20141209233216 AUD0000I Operation succeeded. $ ls -ltr total 404204 -rw------- 1 db2inst1 db2iadm1 9122 Feb 14 2014 db2audit.instance.log.0.20140214184332 -rw-rw-rw- 1 db2inst1 db2iadm1 0 Feb 14 2014 auditlobs -rw------- 1 db2inst1 db2iadm1 403732695 Dec 9 22:10 db2audit.instance.log.0.20141209221020 -rw------- 1 db2inst1 db2iadm1 3636219 Dec 9 23:32 db2audit.db.WCST01.log.0.20141209233216 -rw-rw-rw- 1 db2inst1 db2iadm1 0 Dec 9 23:40 audit.del -rw-rw-rw- 1 db2inst1 db2iadm1 7295 Dec 9 23:41 validate.del -rw------- 1 db2inst1 db2iadm1 5750894 Dec 9 23:42 db2audit.db.DBINST1.log.0 -rw------- 1 db2inst1 db2iadm1 333969 Dec 9 23:42 db2audit.instance.log.0
For more information please refer: