DB2 z/os How to Track table delete Activities

Tags
db2, delete, os, programming, table

 
Thread Tools Search this Thread
# 1  
Old 12-04-2018
DB2 z/os How to Track table delete Activities

Dear Team
I am using DB2 v10 z/os database . Need expert guidance to figure out best way to track table activities ( Ex Delete, Insert,Update )
Scenario
We have a table which is critical and many developer/testing team access on daily basis . We had instance where some deleted entire table . Since its test environment - the privileges are given as expected.
Expectations: Is there a way to capture/track at user level ? Can we track which user deleted this table or updated/inserted etc.
What I Tried : Trigger was my first thought . Second was temporal tables ( Not explored)
I created a trigger with table that captures insert records with below example . I am able to figure out whenever insert/update/delete happens . however I am unable to capture which user did this . How can be track this ? Can we use SYSIBM.SYSTABAUTH ( Grantee column has user name ) But how can this be mapped
Example of insert logic


CREATE TRIGGER TEST_SAMPLE_INSERT
AFTER INSERT ON TEST_SAMPLE
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO X_TEST_SAMPLE ( action_timestamp,action_type,before_or_after, Other attributes of table )
Values (current timestamp,'I','A',Other attributes of table )


Any help appreciated . Would love to learn new ways to capture this at user level
# 2  
Old 12-04-2018
This is my second warning... do not post by cutting and pasting formatting "junk" from another web site here.

You need to get rid of all this "other invalid formatting" before posting.
Code:
[FONT=Corbel]Dear Team 
[/FONT][FONT=Corbel]I am using DB2 v10 z/os database . Need expert guidance to figure out best way to track table activities ( Ex Delete, Insert,Update ) 
[/FONT][B][FONT=Corbel]Scenario [/FONT][/B][FONT=Corbel]
[/FONT][FONT=Corbel]We have a table which is critical and many developer/testing team access on daily basis . We had instance where some deleted entire table . Since its test environment - the privileges are given as expected. [/FONT][FONT=Corbel]
[/FONT][B][FONT=Corbel]Expectations[/FONT][/B][FONT=Corbel]: Is there a way to capture/track at user level ? Can we track which user deleted this table or updated/inserted etc. 
[/FONT][B][FONT=Corbel]What I Tried[/FONT][/B][FONT=Corbel] : Trigger was my first thought . Second was temporal tables ( Not explored) 
[/FONT][FONT=Corbel]I created a trigger with table that captures insert records with below example . I am able to figure out whenever insert/update/delete happens . however I am unable to capture which user did this . How can be track this ? Can we use SYSIBM.SYSTABAUTH ( Grantee column has user name ) But how can this be mapped 
Example of insert logic[/FONT][FONT=Corbel]

CREATE TRIGGER TEST_SAMPLE_INSERT
AFTER INSERT ON TEST_SAMPLE
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO X_TEST_SAMPLE ( action_timestamp,action_type,before_or_after, Other attributes of table )
Values (current timestamp,'I','A',Other attributes of table )
[/FONT][FONT=Corbel]
[/FONT][FONT=Corbel]
[/FONT][FONT=Corbel]Any help appreciated . Would love to learn new ways to capture this at user level[/FONT]


|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
How to track table status delete/update/insert status in DB2 V10 z/os? Perlbaby Programming 1 12-04-2018 05:14 AM
Python Script to take file count and insert into DB2 table Perlbaby Shell Programming and Scripting 4 12-27-2017 06:17 AM
DB2 - Tracking Table history Perlbaby Programming 1 12-18-2017 09:57 AM
DB2 Query to check table counts,start end time Perlbaby Programming 0 12-18-2017 07:09 AM
awk file to read values from Db2 table replacing hard coded values asandy1234 Shell Programming and Scripting 9 05-22-2014 12:26 PM
Delete the records from table zxcjggu708 Shell Programming and Scripting 5 09-24-2013 03:31 AM
Read parameter file in a shell script to unload a DB2 Table??? developer.dwh9 Shell Programming and Scripting 10 12-12-2011 02:13 PM
select values from db1 table and insert into table of DB2 aemunathan Shell Programming and Scripting 2 02-10-2010 11:35 PM
Connect to DB2 table using Shell scripts onlyraja Shell Programming and Scripting 1 01-15-2010 06:06 AM
Export data from DB2 table to .txt file(space delimited) ss3944 Shell Programming and Scripting 2 12-17-2009 08:53 AM
Compare date from db2 table to yesterday's Unix system date sasaliasim Shell Programming and Scripting 9 12-01-2008 11:37 PM
Advice on extracting special characters from a DB2 table to a file in the UNIX ENV cosec UNIX for Dummies Questions & Answers 1 09-03-2008 03:10 AM
How to connect DB2 table using shell script dtidke Shell Programming and Scripting 0 06-24-2008 06:18 AM
Track changes to Oracle table mervinboyz Shell Programming and Scripting 0 03-27-2008 05:48 AM
Possible to track FTP user last login? Last and Finger don't track them. LordJezo UNIX for Dummies Questions & Answers 1 11-08-2007 01:21 PM