Within a relational database system, most interactions between an application and the actual database are in the form of SQL statements within a session or connection. Traditionally, in order to share information between different SQL statements in the same session, an application that issued the SQL statements would have to do this work by copying the values from the set of output arguments (host variables) of one statement to the input host variables of another. Also, SQL statements defined and contained within the database system itself, such as the SQL statements used to define triggers and views, could not access this shared information at all.
Overall, these restrictions limit the flexibility of relational database systems and, thus, the ability of users of such systems to implement complex, interactive models within the database itself. Users of such systems are forced to put supporting logic inside their applications or SQL procedures in order to access and transfer user application information and internal database information within a relational database system. This ensures the security of the information being transferred and accessed is also left to the user to enforce in their application logic.
To overcome this restriction and to maximize the flexibility of a relational database system, DB2 V9.5 introduces the concept of global variables. With the introduction of global variables, users can now freely build a complex system within a relational database where information can be shared between SQL statements on the same sessions, or can be accessed by SQL statements defined and contained within the database system. All of this can be done without any requirement for application logic to support this transfer of information. The purpose of this article is to explain this new feature and how to use it to exploit its potential.
First, learn what global variables are and the basic operations that can be performed with them. Then view some interesting scenarios on how global variables can be exploited. The article concludes with more in-depth topics related to global variables.
What is a global variable?
A global variable is a representation of a changeable value which can be accessed and modified using SQL statements. These variables have been implemented in DB2 as a new type of database object whose definition is stored in the database catalogs. It is important to understand that only the definition of the global variable is stored in the catalogs and not the actual values. The reason for this is that global variables have a session (connection) scope. This means that every session can use the global variables that exist in the catalogs, but each session has its own private value that it can manipulate and use. No other session can access that value.
Another important aspect of global variables is that when security of the information is an issue, controlling access is possible. Specific privileges are required to create or drop a global variable as well as to read or modify the contents of one. This rule also applies to their definition -- privileges associated with a global variable are also defined within the system catalog.
Creating and dropping global variables
To create a global variable, you use the new
CREATE VARIABLE SQL
statement. You need to
SYSADM, DBADM authority or
CREATE_IN for the schema. For example, to create a
global variable that indicates the default printer, you issue this statement:
CREATE VARIABLE mySchema.defaultPrinter VARCHAR(30) DEFAULT 'Printer001'
mySchema.defaultPrinter is the name of the variable.
Please note that global variables have two-part names that consist of the name of a
schema and the name of the variable within that schema. If the qualifier is not
specified, the current schema is implicitly assigned.
VARCHAR(30) is the data type of the variable. The
data type can be a DB2 built-in data type, a distinct type or a reference type. Note that CLOB, DBCLOB, BLOB, LONG VARCHAR, LONG VARGRAPHIC, XML, ARRAY or structured types cannot be specified for global variables.
DEFAULT ‘Printer001’ is the default value this global
variable gets the first time it is referenced if no other value is assigned to it. The
default clause specified can be either a constant, a special register, another global
variable, an expression or the keyword NULL. The expression is any expression of the type
described in "Expressions" in the SQL Reference Guide (see Resources). If a default value is not
specified, the variable is initialized to NULL. The maximum size of the expression is
64K. The default expression calculates a value to assign to the global variable the first time
it is referenced. It can be any expression allowed in DB2 as long as it does not modify
any data in SQL or perform any action outside the scope of the DB2 engine. Also, the
expression must be assignment-compatible to the variable data type.
Some other global variable examples are as follows:
To create a variable that indicates the time when the user first connects, use the following statements:
CREATE VARIABLE loginTime TIMESTAMP DEFAULT CURRENT TIMESTAMP
To create a global variable that indicates the department where the employee works, use the following statement:
CREATE VARIABLE schema1.gvDeptno INTEGER DEFAULT ((select deptno from hr.employees where empUser = SESSION_USER));
If you want to create a global variable that indicates the maximum security level, use this statement:
CREATE VARIABLE mySchema.SecurityLevel CHAR(5) DEFAULT NULL
Please note that the length attribute and data type of a global variable does not vary once it is created. Furthermore, you cannot ALTER the global variable definition at all, including its default value.
When you no longer want the definition of the global variable to exist, you need to drop it. The same privileges are required to drop global variables as any other database object. (Please refer to the SQL Reference for a complete list). For example, to drop the global variable we created, to indicate the default printer you should issue:
DROP VARIABLE mySchema.defaultPrinter
mySchema.defaultPrinter is the name of the variable.
If no schema is specified, special rules are followed to determine the schema name. Name
resolution is discussed later in this article.
Please note that the global variable cannot be dropped if it is referenced in a function, method, trigger or view.
Using global variables
Once a global variable has been created, then any session in the database can use it
providing the right privileges are owned. The
READ privilege is required to reference a
global variable and the
WRITE privilege is required to modify the value of a global
GRANT statement allows an authorized user to grant these privileges and
REVOKE statement is used to remove them. The owner of the global variable is explicitly granted all privileges on the variable.
Global variables can be referenced within any expression as long as the expression does not require being deterministic. The following situations require deterministic expressions, which preclude the use of global variables:
- Check constraints
- Definitions of generated columns
- Refresh immediate Materialized Query Tables (MQTs)
As mentioned before, each session has its own private value of a given global variable. A global variable is instantiated to its default value when it is first referenced in the session.
In order to query all employees that work in the same department as the current user, use this statement:
SELECT * FROM hr.employees WHERE> deptno = schema1.gvDeptno
In order to query the user's department number into host variable hv_depnot, use this statement:
EXEC SQL VALUES schema1.gvDeptno INTO :hv_deptno
In order to query the user's department number, use this statement:
The value of a global variable can be changed using the
SET, SELECT INTO, VALUES INTO
statements. They can also be changed by being the argument of an out or inout parameter in a call statement
For example, to modify the value of the mySchema.defaultPrinter variable to “Printer002”, use this statement:
SET mySchema.defaultPrinter = 'Printer002'
In order to modify the value of the schema1.activeEmployees, you use this statement:
EXEC SQL SELECT count(*) INTO schema1.activeEmployees FROM hr.employees WHERE active = ‘Y’
Now that you know how to do simple operations with global variables, this section provides some interesting scenarios that show you how you can use them.
Global variables to switch triggers off for maintenance
One interesting application for global variables could be to control the behavior of triggers. For example, if you have a table with the following triggers:
CREATE TRIGGER validate_t BEFORE INSERT ON T REFERENCING NEW AS N FOR EACH ROW WHEN (N.ORDER_DATE < CURRENT TIMESTAMP – 7 DAYS) SIGNAL SQLSTATE ‘38000’ SET MESSAGE TEXT = ‘ORDER DATE TOO OLD’;
If the DBA wanted to do some maintenance and insert older data into this table, he would have to prevent other users from inserting new data into the table, drop the trigger, do the maintenance, re-create the trigger and only then allow users to insert data again.
Global variables simplify this task and improve concurrency by creating a global variable that controls the behavior of the trigger. In order to do the maintenance and insert older data into the table, you would use global variables and complete the following steps:
1. Create a global variable:
CREATE VARIABLE schema1.disableTriggers CHAR(1) DEFAULT ‘N’
2. Grant the privilege to read the variable to everyone:
GRANT READ on VARIABLE shema1.disableTriggers TO PUBLIC
3. Grant the privilege to write to the variable only to the DBA user ID:
GRANT WRITE ON VARIABLE shema1.disableTriggers TO dba_user
4. Create the trigger (add checking for the global variable in the
CREATE TRIGGER validate_t BEFORE INSERT ON T REFERENCING NEW AS N FOR EACH ROW WHEN (schema1.disableTriggers = 'N' AND N.ORDER_DATE< CURRENT TIMESTAMP - 7 DAYS) SIGNAL SQLSTATE '38000' SET MESSAGE TEXT = 'ORDER DATE TOO OLD':
Then whenever you want to perform any maintenance in this table, all you have to do is modify the value of the global variable in this session:
SET disable_triggers = ‘Y’;
After changing the value of the global variable, you can do the maintenance while other users continue to insert new records in the table. Once you are done, you can simply change the value back:
SET disable_triggers = ‘N’;
Using global variables to reduce complexity and improve performance
Global variables can be used to calculate values in advance of their use and thus speed up performance and reduce complexity in other SQL entities. For example, a variable could be set by invoking a function that supplies the value of the SESSION_USER special register to fetch the department number for the current user. A view could use the value of this global variable in a predicate to select only those rows for which the user’s department is assigned. The view does not need to contain the sub-query. Therefore, it is less complex and performance for actions against the view is more efficient and faster.
1. As before, create the global variable:
CREATE VARIABLE schema1.gv_deptno INTEGER DEFAULT(get_deptno (SESSION_USER))
2. Or, instead of calling a function, you can use the fullselect in the variable creation definition:
CREATE VARIABLE schema1.gv_deptno INTEGER DEFAULT ((SELECT deptno FROM hr.employees WHERE empUser = SESSION_USER));
3. Create the view:
CREATE VIEW schema1.emp_filtered AS SELECT * FROM employee WHERE deptno = schema1.gv_deptno;
4. Adjust permissions so other users can only SELECT from the view:
REVOKE ALL ON schema1.emp_filtered FROM PUBLIC GRANT SELECT ON schema1.emp_filtered TO PUBLIC
So, any user that uses this view is only able to see the rows that belong to its own department.
Global variable names are qualified two part names. When a global variable is referenced without the schema name, the SQL path is used to assist in the name resolution. For static SQL statements, the SQL path is specified using the FUNCPATH bind option. For dynamic SQL statements, the SQL path is the value of the CURRENT PATH special register. This is the same algorithm used for functions, procedures and data types.
A variable name is only considered a global variable after it is first determined
that the name does not match the name of a column, SQL variable, SQL parameter,
transition variable or a special register. While the CURRENT PATH special register may
contain multiple schemas, a user may not have sufficient privileges to access objects
in one or more of those schemas. Functions and procedures use the
privilege to perform routine resolution -- for example, for routines if the user does
EXECUTE privilege for the routine which is the best match to what is
specified, DB2 moves to the next best match in the list. It would not do that for
VARIABLES. If a user does not have
WRITE as the case may be) on a
particular variable, DB2 does not move on to the next schema - it just fails on compilation.
Conservative binding semantics exist for global variables as they do for functions and data types whenever necessary. There are instances in which global variables are resolved when a statement is processed, and the database manager must be able to repeat this resolution. This is true in
- Static DML statements in packages
- SQL routines
For static DML statements in packages, the global variables are resolved during a bind operation. In views, triggers and SQL routines, they are resolved when the database object is created.
If resolution is performed again on any global variable, it could change the behavior if, for example, a new global variable has been added with the same name in a different schema that is also on the SQL path. To avoid this, the database manager applies conservative binding semantics wherever necessary. This means that only global variables in the SQL path that were defined before the last explicit bind time stamp are considered for global variable name resolution.
When a global variable is referenced within a DML SQL statement or within a trigger,
view or routine, a dependency on the fully-qualified global variable name is recorded
for the statement or object. Also, if applicable, the authorization ID being used for
the statement is checked for
READ privilege on the global variable.
If a global variable is created within a session, then it cannot be used by other sessions until the unit of work has committed. However, the newly created variable can be used within the session that created it before the unit of work is committed. This behavior is consistent with other created objects such as tables.
The setting of a global variable's value is non-transactional. Hence, an application cannot roll back the setting of the value of a global variable. Note the following:
- If the creation of a global variable is rolled back, then the variable no longer exists.
- If the drop of a global variable is rolled back, the value of the variable will be what is was before the drop (either the old value if a reference was made in the session prior to the drop, or uninitialized if no reference existed before).
Global variables are scoped to a per-session level within an XA transaction and are not available to other sessions that join the transaction.
Global variables are non-transactional state objects. Hence, in an XA environment (for both tightly coupled transactions and loosely coupled transactions), global variable objects (both existence and value) will not change at XAend (nor for other XA APIs, like XAstart, XArollback).
Note: In contrast, DB2 has implemented declared global temporary tables as transactional state objects. Hence, when the following XA APIs are executed, declared global temporary tables are dropped.
Auditing global variables
You can audit the information about global variables. The following audit records are generated:
- OBJMAINT audit records are generated when a variable is CREATEd or DROPped
- SECMAINT audit records are generated when a privilege is GRANTed or REVOKEed on global variables
- CHECKING audit records are generated whenever a privilege is checked for a global variable
A comparison with the Oracle package variables
Developers migrating applications from Oracle to DB2 find the new global variables support in DB2 particularly useful, as package variables are commonly used in Oracle. To migrate to DB2, simply map the package name as a schema name and create the variables with that particular schema. This is shown in the samples below:
Example 1. Creating variables for numeric data types
CREATE PACKAGE customerProfile AS maxCredit CONSTANT REAL := 1000.00; numTransactions INT; END customerProfile; /
CREATE SCHEMA customerProfile! CREATE VARIABLE customerProfile.maxCredit DOUBLE DEFAULT (1000.00)! CREATE VARIABLE customerProfile.numTransactions INTEGER!
Example 2. Using variables in a stored procedure
CREATE OR REPLACE PACKAGE students AS PROCEDURE newStudent ( email VARCHAR2, sin NUMBER, initialTution NUMBER, deptno NUMBER); StudentName VARCHAR2; END students; / CREATE OR REPLACE PACKAGE BODY students AS PROCEDURE newStudent ( email VARCHAR2, sin NUMBER, initialTuition NUMBER, deptno NUMBER) IS BEGIN INSERT INTO student VALUES (studentId_seq.NEXTVAL StudentName, email, sin, initialTuition, deptno, SYSDATE); END newStudent; END students; /
CREATE SCHEMA students! CREATE VARIABLE students.StudentName VARCHAR(4000)! CREATE PROCEDURE students.newStudent (email VARCHAR(4000), sin FLOAT, initialTuition FLOAT, deptno FLOAT ) LANGUAGE SQL BEGIN INSERT INTO student VALUES (studentId_seq.NEXTVAL, students.StudentName, email, sin, initialTuition, deptno, CURRENT TIMESTAMP); END!
Example 3. Creating variables for more complex data types
CREATE OR REPLACE PACKAGE myConstants IS initDate CONSTANT DATE := TO_DATE('07-JUL-1969'); mentor CONSTANT VARCHAR2(30) := 'A.Zubiri'; END myConstants; /
CREATE SCHEMA myConstants! CREATE VARIABLE myConstants.initDate TIMESTAMP DEFAULT (TIMESTAMP('1969-07-07-00.00.00.000000'))! CREATE VARIABLE myConstants.mentor VARCHAR(30) DEFAULT ‘A.Zubiri’!
In this article, I present the recently introduced support for global variables in DB2 V9.5. With these new database object, the users can easily build complex systems where information can be shared between SQL statemetns without any application logic to support the transfer of information. Once global variables are created, their definition is stored in the catalogs and any session can use them. And because each global variable has session scope, every session has its own private value that it can manipulate and use and no other session can access that value. Another important aspect of global variables is that where security of the information is an issue, controlling access is possible. Overall, global variables are easy to use, yet they are both powerful and flexible.
- SQL Reference Guide: Get syntax, descriptions, and examples for SQL and RISQL commands, as well as detailed information about supported data types, functions, and query expressions..
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Technology bookstore: Browse for books on these and other technical topics.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in developerWorks blogs and get involved in the developerWorks community.