Global Variables a way to share data in a SQL session

Generations of RPG programmers have been able to store values that could be shared between programs by using the Local Data Area, LDA. Since IBM i 7.1 SQL has had the same functionality being able to share values between programs, procedures, etc within the same SQL session. A SQL session is not the same a job.

The simplest way I can think to describe when a SQL session starts is when the first SQL statement is executed in a program or procedure and ends when the program or procedure ends, or it starts when the STRSQL command is started and ends when I exit. I hope that is clear enough for you, and not confusing as it took me a few minutes to work out what it meant.

These values are stored in Global Variables. Changes made within one SQL session to a Global Variable will be independent of any parallel sessions or following sessions. All other sessions will see the default value of the Global Variable.

A Global Variable can be created for any SQL data type, including User Defined ones. To see how to define User Data types see Using SQL User Defined Types.

I create the Global Variable in a schema (library) of my choice. If I do not give one it will be created in the library QGPL. I cannot create a Global Variable in QTEMP.

I can give the Global Variable a default value. If I do not the default will be null.

So how do I create a Global Variable? The simplest way is to STRSQL, and at the entry line type:

CREATE VARIABLE MYLIB.GLOBALVAR1 VARCHAR(30) DEFAULT 'No value'

If I go look in MYLIB for GLOBALVAR1 I find that it has created a service program (*SRVPGM).

Object Type Attribute Text GLOBALVAR1 *SRVPGM CLE SQL VARIABLE GLOBALVAR1

I can make the length of the name of my Global Variable longer than ten characters. When I do that the system name is shortened:

CREATE VARIABLE MYLIB.GLOBALVAR001 VARCHAR(30) DEFAULT 'No value' Object Type Attribute Text GLOBA00001 *SRVPGM CLE SQL VARIABLE GLOBALVAR001

Prior to IBM i 7.2 TR3 it was not possible to alter a Global Variable, I would have to delete the existing one and create it anew with whatever changes I wanted. To delete a Global Variable I would do:

DROP VARIABLE MYLIB/GLOBALVAR1

I am fortunate to be on a partitition at IBM i 7.2 TR3, so if I want to change the attributes of my Global Variable I can just add "OR REPLACE" and the change is made:

CREATE OR REPLACE VARIABLE MYLIB/GLOBALVAR1 CHAR(30)

The result was to change my Global Variable from VARCHAR to CHAR, and as no default is given my variable is null.

I can change the description of the Global Variable from the default to be whatever I would like it to be by using:

LABEL ON VARIABLE MYLIB.GLOBALVAR1 is 'Global variable number 1' Object Type Attribute Text GLOBALVAR1 *SRVPGM CLE Global variable number 1

Another nice feature in the IBM i 7.2 TR3 command, and I assume 7.1 TR11 too, is a way I can choose the system name I want, rather than have the system shorten it, by using "FOR SYSTEM NAME" followed by a valid system name, which means not more than ten characters:

CREATE VARIABLE MYLIB/GLOBALVAR1 FOR SYSTEM NAME GLBLVAR001 CHAR(30) Object Type Attribute Text GLBLVAR001 *SRVPGM CLE SQL VARIABLE GLOBALVAR001

To retrieve the value from the Global Variable I just use a SELECT:

SELECT * FROM (VALUES(MYLIB.GLOBALVAR1)) VARIABLES(VAR1)

Which will display the default text:

....+....1....+....2....+....3 VAR1 No value ******** End of data ********

To change the value in the Global Variable for this SQL session I can use SET:

SET MYLIB.GLOBALVAR1 = 'Changed text'

So when I use the SELECT to retrieve the value from the variable I get:

....+....1....+....2....+....3 VAR1 Changed text ******** End of data ********

If I use another SQL session, even within the same job, I will get the default value.

Typing in these SQL statements in STRSQL is all fine and dandy, but let me give a more realistic example.

I am going to create a Global Variable to contain the employee's department number.

CREATE VARIABLE RPGPGM1.GVDEPARTMENT FOR SYSTEM NAME GVDEPRTMNT CHAR(10) DEFAULT '*Not given' Object Type Attribute Text GVDEPRTMNT *SRVPGM CLE SQL VARIABLE GVDEPARTMENT

Don't forget I am using an IBM i that is running 7.2 TR3, therefore, I can use the "FOR SYSTEM NAME". I have also decided to prefix my Global Variable's name with "GV" to make it obvious to other programmers that is a Global Variable.

My very simple program will:

1. Retrieve the value of the Global Variable.
2. If it is the default value I will get the employee's department number, and update the Global Variable.
3. Call a second program, which is within the same SQL session.
4. List all the expenses for the department number from the Global Variable.
5. Second program will end and return control to the first program.
6. When the first program ends the SQL session ends, and now when I look at the value of the Global Variable it will be the default value.

The program is written in fully free RPG. If you are unfamiliar with what that means read Trying fully free RPG.

01 **free 02 dcl-pr PGM2 extpgm ; 03 end-pr ; 04 dcl-s GvDept char(10) ; 05 exec sql SET :GvDept = MYLIB.GVDEPARTMENT ; 06 if (GvDept = '*Not given') ; 07 dsply ('1.1. GvDept = ' + GvDept) ; 08 exec sql SET MYLIB.GVDEPARTMENT = (SELECT HOME_DEPARTMENT FROM EMPSECV0 WHERE USER_PROFILE = CURRENT_USER) ; 09 endif ; 10 exec sql SET :GvDept = MYLIB.GVDEPARTMENT ; 11 dsply ('1.2. GvDept = ' + GvDept) ; 12 PGM2() ; 13 exec sql SET :GvDept = MYLIB.GVDEPARTMENT ; 14 dsply ('1.3. GvDept = ' + GvDept) ; 15 *inlr = *on ;

Line 1: As this is fully free my first line starts with a **FREE.

Lines 2 and 3: This is the procedure for the call to my external program PGM2.

Line 4: This is the variable I am going to retrieve the Global Variable into.

Line 5: I am "setting" the value in the Global Variable into my variable defined on line 4.

Lines 6 - 9: If my variable contains the default value I update the Global Variable using the SET and a SELECT to get my home department from the SQL View EMPSECV0. This is a view that is built over a couple of files in our security and payroll libraries and allows us to map a user profile to an employee record. When I created the view I only included the columns/fields that were necessary, all the other that contain private information were not included.

lines 10 and 11: I retrieve the value from the Global Variable and display it.

Line 12: I can my second program.

Lines 13 and 14: Again I retrieve and show the value of the Global Variable.

This is only the parts of PGM2 that demonstrate Global Variables:

01 **free 02 dcl-ds InputDs extname('EXPMSTP') qualified ; 03 end-ds ; 04 dcl-s GvDept char(10) ; 05 exec sql DECLARE C0 CURSOR FOR SELECT * FROM EXPMSTP WHERE DEPARTMENT = MYLIB.GVDEPARTMENT AND PAID = 'U' ORDER BY COUNT FOR READ ONLY ; 06 exec sql OPEN C0 ; 07 dow (1 = 1) ; 08 exec sql FETCH NEXT FROM C0 INTO :InputDs ; . . 09 enddo ; 10 dsply ('2.1. InputDs = ' + InputDs) ; 11 exec sql SET MYLIB.GVDEPARTMENT = 'Pgm 2' ; 12 exec sql SET :GvDept = MYLIB.GVDEPARTMENT ; 13 dsply ('2.2. GvDept = ' + GvDept) ; 14 *inlr = *on ;

Lines 2 and 3: I am defining a data structure to be the same as the Expense Master file, EXPMSTP, so I can FETCH straight into it.

Line 4: I am going to this variable to hold the value of the Global Variable at the end of this program.

Line 5: Here I am defining my DECLARE for my cursor to retrieve data from EXPMSTP where the Home department, DEPARTMENT, is equal to the Global Variable changed in the first program.

Line 6: I open my cursor.

Lines 7 – 9: This is a Do-loop that retrieves the records from EXPMSTP, one at a time, and fetches them into the data structure defined on lines 2 and 3.

Line 11 - 13: I am changing the value of the Global Variable, and then retrieving its value into GvDept.

Line 14: This program ends and control returns to the first program.

When I run the program the DSPLY show the following, which is what I expected:

DSPLY 1.1. GvDept = *Not given DSPLY 1.2. GvDept = 0101002300 DSPLY 2.1. InputDs = 0101002300    DSPLY 2.2. GvDept = Pgm 2 DSPLY 1.3. GvDept = Pgm 2

As the SQL session ends when the first program ends the value in the Global Variable will display its default value:

....+....1....+....2....+....3 VAR1 No value ******** End of data ********

As you can see defining and using Global Variables is an easy way to pass values between programs containing SQL and between procedures, rather than have to pass the values as parameters.

 

You can learn more about the CREATE VARIABLE statement from the IBM website here.

 

This article was written for IBM i 7.2, some of the features will only work with 7.2 TR3, and Global Variables are only available on servers at 7.1 or a higher version of IBM i.