IBM i SQL

DB2 SQL GLOBAL VARIABLES


GLOBAL VARIABLES

  1. GLOBAL VARIABLES are database objects to save a single value at a time(like DTAARA object)
  2. They can be accessed and modified using SQL statements.
  3. Their definition (only definition, not actual value) is stored in the database catalogs.
  4. The reason for this is that global variables have a session scope.
  5. 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.
  6. No session can access the GLOBAL VARIABLE’s value of other session.
  7. CREATE VARIABLE statement is used to create a new GLOBAL VARIABLES.

CREATE VARIABLE myCounter INT DEFAULT 01
SELECT EMPNO, LASTNAME, CASE WHEN myCounter  = 1 THEN SALARY ELSE NULL END FROM EMPLOYEE WHERE WORKDEPT = ’A00’

 

Change Value of GLOBAL VARIABLE

SQL SET command can be used to change value of GLOBAL VARIABLE

SET myCounter = 29
View Value of GLOBAL VARIABLE

You can use any SELECT statement to view value of GLOBAL VARIABLE.

SELECT myCounter from SYSIBM.SYSDUMMY1 
DROP VARIABLE

To delete GLOBAL VARIABLE use DROP VARIABLE

DROP VARIABLE myCounter

IBM i developer.

View Comments
There are currently no comments.