Pages

Wednesday, March 6, 2024

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.

2 comments:

  1. Strangely, I can view the values from my other systems using 3-part naming but if I try your code and join two systems, I get an error: Statement references objects in multiple databases. This SQL statement can only refer to a single database.

    ReplyDelete
    Replies
    1. A 3 part name statement can only handle data sources from one partition only. If you want to join data from multiple sources you will have to copy data from the remote partition(s), which you can use the 3 part name for, and then compare that to the data on the local partition.

      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.