Wednesday, May 4, 2016

Global Variables a way to share data in a SQL session

sql create variable

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.

9 comments:

  1. Great information. It sure would be nice to retrieve the value in a CL program.

    ReplyDelete
    Replies
    1. Good point. I haven't seen anything about support for CL access an SQL Global Variable but we needed to do just that. So, the solution we used was to create a service program with an exported sub-procedure that gets the value and returns it to the caller. Then our CL's could access the Global Variable indirectly using CALLPRC.

      Delete
    2. Hi Simon. A great article.
      I am still wondering if global variables can be used for 'using' dynamic SQL. I say this because they can be created dynamically within the program.
      Example
      @Sql_tot=
      'Values (Select '+ @sql_select +
      ' From '+ @sql_from +
      ' Where ' + @Sql_where +
      ') Into '+ @Sql_into;
      Clear ds_sortida;
      Exec SQl prepare S1 from :@sql_tot;
      Exec SQL Execute S1 using :ds_out;
      Here, 'using' is not dinamical

      Delete
    3. Anywhere you have used a RPG variable you should be able to replace it with a global variable instead.

      Delete
  2. Spot on. Learned something new today!

    ReplyDelete
  3. Nice article, Simon.
    SQL Global variables are quite handy indeed.
    Be aware that sql global variables (stored in service programs) can cause object-authority problems.
    Where most program-objects are monitored and promoted by change management tools, these service programs are not. You need to have a proper strategy for handling the creation, usage and removal of these variables/service programs.

    regards,
    Djurre

    ReplyDelete
  4. You can also use global variables in SQL Views

    ReplyDelete
  5. Thank you, for the article very helpful.

    ReplyDelete
  6. ... and even better global variables are also considered in trigger programs.
    BTW when using SQL global variables in ILE environment. An SQL global variable (value) is only valide within the same Activision group.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.