Pages

Tuesday, January 5, 2021

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.

1 comment:

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.