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

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

Join the forum, it's quick and easy

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

Get Answers for all your queries on IBM i (AS/400).
PMS Information Systems
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SQL TIP: GO GLOBALLY

Go down

SQL TIP: GO GLOBALLY  Empty SQL TIP: GO GLOBALLY

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

Posts : 442
Join date : 2009-07-24

https://pmsinformationsystem.forumotion.net

Back to top Go down

Back to top


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