Get IP Address of connected DB2 client

by | Sep 21, 2007

When DB2 agent (db2bp process) makes a connection for your CONNECT request, it stores the IP address of the client in the application ID as shown below:

$ db2 list applications
Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
VIKRAM   db2taskd       421        *LOCAL.DB2.070921111828                                        SAMPLE   1    
VIKRAM   db2stmm        420        *LOCAL.DB2.070921111827                                        SAMPLE   1    
VIKRAM   db2bp          417        *LOCAL.vikram.070921111825                                     SAMPLE   1

The application ID string has *LOCAL for the local connections and it will have IP address in HEX string for remote connections.

You can also get same information from SYSIBMADM.SNAPAPPL view as shown below:

$ db2 "select inbound_comm_address from sysibmadm.snapappl"

The APPLID column as shown in the output of db2 list applications can also be retrieved from this snapshot view.

$ db2 "select appl_id from sysibmadm.snapappl_info"

APPL_ID                                                                                                                         
---------------------------------------------------
*LOCAL.DB2.070921111828                                                                                                         
*LOCAL.DB2.070921111827                                                                                                         
*LOCAL.vikram.070921111825

If you want to massage APPL_ID as shown above to get the IP address in a readable form, you can use this function to get the address.

DROP FUNCTION GETIP@

CREATE FUNCTION GETIP(INITVALUE VARCHAR(128)) RETURNS VARCHAR(20)
   LANGUAGE SQL 
   CONTAINS SQL
BEGIN ATOMIC
   DECLARE HEXADDRESS VARCHAR(15);
   --SET INITVALUE = '0A2104F0.DB2.040831143247';
   
   IF LOCATE('*LOCAL',INITVALUE) > 0 THEN
      RETURN '127.0.0.1';
   ELSE
      SET HEXADDRESS = SUBSTR(INITVALUE,1,POSSTR(INITVALUE,'.')-1);
      RETURN 
      RTRIM(CHAR(
        (LOCATE(UPPER(SUBSTR(SUBSTR(
           HEXADDRESS,1,2),1,1)),'0123456789ABCDEF')-1)*16+
        (LOCATE(UPPER(SUBSTR(SUBSTR(
           HEXADDRESS,1,2),2,1)),'0123456789ABCDEF')-1)))||'.'||
      RTRIM(CHAR(
        (LOCATE(UPPER(SUBSTR(SUBSTR(
           HEXADDRESS,3,2),1,1)),'0123456789ABCDEF')-1)*16+
        (LOCATE(UPPER(SUBSTR(SUBSTR(
           HEXADDRESS,3,2),2,1)),'0123456789ABCDEF')-1)))||'.'||
      RTRIM(CHAR(
        (LOCATE(UPPER(SUBSTR(SUBSTR(
           HEXADDRESS,5,2),1,1)),'0123456789ABCDEF')-1)*16+
        (LOCATE(UPPER(SUBSTR(SUBSTR(
           HEXADDRESS,5,2),2,1)),'0123456789ABCDEF')-1)))||'.'||
      RTRIM(CHAR(
        (LOCATE(UPPER(SUBSTR(SUBSTR(
           HEXADDRESS,7,2),1,1)),'0123456789ABCDEF')-1)*16+
        (LOCATE(UPPER(SUBSTR(SUBSTR(
           HEXADDRESS,7,2),2,1)),'0123456789ABCDEF')-1)));
   END IF;   
END
@

After you create above function, you can use this in your SQL to get the IP address.

$ db2 "select getip(appl_id) from sysibmadm.snapappl_info"

1                   
--------------------
127.0.0.1           
127.0.0.1           
127.0.0.1

However, the above function may not work correctly for DPF environment (I did not test it on DPF and it will require some modifications in the code to make it working correctly on DPF) so it is recommended that you use the SQL to get the INBOUND_COMM_ADDRESS from SYSIBMADM.SNAPAPPL view.