473,581 Members | 2,444 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DB2 Audit Trigger Generator [long]

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('Ta bleSchema', '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_in d,
char *sqlstate, char *funcname, char *specname, char *msgtext)
{
QP0W_Job_ID_T jobinfo;
int rc;
rc = Qp0wGetJobID(*i n_pid, &jobinfo);
if (rc)
sprintf(out_job name, "%d", rc);
else
sprintf(out_job name, "%.*s/%.*s/%.*s/%d",
JOBNAME_LEN,
jobinfo.jobname ,
USERNAME_LEN,
jobinfo.usernam e,
JOBNUMBER_LEN,
jobinfo.jobnumb er,
getpid()
);
}
*/
--
drop function OW_AUDIT.GETJOB NAME;

CREATE FUNCTION OW_AUDIT.GETJOB NAME (
PID INTEGER )
RETURNS VARCHAR(43)
LANGUAGE C
SPECIFIC OW_AUDIT.GETJOB NAME
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.GETJOB NAME to public;

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

CREATE PROCEDURE ow_audit.RCMD(I N 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.syscolumn s
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(pTab leSchema));
set pAuditSchema = UCASE(TRIM(pAud itSchema));
set pTableName = UCASE(TRIM(pTab leName));
set pAuditAction = UCASE(TRIM(pAud itAction));
set pAuditColumn = UCASE(TRIM(pAud itColumn));
-- create debug message table if not already there
CREATE TABLE ow_audit.DEBUGM SG ( MSG VARCHAR(16384) DEFAULT NULL );

-- turn off any such audit if already present
call ow_audit.stop_a udit( 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.debugm sg 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(v SQLStmt);
insert into ow_audit.debugm sg 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.debugm sg 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(18) with default user';
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugm sg 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_timesta mp';
set vSQLCode = 0;
set vSQLMsg = '<none>';
execute immediate vSQLStmt;
insert into ow_audit.debugm sg 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.debugm sg 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.debugm sg 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.debugm sg 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(v SQLStmt);
insert into ow_audit.debugm sg 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_F410 2_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_F41 02 select old.IBITM, old.IBLITM,
...., user, current timestamp, 'TESTDTA.tr_F41 02_au_IBSAFE',
ow_audit.getjob name(0) from SYSIBM.SYSDUMMY 1;

*/

set vTriggerName = pTableSchema || '.tr_' || pTableName || (case
pAuditAction when 'ADD' then '_aa' when 'UPDATE' then '_au' when
'DELETE' then '_ad' end) ;
if length(pAuditCo lumn) > 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(pAuditCo lumn) > 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(pAuditCo lumn) > 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.debugm sg values ('Obtaining column names for '
|| vAuditTableName || ' in ' || pAuditSchema);
open C1;
fetch C1 into vColumnName;
while at_end != 1 do
if length(vColumnL ist) > 0 then
set vColumnList = vColumnList || ', ';
end if;
set vColumnList = vColumnList || 'old.' || vColumnName;
fetch C1 into vColumnName;
end while;

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

set vSQLStmt = vSQLStmt || ' insert into ' || vAuditTableName || '
select ' || vColumnList || ', user, current timestamp, ''' ||
vTriggerName || ''', ow_audit.getjob name(0) from SYSIBM.SYSDUMMY 1';

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

if vSQLCode < 0 then
goto return_error;
end if;

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

return;

return_error:
insert into ow_audit.debugm sg values ('Trigger ' ||
ifnull(vTrigger Name, '<n/a>') || ' not created.');
insert into ow_audit.debugm sg 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.debugm sg;
/*============== =============== =============== =============== ===============

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_a udit;
create procedure ow_audit.stop_a udit(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.debugm sg;

set pTableSchema = UCASE(TRIM(pTab leSchema));
set pAuditSchema = UCASE(TRIM(pAud itSchema));
set pTableName = UCASE(TRIM(pTab leName));
set pAuditAction = UCASE(TRIM(pAud itAction));

set vTriggerName = pTableSchema || '.tr_' || pTableName || (case
pAuditAction when 'ADD' then '_aa' when 'UPDATE' then '_au' when
'DELETE' then '_ad' end) ;
if length(pAuditCo lumn) > 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.debugm sg values (vSQLStmt || ' SQLCODE = ' ||
cast(vSQLCode as varchar(6)) || ', ' || vSQLMsg);

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

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

end;

/*============== =============== =============== =============== =============*/
-- Tests
call ow_audit.stop_a udit( 'uadta', 'ow_audit', 'f4102', 'update',
'ibsafe');

Nov 12 '05 #1
0 2463

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3408
by: ecastillo | last post by:
i'm in a bit of a bind at work. if anyone could help, i'd greatly appreciate it. i have a web app connecting to a sql server using sql server authentication. let's say, for example, my login/password is dbUser/dbUser. the web app however, is using windows authentication. so if I am logged into the network as 'DOMAIN\Eric', when I access...
0
1454
by: Me | last post by:
Hi... A much lamented question, I guess.. I'm trying to create a simple audit trail. log the changes to an SQL 2000 table, so that they are written into a mirror table. The entire record, only the updated one, i.e. if say only one field changes, the audit table will be inserted with one record that has one field changed. if the record has...
3
2728
by: Me | last post by:
Hi... A much lamented question, I guess.. I'm trying to create a simple audit trail. log the changes to an SQL 2000 table, so that they are written into a mirror table. The entire record, only the updated one, i.e. if say only one field changes, the audit table will be inserted with one record that has one field changed. if the record has...
2
2262
by: Zlatko Matić | last post by:
I tried to implement triggers for filling audit-trail table on this way. Everything works fine as long as I don't update the primary key field value. When I try to update PK value, an error occures. The code is the following: CREATE TRIGGER NameOfTheTrigger ON dbo.TableName FOR DELETE, INSERT, UPDATE AS BEGIN declare @type varchar(10) ,
1
1966
by: Jeff Magouirk | last post by:
Dear Group, I would like to create an audit table that is created with a trigger that reflects all the changes(insert, update and delete) that occur in table. Say I have a table with Subject_ID, visit_number, dob, weight, height, User_name, inputdate The audit table would have .
13
4977
by: Jim M | last post by:
I've been playing with Allen Browne's audit code and found it very useful. I need to track record insertions, deletions, and edits for several tables. I am planning to replace Access with Microsoft SQL server for my back end, but continue to use Access for the front end. I understand I can create an audit trail of record changes in SQL at...
0
2736
by: Santiago Cassina | last post by:
Hi list. I just want to send to you an sql file containing tools for audit the UPDATE and DELETE statements in a database by saving all the modifications made by a network/system/database user. I hope you find it useful El Santi =====
2
2579
by: Scott Cain | last post by:
Hello, I am trying to create audit tables for all of the tables in my database. The function, table and trigger create statements are below. Apparently, I am not doing it quite right, because I get these messages when I try to run the create statements below: CREATE FUNCTION CREATE FUNCTION CREATE TABLE
5
6457
by: Michel Esber | last post by:
Audit trigger Hello, LUW DB2 V8 FP13 I am trying to create audit triggers in order to find out which user/application is deleting data from a table, as well as the statement the user entered.
0
7788
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8137
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8163
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6545
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5355
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3799
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2297
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1397
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1127
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.