On Sep 2, 6:06*pm, Ravi <nissanka.r...@gmail.comwrote:
On Sep 1, 4:09*am, "stefan.albert" <stefan.alb...@spb.dewrote:
Hello Ravi,
what you want to do is very tricky, but possible.
I've had this same problem - we want to identify the top user of
tempspace and eventually force him off when using too much space (FS
runs full).
For temptables (NON-DGTT) you can use db2pd to find the owner (-
tcbstats) here the application handle (agent id) is listed. But this
won't help for DGTTs, because this information isn't given.
--Serge : This would be a nice feature to have... because my
solution only works for SMS!
What you can do is this - quite much work:
When a DGTT is held by an agent, you can get information about it by
sending a signal 36 to the agent process. Then it writes (ascii text)
to a trapfile (name: g[PID].[NODE]) which holds your desired
information: The TID of the DGTT. With this information you can derive
the size from the file object in your SMS-containers. I think with DMS
you can't get this infos...
The g[PID].[NODE] file should be deleted after your work - new text
would be appended, thats not nice to parse (and space is used).
Can you please send me a sample script. I donot know how to send a
signal to a agent process and what signal 36 will do. Secondly, does
the signal needs to be sent to all the agents connected to the
database, as It is not known which agent has created Global temporary
table.
Thanks
Ravi- Hide quoted text -
- Show quoted text -
On Sep 2, 6:06 pm, Ravi <nissanka.r...@gmail.comwrote:
On Sep 1, 4:09 am, "stefan.albert" <stefan.alb...@spb.dewrote:
Hello Ravi,
what you want to do is very tricky, but possible.
I've had this same problem - we want to identify the top user of
tempspace and eventually force him off when using too much space (FS
runs full).
For temptables (NON-DGTT) you can use db2pd to find the owner (-
tcbstats) here the application handle (agent id) is listed. But this
won't help for DGTTs, because this information isn't given.
--Serge : This would be a nice feature to have... because my
solution only works for SMS!
What you can do is this - quite much work:
When a DGTT is held by an agent, you can get information about it by
sending a signal 36 to the agent process. Then it writes (ascii text)
to a trapfile (name: g[PID].[NODE]) which holds your desired
information: The TID of the DGTT. With this information you can derive
the size from the file object in your SMS-containers. I think with DMS
you can't get this infos...
The g[PID].[NODE] file should be deleted after your work - new text
would be appended, thats not nice to parse (and space is used).
Can you please send me a sample script. I donot know how to send a
signal to a agent process and what signal 36 will do. Secondly, does
the signal needs to be sent to all the agents connected to the
database, as It is not known which agent has created Global temporary
table.
Thanks
Ravi- Hide quoted text -
- Show quoted text -
Hello Ravi,
there is no sample script - I've written a perl script to do that - it
is tailored to our environment (SMS only).
Also: I'm not allowed to give away our scripts - thats a company
policy... sorry.
You send signal 36 to an agent with "kill -36 $PID ..." or you can use
"db2pd -db $DBNAME -stack $PID".
This signals the agent to write trapfiles with current status
information to DIAGPATH (dbm cfg).
The PIDs of the agent(s) are listed in a snapshot for application.
For your special problem:
You can make snapshots for all applications and have a look at the
values in "Rows written".
If it is not an Insert/Update and the value is "quite big" this could
be a session which uses much temp (either SYS or USER(=DGTT)). But
this only works for the current SQL - if the DGTT was created by an
older SQL you won't see a reference.
You can only guess with the help of "Rows written" for the whole
Session (the first occurence of "Rows written", the second seems to be
for the current SQL running).
