Sponsored Content
Top Forums Programming How to track table status delete/update/insert status in DB2 V10 z/os? Post 303026855 by Perlbaby on Tuesday 4th of December 2018 03:49:02 AM
Old 12-04-2018
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


 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
DB2_COLUMN_PRIVILEGES(3)						 1						  DB2_COLUMN_PRIVILEGES(3)

db2_column_privileges - Returns a result set listing the columns and associated privileges for a table

SYNOPSIS
resource db2_column_privileges (resource $connection, [string $qualifier], [string $schema], [string $table-name], [string $column- name]) DESCRIPTION
Returns a result set listing the columns and associated privileges for a table. PARAMETERS
o $connection - A valid connection to an IBM DB2, Cloudscape, or Apache Derby database. o $qualifier - A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string. o $schema - The schema which contains the tables. To match all schemas, pass NULL or an empty string. o $table-name - The name of the table or view. To match all tables in the database, pass NULL or an empty string. o $column-name - The name of the column. To match all columns in the table, pass NULL or an empty string. RETURN VALUES
Returns a statement resource with a result set containing rows describing the column privileges for columns matching the specified parame- ters. The rows are composed of the following columns: +-------------+---------------------------------------------------+ |Column name | | | | | | | Description | | | | +-------------+---------------------------------------------------+ | TABLE_CAT | | | | | | | Name of the catalog. The value is NULL if this | | | table does not have catalogs. | | | | |TABLE_SCHEM | | | | | | | Name of the schema. | | | | | TABLE_NAME | | | | | | | Name of the table or view. | | | | |COLUMN_NAME | | | | | | | Name of the column. | | | | | GRANTOR | | | | | | | Authorization ID of the user who granted the | | | privilege. | | | | | GRANTEE | | | | | | | Authorization ID of the user to whom the privi- | | | lege was granted. | | | | | PRIVILEGE | | | | | | | The privilege for the column. | | | | |IS_GRANTABLE | | | | | | | Whether the GRANTEE is permitted to grant this | | | privilege to other users. | | | | +-------------+---------------------------------------------------+ SEE ALSO
db2_columns(3), db2_foreign_keys(3), db2_primary_keys(3), db2_procedure_columns(3), db2_procedures(3), db2_special_columns(3), db2_statis- tics(3), db2_table_privileges(3), db2_tables(3). PHP Documentation Group DB2_COLUMN_PRIVILEGES(3)
All times are GMT -4. The time now is 07:12 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy