DB2 Trigger To Track Table Changes

[keith.culpepper]

Can anyone please provide some assistance with a trigger that I need to
develop. Here is the situation:

Our program updates depend on database updates. If a client receives
the program update and the database hasn't been updated then it's a
huge mess. No problem right? Just document the changes to the
database that you made and then send out a SQL script that will update
the database with the program update right?

Well, I don't trust anyone here to document anything, so I wanted to
automate the tracking of the changes made to the database. So, I want
to create a table that stores changes made to any part of the database
(create table, drop table, rename table, create column, drop column,
column reorder, change column data type/length/precision).

I am very new to triggers and do not know all I need to know about them
to create what I want so that's why I need someone's help.

(I do not know if DB2 tracks changes somewhere, only place I would know
where to look is on a INSERT,UPDATE,or DELETE from the SYSIBM.SYSTABLES
and SYSIBM.SYSCOLUMNS and SYSIBM.SYSCOLPROPERTIES and SYSIBM.SYSOPTIONS
and SYSIBM.SYSCONSTDEP)

What I need is a trigger that will work one of two ways, if a change
was made to the database then the trigger would either:

1) write a line of text to a txt file or xml file explaining the change
or
2) insert a record into a table that I create so that I can select from
it to view the changes.

Is there an easier way of doing this?

Thanks,
Keith Culpepper
McAleer Solutions

[Pierre Saint-Jacques]

Given that you do not expect these changes to occur many times a day then
the DB2 auditor might be what you need.
Do a db2audit -h and you'll find that you can audit for
OBJMAINT which tracks who, what, where, when DDL is run.
It will show the statement used and so on.

Mind, you should set the status to both so you could see who is successful
as well as who fails.

HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<ke*************@gmail.com> a écrit dans le message de news:
11**********************@e56g2000cwe.googlegroups. com...

Can anyone please provide some assistance with a trigger that I need to
develop. Here is the situation:

Our program updates depend on database updates. If a client receives
the program update and the database hasn't been updated then it's a
huge mess. No problem right? Just document the changes to the
database that you made and then send out a SQL script that will update
the database with the program update right?

Well, I don't trust anyone here to document anything, so I wanted to
automate the tracking of the changes made to the database. So, I want
to create a table that stores changes made to any part of the database
(create table, drop table, rename table, create column, drop column,
column reorder, change column data type/length/precision).

I am very new to triggers and do not know all I need to know about them
to create what I want so that's why I need someone's help.

(I do not know if DB2 tracks changes somewhere, only place I would know
where to look is on a INSERT,UPDATE,or DELETE from the SYSIBM.SYSTABLES
and SYSIBM.SYSCOLUMNS and SYSIBM.SYSCOLPROPERTIES and SYSIBM.SYSOPTIONS
and SYSIBM.SYSCONSTDEP)

What I need is a trigger that will work one of two ways, if a change
was made to the database then the trigger would either:

1) write a line of text to a txt file or xml file explaining the change
or
2) insert a record into a table that I create so that I can select from
it to view the changes.

Is there an easier way of doing this?

Thanks,
Keith Culpepper
McAleer Solutions

[keith.culpepper]

Ok, I forgot to mention that I'm also new to DB2. MSSQL, different
story...

Ok, I guessed that I was supposed to run db2audit -h in the command
line processor but that didn't work. Not real sure how I'm supposed to
so what you recommended. Also, reason I would like to write the
changes either to an output file or to a table is so that we can
archive them and track them back to certain version numbers...

So, suggestions?

Thanks,
Keith

[keith.culpepper]

Ok, got the db2audit to work (sortof) but that's not quite what I want.
Before you extract the audit data to a .log file, it has to store that
information somewhere, right? Can I query this information instead of
extracting it? Or can I setup a trigger to query it?

Thanks,
Keith

[Serge Rielau]

ke*************@gmail.com wrote:

Ok, got the db2audit to work (sortof) but that's not quite what I want.
Before you extract the audit data to a .log file, it has to store that
information somewhere, right? Can I query this information instead of
extracting it? Or can I setup a trigger to query it?

No and No.
You don't appear to be doing this for auditing. Looks more like change
management from a development point of view.
Take a look at the Rational side of the IBM house. (Like Rational
Application Developer etc...)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Bill]

Hi All,

I unload the syscolumns table daily

then do a delta compare (good old mf cobol) - and write the delta
changes to a db2 table

Bill
<ke*************@gmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

Can anyone please provide some assistance with a trigger that I need to
develop. Here is the situation:

Our program updates depend on database updates. If a client receives
the program update and the database hasn't been updated then it's a
huge mess. No problem right? Just document the changes to the
database that you made and then send out a SQL script that will update
the database with the program update right?

Well, I don't trust anyone here to document anything, so I wanted to
automate the tracking of the changes made to the database. So, I want
to create a table that stores changes made to any part of the database
(create table, drop table, rename table, create column, drop column,
column reorder, change column data type/length/precision).

I am very new to triggers and do not know all I need to know about them
to create what I want so that's why I need someone's help.

(I do not know if DB2 tracks changes somewhere, only place I would know
where to look is on a INSERT,UPDATE,or DELETE from the SYSIBM.SYSTABLES
and SYSIBM.SYSCOLUMNS and SYSIBM.SYSCOLPROPERTIES and SYSIBM.SYSOPTIONS
and SYSIBM.SYSCONSTDEP)

What I need is a trigger that will work one of two ways, if a change
was made to the database then the trigger would either:

1) write a line of text to a txt file or xml file explaining the change
or
2) insert a record into a table that I create so that I can select from
it to view the changes.

Is there an easier way of doing this?

Thanks,
Keith Culpepper
McAleer Solutions