Calculate different types of averages using SQL

As we learned at school there are three types of averages:

1. Mean:  Which is what people when they use the word "average" mean (pun is intended). It is calculated by adding several values together and dividing them by the total number of values.
2. Median:  The midpoint of a sequence of numbers. This number may or may not be a value in the sequence.
3. Mode: The most frequent number of the set of data.

Fortunately Db2 for i gives us scalar functions for the first two, and we can easily determine the third.

Let me start with my list of values, that are all taken from field FLD1 in the file TESTFILE.

01 SELECT * 02 FROM TESTFILE 03 ORDER BY FLD1

Retrieving the file and library name from a SQL alias

I use SQL aliases a lot of the time to access data from a file that contains multiple members. When an alias has an undescriptive name like ALIAS1 which file was it built over?

Fortunately there are two scalar functions that allow me to retrieve that information from the alias:

• TABLE_NAME:  Name of the file that the alias is built over
• TABLE_SCHEMA:  The library in which file that the alias is built over is found

Before I get to use those scalar functions I need a file with multiple members and aliases built over those members.

Let me start with the file. You will not be surprised to find that it is called TESTFILE.

01 A R TESTFILER 02 A FLD1 100A VARLEN

I use the following command, CRTPF, to create my physical file.

Happy New Year, 2022!

Last year was blighted by the COVID pandemic again. I am sure all of us are glad to see another year dominated by the virus behind us. I am keeping my fingers crossed that we might start returning to a more "normal" life this year.

Despite all the restrictions upon our working lives IBM still managed to bring us a new Power server series, Power 10, starting with the E1080 server. As well as two Technology Refreshes. You can read about it all on this website.

Every year I look to see what were the most read posts of the year. These were:

Log4j vulnerabilities for IBM i

The Log4j vulnerabilities came to light earlier this month. I have not written about it as others have a better understanding of how this effects the operating system we love, and have written good articles about it too.

I was sent this link to an IBM Blog entry that describes what you can to remediate these vulnerabilities. And I thought I would share it with you. The blog post is general to all IBM products, not just IBM i and Power systems.

https://www.ibm.com/blogs/psirt/an-update-on-the-apache-log4j-cve-2021-44228-vulnerability/

Please share this link with your IBM i system administrators, and ask them to check if any updates need to be applied to your IBM products and environments.

Update

Mike Mayer sent me a couple more links that might help too:

• Security bulletin for Power HMC
• IBM Product Security Incident Reponse blog (PSIRT)

While these are not related to Log4j, Peder Udesen shared these vulnerabilities in RDi:

• Security bulletin for RDi
• How to get the patches and install them

Searching for member information with SQL

Db2 optimizes very large SQL tables by dividing the data contained within into partitions. These partitions store rows of data separately from other rows. In Db2 for i these are implemented using something we are all familiar with, members. This allows us, IBM i users, to use the Db2 view SYSPARTITIONSTAT to get information about members in the files and tables in our systems.

Where do we use members? The two scenarios that I am sure spring to all of our minds are in source files and in data files.

I often use SYSPARTITIONSTAT to search for source members that have the same name in multiple source files. This allows me to determine if a member name has already been used. Or if I have multiple versions of the same source member in different source files.

Using SQL to retrieve information about output queues

I have written so many posts there are times when I think I have written one about something useful, and I find to my surprise that I have not. I use the SQL view OUTPUT_QUEUE_INFO often, and I found that I have not written about it. This post makes amends for that oversight.

OUTPUT_QUEUE_INFO has been around since IBM i 7.2. It returns similar information to the Work Output Queue command, WRKOUTQ, whose output is familiar to everyone who uses IBM i.

Work with All Output Queues Type options, press Enter. 2=Change 3=Hold 4=Delete 5=Work with 6=Releas 9=Work with Writers 14=Clear Opt Queue Library Files Writer Status OUTPUT #SYSLOADX 0 RLS CGIDEV2 CGIDEV2 0 RLS DATAOUTQ DATALIB 0 RLS IASAUDIT IASUSR13 0 HLD

Taking one string and breaking it apart into five consecutive rows

The question was: How can I break apart a hundred long field into five twenty long fields and then have them returned, one after another, as separate result rows using SQL?

Obviously substring is going to be used, but how to return the resulting rows one after another?

Fortunately the easiest solution for this scenario came in earlier Technology Refreshes for the currently supported releases of IBM i, 7.4 TR4 and 7.3 TR10.

Before I show that I need to have a file with a field that is a hundred characters long:

01 A R TESTFILER 02 A FLD001 100A

Converting a spool file with bar code to PDF

I keep getting asked this question: If I generate a spool file with a bar code in it and I copy it to PDF will the bar code still display?

I wrote about copying spool files to PDF in the IFS in March 2014. What I am going to describe here is based on that post.

If the people who ask the question had read that post they would have discovered how simple this is.

Let me start with the externally described printer file, as I have not used Output specifications for prints in nigh on 30 years. The printer file's definition only needs two lines:

Adding 1 month to the end of the month and always get the end of the next month

I was asked how to make sure if I added 1 month to the last day of this month I would get the end of next month. If I take a date from a month and add one month to it I get the end of the next month.

In RPG if I add one month to January 31 I get February 28, the last day of the next month.

wkDate = d'2022-01-31' + %months(1) ; dsply wkDate ; DSPLY 2022-02-28

When I add one month to February 28 I do not get the last day of March, I get March 28.

wkDate = d'2022-02-28' + %months(1) ; dsply wkDate ; DSPLY 2022-03-28

Using SQL LIKE with a Subselect

This is the second part of a two part story. In the first part I described how I had downloaded a list of libraries from an IFS file to a DDL table just using SQL. In this part of the story I want to check any of job descriptions on this IBM i partitions contain one of these libraries.

In the "real world" there were 58 libraries. In this example I am only using five. To check what these libraries are called I can using the following SQL statement over the file I built in part one:

SELECT * FROM OLDLIBS

The libraries I will be using are: