IBM i administrators can now use powerful SQL services to easily view, track, and export software license information—far beyond what traditional CL commands like WRKLICINF provide. By querying the QSYS2.LICENSE_INFO view, you can quickly identify expiration dates, grace periods, and upcoming renewals, helping prevent unexpected outages.
IBM has been leveraging SQL services to gain access to various system functions for several versions now. Often, these services give you access to system administration functions that do not exist in CL commands. For instance, there are many CL commands to view information, but those often only offer a print option to save data for comparison or review later. But there is often an IBM I service offering that will let you run an SQL statement and get that same data into a file or exportable format.
These SQL services are being added to, or enhanced, all the time with the DB2 PTF groups and the Technology Refresh (TR) updates.
https://www.ibm.com/support/pages/ibm-i-services-sql
This series will give a tech note on utilizing some of these IBM I services as a systems administrator.
Today’s topic is about checking your IBM software license information
In the past, IBM used perpetual licensing on the IBM I platform. Once you purchased the operating system and licensed products, those licenses lasted for the life of the system. In recent years however, IBM has switched licenses over to subscription based terms. This means that you now have to keep track of when your license keys expire on the system to prevent any potential outages.
The IBM I services (via SQL) give you a tool to check on those licenses. There is a view in QSYS2 library that will show you the products with licenses that exist on the system, as well some varied information. This is similar to issuing a WRKLICINF command on the command line – but with an advantage of seeing all the products at once. Along with that, you can export the licenses to table file as well if you like (versus just a spool file using WRKLICINF).
In this first example, we are using ‘Run SQL Scripts’ to look at the licenses on the system. You will note that like WRKLICINF, you can see some non-IBM licenses listed at times. This depends on whether the software adopted IBM’s license key methodology, and will vary by the vendor.
In the example above, you will note the highlighted sections similar to using the WRKLICINF command.
To get the most basic of license information, you can invoke this SQL statement:
SELECT *
FROM QSYS2.LICENSE_INFO;
This statement gives you all the license information on your partition, with all of the columns available being listed in the output. Sometimes, that is a little more than you might want to scroll through, however. So, let’s take a look at picking some of the columns that might be important to review.
Taking the statement above, we are going to pick some columns that might mean the most to us when reviewing licenses. Using ‘Run SQL Scripts’, we are going to place our cursor on the * (splat) symbol of the statement, and hit F4. This gives us a selection of the fields that exist within the QSYS2.LICENSE_INFO view.
Notice the highlighted section and the F11 function option. That will give you the system naming for those columns (fields) as well.
Now, let’s pick some interesting columns that will help us to track our licenses. In the next example, I want to pick a few that narrow my view on seeing the product, some information about the product, and any potential license expirations for that product. You can achieve these selections by clicking them in the prompt box (while holding the CTRL key) and then hitting Enter. This will update your statement to include those fields instead of all the fields.
Now we get a better understanding of any licenses that are going to expire or are possibly running on a grace period. The highlighted columns indicate the expiration and grace period information below:
Let’s take another look at the license info, focusing on a review for any licenses that might be expiring soon. We can write an SQL statement that will look for any licenses expiring in the next 30, 60, or 90 days.
To accomplish this, we are going to keep our last SQL statement with the reduced view – and add a WHERE clause for comparison.
SELECT PRODUCT_ID,
LICENSE_TERM,
RELEASE_LEVEL,
FEATURE_ID,
INSTALLED,
USAGE_LIMIT,
USAGE_TYPE,
LICENSE_EXPIRATION
FROM QSYS2.LICENSE_INFO
WHERE LICENSE_EXPIRATION <= CURRENT DATE + 30 DAYS;
In this case, our result set comes up empty because we do not have any licenses expiring in the next 30 days.
But if I extend the term out to 1000 days, we will see some of the Rational Development Studio licenses will expire in 2027.
Here is a different iteration of that statement that will tell you how long you have until licenses expire. In this case, we have over 600 days remaining:
SELECT PRODUCT_ID,
LICENSE_TERM,
RELEASE_LEVEL,
FEATURE_ID,
INSTALLED,
USAGE_LIMIT,
USAGE_TYPE,
LICENSE_EXPIRATION,
DAYS(LICENSE_EXPIRATION) - DAYS(CURRENT_DATE) AS DAYS_REMAINING
FROM QSYS2.LICENSE_INFO
WHERE LICENSE_EXPIRATION IS NOT NULL;
The above statement limits your statement to looking only for products that have an expiration date and calculates the remaining days of the license from the current date when the statement is executed.
Knowing when your licenses are going to expire is a pretty handy bit of information!
Extra Tip:
The system I am testing with allows me some limited permissions, so I cannot show this section as a demo. However, I will borrow some information from Ann Wilkerson from IBM on this section.
There are some other services in SYSTOOLS that were introduced with a database group in version 7.4. One of those is called SYSTOOLS.GENERATE_SPREADSHEET. This service does require the CLDownload feature of the IBM I Access Client Solutions (ACS) jar that ships with every IBM i. The jar file is delivered with an HTTP Group PTF, and is found at this path: /QIBM/proddata/Access/ACS/Base/acsbundle.jar
Using GENERATE_SPREADSHEET, you can place a file into an IFS path that contains the contents of a query result. In this example from Ann, you can generate your license information into that file:
VALUES
SYSTOOLS.GENERATE_SPREADSHEET(
PATH_NAME => '/home/your_directory/license_info',
SPREADSHEET_QUERY => 'SELECT*FROMQSYS2.LICENSE_INFO',
COLUMN_HEADINGS => 'COLUMN',
SPREADSHEET_TYPE => 'xlsx');
This generates an XLXS file into the specified path that contains the contents of the SELECT statement. You can expand that SELECT statement with specific columns or with a where clause to narrow down the information as well.
Next step on that path, send it to yourself via email. Introduced in 7.4 at the same time as the GENERATE_SPREADSHEET tool, is the SYSTOOLS.SEND_EMAIL function as well. This allows you to send an email from your system via an SQL function statement. Another example I borrowed from Ann:
VALUES
SYSTOOLS.SEND_EMAIL(
TO_EMAIL => ‘your@email.com’,
SUBJECT => 'License Info Report',
BODY => 'See Attached',
ATTACHMENT => '/home/your_directory/license_info.xlsx');
Now, imagine a simple series of RUNSQL statements tied together for a monthly job that sends you an email with license expiration details.