Wednesday, June 13, 2018

Using Built–in global variables

using sql built in global variables

I wrote about creating my own global variables in the past, but I failed to mention the "built-in" ones. A global variable is a SQL variable you can create, place a value into, and then use within the same session. This is a great way to pass SQL variables from one program or procedure to the next. When the SQL session ends the variables are return to their default. If I change a global variable in one job, it does not change the value in the global variable in another job.

What I failed to mention in that post is since IBM i 7.2 there are some "built-in" global values that contain system information, and are maintained by the operating system. This allows me to get the values from these global variables and use them in my own programs and procedures. These built-in global variables can be found in two libraries, SYSIBM and QSYS2, I have no idea why IBM decided to split them between the two.

As with the user created global variables they are all CLE service programs:

                         Work with Objects Using PDM
Library . . . . .   QSYS2     


Opt Object     Type    Attribute Text
    APPJOBNAME *SRVPGM CLE       SQL VARIABLE SERVER_MODE_JOB_NAME
    JOB_NAME   *SRVPGM CLE       SQL VARIABLE JOB_NAME
    PROCESS_ID *SRVPGM CLE       SQL VARIABLE PROCESS_ID
    THREAD_ID  *SRVPGM CLE       SQL VARIABLE THREAD_ID

In IBM i 7.3 there are thirteen of these built-in global variables:

Global variable Library Contents Type Length
CLIENT_HOST SYSIBM Name of the current client. VARCHAR 255
CLIENT_IPADDR SYSIBM IP address of the current client. VARCHAR 128
CLIENT_PORT SYSIBM Port number used by current client to connect with the server. INTEGER
JOB_NAME QSYS2 Name of the current job. VARCHAR 28
PACKAGE_NAME SYSIBM Name of the current package being used for a DRDA connection. VARCHAR 128
PACKAGE_SCHEMA SYSIBM Name of the schema the contains the current package. VARCHAR 128
PACKAGE_VERSION SYSIBM Version id of the current package. VARCHAR 64
PROCESS_ID QSYS2 Process id of the current job. INTEGER
ROUTINE_SCHEMA SYSIBM Name of the schema of the currently executing routine. VARCHAR 128
ROUTINE_SPECIFIC_NAME SYSIBM Name of the currently executing routine. VARCHAR 128
ROUTINE_TYPE SYSIBM Type of the current routine. VARCHAR 128
SERVER_MODE_JOB_NAME QSYS2 Name of the job that established the SQL server mode connection. VARCHAR 28
THREAD_ID QSYS2 Thread id of current thread. BIGINT

If I want to see what is held in their built-in global variables I can go to my favorite SQL client and type:

SELECT SYSIBM.CLIENT_HOST,
       SYSIBM.CLIENT_IPADDR,
       SYSIBM.CLIENT_PORT,
       SYSIBM.PACKAGE_NAME,
       SYSIBM.PACKAGE_SCHEMA,
       SYSIBM.PACKAGE_VERSION,
       SYSIBM.ROUTINE_SCHEMA,
       SYSIBM.ROUTINE_SPECIFIC_NAME,
       SYSIBM.ROUTINE_TYPE,
       QSYS2.JOB_NAME,
       QSYS2.PROCESS_ID,
       QSYS2.SERVER_MODE_JOB_NAME,
       QSYS2.THREAD_ID
  FROM SYSIBM.SYSDUMMY1

I have qualified the global variables with their library names as neither of those libraries are in my library list.

I am not going to show what each of these global variables contain as it is specific to only my current job, and you would see something different if you were to run the same statement.

How would I use this in a RPG program? Rather than use a SQL Select statement I prefer to use a Set instead.

01  **free
02  dcl-s JobName char(28) ;
03  dcl-s IPAddress char(30) ;
04  dcl-s Host char(30) ;

05  exec sql SET :JobName = QSYS2.JOB_NAME,
06               :IPAddress = SYSIBM.CLIENT_IPADDR,
07               :Host = SYSIBM.CLIENT_HOST ;

08  dsply ('Job name = ' + JobName) ;
09  dsply ('IP address = ' + IPAddress) ;
10  dsply ('Client host = ' + Host) ;

11  *inlr = *on ;

Line 1: Why would I code in an old fashioned way when I can use totally free RPG.

Lines 2 – 4: These are the definitions for the variables that will contain the data from the built-in global variables. The variables for the IP address and the Host name I have code as fixed length character fields as the returned values from the built-in global valuables will fit in that size of variable.

Lines 5 – 7: This is my SQL Set statement to retrieve the data from the built-in global variables. I can separate each retrieval by a comma so that one Set will retrieve all three values.

Line 8 – 10: I am using the Display operation code to display what I retrieved.

When I run the program I can see the values I retrieved from those built-in global variables:

DSPLY  Job name = 197270/SIMON/QPADEV0001
DSPLY  IP address = 999.99.999.99
DSPLY  Client host = 999-99-999-99.SBCGLOBAL.NET

I have used the JOB_NAME global variable in some of my SQLRPGLE programs as it is an easy way to retrieve the job name, in my opinion better than building it myself from the program data structure.

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.3 and 7.2.

4 comments:

  1. Perhaps IBM should make **FREE the default (especially with the focus on storing source in IFS) and add a **OLDFASHIONED for deprecated language versions ;)

    ReplyDelete
  2. The problem is that there are a lot of code out there where people have used the initial columns for various things like programmer's initials on added or modified code or a WO # associated with same. If it was the default they wouldn't compile without adding the **OLDFASHIONED... granted that's not hard but the idea is to be able to not break existing code.

    ReplyDelete
  3. Just be thankful you no longer need the /FREE or /END-FREE statements. :)

    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.