Surge Of Services In DB2 For i, Part 1

QSYS2.NETSTAT_JOB_INFO

This view, related to NETSTAT_INFO, is a real gem as it discloses what jobs are hosting TCP/IP connections. This is a SQL version of the NETSTAT IBM i command. The following dynamic compound statement uses the NETSTAT_JOB_INFO view to identify the jobs hosting all active connections to the database server jobs (identified by connections on ports 8471 or 9471) and ends them. (I don’t necessarily advocate ending jobs in this manner, but it shows how SQL Services can help automate admin tasks.)

BEGIN

DECLARE @COMMAND CHAR(128);

FOR vl AS c1 CURSOR FOR
SELECT JOB_NAME
  FROM QSYS2.NETSTAT_JOB_INFO
 WHERE CONNECTION_TYPE = 'IPV4' 
   AND LOCAL_PORT IN (8471,9471)  -- Database Server Ports
   AND AUTHORIZATION_NAME<>'ODBCUSER'
   AND REMOTE_ADDRESS<>LOCAL_ADDRESS
   AND LOCAL_ADDRESS<>'127.0.0.1'   
   AND JOB_NAME<>QSYS2.JOB_NAME   -- Ignore current job
DO
    SET @COMMAND='ENDJOB ('||JOB_NAME||') OPTION(*IMMED)';
    CALL QSYS2.QCMDEXC(@COMMAND);
END FOR;
      
END

Note that the use of global variable QSYS2.JOB_NAME in this query is restricted to IBM i 7.2 or later.

If you’re responsible for security, it’s important to know (in theory) what can connect to your IBM i. You can use this services view to review unexpected connections and find out where it originates. To learn about the common TCP/IP utilities (FTP, HTTP, LDAP, etc.) and the ports they use, see this link. For a list of common ports used with IBM i Access features (sign-on server, database server, remote command, data queue server, etc.), review this link.

QSYS2.NETSTAT_INTERFACE_INFO

This view provides the same information as shown in the green screen “Work with TCP/IP interfaces” (Option 1 from the CFGTCP menu). The following query shows all active IPV6 interfaces:

SELECT * 
  FROM QSYS2.NETSTAT_INTERFACE_INFO 
 WHERE LINE_DESCRIPTION<>'*LOOPBACK' 
   AND INTERFACE_STATUS='ACTIVE' 
   AND CONNECTION_TYPE='IPV6';

QSYS2.NETSTAT_ROUTE_INFO

This view is the SQL equivalent of using the List Network Routes API. The purpose is to reveal all TCP/IP routes (v4 and V6) defined on the system. The following query shows routes defined for a particular Ethernet interface:

SELECT * 
  FROM QSYS2.NETSTAT_ROUTE_INFO 
 WHERE LOCAL_BINDING_INTERFACE='172.2.168.1';

QSYS2.OBJECT_LOCK_INFO

This majestic services view, analogous to the Work with Object Locks (WRKOBJLCK) command, can reveal what job(s) are holding a lock on any object (excluding the IFS). The following example lists the jobs holding a lock on the PRODUCT table:

SELECT SYSTEM_OBJECT_SCHEMA AS SYSOBJ,
       SYSTEM_OBJECT_NAME AS SYSNAM,
       SYSTEM_TABLE_MEMBER AS MBR,
       SQL_OBJECT_TYPE AS SQLT,
       LOCK_STATE AS STATE,
       LOCK_STATUS AS STATUS,
       LOCK_SCOPE AS SCOPE,
       JOB_NAME AS JOB
  FROM QSYS2.OBJECT_LOCK_INFO
 WHERE OBJECT_SCHEMA='ADVWORKS'
   AND OBJECT_NAME='PRODUCT'
   AND OBJECT_TYPE='*FILE'

The results look like the following:

SELECT *
  FROM TABLE(QSYS2.OBJECT_LOCK_INFO(
  LIBRARY_NAME=>'QSYS',
  OBJECT_NAME=>'MIKE',
  OBJECT_TYPE=>'*USRPRF',
  IASP_NUMBER=>0)) X

SELECT SYSTEM_TABLE_SCHEMA AS SYSSCHEMA,
       SYSTEM_TABLE_NAME AS SYSTABLE,
       SYSTEM_TABLE_MEMBER AS MEMBER,
       RELATIVE_RECORD_NUMBER AS RRN,
       LOCK_STATE AS STATE,
       LOCK_STATUS AS STATUS,
       LOCK_SCOPE AS SCOPE,
       JOB_NAME
  FROM QSYS2.RECORD_LOCK_INFO
 WHERE TABLE_SCHEMA='ADVWORKS12'
   AND TABLE_NAME='PRODUCT';

BEGIN    
    SET SCHEMA ADVWORKS;
    IF EXISTS (
    SELECT *
      FROM QSYS2.RECORD_LOCK_INFO
     WHERE TABLE_SCHEMA=CURRENT_SCHEMA
       AND TABLE_NAME='TRANSACTIONHISTORY') THEN
        SIGNAL SQLSTATE '38I01'
            SET MESSAGE_TEXT = 'Users are still processing transactions';
    END IF;
    
    -- Run month end . . .
END

SELECT *
  FROM TABLE(QSYS2.RECORD_LOCK_INFO(
LIBRARY_NAME=>'ADVWORKS',
FILE_NAME=>'TRANHIST',  /* System Name */
MEMBER_NAME=>'*FIRST',
IASP_NUMBER=>0)) RL
;