How to list IPs and users connections to DB2 database?

CSIA AN
CSIA AN used Ask the Experts™
on
Hi,

We want to know which IPs, and username, have connected last day or week to our DB2 database.  Is is possible?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent OlsenData Warehouse / Database Architect
Commented:
Hi CSIA,

The "list applications for database" command from within the CLI will show most of what you want.

Or, depending on which version of DB2 you're running you can query the system tables.

  SELECT * FROM sysibm.applications;
  SELECT * FROM sysibm.sessions;

Kent

Author

Commented:
Hello,


First sentence runs with "SELECT * FROM sysibmadm.applications" but the second not runs correctly
    SQL0204N  "SYSIBM.SESSIONS" is an undefined name.  SQLSTATE=42704

But the information for first sentence is only the actual connections, is possible see the connections between two dates?


Thanks
Kent OlsenData Warehouse / Database Architect

Commented:
What version of DB2 are you running?  Those system tables are "relatively" new, having been introduced at version 10, I think.

Without them you won't find much (if any) information on past sessions.

Author

Commented:
Hi, FInd below db2 version:

():[db2d] /db2/db2d -> db2level
DB21085I  This instance or install (instance name, where applicable: "db2d")
uses "64" bits and DB2 code release "SQL10013" with level identifier "0204010E".
Informational tokens are "DB2 v10.1.0.3", "s130918", "IP23509", and Fix Pack "3".
Product is installed at "/opt/IBM/db2/V10.1".

Open in new window

Data Warehouse / Database Architect
Commented:
Hi CSIA,

Most of that information is kept in session variables that are not preserved when the connection ends.  The data's accessible from some of the system tables/views or snapshots.

Try this query to see if this will meet your needs.  

SELECT application_handle,
       CLIENT_USERID,
       CLIENT_WRKSTNNAME,
       CLIENT_HOSTNAME,
       CLIENT_IPADDR
FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t
ORDER BY rows_returned DESC;

If so, you'll want to create a new table to record it.

CREATE TABLE login_table as (
SELECT application_handle,
       CLIENT_USERID,
       CLIENT_WRKSTNNAME,
       CLIENT_HOSTNAME,
       CLIENT_IPADDR
FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t
) definition only;

Check to see if there is already CONNECT_PROC defined for the database.

  db2 get db cfg|grep CONNECT_PROC


If there is, add your INSERT statement to the procedure.

If not, create a stored procedure to insert the data into the table.  Then change the CONNECT_PROC parameter to execute your new procedure whenever a connection to DB2 is made.

That won't tell get past connection data for you, but it will record every connection going forward.


Good Luck!
Kent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial