Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2009
    Posts
    5

    Unanswered: Alerts on Table/View Change

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS?

    Andy

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    What Andy said, plus what is the real problem?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Oct 2009
    Posts
    5
    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

  5. #5
    Join Date
    Oct 2009
    Posts
    5
    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?

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Dozzy,
    Why you don't restrict those DDL to only authorized persons by GRANT(and REVOKE) statements.

  8. #8
    Join Date
    Oct 2009
    Posts
    5
    Hey Thanks soo much Cougar.. Lemme try $ let u know!!!

  9. #9
    Join Date
    Oct 2009
    Posts
    5
    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

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Cougar8000
    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.
    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...

  12. #12
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •