PMS Information Systems
Welcome to PMS Information Systems - IBM i (AS/400) Forum !!!

Get Answers for all your queries on IBM i (AS/400).

SQL TIP: GO GLOBALLY

View previous topic View next topic Go down

SQL TIP: GO GLOBALLY

Post  maran on Thu Jun 02, 2016 4:35 pm

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
avatar
maran
Admin

Posts : 442
Join date : 2009-07-24

View user profile http://pmsinformationsystem.forumotion.net

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum