View previous topic :: View next topic
|
Author |
Message |
prasun dhara
New User
Joined: 01 Mar 2008 Posts: 49 Location: kolkata
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
prasun dhara
New User
Joined: 01 Mar 2008 Posts: 49 Location: kolkata
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
prasun dhara
New User
Joined: 01 Mar 2008 Posts: 49 Location: kolkata
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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. . .
And yes, i have provided database administration / support with more than 15 database management systems over the last 40+ years. . . |
|
Back to top |
|
|
prasun dhara
New User
Joined: 01 Mar 2008 Posts: 49 Location: kolkata
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005 Posts: 306
|
|
|
|
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 |
|
|
prasun dhara
New User
Joined: 01 Mar 2008 Posts: 49 Location: kolkata
|
|
|
|
Thank You very much Prasanth.
Just for my knowledge, Is it possible to see the log without any external tools ? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Hi...
is there any similar option in CA-Insight for DB2 ? |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
|
prasun dhara
New User
Joined: 01 Mar 2008 Posts: 49 Location: kolkata
|
|
|
|
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
.
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
ok... Thanks sushanth |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
Quote: |
Thank you enrico-sorichetti for your valuable feedback and value addition to this thread. |
let' s cut the condescending useless BS
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 |
|
Back to top |
|
|
|