Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Trace or way to find who deleted records from a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Tue Sep 13, 2011 7:25 pm    Post subject: DB2 Trace or way to find who deleted records from a table
Reply with quote

Hi,

In our site there is a table which is refreshed(deleted all rows) every day.
Is there any way to know which user/plan is delting the table?

I have checked in the SYSTABAUTH and found only one plan has the INSERT access to that particular table. and I have verified that that plan has only insert statement.So is there any easier way to find out who is deleting the table rows.

We have CA platinum tool also. So if someone tell me how to check it using it would also be fine.

Thanks
Prasun
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 13, 2011 8:39 pm    Post subject:
Reply with quote

Hello,

If this happens every day, it may be a regular scheduled job. . .

Suggest you check with the scheduling or operations people to find out.

If it is not scheduled, you may be able to get the dba to look at the logs and determine which id is involved.
Back to top
View user's profile Send private message
prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Tue Sep 13, 2011 9:46 pm    Post subject: Reply to: DB2 Trace or way to find who deleted records from
Reply with quote

Actually this is happening in a particular test region where no scheduler available.

Can you please let me know if LOG analysing is the only way or not ?

If Yes please tell how to do that using platinum?

Thanks
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Sep 13, 2011 10:10 pm    Post subject:
Reply with quote

Prasun,

CA Log Analyzer can you used to identify the AUTHID, it can produce huge number of data, better to consult with your DBA.

Thanks,
Sushanth
Back to top
View user's profile Send private message
prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Tue Sep 13, 2011 11:40 pm    Post subject:
Reply with quote

Yes I know It is always better to consult with DBA but I want to know if you are a DBA what will you do in this situation/what will be your approach?

Thanks
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 14, 2011 12:34 am    Post subject:
Reply with quote

Hello,

Quote:
but I want to know if you are a DBA what will you do in this situation/what will be your approach?

Which part of looking at the db2 logs was not clear. . . icon_sad.gif

And yes, i have provided database administration / support with more than 15 database management systems over the last 40+ years. . .
Back to top
View user's profile Send private message
prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Fri Sep 16, 2011 10:47 pm    Post subject: Reply to: DB2 Trace or way to find who deleted records from
Reply with quote

Quote:
And yes, i have provided database administration / support with more than 15 database management systems over the last 40+ years. . .


Then I am sure you will be able to help me. I am just looking for help from very experienced people like you.

Could you please let me know the process to analyse the DB2 Log.
If you just give me some link where I can find the detailed process to view and anlyse the log would also be fine

Thanks in advance
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 16, 2011 11:02 pm    Post subject:
Reply with quote

Hello,

The best person to ask is your dba. There is probably already somethig in place. Many places do not allow developers to run this type of process, so the dba must be involved.

There are also site-specific configuration consideratons that your dba will need to provide even if you are permitted to do this.

There are several db2 log analysis tools - one or more of which may be installed on your system. Again, the dba would know.
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Tue Sep 20, 2011 3:02 pm    Post subject:
Reply with quote

Since you specified your site has Platinum tool, I am just specifying main things how can you can get this through LA

One you enter PLA you will have an option LA (Log Analyzer)

When you enter LA option you have an option called Process Log

Enter Process Log and Select DML Activity

You should select the Output Format,Level of Detail etc etc an Include I in Table Filters

Give the tablename creator and where condotion if you have one. Press F3 thrice and enter start and end time ( In your case I guess its one day)

Give the report Dataset name and execute it in batch . It should give you the report of activity happening in that table .Remeber you will get a lot of records if the DML activity is high . Also a bigger time frame makes the job run a pretty long time
Back to top
View user's profile Send private message
prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Tue Sep 20, 2011 7:41 pm    Post subject:
Reply with quote

Thank You very much Prasanth.

Just for my knowledge, Is it possible to see the log without any external tools ?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 20, 2011 8:11 pm    Post subject:
Reply with quote

Hello,

Quote:
Is it possible to see the log without any external tools ?
There are lots of places where people "rolled-their-own". A thorough understanding of the db2 logs is required.

Keep in mind that if it were really simple, there would not be vendors successfully selling products icon_smile.gif
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Sep 21, 2011 3:47 pm    Post subject:
Reply with quote

Hi...

is there any similar option in CA-Insight for DB2 ?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Wed Sep 21, 2011 3:52 pm    Post subject: Reply to: DB2 Trace or way to find who deleted records from
Reply with quote

Quote:
In our site there is a table which is refreshed(deleted all rows) every day.

the topic as posted is pretty <dumb>
&holy &deity how can such an activity go by undocumented and somehow unnoticed ?

I would understand something like ...
sometimes a row disappears we are trying to understand when/what/who/how

but &my &deity why such a question for something that happens everyday
instead of investing on tools and friends I suggest to invest on documentation standards and availability
Back to top
View user's profile Send private message
prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Thu Sep 22, 2011 10:56 am    Post subject:
Reply with quote

Thank you enrico-sorichetti for your valuable feedback and value addition to this thread.

This thing is happening in one test region and really there is no such document.

I tried to find out in this thread if we can identify
Quote:
when/what/who/how
.

But discussing in this thread with technical topic like DB2 Log is enriching my knowledge and it is not at all wastage of time for me at least.

Thanks
Prasun
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Sep 22, 2011 11:50 am    Post subject:
Reply with quote

Hi Gylbharat,

Quote:
is there any similar option in CA-Insight for DB2 ?

In CA-Insight, you can find information using Thread History(if required traces are on), but it doesn't filter by database/table/creator like that as in LA, but it provides more information which you can drill down. I don't think it will provide you information regarding the data affected.

Thanks,
Sushanth
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Sep 22, 2011 12:48 pm    Post subject:
Reply with quote

ok... Thanks sushanth
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Thu Sep 22, 2011 1:10 pm    Post subject: Reply to: DB2 Trace or way to find who deleted records from
Reply with quote

Quote:
Thank you enrico-sorichetti for your valuable feedback and value addition to this thread.

let' s cut the condescending useless BS icon_evil.gif

what I am trying to point out is that You are tackling the issue from the wrong side ...

ask instead ...
what process depends on the table being refreshed ( and that should be documented somewhere )
also why not think that the table might be refreshed using a <reload> utility...
seems very dumb to write a program to delete the old rows and insert the fresh ones
that' s why trying to solve the issue <bottom up> ( using lowly technicalities ) is less effective that the <top down>

ask why and it might be easier to find who

but looks that around here organization is an unknown concept icon_cool.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts DUMP DATASETS VS TRACE DATASETS! lind sh CICS 4 Tue Dec 06, 2016 11:31 pm
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us