Posted by: Serge Rielau


The most satisfying work in developing SQL to me is whenever we add infrastructure to DB2 which makes DB2 more extensible.
For example adding a function like DECODE which is syntactic sugar for CASE expressions is nice, but it only solves a very specific issue.
Contrast that with what we did in DB2 9.7 when we introduced global variables.

Here are some of the key properties of global variables

• They are schema qualified
  That is you can have multiple variables with the same name in different schemas
• Variables are resolved by PATH
  So you can make them available for use with out having to specify the schema
• Variables can be of just about any type
  You can have a simple variable that is a VARCHAR or something really complex such as an ARRAY of ROWs
• Transactions cannot touch them
  Being non transactional allows variables to carry state across transactions. For example you can remember trace information.
• The content of a variable is private the session
  If you set a variable it will only be set in your own session, no other sessions are affected by your actions
• Variables are automatically initialized on first reference
  The initialization is based on the defined default and the default can be an arbitrary and potentially complex expression
• You can prevent setting of a variable by making them constant
  In itself a bit of an oxymoron, but that's how the syntax fell out.
• Full access control
  You can prevent users form reading the content of a variable to keep secret data and you can prevent them from setting variables to keep data secured and trusted.

You think it's hard to figure out what the actual use is? Let me give you some ideas. And I provide the syntax by example:

• Creating constants

CREATE OR REPLACE VARIABLE MATH.MAXINT INTEGER CONSTANT (+2147483647);
CREATE OR REPLACE VARIABLE MATH.PI DECFLOAT(34) CONSTANT (DECFLOAT('3.1415926535897932384626433832795'));
 
SET PATH = CURRENT PATH, MATH;
 
VALUES (MAXINT, PI);
1           2
----------- ------------------------------------------
 2147483647          3.1415926535897932384626433832795
 
  1 record(s) selected.

• Defining a read only, session constant special register

CREATE OR REPLACE VARIABLE REGISTERS.CONNECT_TIME TIMESTAMP(6) CONSTANT (CURRENT TIMESTAMP);
 
--#SET TERMINATOR @<
CREATE OR REPLACE PROCEDURE ADMIN.CONNECT_PROC()
BEGIN
  DECLARE dummy ANCHOR REGISTERS.CONNECT_TIME;
  SET PATH = CURRENT PATH, REGISTERS;
  -- Force initialization
  SET dummy = REGISTERS.CONNECT_TIME;
END@
--#SET TERMINATOR ;
 
UPDATE DB CFG USING CONNECT_PROC ADMIN.CONNECT_PROC;
CONNECT RESET;
CONNECT TO TEST;
VALUES CONNECT_TIME;
1
--------------------------
2011-03-07-06.06.21.093000
 
  1 record(s) selected.

• Defining special registers which are settable and influence built-in functions
  In this case we introduce session level date formats NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT and then teach TO_CHAR, TO_DATE and TO_TIMESTAMP to obey the setting

SET SCHEMA ORAENV;
SET CURRENT PATH = SYSTEM PATH, ORAENV;
 
CREATE OR REPLACE VARIABLE NLS_DATE_FORMAT VARCHAR(40)   DEFAULT 'YYYYMMDD HH.MI.SS';
 
CREATE OR REPLACE VARIABLE NLS_TIMESTAMP_FORMAT VARCHAR(40)  DEFAULT 'YYYYMMDD HH.MI.SS';
 
CREATE OR REPLACE FUNCTION TO_CHAR(stamp DATE) 
  RETURNS VARCHAR(100) DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL 
  RETURN SYSIBM.TO_CHAR(stamp, NLS_DATE_FORMAT);
 
CREATE OR REPLACE FUNCTION TO_CHAR(stamp TIMESTAMP(12)) 
  RETURNS VARCHAR(100) DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL 
  RETURN SYSIBM.TO_CHAR(stamp, NLS_TIMESTAMP_FORMAT);
 
CREATE OR REPLACE FUNCTION TO_DATE(str VARCHAR(100))
  RETURNS TIMESTAMP(0)
  RETURN SYSIBM.TO_DATE(str, NLS_DATE_FORMAT);
 
CREATE OR REPLACE FUNCTION TO_DATE(str CHAR(100))
  RETURNS TIMESTAMP(0)
  RETURN SYSIBM.TO_DATE(str, NLS_DATE_FORMAT);
 
CREATE OR REPLACE FUNCTION TO_TIMESTAMP(str VARCHAR(100))
  RETURNS TIMESTAMP(9)
  RETURN SYSIBM.TO_TIMESTAMP(str, NLS_TIMESTAMP_FORMAT);
 
CREATE OR REPLACE FUNCTION TO_TIMESTAMP(str CHAR(100))
  RETURNS TIMESTAMP(9)
  RETURN SYSIBM.TO_TIMESTAMP(str, NLS_DATE_FORMAT);
 
CREATE OR REPLACE VIEW V$NLS_PARAMETERS(PARAMETER, VALUE)
  AS VALUES (VARCHAR('nls_date_fomat', 64), VARCHAR(NLS_DATE_FORMAT, 64)),
            ('nls_length_semantics', 'BYTE');
 
CREATE OR REPLACE PUBLIC SYNONYM V$NLS_PARAMETERS 
  FOR V$NLS_PARAMETERS;
 
CREATE OR REPLACE PROCEDURE CONNECT_PROC() 
  SET PATH = ORAENV, CURRENT PATH;
 
UPDATE DB CFG USING CONNECT_PROC ORAENV.CONNECT_PROC;
 
CONNECT RESET;
CONNECT TO TEST;
 
SET NLS_DATE_FORMAT = 'DD-MON-YY';
VALUES TO_CHAR(CURRENT DATE);
1
-------------------------
07-MAR-11
 
  1 record(s) selected.

• Pre-executing a security property to execute only once per connection to avoid query complexity

CREATE TABLE Employee(name   VARCHAR(15),
                      salary DECFLOAT(16));
 
CREATE TABLE KnownIP(login VARCHAR(20),
                     ip    VARCHAR(8));
 
CREATE FUNCTION IP(appid VARCHAR(128))
  RETURNS VARCHAR(128) CONTAINS SQL DETERMINISTIC
  NO EXTERNAL ACTION
  RETURN SUBSTR(appid, 1, POSSTR(appid, '.') -1);
 
CREATE VARIABLE knownip INTEGER 
 DEFAULT ((SELECT 1 FROM knownip 
           WHERE login = USER 
             AND ip = ip(application_id())));
 
GRANT READ ON VARIABLE knownip TO PUBLIC;
 
CREATE VIEW employeev AS
  SELECT name, 
         CASE WHEN knownip = 1 
              THEN salary END AS salary
    FROM employee;
 
INSERT INTO employee VALUES ('Jones', 30000);
INSERT INTO knownip VALUES ('SERGE', '*LOCAL');
 
SELECT * FROM employeev;
NAME            SALARY
--------------- ------------------------
Jones                              30000
 
  1 record(s) selected.

One can imagine many more usages. For example to keep a trace back of error stack in SQL or the names of your default printer or email address.

All in all global variables are incredibly versatile.
#Db2