DB2 Audit Trigger Generator [long]

External Since: Jul 24, 2005 Posts: 3

Thanks to Serge and MarkB for recent tips and suggestions.

Ive rolled together a few stored procedures to assist with creating
audit triggers automagically. Hope someone finds this as useful as I've
found it educational.

Note:
- I build this for use in a JDEdwards OneWorld environment. I'm not
sure how generic others find it but it should be fairly generic.
- I use a C stored procedure GETJOBNAME to get some extra audit data,
Thanks to MarkB for the idea there. Many people probably wouldn't need
this level of detail.
- I had to be able to turn on journalling on a table within my
procedure and couldn't find a nice way to do this so had to use a CL
stored procedure RCMD to run the CL command line involved. I have
hardcoded the journal library/name. For general use that should be done
with parameters. Love to hear of a better way to do this. Similarly I
need to dynamically create a Library to contain the audit objects so I
use RCMD to do the CRTLIB calls.
- I'd appreciate any contructive criticism.

Basically my start_audit() procedure assembles a string containing a
CREATE TRIGGER statement and executes it dynamically. You call
start_audit('TableSchema', 'AuditSchema', 'TableName', 'Update',
'Colname') to turn on an audit on updates to column ColName in table
TableName in Library TableSchema. A trigger is created on the table,
and an audit table is created in AuditSchema that contains the before
image records with some extra audit fields. Here's the guts of it:

-------------------------------------------------------------------

-- Stub for C Stored Function GETJOBNAME
-- C Source is in OW_AUDIT/QCSRC, Module GetJobName, SvrPgm UTIL looks
like this
/*
* Query full jobname

#define JOBNAME_LEN 10
#define USERNAME_LEN 10
#define JOBNUMBER_LEN 6
#define JOBID_LEN 16

#include <stdio.h>
#include <string.h>
#include <qp0wpid.h>

void GETJOBNAME(
int *in_pid,
char *out_jobname,
short *in_pid_ind,
short *out_jobname_ind,
char *sqlstate, char *funcname, char *specname, char *msgtext)
{
QP0W_Job_ID_T jobinfo;
int rc;
rc = Qp0wGetJobID(*in_pid, &jobinfo);
if (rc)
sprintf(out_jobname, "%d", rc);
else
sprintf(out_jobname, "%.*s/%.*s/%.*s/%d",
JOBNAME_LEN,
jobinfo.jobname,
USERNAME_LEN,
jobinfo.username,
JOBNUMBER_LEN,
jobinfo.jobnumber,
getpid()
);
}
*/
--
drop function OW_AUDIT.GETJOBNAME;

CREATE FUNCTION OW_AUDIT.GETJOBNAME (
PID INTEGER )
RETURNS VARCHAR(43)
LANGUAGE C
SPECIFIC OW_AUDIT.GETJOBNAME
NOT DETERMINISTIC
NO SQL
RETURNS NULL ON NULL INPUT
DISALLOW PARALLEL
EXTERNAL NAME 'OW_AUDIT/UTIL(GETJOBNAME)'
PARAMETER STYLE SQL ;

grant all on function OW_AUDIT.GETJOBNAME to public;

-- create CL Stored Procedure RCMD
drop PROCEDURE ow_audit.RCMD;

CREATE PROCEDURE ow_audit.RCMD(IN CMD CHARACTER (1000))
LANGUAGE CL NOT DETERMINISTIC EXTERNAL NAME
SLIK.RCMD PARAMETER STYLE GENERAL;

grant all on procedure ow_audit.RCMD to public;

/*
This Stored Procedure requires the following CL Code
compiled into the library SLIK

PGM PARM(&EXEC)
DCL VAR(&EXEC) TYPE(*CHAR) LEN(1000)
DCL VAR(&LEN) TYPE(*DEC) LEN(15 5) VALUE(1000)
ADDLIBLE LIB(B7333SYS)
MONMSG MSGID(CPF2103)
CALL PGM(QCMDEXC) PARM(&EXEC &LEN)
ENDPGM
*/

-- Source for Start_Audit - the Audit Trigger Generator
commit;

/*==========================================================================

Procedure:
start_audit

Parameters:
in pTableSchema varchar(10) - The schema (library) containing the
table to audit
in pAuditSchema varchar(10) - The schema (library) to contain audit
data
in pTableName varchar(128) - The table to audit
in pAuditAction varchar(6) - The action (ADD/UPDATE/DELETE) to audit
in pAuditColumn varchar(1024) - (Optional) The column to trigger on
(UPDATE audit only)

Description:
Create a database trigger to implement auditing on any table

Date:
9 August 2005

Author:
J.Oliver

==========================================================================*

drop procedure ow_audit.start_audit;
create procedure ow_audit.start_audit(in pTableSchema varchar(10), in
pAuditSchema varchar(10), in pTableName varchar(128), in pAuditAction
varchar(6), in pAuditColumn varchar(1024) )
language sql
modifies sql data
begin
declare vSQLStmt varchar(16384);
declare vTriggerName varchar(128);
declare vAuditTableName varchar(128);
declare vSQLMsg varchar(32739) default '<none>';
declare SQLCODE integer default 0;
declare vSQLCode integer default 0;
declare vColumnName varchar(30) default '';
declare vColumnList varchar(16384) default '';
declare at_end integer default 0;
declare NotFound condition for SQLSTATE '02000';
declare C1 cursor for select column_name from qsys2.syscolumns
where table_name = pTableName and table_schema = pTableSchema;

declare continue handler for SQLEXCEPTION, SQLWARNING
begin
set vSQLCode = SQLCODE;
get diagnostics condition 1 vSQLMsg = MESSAGE_TEXT;
end;

declare continue handler for NotFound
set at_end = 1;

commit;

-- Tidy up paramaters
set pTableSchema = UCASE(TRIM(pTableSchema));
set pAuditSchema = UCASE(TRIM(pAuditSchema));
set pTableName = UCASE(TRIM(pTableName));
set pAuditAction = UCASE(TRIM(pAuditAction));
set pAuditColumn = UCASE(TRIM(pAuditColumn));

-- create debug message table if not already there
CREATE TABLE ow_audit.DEBUGMSG ( MSG VARCHAR(16384) DEFAULT NULL );

-- turn off any such audit if already present
call ow_audit.stop_audit( pTableSchema, pAuditSchema, pTableName,
pAuditAction, pAuditColumn);

set vAuditTableName = pAuditSchema || '.a_' || pTableName;

-- Drop existing audit table, if any
set vSQLStmt = 'drop table ' || vAuditTableName;
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = ' ||
cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

-- create audit schema if not there already
set vSQLStmt = 'CRTLIB LIB(' || pAuditSchema || ') ASP(1)';
set vSQLCode = 0;
set vSQLMsg = '<none>';
call ow_audit.rcmd(vSQLStmt);
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = ' ||
cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

-- create audit table
set vSQLStmt = 'create table ' || vAuditTableName || ' like ' ||
pTableSchema || '.' || pTableName;
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = ' ||
cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

-- sqlcode -601: Object Exists
if vSQLCode < 0 /* and vSQLCode != -601 */ then
goto return_error;
end if;

-- if vSQLCode = -601 then
-- goto table_exists;
-- end if;

-- if vSQLCode = 0 or SQLCODE = 7905 then

set vSQLStmt = 'alter table ' || vAuditTableName || ' add
column a_user varchar(1 Cool

with default user';
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = '
|| cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

if vSQLCode != 0 then
goto return_error;
end if;

set vSQLStmt = 'alter table ' || vAuditTableName || ' add
column a_timestamp timestamp with default current_timestamp';
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = '
|| cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

if vSQLCode != 0 then
goto return_error;
end if;

set vSQLStmt = 'alter table ' || vAuditTableName || ' add
column a_vTriggerName varchar(128) with default null';
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = '
|| cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

if vSQLCode != 0 then
goto return_error;
end if;

set vSQLStmt = 'alter table ' || vAuditTableName || ' add
column a_jobname varchar(43) with default null';
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = '
|| cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

if vSQLCode != 0 then
goto return_error;
end if;

set vSQLStmt = 'grant all on ' || vAuditTableName || ' to
public';
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = '
|| cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

if vSQLCode != 0 then
goto return_error;
end if;

-- DB2 for iSeries: the Audit Table must be journalled, in case
the trigger runs within a commit controlled transaction started by the
triggerer.
set vSQLStmt = 'STRJRNPF FILE(' || pAuditSchema || '/A_' ||
pTableName || ') JRN(OWJRNL/OW_JRNL) IMAGES(*BOTH) OMTJRNE(*OPNCLO)';
set vSQLCode = 0;
set vSQLMsg = '<none>';
call ow_audit.rcmd(vSQLStmt);
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = '
|| cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

if vSQLCode != 0 /* and SQLCODE != -443 */ then -- SQLCODE
-443 - an error occurred in the trigger or external procedure - in
this case probably just that the table was already journalled
goto return_error;
end if;

-- end if;

table_exists:

/* build the SQL to create the trigger based in this model:

create trigger TESTDTA.tr_F4102_au_IBSAFE
after UPDATE on TESTDTA.F4102
referencing old row as old new row as new
for each row
when (old.ibsafe != new.ibsafe)
insert into A_TESTDTA.a_F4102 select old.IBITM, old.IBLITM,
...., user, current timestamp, 'TESTDTA.tr_F4102_au_IBSAFE',
ow_audit.getjobname(0) from SYSIBM.SYSDUMMY1;

*/

set vTriggerName = pTableSchema || '.tr_' || pTableName || (case
pAuditAction when 'ADD' then '_aa' when 'UPDATE' then '_au' when
'DELETE' then '_ad' end) ;
if length(pAuditColumn) > 0 then
set vTriggerName = vTriggerName || '_' || pAuditColumn;
end if;

-- Assemble the whole create trigger sql statement into one string

set vSQLStmt = 'create trigger ' || vTriggerName || ' after ' ||
pAuditAction ;

if pAuditAction = 'UPDATE' and length(pAuditColumn) > 0 then
set vSQLStmt = vSQLStmt || ' of ' || pAuditColumn;
end if;

set vSQLStmt = vSQLStmt || ' on ' || pTableSchema || '.' ||
pTableName || ' referencing old row as old new row as new for each row
';
-- set vSQLStmt = vSQLStmt || ' set option COMMIT = *NONE ';

-- ensure old and new value really is different if update trigger over
a specified column
if pAuditAction = 'UPDATE' and length(pAuditColumn) > 0 then
set vSQLStmt = vSQLStmt || ' when (old.' || pAuditColumn || '
!= new.' || pAuditColumn || ') ';
end if;

-- get a list of column names in the table
set at_end = 0;
insert into ow_audit.debugmsg values ('Obtaining column names for '
|| vAuditTableName || ' in ' || pAuditSchema);
open C1;
fetch C1 into vColumnName;
while at_end != 1 do
if length(vColumnList) > 0 then
set vColumnList = vColumnList || ', ';
end if;
set vColumnList = vColumnList || 'old.' || vColumnName;
fetch C1 into vColumnName;
end while;

insert into ow_audit.debugmsg values ('Column List is: ' ||
vColumnList);

set vSQLStmt = vSQLStmt || ' insert into ' || vAuditTableName || '
select ' || vColumnList || ', user, current timestamp, ''' ||
vTriggerName || ''', ow_audit.getjobname(0) from SYSIBM.SYSDUMMY1';

set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = ' ||
cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

if vSQLCode < 0 then
goto return_error;
end if;

insert into ow_audit.debugmsg values ('Trigger ' || vTriggerName ||
' created successfully.');

return;

return_error:
insert into ow_audit.debugmsg values ('Trigger ' ||
ifnull(vTriggerName, '<n/a>') || ' not created.');
insert into ow_audit.debugmsg values (' SQLCODE = ' ||
cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);
signal SQLSTATE value '99001' set MESSAGE_TEXT = vSQLMsg;

end;

/*========================================================================*/

-- Tests:
commit;
call ow_audit.start_audit( 'UADTA', 'A_UADTA', 'F4102', 'UPDATE',
'IBSAFE');
select * from ow_audit.debugmsg;

/*==========================================================================

Procedure:
stop_audit
Parameters:
in pTableSchema varchar(10) - The schema (library) containing the
table being audited
in pAuditSchema varchar(10) - The schema (library) containing audit
data
in pTableName varchar(128) - The table being audited
in pAuditAction varchar(6) - The action (ADD/UPDATE/DELETE) being
audited
in pAuditColumn varchar(1024) - (Optional) The column to trigger on
(UPDATE audit only)

Description:
End DB auditing on a specified table.

Date:
9 August 2005

Author:
J.Oliver

==========================================================================*

drop procedure ow_audit.stop_audit;
create procedure ow_audit.stop_audit(in pTableSchema varchar(10), in
pAuditSchema varchar(10), in pTableName varchar(128), in pAuditAction
varchar(6), in pAuditColumn varchar(1024) )
language sql
modifies sql data
begin
declare vSQLStmt varchar(1024);
declare vTriggerName varchar(128);
declare vSQLMsg varchar(32739) default '<none>';
declare sqlcode integer default 0;
declare vSQLCode integer default 0;

declare continue handler for SQLEXCEPTION, SQLWARNING
begin
set vSQLCode = SQLCODE;
get diagnostics condition 1 vSQLMsg = MESSAGE_TEXT;
end;

commit;

delete from ow_audit.debugmsg;

set pTableSchema = UCASE(TRIM(pTableSchema));
set pAuditSchema = UCASE(TRIM(pAuditSchema));
set pTableName = UCASE(TRIM(pTableName));
set pAuditAction = UCASE(TRIM(pAuditAction));

set vTriggerName = pTableSchema || '.tr_' || pTableName || (case
pAuditAction when 'ADD' then '_aa' when 'UPDATE' then '_au' when
'DELETE' then '_ad' end) ;
if length(pAuditColumn) > 0 then
set vTriggerName = vTriggerName || '_' || pAuditColumn;
end if;

set vSQLStmt = 'drop trigger ' || vTriggerName;
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugmsg values (vSQLStmt || ' SQLCODE = ' ||
cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

if vSQLCode < 0 and vSQLCode != -204 then
return;
end if;

insert into ow_audit.debugmsg values ('trigger ' || vTriggerName ||
' dropped.');

end;

/*========================================================================*/

-- Tests
call ow_audit.stop_audit( 'uadta', 'ow_audit', 'f4102', 'update',
'ibsafe');