Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    27

    Unanswered: The db2 view problem

    Hi all
    I am new to db2. Would any one help me on which view to check which users are connected to db2(just like V$session in oracle), and which view or tool have same function with logminer in oracle?
    thx very much

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: The db2 view problem

    V$session's equivalent is the command 'db2 list applications show detail'

    and AFAIK, there is not equivalent to logminer ..

    I have assumed you are asking about db2 on unix

    Cheers
    Sathyaram

    Originally posted by chansteve1234
    Hi all
    I am new to db2. Would any one help me on which view to check which users are connected to db2(just like V$session in oracle), and which view or tool have same function with logminer in oracle?
    thx very much
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2003
    Posts
    27
    Thx very much.
    How can we check the user have issue which SQL statement and at which time ?
    I have try syscat.statements. But seems i cant see the text of the SQL statement.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    That's a bit tricky ...

    You will have to use event monitors and/or snapshot monitors ...

    SYSCAT.STATEMENTS has one row for each statement in a package ...

    HTH
    Sathyaram

    Originally posted by chansteve1234
    Thx very much.
    How can we check the user have issue which SQL statement and at which time ?
    I have try syscat.statements. But seems i cant see the text of the SQL statement.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Sep 2003
    Posts
    27
    Thx , I can find the statement in the event monitor.
    But, If i want to find specific a user 's statements.(e.g john have issue select * from employee at 6:00), it seems difficult. Cause I only can see the appl_id at connection tag in event monitor. The appl_id may be equal to some strange word like PE833BA.GD80. Can i have a more easy way to find the specific user 's statement??
    I know there are "where auth_id clause" for create event monitor clause.
    But it seems make the system overhead when we create 200 event monitor for 200 user.(if i have 200 user)
    thx very much

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If your application id starts like AC100384 , it is the hex of ip address ...

    AC.10.03.84 - 172.16.3.132 (use you windows calculator in scientific mode)

    Sathayram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Sep 2003
    Posts
    27
    Thx very much.
    Thx. The first part is the ip address in hex.
    Can we "decrypt" the second part (GD80) also???

  8. #8
    Join Date
    Sep 2003
    Posts
    27
    When I log as db2inst1 at local of server, i found the appl_id local_db2inst1.321fe5tert something like that.
    Is the second part (GD80) is auth_id or user name?

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The first is the ip address, second is the port number and third is the timestamp

    Cheers
    Sathyaram

    Originally posted by chansteve1234
    When I log as db2inst1 at local of server, i found the appl_id local_db2inst1.321fe5tert something like that.
    Is the second part (GD80) is auth_id or user name?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Sep 2003
    Posts
    27
    Now i know the ip address and port , Can i know which user issue the SQL statement?

  11. #11
    Join Date
    Sep 2003
    Posts
    27
    First, I try to create 200 event monitor for 200 user using where auth_id clause. But seems there are limit on db2 of maximum of 32 event monitors can be active at a time.

    Now i know the ip address and port , Can i know which user issue the SQL statement?

  12. #12
    Join Date
    Sep 2003
    Posts
    27
    The problem seems have been solved. Now we know the appll_id in the event monitor. We can use db2audit to find the binding of appl_id and auth_id.
    First start the db2audit : db2audit start
    get the information :
    db2audit EXTRACTfile <file name> CATEGORY validate Database <dbname>

Posting Permissions

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