How to locate the static SQL fired by an application in DB2

Hitesh Bagchi

We are migrating an application that uses DB2 UDB 8.1.3 as its
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.

Apr 14 '06 #1

Mark A

"Hitesh Bagchi" <nh******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

We are migrating an application that uses DB2 UDB 8.1.3 as its
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.

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

Apr 14 '06 #2

Hitesh Bagchi

When I run ..db2 get dbm cfg, I see the following:
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?

Apr 14 '06 #3

Pierre Saint-Jacques

Given your condition, it means you used:
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" <nh******@gmail.com> a écrit dans le message de news:
11*********************@e56g2000cwe.googlegroups.c om...

When I run ..db2 get dbm cfg, I see the following:
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?

Apr 15 '06 #4