dba-db2.com - db2 performance tuning, DB2 tuning,db2 performance,sql tuning db2, DBA in DB2, DBA Scripts,DB2,DB2 consulting,DB2 consultant,DB2 architecture,DB2 training

Trace sql statements in DB2 database

30 January,2010 by Tom Collins

DB2 trace sql - how can I set up a trace to capture all sql statements?

Use this process to record all sql statements for a give period of time on DB2.

In other words , a DB2 trace

#connect to db

#1-Create event monitor- make sure you've got write permissions to stated folder
db2 "CREATE EVENT MONITOR stmon FOR STATEMENTS WRITE TO FILE '/tmp'"

#2 Turn on event monitor
db2 SET EVENT MONITOR stmon STATE = 1

#3. (Do stuff to the database that you want to monitor)

#4. Turn off event monitor
db2 SET EVENT MONITOR stmon STATE 0

#5. Translate event monitor into readable stuff
db2evmon -path /tmp > /tmp/filtered.out

#6. Read the events
vi /tmp/filtered.out

#7. Delete event monitor

db2 DROP EVENT MONITOR stmon

 

 

Read More

DB2 Query Tuning – db2expln - DBA DB2

Database Tuning as a process and automation - DBA DB2  

DB2 Tuning Tables - DBA DB2

 

Author: Rambler(http://www.dba-db2.com)

Share:

Posted by Tom Collins at 8:06 AM in Performance, Troubleshooting, Tuning | Permalink | Comments (4) | TrackBack (0)

Searches: , , , , , , , , , , ,

is there a way to trace only failed SQL statements ?
Do failed SQL statements make it to the tables mentioned here ?

Posted by: frank pantaleo | Jun 7, 2018 12:25:57 PM

Hi Frank - yes failed statements are captured through the EVENT MONITOR method.

Posted by: Jack Vamvas | Jul 13, 2018 10:16:02 AM

Tried this. Tried
db2 "CREATE EVENT MONITOR stmon6 FOR STATEMENTS WHERE AUTH_ID='SYSADM' WRITE TO FILE '/Logs'"

this too. But always get this error. "The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1614N An I/O error occurred when activating an event monitor. Reason code
= "2". SQLSTATE=58030"

Any Solution?

Posted by: Fahad | Oct 31, 2019 2:58:44 AM

Hi Fahad ,
Have you confirmed the directory path exists?
Does the DB2 instance owner have the correct permissions to go to this
directory and to create/write there?
Is there anything useful in the db2diag output?

Posted by: Jack Vamvas | Nov 7, 2019 1:43:23 AM

Verify your Comment

Previewing your Comment

Posted by: |

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Trace sql statements in DB2 database

Comments are moderated, and will not appear until the author has approved them.

dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright