Make Your DB2 For i Apps Environment Aware

October 23, 2013 Michael Sansoterra

Time and time again, applications need to know something about the environment they’re running on. For example, if an application uses dynamic SQL and it needs to know what features are available, then knowing what version of IBM i it is using can help it decide how to build a query. Likewise, when auditing the source of database changes, it’s useful to know the TCP/IP address of the host requesting the change.

Consider the case where an application may need to know if a certain required PTF has been installed. While there have always been APIs to retrieve this information, thanks to some relatively new views, table functions, and the like, this information is now a snap to obtain in DB2.

This tip will discuss some recent additions to DB2 for i that return environment specific information based on an IBM i 7.1 machine with database group PTF level 24 installed. Many of them are in IBM i 6.1 and all are in IBM i 7.1. Some of these features are installed via PTF so it’s best to have the latest database group PTFs applied to your system.

At the time of this writing, information about these objects is not available in the IBM i 7.1 DB2 for i SQL Reference manual. Most of the information was gleaned from information found on the IBM developerWorks website.

Many of these objects are delivered as both table functions and views. For this tip, I’m just going to reference the views although know that these views just wrap an underlying table function.

QSYS2.TCPIP_INFO

The QSYS2.TCPIP_INFO view can supply an application with audit information about how a particular app is connecting to the database via TCP/IP, including local port and remote port information. It will only return one row. The columns are listed in the following table:

Pos	Column	Data Type
1	COLLECTED_TIME	TIMESTAMP
2	LOCAL_HOST_NAME	VARCHAR(255)
3	CLIENT_IP_ADDRESS_TYPE	VARCHAR(10)
4	CLIENT_IP_ADDRESS	VARCHAR(45)
5	CLIENT_PORT_NUMBER	INTEGER
6	SERVER_IP_ADDRESS_TYPE	VARCHAR(10)
7	SERVER_IP_ADDRESS	VARCHAR(45)
8	SERVER_PORT_NUMBER	INTEGER
9	HOST_VERSION	VARCHAR(10)

The address type column identifies the connection as either IP4 or IP6. The host version column contains the version of IBM i. Whether using ODBC or a green screen Telnet session, the client IP address column returned my workstation’s IP address.

This table function provides a useful alternative to using the CLIENT_WORKSTATION register, which may not be available to some applications that use ODBC. Currently this register cannot be supplied in the ODBC connection string.

The columns shown above are slightly different on IBM i 6.1 and older versions of IBM i 7.1 as IBM renamed existing columns and added a few new ones.

SYSIBMADM.ENV_SYS_INFO

SYSIBMADM.ENV_SYS_INFO is an administrative catalog view that is available in other versions of DB2 and contains information about the hardware (or virtualized resources) on the IBM i or partition. In particular, this view can be used to warn users of performance problems with the app, if a minimum memory or â€œnumber of CPUsâ€� requirement is not met. In the IBM i world, it would also be helpful to know the current CPW rating and process group (P05, P10, etc.), but alas this view doesn’t include this information. The columns returned are:

Pos	Column	Data Type
1	OS_NAME	VARCHAR(256)
2	OS_VERSION	VARCHAR(256)
3	OS_RELEASE	VARCHAR(256)
4	HOST_NAME	VARCHAR(256)
5	TOTAL_CPUS	INTEGER
6	CONFIGURED_CPUS	INTEGER
7	TOTAL_MEMORY	INTEGER

QSYS2.SYSLIMITS

The QSYS2.SYSLIMITS is a gem of a view that is useful for watching when your system is approaching a critical health condition, such as inching toward the total maximum jobs or prolonged high CPU utilization.

Pos	Column	Data Type
1	LAST_CHANGE_TIMESTAMP	TIMESTAMP
2	LIMIT_CATEGORY	VARCHAR(15)
3	LIMIT_TYPE	VARCHAR(7)
4	SIZING_NAME	VARCHAR(128)
5	COMMENTS	VARCHAR(2000)
6	USER_NAME	VARCHAR(10)
7	CURRENT_VALUE	BIGINT
8	MAXIMUM_VALUE	BIGINT
9	JOB_NAME	VARCHAR(28)
10	JOB_STATUS	CHAR(10)
11	ACTIVE_JOB_STATUS	CHAR(4)
12	RUN_PRIORITY	INTEGER
13	SBS_NAME	CHAR(10)
14	CPU_USED	BIGINT
15	TEMP_STORAGE_USED_MB	INTEGER
16	AUX_IO_REQUESTED	BIGINTÂ
17	PAGE_FAULTS	BIGINTÂ
18	CLIENT_WRKSTNNAME	CHAR(255)
19	CLIENT_APPLNAME	CHAR(255)
20	CLIENT_ACCTNG	CHAR(255)
21	CLIENT_PROGRAMID	CHAR(255)
22	CLIENT_USERID	CHAR(255)
23	SYSTEM_SCHEMA_NAME	VARCHAR(10)
24	SYSTEM_OBJECT_NAME	VARCHAR(30)
25	SYSTEM_TABLE_MEMBER	VARCHAR(10)
26	OBJECT_TYPE	VARCHAR(7)
27	ASP_NUMBER	SMALLINT
28	LIMIT_ID	INTEGER

QSYS2.PTF_INFO catalog

This QSYS2.PTF_INFO catalog view returns info in a format similar to the QpzListPTF() API. Its usefulness lies in allowing an app to discover what PTFs have been installed. If a certain IBM PTF is required for an app to run properly, this will do the trick. The columns are shown here:

Pos	Column	Data Type
1	PTF_PRODUCT_ID	VARCHAR(7)
2	PTF_PRODUCT_OPTION	VARCHAR(6)
3	PTF_PRODUCT_RELEASE_LEVEL	VARCHAR(6)
4	PTF_PRODUCT_DESCRIPTION	VARCHAR(132)
5	PTF_IDENTIFIER	VARCHAR(7)
6	PTF_RELEASE_LEVEL	VARCHAR(6)
7	PTF_PRODUCT_LOAD	VARCHAR(4)
8	PTF_LOADED_STATUS	VARCHAR(19)
9	PTF_SAVE_FILE	VARCHAR(3)
10	PTF_COVER_LETTER	VARCHAR(3)
11	PTF_ON_ORDER	VARCHAR(3)
12	PTF_IPL_ACTION	VARCHAR(19)
13	PTF_ACTION_PENDING	VARCHAR(3)
14	PTF_ACTION_REQUIRED	VARCHAR(12)
15	PTF_IPL_REQUIRED	VARCHAR(9)
16	PTF_IS_RELEASED	VARCHAR(3)
17	PTF_MINIMUM_LEVEL	VARCHAR(2)
18	PTF_MAXIMUM_LEVEL	VARCHAR(2)
19	PTF_STATUS_TIMESTAMP	TIMESTAMP
20	PTF_SUPERCEDED_BY_PTF	VARCHAR(7)
21	PTF_CREATION_TIMESTAMP	TIMESTAMP
22	PTF_TECHNOLOGY_REFRESH_PTF	VARCHAR(3)

Also, an app can use this information to determine if there are PTFs waiting to be installed at the next IPL, if a certain PTF has been applied, etc.

QSYS2.GROUP_PTF_INFO

View QSYS2.GROUP_PTF_INFO is used to return information about the GROUP PTF levels on a system.

Pos	Column	Data Type
1	COLLECTED_TIME	TIMESTAMP
2	PTF_GROUP_NAME	VARCHAR(60)
3	PTF_GROUP_DESCRIPTION	VARCHAR(100)
4	PTF_GROUP_LEVEL	INTEGER
5	PTF_GROUP_TARGET_RELEASE	VARCHAR(6)
6	PTF_GROUP_STATUS	VARCHAR(20)

Here is a sample query that can be used to make sure DB2 Group PTF is installed or the current OS is beyond IBM i 7.1:

-- Verify that DB2 Group PTF 24 or later has been applied
IF NOT EXISTS (
SELECT *
  FROM QSYS2.GROUP_PTF_INFO
 WHERE PTF_GROUP_TARGET_RELEASE LIKE 'V7R1%'
   AND PTF_GROUP_LEVEL>=24
   AND PTF_GROUP_DESCRIPTION ='DB2 FOR IBM I'
)
-- Or check if the OS level is beyond IBM i 7.1
AND NOT EXISTS (
SELECT *
  FROM SYSIBMADM.ENV_SYS_INFO
 WHERE OS_VERSION>7
	OR (OS_VERSION=7 AND OS_RELEASE>1)) THEN
... Some Error

QSYS2.SYSPROGRAMSTAT

This catalog view contains information about all programs and service programs that contain one or more SQL statements. Although it is a catalog view, it is useful because it records the environment settings for embedded SQL programs, including any SQL routines that are really C programs with embedded SQL.

The result set contains the same DB2 module information that is conveyed by the PRTSQLINF and DSPPGM commands when used with an embedded SQL program.

Pos	Column	Data Type
1	PROGRAM_SCHEMA	VARCHAR(128)
2	PROGRAM_NAME	VARCHAR(128)
3	PROGRAM_TYPE	VARCHAR(128)
4	PROGRAM_OWNER	VARCHAR(128)
5	PROGRAM_CREATOR	VARCHAR(128)
6	CREATION_TIMESTAMP	TIMESTAMP
7	DEFAULT_SCHEMA	VARCHAR(128)
8	ISOLATION	CHAR(2)
9	CONCURRENTACCESSRESOLUTION	CHAR(1)
10	NUMBER_STATEMENTS	INTEGER
11	PROGRAM_USED_SIZE	INTEGER
12	NUMBER_COMPRESSIONS	INTEGER
13	STATEMENT_CONTENTION_COUNT	BIGINT
14	ORIGINAL_SOURCE_FILE	VARCHAR(128)
15	ORIGINAL_SOURCE_FILE_CCSID	INTEGER
16	ROUTINE_TYPE	VARCHAR(9)
17	ROUTINE_BODY	VARCHAR(8)
18	FUNCTION_ORIGIN	CHAR(1)
19	FUNCTION_TYPE	CHAR(1)
20	NUMBER_EXTERNAL_ROUTINES	SMALLINT
21	EXTENDED_INDICATOR	VARCHAR(9)
22	C_NUL_REQUIRED	VARCHAR(10)
23	NAMING	VARCHAR(4)
24	TARGET_RELEASE	VARCHAR(6)
25	EARLIEST_POSSIBLE_RELEASE	VARCHAR(6)
26	RDB	VARCHAR(18)
27	CONSISTENCY_TOKEN	VARBINARY(8)
28	ALLOW_COPY_DATA	VARCHAR(9)
29	CLOSE_SQL_CURSOR	VARCHAR(10)
30	DATE_FORMAT	CHAR(1)
31	DATE_SEPARATOR	CHAR(1)
32	TIME_FORMAT	CHAR(1)
33	TIME_SEPARATOR	CHAR(1)
34	DYNAMIC_DEFAULT_SCHEMA	VARCHAR(4)
35	CURRENT_RULES	VARCHAR(4)
36	ALLOW_BLOCK	VARCHAR(8)
37	DELAY_PREPARE	VARCHAR(4)
38	USER_PROFILE	VARCHAR(7)
39	DYNAMIC_USER_PROFILE	VARCHAR(6)
40	SORT_SEQUENCE	VARCHAR(12)
41	LANGUAGE_IDENTIFIER	CHAR(3)
42	SORT_SEQUENCE_SCHEMA	CHAR(10)
43	SORT_SEQUENCE_NAME	CHAR(10)
44	RDB_CONNECTION_METHOD	VARCHAR(4)
45	DECRESULT_MAXIMUM_PRECISION	SMALLINT
46	DECRESULT_MAXIMUM_SCALE	SMALLINT
47	DECRESULT_MINIMUM_DIVIDE_SCALE	SMALLINT
48	DECFLOAT_ROUNDING_MODE	VARCHAR(8)
49	DECFLOAT_WARNING	VARCHAR(3)
50	SQLPATH	VARCHAR(3483)
51	DBGVIEW	VARCHAR(9)
52	DBGKEY	VARCHAR(3)
53	LAST_USED_TIMESTAMP	TIMESTAMP
54	DAYS_USED_COUNT	INTEGER
55	LAST_RESET_TIMESTAMP	TIMESTAMP
56	SYSTEM_PROGRAM_NAME	CHAR(10)
57	SYSTEM_PROGRAM_SCHEMA	CHAR(10)

This puts quite a bit of information at your fingertips with a single table SELECT! You can check for procedures that don’t use commitment control, objects that run with *USER authority instead of *OWNER, objects that use the *SQL naming convention instead of *SYS, objects that can be source debugged, etc. In other words, this catalog can be used to detect any modules that have attributes that are inconsistent with existing shop standards.

Columns LAST_USED_TIMESTAMP and DAYS_USED_COUNT appear to be the same information that is retrieved using display object description (DSPOBJD). These fields can be used to examine program usage frequency; a useful tool when attempting to remove unused code. Even though LAST_USED_TIMESTAMP is defined as a timestamp, this column only shows a date on my system. Further, for IBM functions like the new HTTP functions in library SYSTOOLS, these columns are not updated.

QSYS2.GET_JOB_INFO

Finally, the new QSYS2.GET_JOB_INFO() table function can be used to get information about a specific job on the system. As shown in these examples, the job ID can be supplied in at least two ways:

SELECT * 
  FROM TABLE(QSYS2.GET_JOB_INFO('QPADEV0001PRODUSER  003332')) JOB;

SELECT * 
  FROM TABLE(QSYS2.GET_JOB_INFO('003332/PRODUSER/QPADEV0001')) JOB;

The function’s output columns are shown below:

Pos	Column	Data Type
1	V_JOB_STATUS	CHAR(10)
2	V_ACTIVE_JOB_STATUS	CHAR(4)
3	V_RUN_PRIORITY	INTEGER
4	V_SBS_NAME	CHAR(10)
5	V_CPU_USED	BIGINT
6	V_TEMP_STORAGE_USED_MB	INTEGER
7	V_AUX_IO_REQUESTED	BIGINT
8	V_PAGE_FAULTS	BIGINT
9	V_CLIENT_WRKSTNNAME	CHAR(255)
10	V_CLIENT_APPLNAME	CHAR(255)
11	V_CLIENT_ACCTNG	CHAR(255)
12	V_CLIENT_PROGRAMID	CHAR(255)
13	V_CLIENT_USERID	CHAR(255)

This function is limited in usefulness. It would be nice if it returned the job’s starting timestamp. That way the CPU or Page Faults could be monitored based on how long the job has been running. Nevertheless, it’s useful in that it can look up the SQL client registers of any job, identify what subsystem a job is in, and so forth.

Security Usage

If your application needs to know about IBM i security settings, there are a few table functions that can help.

The GROUP_USERS table function accepts a group profile name and returns a single column called USERNAME that identifies all the user profiles in the group, including supplemental group profiles.

IF NOT EXISTS (SELECT * FROM TABLE(QSYS2.GROUP_USERS ('APP_GRP')) G WHERE USERNAME=SYSTEM_USER) THEN
    -- Blow up here because the user isn't in APP_GRP
END IF;

The USERS table function returns the user profiles on the system. This function is built over the DSPOBJD command and returns two familiar column names: ODOBNM and ODOBTX.

SELECT ODOBNM,ODOBTX FROM TABLE(QSYS2.USERS()) U;

Finally, the GROUPPROFILE() scalar function can be used to return the current user profile’s primary group profile:

SELECT QSYS2.GROUPPROFILE() FROM SYSIBM.SYSDUMMY1;

Environment Awareness

Of course, these statements should be running under adopted authority or the user’s profile must have the required authority (*JOBCTL, *SECADM, etc.) required to access the information.

Once upon a time, I dreamed of writing a large collection of scalar and table function wrappers around various APIs to make all of this environment information available to SQL. Thankfully IBM has done much of the work already. Say goodbye to complex API management because database apps can now take advantage of this information with the simplicity of a simple SELECT statement.

Even better, the recently announced TR7 will include yet more objects to aid an apps’ environment awareness, including the ability to query user storage and system values.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot