I am sure we have all had to retrieve a System Value. I would use the Retrieve System Value command, RTVSYSVAL , to do so. In one of the Technology Refreshes to IBM i 7.1 a SQL View, SYSTEM_VALUE_INFO , was introduced to allow us to access the same information using a simple SQL Select statement.

For those of you who worry that this might allow people to change System Values using SQL, rest assured that as this is a View it cannot be used to change data.

SYSTEM_VALUE_INFO contains three columns/fields:

Column name Data type Description SYSTEM_VALUE_NAME VARCHAR(10) System Value name CURRENT_NUMERIC_VALUE BIGINT If System Value is numeric, contains the number. If not then null. CURRENT_CHARACTER_VALUE VARGRAPHIC If System Value is character, contains the value(s). If not then null.

One column I wish was present is one to flag if the System Value has been changed from the "shipped value" (default). To see that I still need to use a CL command:

WRKSYSVAL SYSVAL(*ALL) OUTPUT(*PRINT)

Let me go straight to an example. In this snippet I want to retrieve the System Value for the date format:

01 dcl-s SystemValue char(10) ; 02 dcl-s ValueNbr int(10) ; 03 dcl-s ValueChar char(10) ; 04 dcl-s Null1 int(5) ; 05 dcl-s Null2 like(Null1) ; 06 exec sql SET OPTION NAMING = *SQL, CLOSQLCSR = *ENDMOD ; 07 exec sql SELECT CAST(CURRENT_CHARACTER_VALUE 08 AS CHAR(10) CCSID 37) 09 INTO :ValueChar 10 FROM QSYS2/SYSTEM_VALUE_INFO 11 WHERE SYSTEM_VALUE_NAME = 'QDATFMT' ;

Line 1 – 5: As I am going to be using this program in several examples I need to define a variable to the name of a System Value, line 1 SystemValue , one to contain a number, line 2 ValueNbr , another for a character, line 3 ValueChar , and a pair of null indicators, Lines 4 and 5 Null1 and Null2 .

Line 6: I am setting my SQL options within my program. For more information what this is and why I would want to do this see Putting the SQL options into the source.

Lines 7 – 11: As the QDATFMT System Value is alphanumeric its value is held in the CURRENT_CHARACTER_VALUE column/field. The problem for me is that this value is stored in CCSID 1200, which is undecipherable on the IBM i I am using. Therefore, I am using CAST to convert the CCSID and make a fixed length variable of 10 characters, line 7 and 8. Line 9 is where I give the variable I want to place the retrieved value into. Lines 10 and 11 should be of no surprise as this is where I give the name of the View and the selection used.

As I am in the USA the returned value is " MDY ".

How about for a System Value that returns a numeric value?

07 exec sql SELECT CURRENT_NUMERIC_VALUE 08 INTO :ValueNbr 09 FROM QSYS2.SYSTEM_VALUE_INFO 10 WHERE SYSTEM_VALUE_NAME = 'QLEAPADJ' ;

I do not have to CAST as I did in the previous statement as a number is a number.

Below is a more generic Select statement. It will retrieve the values from the alphanumeric and numeric fields for the System Value given in the variable SystemValue :

07 SystemValue = 'QCCSID' ; 08 exec sql SELECT CAST(CURRENT_CHARACTER_VALUE 09 AS CHAR(10) CCSID 37), 10 CURRENT_NUMERIC_VALUE 11 INTO :ValueChar :Null1,:ValueNbr :Null2 12 FROM QSYS2.SYSTEM_VALUE_INFO 13 WHERE SYSTEM_VALUE_NAME = :SystemValue ;

As one of the retrieved columns will be null I need to give null indicators too, see line 11. For more information about this see the post SQL and null.

CCSID System Value, QCCSID , returns a number, therefore:

Null1 = -1

= -1 ValueChar = blank

= blank Null2 = 0

= 0 ValueNbr = the CCSID for this IBM i

How about a list of all the System Values? For this IBM i there are 158 System Values. If I do a multiple row Fetch I can get all the System Values into a data structure with one Input operation.

01 dcl-ds InDs qualified dim(160) ; 02 SystemValue char(10) ; 03 Numeric packed(10) ; 04 Character char(1280) ; 05 end-ds ; 06 dcl-ds InNulls qualified dim(160) ; 07 NullInds int(5) dim(3) ; 08 end-ds ; 09 dcl-s Rows int(10) inz(%elem(InDs)) ; 10 exec sql DECLARE C0 CURSOR FOR SELECT SYSTEM_VALUE_NAME, CURRENT_NUMERIC_VALUE, CAST(CURRENT_CHARACTER_VALUE AS CHAR(1280) CCSID 37) FROM QSYS2.SYSTEM_VALUE_INFO ORDER BY SYSTEM_VALUE_NAME ; 11 exec sql OPEN C0 ; 12 exec sql FETCH C0 FOR :Rows ROWS INTO :InDs :InNulls ; 13 exec sql CLOSE C0 ; 14 dsply ('1. ') ; 15 dsply ('1. ') ; 16 dsply ('1. ') ; 17 dsply ('2. ') ; 18 dsply ('2. ') ; 19 dsply ('2. ') ;

Lines 1 – 5: This is the definition of data structure array that will contain all of the System Values information. I deliberately made it have more elements, 160, than the number of System Values, 158, as I am sure IBM will add more to a future release or TR.

Lines 6 – 8: As the Fetched values can be null I need a null data structure array to, which needs to have the same number of elements as the InDs data structure array.

Line 9: The variable Rows will be used in the Fetch statement.

Line 10: Declaration of a cursor, C0 , to select all of the rows/records from the View, sorted by System Value name.

Line 11: The cursor is opened.

Line 12: With one Fetch 160 rows from SYSTEM_VALUE_INFO is retrieved in one Input operation. There are only 158 rows so the 159th and 160th elements of the arrays will be their default values.

Line 13: Cursor is closed.

Lines 14 – 16: The data from the 13th element of IndDs is displayed.

Lines 17 – 19: The data from the 21st element is displayed.

The displayed data looks like:

DSPLY 1. <QAUDENDACN> DSPLY 1. <0> DSPLY 1. DSPLY 2. <QBASACTLVL> DSPLY 2. <209> DSPLY 2. < >

In the 13th element, 1 , the numeric value is zero as the System Value returns an alphanumeric value, as the null indicator is in the null data structure array I do not have to worry about checking if the subfield of the element in InDs is null or not. In the 21st element, 2 , the alphanumeric value is blank as the System Values returns a number.

You can learn more about the SYSTEM_VALUE_INFO View from the IBM website here.

This article was written for IBM i 7.2, and should work for some 7.1 TRs too.