Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    6

    Unanswered: How to find last accessed(login) date of db2 user?

    Hi,

    I am using db2 8.
    Is there any attribute which indicate the date/time a user last accessed db2?

    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You would have to turn on the DB2 Audit facility to find out when someone connected to the databases or accessed a particular table. The information captured by the Audit Facility depends on how it is configured.

    However, since DB2 users are controlled by the operating system, the OS may be able to track when someone logged on the database server, assuming they connected to DB2 locally (after a telnet or ssh to the database server). This would not include someone accessing DB2 from a remote client.

    If the remote or local user is still connected, you could list the connections (applications) to determine when they first connected. This can be complicated if you remote app uses connection pooling.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by Marcus_A
    This can be complicated if you remote app uses connection pooling.
    Hi,
    this is the problem I would like to solve out for couple of years. I have a WebSphere App Server 6 on Linux connecting to DB2 Enterprice Edition v8 fp11 on Linux.

    I can see 7 connection from WAS connection polling but that is all. I can't see which application or user is connected to DB. Is there any way to see this info?

    Sample data from 'list application' on DB2 server site

    Code:
    Auth Id  Application    Appl.      Application Id                 DB       # of
             Name           Handle                                    Name    Agents
    -------- -------------- ---------- ------------------------------ -------- -----
    DB2INST1 db2jcc_applica 520        C0A8C866.E768.070820053440     MYDB      1
    DB2INST1 db2jcc_applica 177        C0A8C866.E6DB.070820042804     MYDB      1
    DB2INST1 db2jcc_applica 926        C0A8C866.D285.070818065124     MYDB      1
    DB2INST1 db2jcc_applica 1009       C0A8C866.CE62.070817214434     MYDB      1
    DB2INST1 db2jcc_applica 1403       C0A8C866.C895.070817110813     MYDB      1
    DB2INST1 db2jcc_applica 463        C0A8C866.C877.070817110445     MYDB      1
    DB2INST1 db2jcc_applica 1399       C0A8C866.C76F.070817100111     MYDB      1
    Thanks,
    Grofaty

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The user is DB2INST1. I assume that this is the instance owner id, which is probably not a good idea for the applications to be using.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Marcus_A,
    I agree this is instance owner id, but this is my test system, just to play around.

    But back to my main question: is there any way I could track down user and application that is connected to DB2? The userid=DB2INST1 and application=db2jcc_applica doesn't tell me anything but this user/app is connected by WAS.

    Let say, one application makes problems on DB2 site. It locks tables and doesn't release them. I can see in DB2 that locking has appeared. Using snapshot monitor I can see table names having locks, I can see application id, but I can't see the name of application that is using connection pulling. The name db2jcc_applica is useless.

    I am not an expert on WAS server, but admin guys said this info is not available at WAS site. I am little bit sceptical, if WAS knows witch connection is used by each application in connection pooling, why is there no command or tool admin could look into.

    In brief: I am actually not interested into which user has connected to database, but it would be enough just to get info which WAS application is connected to db2 using connection pulling.

    Any idea is very appreciated.
    Thanks,
    Grofaty
    Last edited by grofaty; 08-21-07 at 04:07.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    To distinguish between WAS applications when monitoring a DB2 instance, you would need to define a separate WAS data source for each application. In the custom properties of a data source you can then define the clientApplicationInformation string, which will show up in the DB2 snapshot (CLIENT_APPLID special register).

    If you only have a single WAS data source this technique, obviously, won't help.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    I need to discuss with my WAS admin to get more info. But in the mean time, can you please specify exact special register name. I have executed 'db2 get snapshot for all on mydb' command and I can't find this special register CLIENT_APPLID.

    Is this special register available in v8 fp11 on Linux (my system) or is this a v9 feature?

    BTW, what does this mean if each application is using separate WAS data source - does this mean each application is using each separate connection pooling?
    Thanks,
    Grofaty
    Last edited by grofaty; 08-21-07 at 16:24.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by grofaty
    exact special register name. I have executed 'db2 get snapshot for all on mydb' command and I can't find this special register CLIENT_APPLID.
    It shows as "Application ID" on the application snapshot, for example.

    Quote Originally Posted by grofaty
    BTW, what does this mean if each application is using separate WAS data source - does this mean each application is using each separate connection pooling?
    Yes.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by grofaty
    BTW, what does this mean if each application is using separate WAS data source - does this mean each application is using each separate connection pooling?
    Quote Originally Posted by n_i
    Yes.
    Hi,
    is there any way to have this info if only one connection pooling is used only one data source?

    WAS server has to have this info to know which application is using each connection. So I am wondering how to display 'application name' info at DB2 site.

    Any idea?

    Regards,
    Grofaty

Posting Permissions

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