Changes to the SQL View for System Values

There are times when IBM updates a SQL View to add information to it that makes more useful and easier to use than it was before. In the last round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, came the addition of columns to the View that we can use to retrieve information about system values. The added rows doubled the number of columns in the View, by all means it previously had just a few rows.

The SYSTEM_VALUE_INFO View was introduced as part of IBM i 7.2 and 7.1. I have primarily used it for comparing the system values in different partitions that is an audit requirement. I could get the information from multiple partitions and display it in the same "report". The "report" is a Microsoft Excel spreadsheet.

The original version of the View contained just three columns:

1. System value name
2. Numeric value, contains system value if it is a number
3. Character value, contains systems value if it is character data

I combined the numeric and character values into one column for my "report". But I was missing one piece of information I knew would be really useful: what was the default value of the system value when the partition was created? These two are new columns in the changed View. All of the new columns are:

1. SYSTEM_VALUE:  Created from the combination from the numeric or character values as I did in my report
2. TEXT_DESCRIPTION:  The description of the system value
3. CATEGORY:  Category that the system value belongs to
4. CHANGEABLE:  Can the system value be changed?
5. DEFAULT_VALUES:  The shipped/default value for the system value

If you want to see what is in these new columns you can use the following statement:

01 SELECT SYSTEM_VALUE_NAME, 02 SYSTEM_VALUE,TEXT_DESCRIPTION,CATEGORY,CHANGEABLE, 03 SHIPPED_DEFAULT_VALUE 04 FROM QSYS2.SYSTEM_VALUE_INFO

I am not going to show these results, but I will show the results for a "report" combining data from two partitions.

In the following examples I have two partitions:

1. MYSYS:  The partition I primarily use, and where I will be running the following scripts in.
2. OTHERSYS  Another partition, that is like MYSYS when it comes to IBM i release, technology refreshes, and PTFs.

The SQL statement below combines the results from the two partitions.

01 SELECT A.SYSTEM_VALUE_NAME,A.CATEGORY,A.SHIPPED_DEFAULT_VALUE, 02 A.SYSTEM_VALUE AS "Local",B.SYSTEM_VALUE AS "Remote", 03 A.TEXT_DESCRIPTION 04 FROM QSYS2.SYSTEM_VALUE_INFO A 05 JOIN OTHERSYS.QSYS2.SYSTEM_VALUE_INFO B 06 ON A.SYSTEM_VALUE_NAME = B.SYSTEM_VALUE_NAME 07 ORDER BY 1

Line 1: From the partition I am on I want the system value name, the category it belongs to, and its default value.

Line 2: The first column in the line is the system value's value from the local partition. The second is the same from the other partition.

Line 3: And I want the system value's description.

Lines 4 – 6: I have defined the View on the local partition, and then joined it to the View on the remote system using the three part name, line 5. The key to the join the two is the system value's name.

Line 7: And I want the results sorted by the system value name.

I am only showing the first five results that were returned:

SHIPPED_ SYSTEM_VALUE DEFAULT _NAME CATEGORY _VALUE Local Remote ------------ -------- --------- ----- ---- QABNORMSW *SYSCTL 0 0 0 QACGLVL *MSG *NONE *NONE *NONE QACTJOB *ALC 200 200 200 QADLACTJ *ALC 30 30 30 QADLSPLA *ALC 2048 2048 2048 TEXT_DESCRIPTION ------------------------------------------ Previous end of system indicator Accounting level Initial number of active jobs Additional number of active jobs Spooling control block additional storage

No changes have been made to these system values. The system values' values on the local and remote partitions are both the same as the default system values.

I had created a View to gather all the information and format it in the way I wanted with the original SYSTEM_VALUE_INFO View. I modified the View to incorporate these new columns:

01 CREATE OR REPLACE VIEW MYLIB.TESTVIEW 02 (SYSTEM_VALUE_NAME,CATEGORY,DEFAULT_VALUE,LOCAL_SYSTEM, 03 LOCAL_SYSTEM_VALUE,REMOTE_SYSTEM,REMOTE_SYSTEM_VALUE, 04 TEXT_DESCRIPTION,DEFAULT_VS_LOCAL, LOCAL_VS_REMOTE) 05 AS 06 (SELECT A.SYSTEM_VALUE_NAME,A.CATEGORY,A.SHIPPED_DEFAULT_VALUE, 07 'MYSYS',A.SYSTEM_VALUE,'OTHERSYS',B.SYSTEM_VALUE, 08 A.TEXT_DESCRIPTION, 09 CASE WHEN A.SHIPPED_DEFAULT_VALUE = A.SYSTEM_VALUE 10 THEN BOOLEAN(TRUE) 11 ELSE BOOLEAN(FALSE) 12 END, 13 CASE WHEN A.SYSTEM_VALUE = B.SYSTEM_VALUE 14 THEN BOOLEAN(TRUE) 15 ELSE BOOLEAN(FALSE) 16 END 17 FROM QSYS2.SYSTEM_VALUE_INFO A 18 JOIN OTHERSYS.QSYS2.SYSTEM_VALUE_INFO B 19 ON A.SYSTEM_VALUE_NAME = B.SYSTEM_VALUE_NAME) ;

Line 1: All my Views have the CREATE OR REPLACE. This View will be called TESTVIEW.

Lines 2 – 4: The names of the columns in this View. I have added four columns that were not in the SQL statement above:

1. LOCAL_SYSTEM:  Name of the local partition
2. REMOTE_SYSTEM:  Name of the remote partition
3. DEFAULT_VS_LOCAL:  On the local partition is the system value the same as the default
4. LOCAL_VS_REMOTE:  Are the system value's value the same on the local and remote partitions

Lines 6 – 17: The SQL Select statement used for the View.

Line 7: You will see that I have hard coded the name of the local and remote partitions.

Lines 9 – 12: I use a Case statement to determine if the default and current system value's value in the partition are the same. If they are Boolean true is returned, if not Boolean false is returned.

Lines 13 – 16: This Case statement compares the local system value's value, if they are the same Boolean true is returned and Boolean false if they are not.

If my partitions were not IBM i 7.5 I cannot use Boolean data types. I would replace the logic on lines 10 and 11, and 14 and 15 with:

XX THEN '1' XX ELSE '0'

Once I have created the View I can then inquire upon it:

01 SELECT * FROM TESTVIEW ORDER BY 1

Which returns the following:

SYSTEM_ LOCAL_ REMOTE_ VALUE_ DEFAULT LOCAL_ SYSTEM REMOTE_ SYSTEM NAME CATEGORY _VALUE SYSTEM _VALUE SYSTEM _VALUE --------- -------- ------- ------ ------ -------- ------- QABNORMSW *SYSCTL 0 MYSYS 0 OTHERSYS 0 QACGLVL *MSG *NONE MYSYS *NONE OTHERSYS *NONE QACTJOB *ALC 200 MYSYS 200 OTHERSYS 200 QADLACTJ *ALC 30 MYSYS 30 OTHERSYS 30 QADLSPLA *ALC 2048 MYSYS 2048 OTHERSYS 2048 QCONSOLE *SYSCTL QCONSOLE MYSYS DSP01 OTHERSYS DSP01 DEFAULT_ LOCAL_VS TEXT_DESCRIPTION VS_LOCAL _REMOTE ------------------------- -------- -------- Previous end of system... true true Accounting level true true Initial number of acti... true true Additional number of a... true true Spooling control block... true true Console name false true

As before I am only showing a few of the results. The first five results have "true" in the comparison columns, therefore, I know that the current values on the two partitions are the same as the original default value.

I have added a sixth result for the QCONSOLE system value. The value on the local partition has been changed from the default. The local and remote partitions have the same changed value.

As I have now changed my View to get the information I need, now I am going to change the way I send my data to the IFS.

Before I had used the Copy To Import File command, CPYTOIMPF, to copy the data from the View to the IFS, where it would be a CSV file.

Thanks to another recent addition I can create a Microsoft Excel file using the GENERATE_SPREADSHEET Table Function. This Table Function was released in the same TR as this View was enhanced.

The RPG program below shows how I copied data from the TESTVIEW to a Excel in the IFS:

01 **free 02 dcl-s ReturnCode char(1) ; 03 dcl-s PathName varchar(100) ; 04 exec sql CALL QSYS2.QCMDEXC('DROP TABLE IF EXISTS QTEMP.STDOUT') ; 05 exec sql CALL QSYS2.QCMDEXC('OVRDBF FILE(STDOUT) TOFILE(QTEMP/STDOUT) + OVRSCOPE(*JOB)') ; 06 PathName = '/home/MyFolder/system_values_comparison_' + %char(%date():*iso) ; 07 exec sql SET :ReturnCode = 08 SYSTOOLS.GENERATE_SPREADSHEET( 09 PATH_NAME => :PathName, 10 LIBRARY_NAME => 'MYLIB', 11 FILE_NAME => 'TESTVIEW', 12 SPREADSHEET_TYPE => 'xlsx', 13 COLUMN_HEADINGS => 'COLUMN') ; 14 exec sql CALL QSYS2.QCMDEXC('DLTOVR FILE(STDOUT) LVL(*JOB)') ; 15 dsply ('Return code = ' + ReturnCode) ; 16 *inlr = *on ;

I am not going to go into the details of this program as this is basically the same as one of the examples I wrote about the GENERATE_SPREADSHEET Table Function. I recommend you go there if you are unsure what the above is doing.

The program returned:

DSPLY Return code = 1

Which tells me that the Excel file was created.

I can use the IFS_OBJECT_STATISTICS Table Function to confirm:

01 SELECT PATH_NAME 02 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 03 START_PATH_NAME => '/home/MyFolder', 04 SUBTREE_DIRECTORIES => 'NO', 05 OBJECT_TYPE_LIST => '*STMF')) ;

Lines 3 – 5: This where I say I want to look in my folder and for all stream files. All data files in the IFS are steam files.

And my results show my file in my folder and has the date as part of the file's name.

PATH_NAME ------------------------------------------------------- /home/MyFolder/system_values_comparison_2024-03-06.xlsx

These additional columns in SYSTEM_VALUE_INFO are a great addition, and they are going to make my "report" for the auditors so much easier to generate, and in the file format they like.

 

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

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.