Stretch Imagination with SQL Part 2

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 CL 5250 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 :

 Let us now also check the result produced now:

Much better!.

A quick CREATE TABLE QTEMP.SPOOLFILE AS (<FULL QUERY>) to make life easier :

 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 :

 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:

 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”)

 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.

SQL:

 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:

 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.
Note : I am going to take advantage here to already rename the columns for myself. to the final query based on the previous query.

SQL:

 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:

1.      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.

a.      Doing a DSPCMD WRKSRVTBLE will show that it can only run under these conditions:

b.     One can change it with this command : CHGCMD WRKSRVTBLE

c.      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.

d.     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.

2.      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.

a.      Here is the prompt I used for “exploring” (after completing MY own solution):
Prompt:

b.     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”.

c.      Some are fun to try out, just to learn and stretch the brain a bit, even if they are wrong.

d.     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.