Get ComputerName/IP in DB2

Question

I need to get info about user connected to my DB2. How can I get (if I can) computer name/login or IP of that user? I can get host_name, current user and login to DB2, but I want more specific information like IP or computer name. How I can do this?

Answer 1

You can obtain some useful data using the MetaData. For example the following code:

ResultSet rS = dataSource.getConnection().getMetaData().getClientInfoProperties();
while(rS.next()){
    System.out.println("**************************************************");
    System.out.println("NAME: <" + rS.getString(1) + ">");
    System.out.println("MAX_LEN: <" + rS.getInt(2) + ">");
    System.out.println("DEFAULT_VALUE: <" + rS.getString(3) + ">");
    System.out.println("DESCRIPTION: <" + rS.getString(4) + ">");           
}
    System.out.println("**************************************************");

Should give you something close to this:

**************************************************
NAME: ApplicationName
MAX_LEN: 255
DEFAULT_VALUE: 
DESCRIPTION: The name of the application currently utilizing the connection. This is stored in DB2 special register CURRENT CLIENT_WRKSTNNAM.
**************************************************
NAME: ClientAccountingInformation
MAX_LEN: 255
DEFAULT_VALUE: 
DESCRIPTION: The value of the accounting string from the client information that is specified for the connection. This is stored in DB2 special register CURRENT CLINT_ACTNG
**************************************************
NAME: ClientHostname
MAX_LEN: 255
DEFAULT_VALUE: ADMIN-9XYZK
DESCRIPTION: The hostname of the computer the application using the connection is running on. This is stored in DB2 special register CURRENT CLINT_WRKSTNNAM
**************************************************
NAME: ClientUser
MAX_LEN: 255
DEFAULT_VALUE: 
DESCRIPTION: The name of the user that the application using the connection is performing work for. This is stored in DB2 special register CURRENT CLINT_USRID.
**************************************************

---

Edit # 2

The DB_MEMBERS table function returns basic member information about a DB2 instance.

db2SelectStatement = "select * from table(SYSPROC.DB_MEMBERS()) as members";

This would output something like the following:

---

MEMBER_NUMBER-----------HOST_NAME--------------PARTITION_NUMBER------MEMBER_TYPE

---

0-------------------------member1.mycompany.com----------------0--------------------------D
1-------------------------member2.mycompany.com----------------0--------------------------C
7-------------------------member3.mycompany.com----------------0--------------------------D

You can select these columns:

MEMBER_NUMBER
HOST_NAME
PARTITION_NUMBER
MEMBER_TYPE
PORT_NUMBER
SWITCH_NAME
STATUS

I tested this on DB 10 and it's working, however I didn't test it on DB 9

Answer 2

db2

select AGENT_ID as AGENT, 
       substr(APPL_NAME, 1, 30) as APP_NAME,
       substr(APPL_ID, 1, 40)as APP_IP_ADD,
       substr(CLIENT_NNAME, 1, 40)as CLIENT 
from sysibmadm.snapappl_info order by CLIENT_NNAME

Output

AGENT                APP_NAME                       APP_IP_ADD                               CLIENT
-------------------- ------------------------------ -------------------------------------- ----------------------------------------
.....................................................................................................................................

341 record(s) selected.

Answer 3

You can get that information from the MON_GET_CONNECTION table function.

The table functions have change a lot in the last versions, so it depends on your DB2 version what values you can get.

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;

For example CLIENT_HOSTNAME and CLIENT_IPADDR does not work in v9.7

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0053938.html