Solved

How to list IPs and users connections to DB2 database?

Posted on 2016-08-18
6
32 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen 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:Kent Olsen
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:
Kent Olsen 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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

636 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