Putting multiple SQL Selects together to make one row

I was working with one of the Tech Support guys at work putting together bits of information into a one row of data that would allow him to display information on any IBM i partition. This is not something I had done before, and after a little while "playing" I had a statement that put bits of information from different sources together into one row.

I am sure we are familiar with using a Select to get information from a table/file, or perhaps join tables/files together, to get the results. But they are always related, with columns/fields that can be used to link the tables/files together. In this scenario I am running several independent Select statements, each producing one row of results, I want to combine into one. The information want is:

1. Partition name
2. Date and time
3. Default CCSID, from system value
4. Number of spool files in QEZJOBLOG output queue
5. Model number
6. Release of IBM i

The first two columns are the system name can be got by using the special registers: CURRENT SERVER and CURRENT TIMESTAMP. In this case I do not want the microseconds in the timestamp, therefore, I use the (0) to indicate that no microseconds will be returned:

SELECT CURRENT SERVER AS "Partition", CURRENT TIMESTAMP(0) AS "Time" FROM SYSIBM.SYSDUMMY1 Partition Time --------- ------------------- DEV730 2019-10-08 19:25:05

Two of the other columns are system values and I can use the SYSTEM_VALUE_INFO View to get the data.

SELECT SYSTEM_VALUE_NAME AS "Value", CAST(CURRENT_CHARACTER_VALUE AS CHAR(4)) AS "Char value", CAST(CURRENT_NUMERIC_VALUE AS DEC(5,0)) AS "Dec value" FROM QSYS2.SYSTEM_VALUE_INFO WHERE SYSTEM_VALUE_NAME IN ('QCCSID','QMODEL') Value Char value Dec value ------ ---------- --------- QMODEL 41A - QCCSID - 65535

The result for the model number is in the character column of the View, with the number column being null. The CCSID value is in the number column, with the character column being null.

Next up is the column that contains the number of spool files in the QEZJOBLOG output queue. As I am only interested in the contents of one output queue I can use the OUTPUT_QUEUE_ENTRIES table function. As I am only interested in the number of spool files in the output queue I can just use the COUNT(*), rather than give any column names:

SELECT COUNT(*) AS "Count" FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('*LIBL', 'QEZJOBLOG', '*NO')) Count ----- 1040

The last item was the hardest to get. If the IBM i partition I am using is at release 7.4 or 7.3 TR6 I can use the DATA_AREA_INFO table function:

SELECT CAST(DATA_AREA_VALUE As CHAR(6)) AS "Value" FROM TABLE(QSYS2.DATA_AREA_INFO( DATA_AREA_NAME => 'QSS1MRI', DATA_AREA_LIBRARY => 'QGPL')) Value ------ V7R3M0

If the partition is at a earlier release or TR the only place I could find the IBM i release in a place I could fetch it was from the PTF_INFO View:

SELECT PTF_PRODUCT_RELEASE_LEVEL FROM QSYS2.PTF_INFO WHERE PTF_PRODUCT_ID = '5770999' LIMIT 1 PTF_PRODUCT_RELEASE_LEVEL -------------------------- V7R3M0

I have to use the LIMIT as I only one of the possible result returned.

Having established what my SQL Select statements are I now need to put them together so they will produce one row of results. I must not call these "sub-selects" as that means something different. But I can put each of these of SQL select statements into one "master" SQL Select:

01 SELECT 02 CURRENT SERVER AS "Partition", 03 CURRENT TIMESTAMP(0) AS "Time", 04 (SELECT CAST(CURRENT_NUMERIC_VALUE AS DEC(5,0)) 05 FROM QSYS2.SYSTEM_VALUE_INFO 06 WHERE SYSTEM_VALUE_NAME = 'QCCSID') AS "CCSID", 07 (SELECT COUNT(*) 08 FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('*LIBL', 09 'QEZJOBLOG','*NO'))) AS "QEZJOBLOG", 10 (SELECT CAST(CURRENT_CHARACTER_VALUE AS CHAR(5)) 11 FROM QSYS2.SYSTEM_VALUE_INFO 12 WHERE SYSTEM_VALUE_NAME = 'QMODEL') AS "Model", 13 (SELECT CAST(DATA_AREA_VALUE As CHAR(6)) 14 FROM TABLE(QSYS2.DATA_AREA_INFO( 15 DATA_AREA_NAME => 'QSS1MRI', 16 DATA_AREA_LIBRARY => 'QGPL'))) AS "Release 1" 17 FROM SYSIBM.SYSDUMMY1

Each of the individual SQL statements are enclosed within parenthesis ( ( ) ). If you are from an English-English speaking nation, rather than American-English, they are brackets.

Lines 2 and 3: No need for their own separate SQL Select statements to get the partition name and the current timestamp.

Lines 4 - 6: The first Select is enclosed in parenthesis. Here I get the CCSID number.

Lines 7 – 9: Number of spool files in QEZJOBLOG.

Lines 10 – 12: Model number.

Lines 13 – 16: Get the release by retrieving the value from the data structure.

Line 17: The FROM is needed, and as I am not getting data I use Db2 dummy table.

For pre-IBM i 7.4 and 7.3 TR6 then I need to use the PTF_INFO View my statement, lines 13 - 16:

01 SELECT 02 CURRENT SERVER AS "Partition", 03 CURRENT TIMESTAMP(0) AS "Time", 04 (SELECT CAST(CURRENT_NUMERIC_VALUE AS DEC(5,0)) 05 FROM QSYS2.SYSTEM_VALUE_INFO 06 WHERE SYSTEM_VALUE_NAME = 'QCCSID') AS "CCSID", 07 (SELECT COUNT(*) 08 FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('*LIBL', 09 'QEZJOBLOG','*NO'))) AS "QEZJOBLOG", 10 (SELECT CAST(CURRENT_CHARACTER_VALUE AS CHAR(5)) 11 FROM QSYS2.SYSTEM_VALUE_INFO 12 WHERE SYSTEM_VALUE_NAME = 'QMODEL') AS "Model", 13 (SELECT PTF_PRODUCT_RELEASE_LEVEL 14 FROM QSYS2.PTF_INFO 15 WHERE PTF_PRODUCT_ID = '5770999' 16 LIMIT 1) AS "Release 2" 17 FROM SYSIBM.SYSDUMMY1

The results are the same (well not the timestamp), no matter how the IBM i release is fetched:

Partition Time CCSID QEZJOBLOG Model Release 1 --------- ------------------- ----- --------- ----- --------- DEV730 2019-10-08 20:03:48 65535 1040 41A V7R3M0 Partition Time CCSID QEZJOBLOG Model Release 2 --------- ------------------- ----- --------- ----- --------- DEV730 2019-10-08 20:06:47 65535 1040 41A V7R3M0

Having discovered this I am sure that I will be using this in the future as the ability to pull unrelated data together is going to be useful in a lot of the Tech Support Views and statements I have built.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.