Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

How to list IPs and users connections to DB2 database?

Hi,

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

Thanks.
0
CSIA AN
Asked:
CSIA AN
  • 3
  • 2
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
CSIA ANAuthor 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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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.
0
 
CSIA ANAuthor 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

0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now