-1

I am using DB2 LUW in a windows machine. I want to get the logs for DDL & DML queries used in the database.

The default logs(for example S000001.LOG) contains 'null' and not in a readable format. So I enabled auditing and extracted the archived audit logs into .del files.

But the audit log extraction creates .del like this:

execute.del

"2019-09-05-01.19.44.443001","EXECUTE","STATEMENT",13,0,"TEST2","Administrator","ADMINISTRATOR","ADMINISTRATOR",,,"*LOCAL.DB2.190904193137","db2bp.exe",,,,,,,,"ADMINISTRATOR","SQLC2O29",203,," "," ",10,1,0,0,"WRITE_DML","auditlobs.0.42/","CS","auditlobs.42.808/",1,0,,,,,,"2019-09-05-01.19.44.178765",,"DB2","DESKTOP-R9O62O0"

the empty spaces are like NULNULNUL while opening the file in notepad++

auditlob.file

insert into db2admin.testtable values(223)GEN_CMPL ( DD ( ¸ 0 ¸ 8 ¸ @ ¸ H ¸ P ¸
X ¸

This file contains characters like STX NUL EOX US... etc

In my case either I should get the logs in any readable format(Like db2diag.log file) or I have to forward the logs to a syslog server in a standard format.

What is the best way to do it?

Is there any possibility to write the audit logs as System Application Events Like MSSQL DDL/DML Auditing? so that I could easily forward those logs.

auditlobs.file and execute.del -> https://i.stack.imgur.com/vapyB.jpg

Thanks in advance..!

Selva
  • 25
  • 6

1 Answers1

0

These CSV files may be imported or loaded into Db2 tables for further analysis / processing.
You may use any other tools with an ability to process CSV files and log their contents to whatever system.

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • While loading field "auditlobs.0.42/" with "lobs from" command, I got this result -> https://imgur.com/a/X0xygYL I need the statement in a readable format.! – Selva Sep 06 '19 at 11:21
  • The auditlobs file and execute.del file looks like this -> https://imgur.com/a/9LydhYK – Selva Sep 06 '19 at 11:35
  • @Selva Refer to the following link: [Loading Db2 audit data into tables](https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/t0011543.html). You must use `MODIFIED BY DELPRIORITYCHAR LOBSINFILE` clause for `IMPORT` / `LOAD`. – Mark Barinstein Sep 06 '19 at 14:44