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

chandurp · #1 (**permalink**) 08-20-07, 01:01

Hi,

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

Thanks.

Marcus_A · #2 (**permalink**) 08-20-07, 01:40

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.

grofaty · #3 (**permalink**) 08-20-07, 02:14

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

Marcus_A · #4 (**permalink**) 08-20-07, 23:06

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.

grofaty · #5 (**permalink**) 08-21-07, 03:04

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

n_i · #6 (**permalink**) 08-21-07, 09:52

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.

grofaty · #7 (**permalink**) 08-21-07, 15:16

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

n_i · #8 (**permalink**) 08-21-07, 17:08

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.

grofaty · #9 (**permalink**) 08-23-07, 07:08

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

Internet Services

Web Development

Digital Marketing

Consumer Tech