How to locate the static SQL fired by an application in DB2
Hitesh Bagchi
database. It's an old application and the code is missing as usual.
We are trying to find out the SQL the application is firing by
attaching a statement level event monitor to DB2 and looking at the
report generated by the db2evmon program. We can see to types of
statements in the report, Static and Dynamic. While the Dynamic text
shows actual SQL fired, the static statement comes as blank. Later, we
did see some of these static SQL statements in the SYSCAT.STATMENTS
tables. Yet, it does not show all the SQLs the application is firing
since we are seeing some data entering the database from the
application and which we can subsequently retrieve after an application
restart while, no such SQLs are visible in the report.
Is there a common solution to this problem which I am missing.
Thanks
Warm Regards,
Hitesh Bagchi.
Mark A
news:1144995473.2...@u72g2000cwu.googlegroups.com...
All of the static SQL should be in syscat.statements view. You should do a
snapshot for dynamic SQL. But you must turn on the DFT_MON_STMT switch at
the instance level (db2 get dbm cfg). See the Command Reference for get
snapshot syntax.
If you upgrade to DB2 8.2, then you can use the following to see the dynamic
statements in package cache. If your package cache is big enough, everything
should be there (without having been flushed out). This is probably easier
than the snapshot.
db2pd -database sample -dynamic
Hitesh Bagchi
Default database monitor switches
Buffer pool
(DFT_MON_BUFPOOL) = OFF
Lock
(DFT_MON_LOCK) = OFF
Sort
(DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT)
= OFF
Table
(DFT_MON_TABLE) = OFF
Timestamp
(DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW)
= OFF
Monitor health of instance and databases (HEALTH_MON) = OFF
It shows that DFT_MON_STMT is off. Yet when I run -> db2 get monitor
switches..it shows as on for the statement level. Any idea were I am
going wrong?
Pierre Saint-Jacques
db2 update monitor switches using statements on
This sets the switch on for your session only, not at the dbm cfg level.
DB2 will only provide snapshot statement based info to that session not to
any other appl. or session that would issue the snaphot command.
Example:
D:\SQLLIB\BIN>db2 get dbm monitor switches
DBM System Monitor Information Collected
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 2006-04-14
23.45.10.021050
Lock Information (LOCK) = ON 2006-04-14
23.45.10.021050
Sorting Information (SORT) = ON 2006-04-14
23.45.10.021050
SQL Statement Information (STATEMENT) = ON 2006-04-14
23.45.10.021050
Table Activity Information (TABLE) = ON 2006-04-14
23.45.10.021050
Take Timestamp Information (TIMESTAMP) = ON 2006-04-14
23.45.10.021050
Unit of Work Information (UOW) = ON 2006-04-14
23.45.10.021050
D:\SQLLIB\BIN>db2 update monitor switches using statement off
DB20000I The UPDATE MONITOR SWITCHES command completed successfully.
D:\SQLLIB\BIN>db2 get monitor switches
Monitor Recording Switches
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 2006-04-14
23.45.10.021050
Lock Information (LOCK) = ON 2006-04-14
23.45.10.021050
Sorting Information (SORT) = ON 2006-04-14
23.45.10.021050
SQL Statement Information (STATEMENT) = OFF
Table Activity Information (TABLE) = ON 2006-04-14
23.45.10.021050
Take Timestamp Information (TIMESTAMP) = ON 2006-04-14
23.45.10.021050
Unit of Work Information (UOW) = ON 2006-04-14
23.45.10.021050
HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Hitesh Bagchi" <nhba...@gmail.com> a écrit dans le message de news:
1145005726....@e56g2000cwe.googlegroups.com...
