Wednesday, August 3, 2016

Retrieving System Values using SQL

retrieve system values using sql

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.

4 comments:

  1. Great article. Is it possible to sort the values before presenting it? Say the sysval QAUDJRN.

    ReplyDelete
  2. QSYS2/SYSTEM_VALUE_INFO not ...QSYS2/SYSTEM_VALUE_INDEX

    ReplyDelete
    Replies
    1. Oops. Thank you pointing that out. I have made the correction.

      Delete

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.