Article Summary
This article is about tackling a fun SQL challenge!
The goal was to parse a spool file in a different way than usual, so I could show how to display the data in a table and automate the process. I also wanted to demonstrate how to handle repeating lines in SQL and how to create your own “SQL Services” for commands that don’t yet have an equivalent service.
While you can view the same details using a QSYS2 query for the network views in NETSTAT for example, the point of this article is to show how to parse spool files in a unique way and possibly create custom automation SQL.
This lets programmers and admins build their own “QSYS2” commands for things that are not available yet, though it takes some effort, and maybe a few curse words!
Use Case
I had to go and compare all the Service Table Entries for various IBM i systems with the WRKSRVTBLE command and saw it has a unique peculiarity with two different ways of outputting the data to spool file.
Given that these values can change over time, I would like to automate as much of this as possible to keep consistency amongst the different partitions. Using SQL will make this job much easier to query from a common host IBM i system through 3 part naming. Something that I would think would make a few sysadmins lives easier.
Let’s start with the command in question: – WRKSRVTBLE
There is an option to print the list shown below :
Which results in a nice, formatted spool file :
Yet do you notice that if you are doing the WRKSRVTBLE OUTPUT(*PRINT) :
It looks different? why?
Well, it has to do with UIM panels, they are “nicely formatted” due to the way the data is loaded. Whereas the other command from the external call, well, that you will recognise, with a lot of other commands with similar UIM Panels, shows differently depending on the options used.
Yes, these commands have different outputs depending on which method is used. I would like to “automate” this spool output remember?
I am seeing the one with the F6 print is WAY easier to parse – but I will leave that to the readers as an exercise on their own.
Now let us take this beast to be slain:
Now the fun starts:
Notice that there are repeating lines starting with “Service” and “Alias”, this will become more useful later on.
Fire up your favourite GUI SQL :
Here I am approaching it step by step (to give you the reader, my thought process – it is a good learning method for beginners to break apart “steps” to make future challenges easier to digest)
Let us first get the spool file to a “QTEMP” table:
Ah! hit the first snag:
(later I will show how to “get this sorted” but for now, run the command manually on a 5250 terminal and make sure that is the ONLY spoolfile you have under your profile)
Using one of Scott Forstie’s examples to read the last spool file I generated magic queries located here; (thanks Scott for doing the heavy lifting on this one ????)
Under here (*use Spool -> Spool – Consume my most recent Spooled File):
Insert that and off you go!
It generates a lovely output directly for us to view in SQL
Here comes the tricky and fun part:
I notice there are some “junk” stuff that I do not like to go through?
Such as the
And this on the LAST page of the spool file:
For reference (they are also here in the 5250 view)
Now let us exclude those with Magic SQL :
WHERE
SPOOLED_DATA NOT LIKE '%Work with Service Table Entries%'
AND SPOOLED_DATA NOT LIKE '%* * * * * E N D O F L I S T I N G * * * * * %'
AND SPOOLED_DATA NOT LIKE '% -=* http://pub400.com *=- %'
AND SUBSTRING(SPOOLED_DATA,1,7) NOT IN ('5770SS1')Let us now also check the result produced now:
Much better!.
A quick CREATE TABLE QTEMP.SPOOLFILE AS (<FULL QUERY>) to make life easier:
--
-- Create a Temporary Spoolfile in QTEMP
-- (*credit to Scott Forstie's example as inspiration herre)
--
CREATE TABLE QTEMP.SPOOLFILE AS (
WITH my_spooled_files (
job,
FILE,
file_number,
user_data,
create_timestamp
)
AS (SELECT job_name,
spooled_file_name,
file_number,
user_data,
create_timestamp
FROM qsys2.output_queue_entries_basic
WHERE user_name = USER
ORDER BY create_timestamp DESC
LIMIT 1)
SELECT job,
FILE,
file_number,
spooled_data
FROM my_spooled_files,
TABLE (
systools.spooled_file_data(
job_name => job, spooled_file_name => FILE,
spooled_file_number => file_number)
)
WHERE
SPOOLED_DATA NOT LIKE '%Work with Service Table Entries%'
AND SPOOLED_DATA NOT LIKE '%* * * * * E N D O F L I S T I N G * * * * * %'
AND SPOOLED_DATA NOT LIKE '% -=* http://pub400.com *=- %'
AND SUBSTRING(SPOOLED_DATA,1,7) NOT IN ('5770SS1')
) WITH DATA;From here on out, I need to make these “columns from rows”, another great challenge for me, and you the reader.
Let me see what I need to become more creative.
Notice that on the actual spool file there are “repeatable rows” mentioned earlier on, right? Most spool files follow the same/similar format. We can use this to our advantage.
Counting these lines gives me every 9 rows it repeats the same format.
For this I can use this insight to “block it off” with the following SQL :
With Tab1 As (
Select A.Spooled_Data,
Row_Number() Over (
) As Rn
From Qtemp.Spoolfile A
) --select * from tab1;
,
Tab2 As (
Select Rn,
(Rn / 9) As Groupval,
A.Spooled_Data,
Mod(A.Rn, 9) As Modnum
From Tab1 A
)
Select *
From Tab2;
Let me explain what is going on with the SQL:
I use the RN(Row Number) divided by 9 as a GROUPING VALUE (more on this part later)
And MOD(RN,9) to generate a value that could be used for GROUPING to COLUMNS.
Notice the selection here.
A quick scroll down, to check that all still fine.
No, it is not!!! what happened???
Let us troubleshoot:
Probably another “off by one error” somewhere in my own SQL. Clearly GROUPVAL, the way I used to think of it is not a reliable way to “group” these records together.
(perhaps I will ask ChatGPT for guidance)
PROMPT
Objective: I have a DB2 table with rows that follow a repeating structure every 9 rows — each group of 9 represents a logical block of related information (e.g., service info, port, protocol, description, aliases, etc that contains data after a colon “:”.). There is no existing GROUPVAL field, but I want to: 1. Generate a group number (GROUPVAL) every 9 rows. 2. Pivot these 9 rows into a single row per group, with meaningful column names based on criteria found in each of these rows.
Here is the DB2 for i SQL that I have used so far “with tab1 as ( select a.SPOOLED_DATA, row_number() OVER() as RN from qtemp.Spoolfile a ) –select * from tab1; , tab2 as ( select RN, (RN/9) AS GROUPVAL, a.SPOOLED_DATA, MOD(a.RN,9) AS MODNUM from tab1 a ) select * from tab2”
Assumptions:
Table name = QTEMP.SPOOLFILE
Column inside this table = SPOOLED_DATA
Rows are in a consistent manner inside this table
I am at the point where I have 9 rows per group, with ROW_NUMBER() and MOD applied. I think the group numbering logic is slightly off. Can you help me diagnose why it is not splitting cleanly every 9 rows, and maybe hint at the part to fix it but do not give me the full corrected SQL just yet.
Help me find the bug so that I can continue with this query (the readers are waiting for another cool article with your help and assistance!)
It comes through with some VERY helpful insight into where I went wrong (and also, I forgot how DB2 uses Integer math with auto rounding), you will have a similar output that will guide you and show you what you can do.
Armed with this information and suggestion, I am fixing the part now that is incorrect and reconfirming my result.:
SQL: (fixed with the part that Chat says about the “off by one error”)
With Tab1 As (
Select A.Spooled_Data,
Row_Number() Over (
) As Rn
From Qtemp.Spoolfile A
) --select * from tab1;
,
Tab2 As (
Select Rn,
((Rn-1) / 9) As Groupval,
A.Spooled_Data,
Mod(A.Rn-1, 9) As Modnum
From Tab1 A
)
Select *
From Tab2;
First Block of the result set looks good:
Last Block of result set looks also good: 
Now we are onto some magic!
Given then this new GROUPVAL , I can use that to pivot the rows to Columns, though there is also another challenge which you will soon see.
-- Step 1: Number the rows
With Tab1 As (
Select Spooled_Data,
Row_Number() Over (
) As Rn
From Qtemp.Spoolfile
),
-- Step 2: Assign group number (every 9 rows)
Tab2 As (
Select Rn,
((Rn - 1) / 9) + 1 As Groupval, -- ensures group 1 starts at row 1–9
Mod((Rn - 1),
9) As Modnum, -- 0 to 8 within each group
Spooled_Data
From Tab1
),
-- Step 3: Pivot 9 rows into 1 row per group
Tab3 As (
Select Groupval,
Max(
Case
When Modnum = 0 Then Spooled_Data
End) As Line1,
Max(
Case
When Modnum = 1 Then Spooled_Data
End) As Line2,
Max(
Case
When Modnum = 2 Then Spooled_Data
End) As Line3,
Max(
Case
When Modnum = 3 Then Spooled_Data
End) As Line4,
Max(
Case
When Modnum = 4 Then Spooled_Data
End) As Line5,
Max(
Case
When Modnum = 5 Then Spooled_Data
End) As Line6,
Max(
Case
When Modnum = 6 Then Spooled_Data
End) As Line7,
Max(
Case
When Modnum = 7 Then Spooled_Data
End) As Line8,
Max(
Case
When Modnum = 8 Then Spooled_Data
End) As Line9
From Tab2
Group By Groupval
)
Select *
From Tab3;
As you can see here, it’s getting there, though there is still some “extra stuff” inside each “Line1” , let us remove that with some REGEX Substring magic, shall we?
Let us ask old ChatGPT friend here if he can further assist us? (Truth be told, REGEX scares me and sometimes leaves me with nightmares, here I am taking a shortcut, with a simple prompt to help out)
PROMPT
SITUATION/SCENARIO:
given these values
Service . . . . . . . . . . . . . :
are-http Port . . . . . . . . . . . . . . : 12401
Protocol . . . . . . . . . . . . : TCP , etc
I would like the value after the “:” COLON with a REGEXP_SUBSTR in DB2 for i SQL ,
OBJECTIVE : can you generate those for me for each of these ?
ADDITIONAL : perhaps look at a common one that I can use if there is any?
It helps me nicely
(perhaps the nightmares will fade if I find the patterns as common items to remember one day… )
Now let us see if we can use this in our query. (do take note : I am going to take advantage here to already rename the columns for myself. to the final query based on the previous query).
-- Step 1: Number the rows
WITH tab1 AS (
SELECT
SPOOLED_DATA,
ROW_NUMBER() OVER() AS RN
FROM QTEMP.SPOOLFILE
),
-- Step 2: Assign group number (every 9 rows)
tab2 AS (
SELECT
RN,
((RN - 1) / 9) + 1 AS GROUPVAL, -- ensures group 1 starts at row 1–9
MOD((RN - 1), 9) AS MODNUM, -- 0 to 8 within each group
SPOOLED_DATA
FROM tab1
),
-- Step 3: Pivot 9 rows into 1 row per group
tab3 AS (
SELECT
GROUPVAL,
MAX(CASE WHEN MODNUM = 0 THEN SPOOLED_DATA END) AS Service,
MAX(CASE WHEN MODNUM = 1 THEN SPOOLED_DATA END) AS Port,
MAX(CASE WHEN MODNUM = 2 THEN SPOOLED_DATA END) AS Protocol,
MAX(CASE WHEN MODNUM = 3 THEN SPOOLED_DATA END) AS Text,
MAX(CASE WHEN MODNUM = 4 THEN SPOOLED_DATA END) AS Aliases, -- this one can be ignored
MAX(CASE WHEN MODNUM = 5 THEN SPOOLED_DATA END) AS Alias1,
MAX(CASE WHEN MODNUM = 6 THEN SPOOLED_DATA END) AS Alias2,
MAX(CASE WHEN MODNUM = 7 THEN SPOOLED_DATA END) AS Alias3,
MAX(CASE WHEN MODNUM = 8 THEN SPOOLED_DATA END) AS Alias4
FROM tab2 GROUP BY GROUPVAL
)
-- Step 4 : REGEXP_SUBSTR to remove the extra stuff before the ":"
select
GROUPVAL,
REGEXP_SUBSTR(SERVICE, ':\\s*(.*)', 1, 1, ' ', 1) as SERVICE,
REGEXP_SUBSTR(PORT, ':\\s*(.*)', 1, 1, ' ', 1) as PORT,
REGEXP_SUBSTR(PROTOCOL, ':\\s*(.*)', 1, 1, ' ', 1) as PROTOCOL,
REGEXP_SUBSTR(TEXT, ':\\s*(.*)', 1, 1, ' ', 1) as TEXT ,
REGEXP_SUBSTR(ALIASES, ':\\s*(.*)', 1, 1, ' ', 1) as ALIASES ,
REGEXP_SUBSTR(ALIAS1, ':\\s*(.*)', 1, 1, ' ', 1) as ALIAS1,
REGEXP_SUBSTR(ALIAS2, ':\\s*(.*)', 1, 1, ' ', 1) as ALIAS2,
REGEXP_SUBSTR(ALIAS3, ':\\s*(.*)', 1, 1, ' ', 1) as ALIAS3,
REGEXP_SUBSTR(ALIAS4, ':\\s*(.*)', 1, 1, ' ', 1) as ALIAS4
from tab3;
,
ALAS! Again, not so well here? Why I wonder?
My Spidey sense says it might be something to do with the “\\” , the part that Old ChatGPT said he would like to “escape”, now I am not 100% sure if a “:” needs an escape now, as it’s not a special character that I normally recognise? Well let me try that to see if it will solve the null values if I do not escape that character.
Would you look at that? Sense of relief after this long learning article of what one can do with the powers of the human knowledge summarized onto the world wide web for the purpose of learning new skills :
That is perfect!. *Chef’s Kiss!”
Some additional information that I promised earlier:
- The WRKSRVTBLE command can only run under certain conditions, it would be an easy change, though there are some considerations and risks you need to be aware of.
- Doing a DSPCMD WRKSRVTBLE will show that it can only run under these conditions:
- One can change it with this command : CHGCMD WRKSRVTBLE
- But the risk is that if there is a PTF or a Operating system upgrade, this goes bye bye! It is better to copy this CMD object to a common library that is in the library list and, rather, change that CMD object instead and proxy it to this QSYS command.
- Also to be quite 100 % honest here, I am not even sure that if IBM intended this command not to be run in batch, or not and if there is other far-reaching consequences, best to err on the side of caution and allow your own CMD command object to be changed instead and test.
- Doing a DSPCMD WRKSRVTBLE will show that it can only run under these conditions:
- For expanding your own knowledge on SQL, see if ChatGPT can give you other options to consider. It sometimes can even come up with other “LOCATE Options” where I am using REGEXP syntax for filtering of the field data. Who knows? it “might” show you a few new tricks or two.
- Here is the prompt I used for “exploring” (after completing MY own solution):
Prompt
OBJECTIVE:
I have a DB2 table with rows that follow a repeating structure every 9 rows — each group of 9 represents a logical block of related information (e.g., service info, port, protocol, description, aliases, etc that contains data after a colon “:”.).
There is no existing GROUPVAL field, but I want to:
1. Generate a group number (GROUPVAL) every 9 rows.
2. Pivot these 9 rows into a single row per group, with meaningful column names based on criteria found in each of these rows.
MY QUERY THAT I USED TO ACCOMPLISH THE GOAL : “<<FULL FINAL QUERY PASTED HERE>>“
INSTRUCTION :
Evaluate alternative solutions without REGEX in DB2 for i SQL to accomplish the same objective, use REMARKS in | (pipe) characters where you can indicate room for improvement in the original SQL that I used if you see any , else just list them separately in this conversation.
-
- It came up with some good but not “great” answers. For example, the ALIAS strings that it tried to group on, with excluded “others”, as there was not a great way to group multiples of the same text criteria called “Alias”.
- Some are fun to try out, just to learn and stretch the brain a bit, even if they are wrong.
- It also helps you tweak your own prompting skills to talk more “fluently AI”.
Final Ending
Using ChatGPT/AI/ LLMs can be a great benefit if you are stuck, think of the rubber duck co-pilot. Though if you have some out of the box things that you need to accomplish, like parsing a spool file per rows of common pattern lines, one can break these steps apart and solve it quite quickly and effortlessly as long as you know where some of the limits are and how you are able to stretch it.
This also shows you the reader, how you can create your own “QSYS2” commands with some elbow grease and time.
Till next time.
Leave a Reply