SQL TIP: GO GLOBALLY
Page 1 of 1
SQL TIP: GO GLOBALLY
SQL GLOBAL VARIABLES
IBM i 7.1, SQL offers something called a global variable, which can be used to share information among procedures and functions in an SQL session, It cab be used as LDA to share the data with in SQL session.
How to declare them?
Global variables are defined using the CREATE VARIABLE statement.
How to use them?
They can be defined with any built-in or user-defined type and can be assigned a specific default value. Once created, the variables are available for use by any and all SQL sessions. But don't be confused by the term "Global variable." In this sense, "global" only pertains to everything in the active SQL session (or i/OS job.) A global variable that is changed in SQL session A will not be visible to another process that accesses the same variable within SQL session B. Also, when a global variable is changed, the modified value is discarded when the session ends.
Once a global variable has been created, it can be accessed just about anywhere a variable or expression is allowed (INSERT/UPDATE/DELETE/SELECT/SET, etc.).
Example:
CREATE VARIABLE MYLIB.MYVAR INT DEFAULT 5;
LABEL ON VARIABLE MYLIB.VAR IS 'Number of retries allowed';
Once Global variable is declared and this can be used as follows:
SELECT * FROM (VALUES(DATALIB.RETRY)) VARIABLES(RETRY) *SQL Naming
SELECT RETRY FROM SYSIBM/SYSDUMMY1 -- *SYS Naming
IBM i 7.1, SQL offers something called a global variable, which can be used to share information among procedures and functions in an SQL session, It cab be used as LDA to share the data with in SQL session.
How to declare them?
Global variables are defined using the CREATE VARIABLE statement.
How to use them?
They can be defined with any built-in or user-defined type and can be assigned a specific default value. Once created, the variables are available for use by any and all SQL sessions. But don't be confused by the term "Global variable." In this sense, "global" only pertains to everything in the active SQL session (or i/OS job.) A global variable that is changed in SQL session A will not be visible to another process that accesses the same variable within SQL session B. Also, when a global variable is changed, the modified value is discarded when the session ends.
Once a global variable has been created, it can be accessed just about anywhere a variable or expression is allowed (INSERT/UPDATE/DELETE/SELECT/SET, etc.).
Example:
CREATE VARIABLE MYLIB.MYVAR INT DEFAULT 5;
LABEL ON VARIABLE MYLIB.VAR IS 'Number of retries allowed';
Once Global variable is declared and this can be used as follows:
SELECT * FROM (VALUES(DATALIB.RETRY)) VARIABLES(RETRY) *SQL Naming
SELECT RETRY FROM SYSIBM/SYSDUMMY1 -- *SYS Naming
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|