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
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
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
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
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
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
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
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
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
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
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
LEARN ABOUT DEBIAN
ocf_heartbeat_db2
OCF_HEARTBEAT_DB2(7) OCF resource agents OCF_HEARTBEAT_DB2(7)
NAME
ocf_heartbeat_db2 - Resource Agent that manages an IBM DB2 LUW databases in Standard role as primitive or in HADR roles as master/slave
configuration. Multiple partitions are supported.
SYNOPSIS
db2 [start | stop | monitor | promote | demote | meta-data | validate-all]
DESCRIPTION
Resource Agent that manages an IBM DB2 LUW databases in Standard role as primitive or in HADR roles in master/slave configuration. Multiple
partitions are supported.
Standard mode:
An instance including all or selected databases is made highly available. Configure each partition as a separate primitive resource.
HADR mode:
A single database in HADR configuration is made highly available by automating takeover operations. Configure a master / slave resource
with notifications enabled and an additional monitoring operation with role "Master".
In case of HADR be very deliberate in specifying intervals/timeouts. The detection of a failure including promote must complete within
HADR_PEER_WINDOW.
In addition to honoring requirements for crash recovery etc. for your specific database use the following relations as guidance:
"monitor interval" < HADR_PEER_WINDOW - (appr 30 sec)
"promote timeout" < HADR_PEER_WINDOW + (appr 20 sec)
For further information and examples consult http://www.linux-ha.org/wiki/db2_(resource_agent)
SUPPORTED PARAMETERS
instance
The instance of the database(s). (unique, required, string, no default)
dblist
List of databases to be managed, e.g "db1 db2". Defaults to all databases in the instance. Specify one db for HADR mode. (optional,
string, no default)
admin
DEPRECATED: The admin user of the instance. (optional, string, no default)
dbpartitionnum
The number of the partion (DBPARTITIONNUM) to be managed. (optional, string, default 0)
SUPPORTED ACTIONS
This resource agent supports the following actions (operations):
start
Starts the resource. Suggested minimum timeout: 120.
stop
Stops the resource. Suggested minimum timeout: 120.
promote
Promotes the resource to the Master role. Suggested minimum timeout: 120.
demote
Demotes the resource to the Slave role. Suggested minimum timeout: 120.
notify
Suggested minimum timeout: 10.
monitor
Performs a detailed status check. Suggested minimum timeout: 60. Suggested interval: 20.
monitor (Master role)
Performs a detailed status check. Suggested minimum timeout: 60. Suggested interval: 22.
validate-all
Performs a validation of the resource configuration. Suggested minimum timeout: 5.
meta-data
Retrieves resource agent metadata (internal use only). Suggested minimum timeout: 5.
EXAMPLE
The following is an example configuration for a db2 resource using the crm(8) shell:
primitive p_db2 ocf:heartbeat:db2
params
instance=string
op monitor depth="0" timeout="60" interval="20"
op monitor depth="0" timeout="60" role="Master" interval="22"
ms ms_db2 p_db2
meta notify="true" interleave="true"
SEE ALSO
http://www.linux-ha.org/wiki/db2_(resource_agent)
AUTHOR
Linux-HA contributors (see the resource agent source for information about individual authors)
resource-agents UNKNOWN 03/09/2014 OCF_HEARTBEAT_DB2(7)