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.
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:
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 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:
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.
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:
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.
View QSYS2.GROUP_PTF_INFO is used to return information about the GROUP PTF levels on a system.
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
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.
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.
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:
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.
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;
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.