It's a luggable, huggable AS/400!

Opposite my office at work is a closet. I finally was given a key to it yesterday. I found that it is full of many, many old things: documentation for software my employer no longer uses, tapes of various types and size, CDs, floppy disks, what looks like many lengths of twinax cable, and one bag with an IBM logo on it.

I am a curious person so I took the bag from the closet into my office, wondering what this bag could contain.

Deleting rows with SQL Merge statement

Someone pointed out to me last week that the SQL Merge statement can delete rows from the table too. This struck me as bizarre as in my humble opinion a merge should just be the equivalent of insert and update. When I checked the documentation there it was, delete in the merge.

I have written about the update and insert of data into a table using the Merge in a previous post. I will be using the same files and tables in these examples, therefore, if you want to know more information about them please go to that article here.

I am going to start after the data from FILE1 has been inserted into TABLE1, using an Insert statement. I can view the contents of the table with this simple Select statement:

01 SELECT * FROM TABLE1

When was the last IPL performed?

One of the IBM i operators and I were talking about the contents on one of the little use Power server partitions and the following question came up: When was the last time an IPL was performed on this partition?

I know I could find that information in Service Tools. But how would I do so if I am not authorized to use the Service Tools menu?

Fortunately SQL comes to my rescue.

When an IPL starts a job with the following job number is always the first to start: 000000/QSYS/SCPF

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: