Solved

How to list IPs and users connections to DB2 database?

Posted on 2016-08-18
6
9 Views
Last Modified: 2016-09-23
Hi,

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

Thanks.
0
Comment
Question by:CSIA AN
  • 3
  • 2
6 Comments
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 500 total points
ID: 41761137
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
 
LVL 2

Author Comment

by:CSIA AN
ID: 41761199
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
 
LVL 45

Expert Comment

by:Kdo
ID: 41761278
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
 
LVL 2

Author Comment

by:CSIA AN
ID: 41762581
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
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 41762687
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now