Using SQL to create, update, and get from Environmental Variables

Within the latest database PTFs that came as part of the last Technology Refresh were a couple of new Scalar functions that can be used to do things with Environmental Variables. They are named after the APIs that can be used to put and get information from the Environmental Variables. These new SQL Scalar functions are:

• PUTENV:  Creates or changes the Environmental Variable. Equivalent of the following commands: ADDENVVAR and CHGENVVAR, and the putenv API
• GETENV:  Retrieves the value from the Environmental Variable. Equivalent of the getenv API

Both of these Scalar functions are found in the library SYSTOOLS.

For these first examples I used ACS Run SQL Scripts, RSS.

I am going to use the ENVIRONMENT_VARIABLE_INFO to list the Environmental Variables that already exists for this job:

01 SELECT ENVIRONMENT_VARIABLE_TYPE AS "Type", 02 ENVIRONMENT_VARIABLE_NAME AS "Name", 03 ENVIRONMENT_VARIABLE_VALUE AS "Value" 04 FROM QSYS2.ENVIRONMENT_VARIABLE_INFO

Which returns the following results:

Type Name Value ----- ---------------------- ------------------------------ JOB LANG /QSYS.LIB/EN_US.LOCALE JOB ILE_DEBUGGER_1 ALLOW_WIDE_SCREEN

The two above were added as part of my personal start up program.

Now I want to create a new Environment Variable using the SQL PUTENV Scalar function:

01 VALUES SYSTOOLS.PUTENV( 02 ENVIRONMENT_VARIABLE_NAME => 'MY_TEST', 03 ENVIRONMENT_VARIABLE_VALUE => 'Test data from Simon' 04 )

Line 1: As PUTENV is a Scalar function I can execute it using the VALUES statement. If the Environmental variable does not exist PUTENV will create it.

Line 2: The first parameter used by the PUTENV is the name of the Environmental variable.

Line 3: Second parameter is value for the Environmental variable.

The Scalar function returns a ERRNO value:

00001 ------ 0

The ERRNO of zero indicates that the statement completely successfully.

I can use the ENVIRONMENT_VARIABLE_INFO statement, I gave above, to confirm that I now have three Environmental Variables:

Type Name Value ----- ---------------------- ------------------------------ JOB LANG /QSYS.LIB/EN_US.LOCALE JOB ILE_DEBUGGER_1 ALLOW_WIDE_SCREEN JOB MY_TEST Test data from Simon

If I had been returned a different ERRNO I would not know what it means. This is where I can use the ERRNO_INFO Scalar function to translate the code to a description:

01 VALUES SYSTOOLS.ERRNO_INFO( 02 SYSTOOLS.PUTENV( 03 ENVIRONMENT_VARIABLE_NAME => 'MY_TEST', 04 ENVIRONMENT_VARIABLE_VALUE => 'Changed data for Simon' 05 ) 06 )

Line 1: The ERRNO_INFO Scalar function surrounds the PUTENV.

Line 2: The PUTENV Scalar function.

Line 3: This is the same Environmental Variable name as before, therefore, it will be changed to be the value that follows.

Line 4: New value for the Environmental Variable.

I get the same ERRNO as before, zero, and the ERRNO_INFO has returned the ERRNO description.

00001 ------------------ There is no error.

I retrieve data from the Environmental Variable I created using the GETENV Scalar function. I just need to pass to it the Environmental Variable name, and it returns the value:

01 VALUES SYSTOOLS.GETENV('MY_TEST')

The result is:

00001 ------------------------ Changed data for Simon

While what I have shown are good examples. When I use this it is going to be in a RPG program. Below is an example of how I could code a program to use these new Scalar functions:

01 **free 02 dcl-s ErrNo int(5) ; 03 dcl-s String varchar(40) ; 04 exec sql SET :ErrNo = SYSTOOLS.PUTENV('MY_TEST','Test data from Simon') ; 05 if (ErrNo <> 0) ; 06 exec sql SET :String = SYSTOOLS.ERRNO_INFO( :ErrNo ) ; 07 String = 'ErrNo: ' + String ; 08 else ; 09 exec sql SET :String = SYSTOOLS.GETENV('MY_TEST') ; 10 endif ; 11 dsply String ; 12 *inlr = *on ;

Line 2: Declaring an integer variable that will contain the ERRNO.

Line 3: Defining a variable length character variable for text.

Line 4: I use the SQL Set statement to move the value from the PUTENV scalar function into the integer variable, ErrNo. I am using the same Environmental Variable as I did before.

Line 5: If the value in the variable ErrNo is not zero...

Line 6: I use the Set statement to retrieve the ERRNO description into the variable String using the ERRNO_INFO scalar function.

Line 7: I do this to append the string "ErrNo:" to the start of the string in String.

Line 8: Else...

Line 9: I retrieve value from the Environmental Variable into the variable String.

Line 11: I display the contents of the variable String.

The following is display. This shows that I was able to change the Environmental Variable, and I was able to retrieve from it.

DSPLY Test data from Simon

I do like these Scalar function, and would prefer to use them in place of the putenv and getenv APIs.

 

You can learn more about this from the IBM website:

• PUTENV Scalar function
• GETENV Scalar function

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.