Author |
Message |
Greg Johnson
Joined: 20 Oct 2001 Posts: 26
|
|
DB2 sys_audit_trail table not being populated |
|
I am evaluating DB Audit using it with DB2. Everything seems to work except the sys_audit_trail is not getting populated which causes no data to apear on the reports. All of the other work audit tables like SYS_CHECKING, SYS_SECMAINT, etc. are getting populated by the SP_DB2AUDITTRAIL procedure. The audit log shows all tables being load. There is no information posted for the SYS_AUDIT_TRAIL table. Is there anything special you need to do to get that table populated?
|
|
Thu Oct 12, 2006 3:04 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
Re: DB2 sys_audit_trail table not being populated |
|
Do you see db_audit.sp_db2audittrail store procedure in the database? If yes, please try executing it manually CALL db_audit.sp_db2audittrail and check if you get any errors. Please let us know what happens when you execute this procedure. : I am evaluating DB Audit using it with DB2. Everything seems to work except : the sys_audit_trail is not getting populated which causes no data to apear : on the reports. All of the other work audit tables like SYS_CHECKING, : SYS_SECMAINT, etc. are getting populated by the SP_DB2AUDITTRAIL : procedure. The audit log shows all tables being load. There is no : information posted for the SYS_AUDIT_TRAIL table. Is there anything : special you need to do to get that table populated?
|
|
Thu Oct 12, 2006 3:28 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Greg Johnson
Joined: 20 Oct 2001 Posts: 26
|
|
Re: DB2 sys_audit_trail table not being populated |
|
Here is what I executed manually: CONNECT TO POSTEST USER xxxx USING xxxx; CALL DB_AUDIT.SPDB2AUDITTRAIL(); I got a good connection to the database and the following in the results from the procedure: Return Status = 0 Nothing came out in the db_audit.log and there are still no rows in the SYS_AUDIT_TRAIL table. When I run the following manually I get db_audit.log entries for the work tables but still no SYS_AUDIT_TRIAL table log entries or rows : SELECT DB_AUDIT.DB2AUDITLOAD('d:\sqllib', 'POSTEST' ) FROM SYSIBM.SYSDUMMY1; CALL DB_AUDIT.SP_DB2AUDITTRAIL() ; I checked the db2diag.log. It shows all of the loads for the work tables only. : Do you see db_audit.sp_db2audittrail store procedure in the database? : If yes, please try executing it manually : CALL db_audit.sp_db2audittrail : and check if you get any errors. Please let us know what happens when you : execute this procedure.
|
|
Thu Oct 12, 2006 4:17 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
Re: DB2 sys_audit_trail table not being populated |
|
This is strange. The mentioned procedure is supposed to merge data from worktables into the SYS_AUDIT_TRAIL table. That's all it does. Please take look what you get in db_audit.log file on the server. The file should be in the same directory where you dropped dbauditRunner.jar file. : Here is what I executed manually: CONNECT TO POSTEST USER xxxx USING xxxx; : CALL DB_AUDIT.SPDB2AUDITTRAIL(); : I got a good connection to the database and the following in the results from : the procedure: Return Status = 0 : Nothing came out in the db_audit.log and there are still no rows in the : SYS_AUDIT_TRAIL table. : When I run the following manually I get db_audit.log entries for the work : tables but still no SYS_AUDIT_TRIAL table log entries or rows : SELECT : DB_AUDIT.DB2AUDITLOAD('d:\sqllib', 'POSTEST' ) FROM SYSIBM.SYSDUMMY1; : CALL DB_AUDIT.SP_DB2AUDITTRAIL() ; : I checked the db2diag.log. It shows all of the loads for the work tables : only.
|
|
Thu Oct 12, 2006 4:25 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Greg Johnson
Joined: 20 Oct 2001 Posts: 26
|
|
Re: DB2 sys_audit_trail table not being populated |
|
I found the problem. Your process does a DB2 LOAD process for each work table. The LOAD process puts the tablespace into a backup pending status. If you are going to use LOAD then you probably need to think about offering the ablility to run the LOAD without recovery so the tablespace will not go into backup pending. After the LOAD processes have executed your stored procedure tries to do inserts into the SYS_AUDIT_TRAIL table. This fails because the tablespace is in backup pending status. Can you fix your process so that we do not have to backup the tablespace every time and then issue the stored procedure manually? : This is strange. The mentioned procedure is supposed to merge data from : worktables into the SYS_AUDIT_TRAIL table. That's all it does. : Please take look what you get in db_audit.log file on the server. The file : should be in the same directory where you dropped dbauditRunner.jar file.
|
|
Thu Oct 12, 2006 5:10 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
Re: DB2 sys_audit_trail table not being populated |
|
Let me look into this. I am not sure why this is happening. It works fine for other people. : I found the problem. : Your process does a DB2 LOAD process for each work table. The LOAD process : puts the tablespace into a backup pending status. If you are going to use : LOAD then you probably need to think about offering the ablility to run : the LOAD without recovery so the tablespace will not go into backup : pending. : After the LOAD processes have executed your stored procedure tries to do : inserts into the SYS_AUDIT_TRAIL table. This fails because the tablespace : is in backup pending status. : Can you fix your process so that we do not have to backup the tablespace : every time and then issue the stored procedure manually?
|
|
Thu Oct 12, 2006 5:23 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
Re: DB2 sys_audit_trail table not being populated |
|
I don't see anywhere in DB2 docs that LOAD puts tablespace into BACKUP mode. Which DB2 version are you running? On which platform? : Let me look into this. I am not sure why this is happening. It works fine for : other people.
|
|
Thu Oct 12, 2006 5:28 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Greg Johnson
Joined: 20 Oct 2001 Posts: 26
|
|
Re: DB2 sys_audit_trail table not being populated |
|
Here is the URL that can help explain various states of a tablespace: http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0407melnyk/index.html#bp Here is a section from the document: A table space is in this state after a point-in-time table space rollforward operation, or after a load operation (against a recoverable database) that specifies the COPY NO option. The table space (or, alternatively, the entire database) must be backed up before the table space can be used. If the table space is not backed up, tables within that table space can be queried, but not updated. NOTE: A database must also be backed up immediately after it is enabled for rollforward recovery. A database is recoverable if the logretain database configuration parameter is set to RECOVERY, or the userexit database configuration parameter is set to YES. You cannot activate or connect to such a database until it has been backed up, at which time the value of the backup_pending informational database configuration parameter is set to NO. We have set our USEREXIT database configuration parameter to YES so that we are able to use logs for recovering and rollforward a database after a restore. This should be a very common practice. Let me know what you think. : I don't see anywhere in DB2 docs that LOAD puts tablespace into BACKUP mode. : Which DB2 version are you running? On which platform?
|
|
Thu Oct 12, 2006 5:39 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Greg Johnson
Joined: 20 Oct 2001 Posts: 26
|
|
Re: DB2 sys_audit_trail table not being populated |
|
Sorry .... forgot the version and environment.... DB2 version 8 fixpack 10 Windows 2003 : I don't see anywhere in DB2 docs that LOAD puts tablespace into BACKUP mode. : Which DB2 version are you running? On which platform?
|
|
Thu Oct 12, 2006 5:45 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
Re: DB2 sys_audit_trail table not being populated |
|
Thanks for the link. Do you think running LOAD with NONRECOVERABLE option will solve the issue? : Here is the URL that can help explain various states of a tablespace: : http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0407melnyk/index.html#bp : Here is a section from the document: A table space is in this state after a : point-in-time table space rollforward operation, or after a load operation : (against a recoverable database) that specifies the COPY NO option. The : table space (or, alternatively, the entire database) must be backed up : before the table space can be used. If the table space is not backed up, : tables within that table space can be queried, but not updated. NOTE: A : database must also be backed up immediately after it is enabled for : rollforward recovery. A database is recoverable if the logretain database : configuration parameter is set to RECOVERY, or the userexit database : configuration parameter is set to YES. You cannot activate or connect to : such a database until it has been backed up, at which time the value of : the backup_pending informational database configuration parameter is set : to NO. : We have set our USEREXIT database configuration parameter to YES so that we : are able to use logs for recovering and rollforward a database after a : restore. This should be a very common practice. : Let me know what you think.
|
|
Thu Oct 12, 2006 6:02 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Greg Johnson
Joined: 20 Oct 2001 Posts: 26
|
|
Re: DB2 sys_audit_trail table not being populated |
|
Yes.... running LOAD with the NONRECOVERABLE option is what we would need. For large loads doing a RUNSTATS on the SYS_AUDIT_TRAIL might be something to consider also. : Thanks for the link. Do you think running LOAD with NONRECOVERABLE option : will solve the issue?
|
|
Fri Oct 13, 2006 9:59 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
Re: DB2 sys_audit_trail table not being populated |
|
There is a version with NONRECOVERABLE option available. I will ask for this stuff and email it to you. As for the RUNSTATS you can probably schedule this as part of periodic audit trail flush to tables process. : Yes.... running LOAD with the NONRECOVERABLE option is what we would need. : For large loads doing a RUNSTATS on the SYS_AUDIT_TRAIL might be something : to consider also.
|
|
Fri Oct 13, 2006 10:45 am |
|
![](templates/GreenTech/images/spacer.gif) |
|