Creating and using global variables

Use the CREATE VARIABLE statement to create a global variable. The following example creates a global variable that defines a user class.

  CREATE VARIABLE USER_CLASS INT DEFAULT (CLASS_FUNC(USER))

This variable will have its initial value set based on the result of invoking a function called CLASS_FUNC. This function is assumed to assign a class value such as administrator or clerk based on the USER special register value.

A global variable is instantiated for a session the first time it is referenced. Once it is set, it will maintain its value unless explicitly changed within the session.

A global variable can be used in a query to determine what results will be returned. In the following example, a list of all employees from department A00 are listed. Only a session that has a global variable with a USER_CLASS value of 1 will see the salaries for these employees.

SELECT EMPNO, LASTNAME, CASE WHEN USER_CLASS = 1 THEN SALARY ELSE NULL END
   FROM EMPLOYEE
   WHERE WORKDEPT = 'A00'

Global variables can be used in any context where an expression is allowed. Unlike a host variable, a global variable can be used in a CREATE VIEW statement.