New in DB2 for i 7.1: Use Global Variables to Track Environment Settings

The concept of having a locally maintained application “scratch pad” to keep track of session specific information has been around for quite awhile. RPG applications have long used the LDA (Local Data Area) to track common information meant to be shared between programs within a job. (This saves developers the trouble of passing around all of this common information as parameters.)

Likewise, many operating systems have the concept of an environment variable, which is similarly used to maintain and publish many OS-defined and user-defined values. Under IBM i 7.1, SQL offers something called a global variable, which can be used to share information among procedures and functions in an SQL session.

Global variables are defined using the CREATE VARIABLE statement. They can be defined with any built-in or user-defined type and can be assigned a specific default value. Once created, the variables are available for use by any and all SQL sessions.

But don’t be confused by the term “Global variable.” In this sense, “global” only pertains to everything in the active SQL session (or i/OS job.) A global variable that is changed in SQL session A will not be visible to another process that accesses the same variable within SQL session B. Also, when a global variable is changed, the modified value is discarded when the session ends.

Here is an example of how to create a global variable:

CREATE VARIABLE DATALIB.RETRY INT DEFAULT 5;
LABEL ON VARIABLE DATALIB.RETRY IS 'Number of retries allowed';

Once a global variable has been created, it can be accessed just about anywhere a variable or expression is allowed (INSERT/UPDATE/DELETE/SELECT/SET, etc.). Unless overridden, the variable will always return the default value assigned in the CREATE VARIABLE statement. For unqualified global variable names, the schemas in the SQL PATH register are searched in an attempt to resolve the name.

Here are sample SELECT statements that return the value of the RETRY global variable created above:

SELECT * FROM (VALUES(DATALIB.RETRY)) VARIABLES(RETRY) -- *SQL Naming
SELECT RETRY FROM SYSIBM/SYSDUMMY1  -- *SYS Naming

As you can see from the second example, it can be difficult to discern the difference between a column name, a local procedure variable name, or a global variable name. Therefore, I recommend you adopt some kind of naming standard (e.g., a GV_ prefix) so that your global variables are easily recognized as such.

The variable’s default can be overridden for the duration of the current SQL session by using the SET statement. (When referencing a global variable, SET can now be invoked by dynamic SQL tools such as STRSQL.)

SET DEV.RETRY=10

Surprisingly, when a global variable is created, behind the scenes a service program object is built. I expected something like a data area to be created to store the default. However, considering the range of data types that can be used for a variable ranging from a SMALLINT to a large BLOB, this service program implementation makes sense. Additionally, the use of a service program for a global variable allows developers to create versatile default assignments that can do useful things such as referencing a user-defined function or even executing a scalar subselect.

The following two examples from the IBM manual show off this power. This first CREATE VARIABLE statement references a scalar subselect to retrieve its default:

CREATE VARIABLE SCHEMA1.GV_DEPTNO INTEGER
DEFAULT ((SELECT DEPTNO FROM HR.EMPLOYEES
WHERE EMPUSER = SESSION_USER))

This CREATE VARIABLE statement defaults to a user-defined function (where the function is assumed to be deterministic).

CREATE VARIABLE SCHEMA2.GV_SECURITY_LEVEL INTEGER
DEFAULT (GET_SECURITY_LEVEL(SESSION_USER))

Here’s a final example that retrieves the customer number with the highest balance due:

CREATE VARIABLE DEV.GV_TOP_CUSTOMER NUMERIC(6,0) DEFAULT
(SELECT CUSNUM 
   FROM QIWS.QCUSTCDT 
 ORDER BY BALDUE DESC 
 FETCH FIRST 1 ROW ONLY)

In other words, global variables can contain logic and in this context, they somewhat resemble scalar user-defined functions. Using global variables in this manner can make coding simpler for novice developers or super users by allowing them to reference a simple name in their SELECT statement (instead of having to specify something complex like a function name with parameters).

Another use for global variables is to share data between procedures or functions (similar to the LDA or environment variables). There are many cases where developers may want to optionally pass some additional information to a stored procedure but, due to development and testing costs, do not have the resources to modify the procedure’s parameter signature and all of its references. In these cases, a global variable can be used to get additional information to the procedure without the refactoring costs.

One particular thorny problem for SQL developers is the ability to adjust a trigger’s behavior based on a specific circumstance within the session. Since data-modification statements have no mechanism to pass a parameter to a trigger, there is often a need to resort to a hack such as inserting “fudged” data into a user-defined data column that the trigger inspects. Clearly, this type of “hack” is undesirable.

But global variables offer the perfect mechanism to control a trigger’s behavior. Say, for instance, during a mass insert operation you don’t want a relatively slow row-based trigger to fire. You can simply create a global variable, set its value accordingly, and put a simple condition in the trigger.

CREATE VARIABLE GV_SKIP_TRIGGER CHAR(1) DEFAULT 'N'
CREATE OR REPLACE TRIGGER QIWS.TRG_CUSTOMER_INSERT
BEFORE INSERT   
ON QIWS.QCUSTCDT 
REFERENCING NEW ROW AS INSERTED 
FOR EACH ROW 
BEGIN 
    IF GV_SKIP_TRIGGER='N' THEN
	/* Conditionally run trigger logic */
    END IF; 
END 

Before a large INSERT is executed, you can simply change the value of GV_SKIP_TRIGGER to a “Y” so that the trigger is effectively ignored:

SET GV_SKIP_TRIGGER='Y'

Change it back to “N” when the mass INSERT is done so that the trigger functions normally again.

There are few more points of interest for global variables:

• They are not subject to transaction boundaries (a ROLLBACK will not revert a changed variable value that was made within a transaction).
• DROP VARIABLE is used to permanently delete a global variable (and hopefully there is no lingering code left to reference a permanently dropped variable).
• A global variable is first instantiated to its defined default when the variable is referenced (not evaluated) within the current routine’s scope.

Global variables have many uses. They can be used to store environment settings, which are often stored in an XML file or a special purpose database table for environment settings. They can retrieve their default value by executing queries and deterministic functions. Finally, they can be used to relay information between routines where parameterized communication is difficult (such as refactoring the interface of stored procedures), or impossible (such as a stored procedure passing info to a trigger.)

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