Solved

How to list IPs and users connections to DB2 database?

Posted on 2016-08-18
6
14 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
help with Oracle stored procedure with input and output parameters 8 707
How do identify a "break in coveage" 8 182
DB2 Integer to Decimal 1 131
Shell Script on AIX 7 97
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

809 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