Retrieving System Values using SQL

Addendum (March 6, 2024)

New columns added to the SYSTEM_VALUE_INFO View. Read about the enhancements here.

---

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
• ValueChar = blank
• Null2 = 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. <' + InDs(13).SystemValue + '>') ; 15 dsply ('1. <' + %char(InDs(13).Numeric) + '>') ; 16 dsply ('1. <' + %subst(InDs(13).Character:1:40) + '>') ; 17 dsply ('2. <' + InDs(21).SystemValue + '>') ; 18 dsply ('2. <' + %char(InDs(21).Numeric) + '>') ; 19 dsply ('2. <' + %subst(InDs(21).Character:1:40) + '>') ;

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. <*NOTIFY > 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.