Hi everyone,

Is there any ways to place alerts on DB objects, if a table/View is modified?. So that an alert email will be triggered to us notifying the change??.

Kindly help me out in solving the issue!!!

Thanks,
Dozzy

What DB2 version and OS?

Andy

What Andy said, plus what is the real problem?

Hi,
Details are,
DB2 V8.1 and OS is UNIX.

I have identified a few solution like

1. Using System tables (SYSTABLES, SYSCOLUMNS) for tracking changes
2. Using db2audit system comand.

Can you pls tell me any other solution?

Thanks a lot,
Dozzy

Actual problem is, I need to track the DDL changes in any DB2 table/View.
For example, I have 10 tables and 5 views in my database. If somebody creates a new table and deletes a existing view, then i need to identify 'who, whatand when' information!!!

Is there any ways apart from the above mentioned solutions?

I would start by locking down to make sure that only authorized people can perform those changes.

Then what I have done in the past is created a job that ran every morning and collected the information using db2look. It stored DDL in a file then compared this file to a file that was generated a day prior. And if there were changes found it generated a report that was mailed to me.

Reasons I went with this approach. If an object was dropped and recreated or changed in any way by someone else and I was questioned on it weeks later I would be able to see how it was defined prior to a change. Same went if I changed the objects, as I generally do not remember what I had for breakfast

After performing a comparison I would compress yesterdays file and file it. I stored a year worth of files. Sounds like an over kill, but it saved by behind on at least 2 occasions. If coded properly this process would require no input from you what so ever unless there is a change.

Dozzy,
Why you don't restrict those DDL to only authorized persons by GRANT(and REVOKE) statements.

Hey Thanks soo much Cougar.. Lemme try $ let u know!!!

Tonkuma,

U are correct.. But the problem is, in a batabase of some 3000 tables, it is very difficult to track who has done what chnage and when the change occured!!... So need a unique solution

I think what Tonkuma is referring too is what I have mentioned earlier as well. Only DBA are supposed to make changes in the supporting schema. I did not say db, I said schema. I would not support a schema if I have no full control over for one simple reason. How can I guaranty something if I am not in control of it. Specially if you have 3k tables.

When I say I must have control, I mostly mean DBA team. That said, some DBAs do make changes on the fly with very little or no documentation at all. And those are most of the time that create issues.

Unless you set up some elaborate tracking system I am not sure you would be able to know who made a change. Just that the change was made. Specially if the whole DBA group using the same ID.

Since I do not care to play a blame game, not knowing which DBA made a change is little if any value to me. As long as I know what and when was changed I am a happy camper and my process did just that.

How can you tell who to blame for accidentally dropping a table if all DBAs are using the same instance ID. I'm not sure if db2audit can track the IP address of where the drop was run from...

That is what I mean. You can NOT tell who unless you involve UNIX tracking on who and from where logged in using that id. That said, it is the least of my worries at 2am