Using Built-In Global Variables In DB2 For i 7.2

May 14, 2014 Michael Sansoterra

The long awaited announcement of IBM i 7.2 comes with several enhancements to DB2 for i. In this tip, I’m going to explore the new set of built-in global variables that come with DB2 for i 7.2. If you’re unfamiliar with global variables, see New in DB2 for i 7.1: Use Global Variables to Track Environment Settings or the CREATE VARIABLE statement.

The list of built-in global variables can be divided into three groups:

Group 1: Client TCP/IP Info

CLIENT_IPADDR VARCHAR(128)

CLIENT_PORT INT

As their names imply, these global variables return the IP address and port of the current client’s connection. This is very useful when auditing the origin of changes to the database. For IBM i 6.1, 7.1 users (with recent group PTF levels) and 7.2 users, this same information and more can be retrieved using the QSYS2.TCPIP_INFO “DB2 for i Services” view (except this view doesn’t return information when a telnet host connects). These variables will return NULL if the connection was not made using TCP/IP or SSL.

Group 2: Current Routine Info

ROUTINE_SCHEMA VARCHAR(128)

ROUTINE_SPECIFIC_NAME VARCHAR(128)

ROUTINE_TYPE CHAR(1)

These global variables return the schema, name, and type of the current running SQL routine (i.e., function or procedure; triggers are not included). This is similar in concept to retrieving the name of the currently executing RPG program using the information in the program status data structure. The ROUTINE_TYPE will return a ‘P’ for a procedure and an ‘F’ for a function. These variables return a NULL when they’re not accessed in the context of a routine.

Pay attention that the specific name is returned instead of the normal name. Since procedures and functions can be “overloaded” in DB2 for i, multiple procedures and functions in the same schema can share the same name so long as the parameter signatures are different. It’s actually the routine’s “specific name” that uniquely identifies it. If you want to look up the normal function or procedure name using the specific name, just query the SYSROUTINES catalog view.

The thing that vexes me about these built-in variables is that I recently wrote a table function called CALLSTACKENTRY to do the same thing. Why weren’t these variables available in IBM i 7.1?! At least the CALLSTACKENTRY table function can still be used to identify the caller of the current program.

Group 3: DRDA Package Info

PACKAGE_NAME VARCHAR(128)

PACKAGE_SCHEMA VARCHAR(128)

PACKAGE_VERSION VARCHAR(64)

Finally, the last group of built-in global variables is intended for DRDA users. DRDA allows a local DB2 instance to connect to and execute statements against other DB2 databases (including other server platforms such as Windows, Linux, etc.). For a brief intro to what DRDA can do see Bringing Home The Data and the references at the end of the tip.

The PACKAGE_SCHEMA and PACKAGE_NAME identify the SQL package (*SQLPKG) object that is being used to execute the query.

The PACKAGE_VERSION variable will return NULL unless the package was created by another version of DB2 such as DB2 for LUW.

The following column expression shows one way auditing can be done using these new global variables:

UPDATE MYTABLE
SET MYDATA=MY_DATA_EXPRESSION,
CHANGED_BY_PGM=COALESCE(
SYSIBM.ROUTINE_SPECIFIC_NAME,
SYSIBM.PACKAGE_NAME,
SYSIBM.CLIENT_IPADDR,
'SQL Script')
WHERE ...

The CHANGED_BY_PGM column will be populated with the first non-NULL value it encounters among the various global variables as a means of identifying the origin of the change.

All of these global variables are session scoped, located in the SYSIBM schema and are read only. (For all practical purposes, the new built-in variables seem like they should’ve been implemented as special registers, but I’m sure my ignorance prevents me from understanding why they’re variables.) These built-in global variables are a great asset as they provide an application with information about the context of the database connection. This type of information is used a great deal in application areas of auditing and security.

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.