How to track table status delete/update/insert status in DB2 V10 z/os?


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
# 1  
How to track table status delete/update/insert status in DB2 V10 z/os?



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  
Please do not cut and paste all your pre-formatted (junk tags) into the forum:

This is text from your irresponsible cut and paste:

Code:
[FONT=Times New Roman][SIZE=3]  
[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dear Team [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]  [FONT=Calibri][SIZE=3]I am using DB2 v10 z/os database . Need expert guidance to figure out best way to track table activities ( Ex Delete, Insert,Update ) [/SIZE][/FONT]
[/SIZE][/FONT][FONT=Calibri][SIZE=3][B]Scenario [/B][/SIZE][/FONT]



[FONT=Times New Roman][SIZE=3]  [FONT=Calibri][SIZE=3]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. [/SIZE][/FONT][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]  [FONT=Calibri][SIZE=3][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][B]Expectations[/B]: Is there a way to capture/track at user level ? Can we track which user deleted this table or updated/inserted etc. [/SIZE][/FONT]
[/SIZE][/FONT][FONT=Times New Roman][SIZE=3]
[/SIZE][/FONT]
[FONT=Calibri][SIZE=3][B]What I Tried[/B] : Trigger was my first thought . Second was temporal tables ( Not explored) [/SIZE][/FONT]


[FONT=Times New Roman][SIZE=3]  [FONT=Calibri][SIZE=3]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 [/SIZE][/FONT]
[/SIZE][/FONT][FONT=Times New Roman][SIZE=3]Example of insert logic[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]
[/SIZE][/FONT]
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=Times New Roman][SIZE=3]  
[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Any help appreciated . Would love to learn new ways to capture this at user level [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]  
[/SIZE][/FONT]

Get rid of all the junk formatting from the site you cut and pasted from and add the correct code tags to your code and repost in a new thread.

Thanks.
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

10 More Discussions You Might Find Interesting

1. Programming

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 .... (1 Reply)
Discussion started by: Perlbaby
1 Replies

2. Shell Programming and Scripting

UNIX with DB2 error status Issue

I have a shell script main.ksh We are calling dbscript.ksh from main.ksh I am using select statement in dbscript.ksh but there is a problem with the select statement in dbscript.ksh but still echo $? is showing as zero. I am using DB2 commands in dbscript.ksh Main.ksh dbscript.ksh echo $? ... (13 Replies)
Discussion started by: vamsi.valiveti
13 Replies

3. Shell Programming and Scripting

Python Script to take file count and insert into DB2 table

Team I have files in different directories . How can i take the count of latest file and insert into Db2 table . I am using awk 'END{print NR+1-ARGC}' (File name) to get the counts. How can i take 1.The count of latest file 2.Insert into Db2 table( File Name and Counts) . cd... (4 Replies)
Discussion started by: Perlbaby
4 Replies

4. UNIX for Dummies Questions & Answers

Problem with script status table

I have a bunch of new, complex scripts that I'm running in Oracle via SQL*Plus under my UNIX account. In an attempt verify that various stages of the script have completed and to locate any errors I periodically toss in a statement such as INSERT INTO status_run9 VALUES... (0 Replies)
Discussion started by: Mike Welch
0 Replies

5. Shell Programming and Scripting

Script to monitor a process and track status in a file

i have a scenario where i need a script that monitors a process "Monitor" based on process id... there can be any number of instances of this running... i start this across 4 servers in NFS. Now i need a file which has the process ids of the process that are currently in execution at any... (9 Replies)
Discussion started by: niteesh_!7
9 Replies

6. Shell Programming and Scripting

Connect status of db2 in Unix shell script

Hi I am trying to connect to db2 database from Unix shell script (k shell) I need to catch the status of connect whether it is succesful or not. ---------------------------------------------------------- # Read login credentials for db2 tput clear echo "Please enter your Userid:\c"... (3 Replies)
Discussion started by: mKarri
3 Replies

7. Shell Programming and Scripting

select values from db1 table and insert into table of DB2

Hi I am having three oracle databases running in three different machine. their ip address is different. from one of the DB am able to access both the databases.(means am able to select values and insert values in to tables individually.) I need to fetch some data from DB1 table(say DB1 ip is... (2 Replies)
Discussion started by: aemunathan
2 Replies

8. Shell Programming and Scripting

How to track exit status of ftp automation

ftp automation code is ftp -v -n -i $host_name << EOF user $u_name $u_pass bi mput $tar_file bye EOF How to check whether the file is successfully transfered or not. Suppose the user name or password is provided wrongly then the code should track the error and ask the end user to enter... (2 Replies)
Discussion started by: Dip
2 Replies

9. Red Hat

Installing RedHat 8.0 onto Dell PowerEdge SC1425 - hdc: status error: status = 0x58

I have successfully installed RedHat 8.0 onto a Dell PowerEdge SC1425 today. This server has two SATA hard drives, and an IDE DVD-ROM drive. Using the following kernel parameters, i successfully installed across both hard drives from CD: ide0=0x1f0,0x3f6,14 vga=791 resolution=1024x768 expert... (5 Replies)
Discussion started by: fishsponge
5 Replies

10. UNIX for Dummies Questions & Answers

Couldn't open status file /var/samba/STATUS.LCK

I believe i have most of samba configured right but i get this error each time time try to run it. I was given suggestion that i touch the file, i did, but i still cannot rid myself of this error. Any suggestions (2 Replies)
Discussion started by: macdonto
2 Replies

Featured Tech Videos