Introduction

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.

Back to top

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.

Back to top

Creating and dropping global variables

To create a global variable, you use the new CREATE VARIABLE SQL statement. You need to have 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

where:

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.

Back to top

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 variable. The GRANT statement allows an authorized user to grant these privileges and the 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:

VALUES schema1.gvDeptno

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’

Back to top

Examples

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 WHEN clause):

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.

Back to top

Name resolution

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 EXECUTE privilege to perform routine resolution -- for example, for routines if the user does not have 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 READ (or 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
• Views
• Triggers
• 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.

Back to top

Transactions

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).

XA interaction

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.

XArollback

XAend(SUCCESS)

XAstart(NOFLAGS)

XAstart(JOIN)

Back to top

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

Back to top

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

Oracle

  
   CREATE PACKAGE customerProfile AS
   maxCredit CONSTANT REAL := 1000.00;
   numTransactions INT;
   END customerProfile;
   /

DB2

   CREATE SCHEMA customerProfile!

   CREATE VARIABLE customerProfile.maxCredit DOUBLE DEFAULT (1000.00)!
   
   CREATE VARIABLE customerProfile.numTransactions INTEGER!
            

Example 2. Using variables in a stored procedure

Oracle

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;
/

DB2

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

Oracle

  CREATE OR REPLACE PACKAGE myConstants IS
   initDate  CONSTANT DATE := TO_DATE('07-JUL-1969');
   mentor CONSTANT VARCHAR2(30) := 'A.Zubiri';
   END myConstants;
   /

DB2

   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’!
      

Back to top

Summary

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.

Resources

Learn

• 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.
  
  

Discuss

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Adriana Zubiri is a senior developer in the DB2 LUW Team in the IBM Toronto Lab. She holds a MSc in computer science from the University of Alberta, Canada. She has 10 years of experience working with DB2 and more than 14 years in the database field. Since she joined IBM she has worked extensively with customers and specialized in the performance area for data warehouses. Her current area of expertise is join processing and she is the current join expert in the DB2 LUW Runtime team.

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• What is a global variable?
• Creating and dropping global variables
• Using global variables
• Examples
• Name resolution
• Transactions
• Auditing global variables
• A comparison with the Oracle package variables
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers