Surge Of Services In DB2 For i, Part 1
April 5, 2016 Michael Sansoterra
The IBM i 7.2 TR3 and 7.1 TR11 technology refreshes come with a glut of new services. If you are responsible for IBM i administration, you are guaranteed to be tickled pink with these great enhancements. My examples are to call general attention to the availability of these services and give ideas on how they may be used. However, I still recommend going to the IBM documentation on the DeveloperWorks site (or the IBM knowledge center) to get full information on the parameters and all columns used by these special views and table functions.
For clarification, there are actually two types of services supported by DB2 for i: IBM i Services and DB2 for i Services. In my past writing, I wasn’t careful in properly categorizing each service type. The IBM i Services deal mainly with providing OS related information (PTFs, memory, outq content, object lists, etc., which are discussed in this article) and the DB2 for i Services are helper services with DB2 coding and management tasks.
This view allows administrators to query all active network connections to the IBM i. The following query gets a list of IPv4 connections not originating from a 10.10.x.x network scheme.
SELECT REMOTE_ADDRESS,REMOTE_PORT, LOCAL_ADDRESS,LOCAL_PORT,LOCAL_PORT_NAME,TCP_STATE, BYTES_SENT_REMOTELY,BYTES_RECEIVED_LOCALLY, IDLE_TIME,NUMBER_OF_ASSOCIATED_JOBS FROM NETSTAT_INFO WHERE CONNECTION_TYPE='IPV4' AND REMOTE_ADDRESS NOT IN ('0','0.0.0.0','127.0.0.1') AND REMOTE_ADDRESS NOT LIKE '10.10.%'
The results will look something like this (not all columns shown):
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.
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';
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='126.96.36.199';
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:
Be sure to check out all of the columns returned by this view including the PROGRAM_NAME, MODULE_NAME, instruction, etc. (not shown above).
Use this view with caution as it can take a while to assemble this system wide content. If you generate the source for this view, you will see it relies on table function OBJECT_LOCK_INFO. For performance reasons, I would prefer to use OBJECT_LOCK_INFO to detect locks against a single object:
SELECT * FROM TABLE(QSYS2.OBJECT_LOCK_INFO( LIBRARY_NAME=>'QSYS', OBJECT_NAME=>'MIKE', OBJECT_TYPE=>'*USRPRF', IASP_NUMBER=>0)) X
However, this table function is not listed as part of the services in the DeveloperWorks documentation. This may be an oversight on IBM’s part or it may mean that it is not intended for the end user’s use and therefore may be subject to change in a future version. So use with caution!
Services view RECORD_LOCK_INFO is a fitting companion to the OBJECT_LOCK_INFO view. Akin to the Display Record Lock (DSPRCDLCK) command, this view reveals all jobs that are holding row locks in a table and even returns the relative record number of locked rows:
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';
The results will looks like this:
This view can help verify that no one is holding row locks in one or more tables before some important processing like month end is about to happen:
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
Since this view generates a system wide report on locked records, it can take a while to process. Here I regress to the same story as shown for OBJECT_LOCK_INFO. Use the companion (yet undocumented) table function with the knowledge that it may change or go away in a future release. Here is an example showing how to use the RECORD_LOCK_INFO table function:
SELECT * FROM TABLE(QSYS2.RECORD_LOCK_INFO( LIBRARY_NAME=>'ADVWORKS', FILE_NAME=>'TRANHIST', /* System Name */ MEMBER_NAME=>'*FIRST', IASP_NUMBER=>0)) RL ;
The Surge Continues
As evidenced by these useful new services, SQL is becoming the easiest way to gain quick insight into the state of your IBM i and to automatically do something in response when certain conditions arise. Don’t worry, there are more services to discuss so stay tuned for Part 2 as the surge continues.