Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    56

    Unanswered: last operation of application

    Hi ,
    I have db2 v8.2 on unix aix , and i want to know how can i get the last operatioin of the exists connections ("sql close", "sql static commit","sql fetch" etc') .

    Can anybody know some usefull select ?

    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if the connect is still available
    db2 list applications ---- get agent id xx
    db2 get snapshot for application agentid xx
    you will see the last operation - check if monitor switches are on - otherwise some fields keep : not collected as info
    also see : GET SNAPSHOT
    db2pd can also collect this info if available in v8
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Oct 2010
    Posts
    56
    Hi przytula,
    But if i have a lot of availlable connections and i want to know what everyone's last operation , is there a function that knows how to put it back ?

    Tks,

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    having a script that executes the snapshot for each user as I believe sysibmadm views are not available in V8
    or have a look at db2pd at
    DB2 Universal Database and search for db2pd
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    On V8, you can try to run the following query from command-prompt,

    db2 "select * from table(snapshot_agent('SAMPLE',0)) as snap_ag ORDER BY SNAPSHOT_TIMESTAMP DESC "

    In the above example "SAMPLE" is the DB_NAME.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  6. #6
    Join Date
    Jan 2004
    Location
    UK
    Posts
    11
    Below works with db2 version 9.5 and KSH-93.

    #!/bin/ksh
    #--------------------------------------------------------------------------------
    #--- set you db2profile here

    for i in `db2 "list applications" |egrep -v 'Auth|--|Name' |awk '{print $3}' |xargs `
    do
    db2 get snapshot for application agentid $i|egrep -i "Most recent operation =|handle"
    done


    You can refine it as per your requirements.

  7. #7
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Seems for your case ( on db2 V8.2 LUW), the following select query will be more appropriate: I ran it on my local system and its returning desired result.

    db2 "select agent_id, UOW_COMP_STATUS,UOW_STOP_TIME from TABLE(SNAPSHOT_APPL(CAST(NULL as varchar(255)), -1)) as appsnap order by UOW_STOP_TIME "

    Here UOW_COMP_STATUS will provide you with the flag which will identify what was the last status of the unit of work for that particular agentid e.g. commit, rollback, rollback due to deadlock, etc.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

Posting Permissions

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