Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Posts
    23

    Question Unanswered: Way to see what SQL Statements are running?

    Hi all,

    Sorry if this question is asked alot.

    We have a query that keeps blowing up. In the logs:

    "A reference to column <column> is ambiguous"

    So, I'm trying to track down where this thing is blowing up, by seeing what the statement is that is executing when the blow up occurs.

    Is there anyway to see what SQL is running in realtime? A tracefile, or monitor perhaps? I am thinking to use the event monitor...

    Using DB2 V7.2 on Linux Red Hat

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your problem is that you have an sql statement that does a join and both tables have a column of the same name. DB2 is unable to determine which table-column is called for. You should always qualify your columns when you join. e.g.

    select a.c1,a.c2,a.c3,b.c1,b.c2 from mytable1 as a, mytable2 as b
    where (a.c4 = b.c4).

    If you need a monitor, you can look into:
    http://chuzhoi_files.tripod.com/index.html
    for a free DB2 monitor that is rather quite good.

    Is the offending SQl executing in an application, script or what?

    Andy

  3. #3
    Join Date
    Apr 2003
    Posts
    23
    ARWinner,

    Thanks for the tip, that monitor is indeed quite good.

    I also did this for the built in DB2 stuff, but not as good as the tool you showed me:

    1. Create event monitor
    db2 CREATE EVENT MONITOR stmon FOR STATEMENTS WRITE TO FILE '/usr/event_monitor'

    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 /usr/event_monitor > /usr/event_monitor/filtered/filtered.out

    6. Read the events
    vi /usr/event_monitor/filtered/filtered.out
    Last edited by iceman; 04-07-03 at 18:19.

  4. #4
    Join Date
    Apr 2003
    Posts
    23
    ARWinner,

    Thanks for the info on the bombing column. Still haven't tracked it down but trying to trace which statement is bombing. The script is in a Websphere jave application, coming across a remote Websphere server.

    I think you are right though on this one. I'll need to make the SQL more explicit to tell it exactly which column it needs (once I figure it out).

    Thanks millions. :b:

  5. #5
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Event_monitor

    Hai ,

    I tried this method but go an error that the path specifed for event_monitor was not found.

    I am using an windows machine and whould like to know where to create the event_monitor( as /usr/event_monitor).

    I wold be greate full if u could show me the path where to place the event_monitor

    Thanks
    micky

  6. #6
    Join Date
    Sep 2002
    Posts
    456

    Re: Event_monitor

    You need to specify a directory as the path name, sometime you have to put the path in double quotes. If you find it hard to make it work using command line tools, use the GUI tool to create the monitor.

    dollar

    Originally posted by mickykt
    Hai ,

    I tried this method but go an error that the path specifed for event_monitor was not found.

    I am using an windows machine and whould like to know where to create the event_monitor( as /usr/event_monitor).

    I wold be greate full if u could show me the path where to place the event_monitor

    Thanks
    micky

  7. #7
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Re: Event_monitor

    Hai ,
    I tried these stmts:

    db2 => create event monitor stmon1 for statements write to file 'E:\event_monito
    r'
    DB20000I The SQL command completed successfully.
    db2 => set event monitor stmon1 state 1
    DB21034E 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

    what can i do for this.

    Can u also help me to find ant system tables that show the sql statements that have been executen on the database.


    Thanks in advance

    micky

  8. #8
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    db2 get snapshot for all applications , you can run this while the SQL is running to see what db2 is running or you can look at all the dynamic sql by db2 get snapshot for dynamic sql on <instance>

  9. #9
    Join Date
    Apr 2003
    Posts
    23

    Re: Event_monitor

    Originally posted by mickykt
    Hai ,
    I tried these stmts:

    db2 => create event monitor stmon1 for statements write to file 'E:\event_monito
    r'
    DB20000I The SQL command completed successfully.
    db2 => set event monitor stmon1 state 1
    DB21034E 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

    what can i do for this.

    Can u also help me to find ant system tables that show the sql statements that have been executen on the database.


    Thanks in advance

    micky
    Does the directory e:\event_monitor exist?

    Does DB2 have permission to write there?

    Is there enough space on that drive?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •