Getting partition, database names, release and TR in one SQL statement

I am often asked how is it possible to find the IBM i release and Technology Refresh level on a partition. In previous posts I have given ways to display the release, which PTFs are loaded, and the partition name. So I thought it was time I rolled this all into one SQL statement, from which I would get:

1. Partition name
2. Database name
3. Release
4. Technology Refresh number

I can hear a number of you say: "Isn't the partition and database name the same?"

With the vast majority of IBM i and earlier models I have used when the partition was installed the database name was changed to be the same as the partition name. But there are occasions they are not, or it was given a different name. One of the IBM i partitions I use to write these posts has a different database name, and so do two of the four partitions at my work. This post will use RZKH's DEV730, which is the one where the partition and database names are different.

The database name can be retrieved using the special register CURRENT_SERVER, which is the current database server name. If I am going to use a three part name to connect to another partition I would need to use the database name.

SELECT CURRENT_SERVER FROM SYSIBM.SYSDUMMY1

I get DEV730 database's name:

0001 -------- G102A08R

Where can I get the partition name from?

Alas, it is not a special register. I need to perform a SQL statement to retrieve it from an IBM table:

SELECT DOSNAM FROM QPFRDATA.QAPMHDWR LIMIT 1

I use the LIMIT 1 as I only one row returned in the results as the partition name is in every row in this table.

DOSNAM -------- DEV730

The release and Technology Refresh I get from the SQL View GROUP_PTF_INFO. This has a row for each PTF that has been downloaded into the partition.

Fortunately I can find the PTFs for Technology Refreshes from the PTF group description column, PTF_GROUP_DESCRIPTION, when it is "TECHNOLOGY REFRESH".

The PTF group level column, PTF_GROUP_LEVEL, gives me the Technology Refresh number.

As a PTF is not active until it is applied, I need to check that the PTF group status column, PTF_GROUP_STATUS, if it contains "INSTALLED".

Then I need to sort the results by the PTF group level column, PTF_GROUP_LEVEL, in descending order as the most recent PTF group has the greater name.

And I only need one row from the results, LIMIT 1.

From those requirements I build the following SQL Select statement:

SELECT PTF_GROUP_TARGET_RELEASE, PTF_GROUP_LEVEL FROM QSYS2.GROUP_PTF_INFO WHERE PTF_GROUP_DESCRIPTION = 'TECHNOLOGY REFRESH' AND PTF_GROUP_STATUS = 'INSTALLED' ORDER BY PTF_GROUP_LEVEL DESC LIMIT 1

Which gives me:

PTF_GROUP_TARGET_RELEASE PTF_GROUP_LEVEL ------------------------ --------------- V7R3M0 8

Now to combine the information for the Partition and Database names into the SQL statement.

01 SELECT 02 (SELECT DOSNAM FROM QPFRDATA.QAPMHDWR LIMIT 1) AS "Partition name", 03 CURRENT_SERVER AS "Database name", 04 PTF_GROUP_TARGET_RELEASE AS "Release", 05 PTF_GROUP_LEVEL AS "TR" 06 FROM QSYS2.GROUP_PTF_INFO 07 WHERE PTF_GROUP_DESCRIPTION = 'TECHNOLOGY REFRESH' 08 AND PTF_GROUP_STATUS = 'INSTALLED' 09 ORDER BY PTF_GROUP_LEVEL DESC 10 LIMIT 1

Line 2: Is a subselect statement, it is a select statement that runs inside another.

Line 3: The special register to get the database name.

Lines 4 – 10: The rest of the statement is basically the same as the previous one, getting the results for the latest Technology Refresh group PTF.

The results are what I wanted:

Partition name Database name Release TR -------------- ------------- ------- -- DEV730 G102A08R V7R3M0 8

I ran the same statement on the other two of RZKH's partitions I have access to for their results:

Partition name Database name Release TR -------------- ------------- ------- -- DEV740 DEV740 V7R4M0 3 Partition name Database name Release TR -------------- ------------- ------- -- DEV720 DEV720 V7R2M0 7

 

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