Hello Manas,
we also collect the information available in the dynamic sql cache:
db2 get snapshot for dynamic sql on <dbname> write to file
select current timestamp,length(a.stmt_text) ,generate_unique() ,a.*
from table(sysfun.sqlcache_snapshot()) a
or
insert into table select current timestamp,length(a.stmt_text)
,generate_unique() ,a.*
from table(sysfun.sqlcache_snapshot()) a
or (should work too)
export .. of del/asc ... select current timestamp,length(a.stmt_text)
,generate_unique() ,a.*
from table(sysfun.sqlcache_snapshot()) a
We run this once every hour and insert it into a table.
The problem with this approach is:
- if the interval you use between 2 runs is too smal, you will get a lot of
already captured queries again. To eliminate these duplicates, we use the
soundex function.
- If your interval is too huge, you will loose some of the dynamic sql
statement. This happens if the dynamic sql cache runs out of memory and udb has
to make space for the next statement.
You should run the snapshot-command on a db partition where the data are, to
get information about inserted, updated, delete rows,... You do not get this
information if you run it on partition which is f.e. a dedicated catalog or
connection partition.
Maybe this approach isn't as 100% as a statement event monitor, but I think it
is easier to implement and probably appropriate to get the top xx problem
queries.
Regards,
Daniel
[EMAIL PROTECTED]
-----Original Message-----
From: manas.dasgupta [mailto:[EMAIL PROTECTED]]
Sent: Donnerstag, 12. April 2001 23:02
To: db2eug
Subject: Re: DB2EUG: Formatting snapshot/event monitor outputs:DB2/AIX
Thanks, Daniel....the script is a big help....I had started doing something
similar but using java ....after seeing your script I think it makes more
sense doing it in perl.
Manas.
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 12, 2001 7:26 AM
Subject: RE: DB2EUG: Formatting snapshot/event monitor outputs:DB2/AIX
> Manas,
>
> I use a perl script to bring the snapshot-output for tables and
tablespaces
> into a comma delimited format:
>
> db2_all db2 get snapshot for tables on <DB_NAME> |
fmt_table_tablespace >
> file1.del
> db2_all db2 get snapshot for tablespaces on <DB_NAME> |
fmt_table_tablespace >
> file1.del
>
>
> Quick and dirty, but it works.
>
> Hope this help
>
> Daniel
>
> [EMAIL PROTECTED]
>
> -----Original Message-----
> From: manas.dasgupta [mailto:[EMAIL PROTECTED]]
> Sent: Mittwoch, 11. April 2001 21:50
> To: db2eug
> Subject: DB2EUG: Formatting snapshot/event monitor outputs:DB2/AIX
>
>
> I am currently working on setting up a process of extracting performance
> data from DB2/AIX (snapshot and event monitors) and putting them in tables
> so that we have a record of DB2 activity that we can query against.
> I was wondering if anyone had done similar work parsing through
> snapshot/event monitor output and would like to share scripts or have any
> advice or pointers on this topic.
>
> Thanks,
> Manas.
>
>
>
> =====
> To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
> For other info (and scripts), see
http://people.mn.mediaone.net/scottrmcleod
>
>
----------------------------------------------------------------------------
----
> #!/usr/bin/perl
> # table o.k.
> # tablespace o.k.
>
> $cDelimiter = "";
>
> switch: while (<STDIN>) {
> next if /^\s*(#|$)/;
>
#___________________________________________________________________________
__________
> # skip unimportant header , table related
>
#___________________________________________________________________________
__________
>
> next if /^\s*Table Snapshot/;
> next if /^\s*Tablespace Snapshot/;
> next if /Table List/;
> next if /^\s*Number of accessed tables/;
> next if /^\s*Number of accessed tablespaces/;
> next if /SQL1611W/;
> next if /^\s*Last reset timestamp/; # only for tablespace
> next if /db2 get snapshot for/;
>
>
#___________________________________________________________________________
__________
> # header for table and tablespace
>
#___________________________________________________________________________
__________
>
> /First database connect timestamp/ && do { s/.*=\s(.*)\/(.*)\/(.*)
(.*)\s*/$3-$2-$1-$4/; $szPrefix = $_; next switch; };
> /Snapshot timestamp/ && do { s/.*=\s(.*)\/(.*)\/(.*)
(.*)\s*/$3-$2-$1-$4/; $szPrefix = $szPrefix . "," . $_; next switch; };
> /Database name/ && do { s/.*=\s(.*)\s*/$1/;
$szPrefix = $szPrefix . "," . $_; next switch; };
> /Database path/ && do { s/.*=\s(.*)\s*/$1/;
$szPrefix = $szPrefix . "," . $_; next switch; };
> /Input database alias/ && do { s/.*=\s(.*)\s*/$1/;
$szPrefix = $szPrefix . "," . $_; next switch; };
>
>
#___________________________________________________________________________
__________
> # group change tablespace
>
#___________________________________________________________________________
__________
>
> /Tablespace name/ && $cDelimiter !~ /^$/ && do { printf "\n" ; };
> /Tablespace name/ && do {
> $cDelimiter=",";
> printf "%s" , $szPrefix;
> };
>
>
#___________________________________________________________________________
__________
> # group change table
>
#___________________________________________________________________________
__________
>
> /Table Schema/ && $cDelimiter !~ /^$/ && do { printf "\n" ; };
> /Table Schema/ && do {
> $cDelimiter=",";
> printf "%s" , $szPrefix;
> };
>
>
> /Table Name/ && do { tr/,/./ ; };
>
> s/.*?=\s(.*)\s*/$1/;
> printf "%s%s" , $cDelimiter, $_;
>
> }
>
> printf "%s\n",$szOutputBuffer;
>
>
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
