Question & Answer
Question
It is needed to run db2audit extract on other machine immediately. Could it be possible?
Answer
It is not an expected usage that copy db2audit file db2audit.db.DBNAME.log.0
manually to other machine at any situation. In this article, right after step 2,
the host1 production system crashed by hard disks troubles. There is no Db2 command
available any more but db2audit file only available. There is not enough time to
recover all hard disks and running restore command because local security audit committee
requests the latest db2audit data immediately. It is needed to extract data somehow.
manually to other machine at any situation. In this article, right after step 2,
the host1 production system crashed by hard disks troubles. There is no Db2 command
available any more but db2audit file only available. There is not enough time to
recover all hard disks and running restore command because local security audit committee
requests the latest db2audit data immediately. It is needed to extract data somehow.
For the above type of situation, here is example steps how to try. It has been
verified on AIX 7.2 and Db2 V11.5 Mod4 FixPack 0. It should be applied on Linux as
well. And also Windows as well to run each SQLs and commands manually.
verified on AIX 7.2 and Db2 V11.5 Mod4 FixPack 0. It should be applied on Linux as
well. And also Windows as well to run each SQLs and commands manually.
Environment:
host1 : AIX 7.2 + V11.5 FP4
host2 : AIX 7.2 + V11.5 FP4
host1 : AIX 7.2 + V11.5 FP4
host2 : AIX 7.2 + V11.5 FP4
Steps:
1. Logon as instance owner on host1 and make sure Db2 is up and running.
And then run below on host1.
----------
#!/bin/sh
mkdir $HOME/temp
mkdir $HOME/temp/db2audit
mkdir $HOME/temp/db2audit
db2 "create db db1"
db2 "connect to db1"
db2 "connect to db1"
db2audit configure reset
db2audit configure scope all status both errortype normal
db2audit configure datapath $HOME/temp/db2audit
db2audit configure archivepath $HOME/temp/db2audit
db2audit describe
db2audit configure scope all status both errortype normal
db2audit configure datapath $HOME/temp/db2audit
db2audit configure archivepath $HOME/temp/db2audit
db2audit describe
### start and stop database level auditing ###
db2 -v "create audit policy DB_Policy1 categories all status both error type normal"
db2 -v "audit database using policy DB_Policy1"
db2audit start
db2 -v "create audit policy DB_Policy1 categories all status both error type normal"
db2 -v "audit database using policy DB_Policy1"
db2audit start
db2 -v "create table administrator.t1 (c1 int, c2 int)"
db2 -v "insert into administrator.t1 values (1,1)"
db2 -v "select * from administrator.t1"
db2 -v "delete from administrator.t1"
db2 -v "insert into administrator.t1 values (1,1)"
db2 -v "select * from administrator.t1"
db2 -v "delete from administrator.t1"
db2audit stop
db2 -v "audit database remove policy"
----------
db2 -v "audit database remove policy"
----------
* At this point, host1 has $HOME/temp/db2audit/db2audit.db.DB1.log.0 file.
2. Logon as instance owner on host2 and make sure Db2 is up and running.
And then run below on host2.
----------
#!/bin/sh
And then run below on host2.
----------
#!/bin/sh
mkdir $HOME/temp
mkdir $HOME/temp/db2audit
mkdir $HOME/temp/db2audit
db2 "create db db1"
db2 "connect to db1"
db2 "connect to db1"
db2audit configure reset
db2audit configure scope all status both errortype normal
db2audit configure datapath $HOME/temp/db2audit
db2audit configure archivepath $HOME/temp/db2audit
db2audit describe
db2audit configure scope all status both errortype normal
db2audit configure datapath $HOME/temp/db2audit
db2audit configure archivepath $HOME/temp/db2audit
db2audit describe
### start and stop database level auditing ###
db2 -v "create audit policy DB_Policy1 categories all status both error type normal"
db2 -v "audit database using policy DB_Policy1"
db2audit start
db2 -v "create audit policy DB_Policy1 categories all status both error type normal"
db2 -v "audit database using policy DB_Policy1"
db2audit start
db2audit stop
db2 -v "audit database remove policy"
----------
db2 -v "audit database remove policy"
----------
* At this point, host2 has $HOME/temp/db2audit/db2audit.db.DB1.log.0 file.
* Difference between step 1 and 2 are create, insert, select and delete only
so there is a dummy db2audit start and stop.
* Difference between step 1 and 2 are create, insert, select and delete only
so there is a dummy db2audit start and stop.
3. On host2, run below as backup:
----------
mv $HOME/temp/db2audit/db2audit.db.DB1.log.0 $HOME/temp/db2audit/backup.db2audit.db.DB1.log.0
----------
----------
mv $HOME/temp/db2audit/db2audit.db.DB1.log.0 $HOME/temp/db2audit/backup.db2audit.db.DB1.log.0
----------
4. Copy from host1's $HOME/temp/db2audit/db2audit.db.DB1.log.0 file
to host2's $HOME/temp/db2audit by using rcp/ftp/sftp or any other copying tool.
to host2's $HOME/temp/db2audit by using rcp/ftp/sftp or any other copying tool.
5. On host2, run below two commands by instance owner:
----------
db2audit archive database db1
db2audit extract file audit.txt from files `ls $HOME/temp/db2audit/db2audit.db* | tail -1`
----------
----------
db2audit archive database db1
db2audit extract file audit.txt from files `ls $HOME/temp/db2audit/db2audit.db* | tail -1`
----------
* If the copied db2audit.db.DB1.log.0 file was corrupted,
db2audit extract returns "AUD0022N The Audit Log File is corrupted."
message.
db2audit extract returns "AUD0022N The Audit Log File is corrupted."
message.
* If the copied db2audit.db.DB1.log.0 file was not corrupted,
db2audit extract returns "AUD0000I Operation succeeded."
db2audit extract returns "AUD0000I Operation succeeded."
6. Running 'grep "statement text" audit.txt' returns db2 audit data as:
----------
statement text=create table administrator.t1 (c1 int, c2 int);
statement text=insert into administrator.t1 values (1,1);
statement text=select * from administrator.t1;
statement text=delete from administrator.t1;
statement text=audit database remove policy;
----------
----------
statement text=create table administrator.t1 (c1 int, c2 int);
statement text=insert into administrator.t1 values (1,1);
statement text=select * from administrator.t1;
statement text=delete from administrator.t1;
statement text=audit database remove policy;
----------
Note:
This behavior might be changed without notice in the future. We can confirm whether this
technote is valid or not by following the steps. Please contact your
Sales Rep to submit a potential design change towards a future release. Or please open
a ticket, Request For Enhancement at https://www.ibm.com/developerworks/rfe/
This behavior might be changed without notice in the future. We can confirm whether this
technote is valid or not by following the steps. Please contact your
Sales Rep to submit a potential design change towards a future release. Or please open
a ticket, Request For Enhancement at https://www.ibm.com/developerworks/rfe/
For opening a ticket, here is a good article, may help.
How can I submit a suggestion, Document Change Request (DCR) or Request For
Enhancement (RFE) for DB2 LUW?
http://www-01.ibm.com/support/docview.wss?uid=swg21987419
How can I submit a suggestion, Document Change Request (DCR) or Request For
Enhancement (RFE) for DB2 LUW?
http://www-01.ibm.com/support/docview.wss?uid=swg21987419
Related Information
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PmlAAE","label":"Security and Plug-Ins->Audit"}],"ARM Case Number":"TS003893923","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
05 July 2020
UID
ibm16244094