Option to record query history?

paulbian · #1 (**permalink**) 06-13-07, 11:48

Hi, I'm very much inexperienced with using DB2 besides the basic queries. There's a problem I'm dealing with, and the simplest solution seems to be recording all the queries that is executed on the database for a short period of time.

Is there an option for DB2 that would allow me to do so? As in:
1. start the process
2. queries made, for example, from a jsp
3. the queries made/history are recorded on file/displayed
4. stop the process and be able to review these actions.

Any feedback is appreciated.

n_i · #2 (**permalink**) 06-14-07, 10:33

It's called "event monitor". Details are in the manual.

paulbian · #3 (**permalink**) 06-14-07, 11:22

Thanks for the reply

However, is there a way to see specific queiries made to the database? I checked the event type "statements", but I don't think it'll allow me to see a "SELECT * from tablename", if that query were to be made to the database.

n_i · #4 (**permalink**) 06-14-07, 12:21

We must be checking different monitors...

Code:

40) Statement Event ...
  Appl Handle: 455
  Appl Id: xxxxxxxxxx.J211.070713142100
  Appl Seq number: 0001

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Open
  Section  : 8
  Creator  : NULLID
  Package  : SYSSN300
  Consistency Token  : SYSLVL01
  Package Version ID  :
  Cursor   : SQL_CURSN300C8
  Cursor was blocking: TRUE
  Text     : SELECT SENSITIVITY FROM USERS WHERE USERNAME=? WITH CS
  -------------------------------------------
  Start Time: 06/14/2007 12:15:32.366847
  Stop Time:  06/14/2007 12:15:32.366942
  Exec Time:  0.000095 seconds
  Number of Agents created: 1
  User CPU: 0.000000 seconds
  System CPU: 0.000000 seconds
  Fetch Count: 0
  Sorts: 0
  Total sort time: 0
  Sort overflows: 0
  Rows read: 0
  Rows written: 0
  Internal rows deleted: 0
  Internal rows updated: 0
  Internal rows inserted: 0
  Bufferpool data logical reads: 0
  Bufferpool data physical reads: 0
  Bufferpool temporary data logical reads: 0
  Bufferpool temporary data physical reads: 0
  Bufferpool index logical reads: 0
  Bufferpool index physical reads: 0
  Bufferpool temporary index logical reads: 0
  Bufferpool temporary index physical reads: 0
  SQLCA:
   sqlcode: 0
   sqlstate: 00000

paulbian · #5 (**permalink**) 06-14-07, 12:24

Alright thanks

I couldn't do it through command line, it keeps giving me some funky error, and I"m pretty sure I have the syntax correctly.

All I needed to do is add the "stmt txt" column to the event analyzer started from the control center. Thanks again!

ankur02018 · #6 (**permalink**) 06-15-07, 13:00

yes there is way through we can look for our fired queries

first take backup of ur database

db2move databasename export -u username - p password

then

db2look - d databasename user username using password -e -a -o db2look.sql

in this way u can see ur queries

u can open db2look.sql file by editplus editor

Internet Services

Web Development

Digital Marketing

Consumer Tech