Checking Software License expiration dates using SQL

Included in the latest Technical Refreshes for IBM i 7.3 and 7.2 is a SQL View and Procedure to help us monitor the licenses of the software on our IBM i.

IBM uses Software License Keys with certain IBM i products to ensure that I have paid the appropriate license fees to use these products on an IBM i. I know of several third party IBM i software vendors who also use this method to stop the unauthorized copying of their software to unlicensed servers. It is all standard stuff to prevent the distribution of unauthorized software.

I have always been able to see the license information on the IBM i I am on by using the Display License Key Info command, DSPLICKEY. If I wanted to see which licensed products were installed I could just use the DSPLICKEY to produce an output file I could then view with Query or SQL.

DSPLICKEY OUTPUT(*LICKEYFILE) LICKEYFILE(QTEMP/@LICKEY)

If I use any licensed products I need to be aware of when the license expires. I would always use DSPLICKEY to create the output file, that I would then check the license expiration dates to ensure I renewed any licenses before they expire.

The new View LICENSE_INFO, in library QSYS2, allows me to perform this check in one step, just with a simple SQL statement. This View contains more fields than I would use. I am only going to describe the ones I use, and refer you to the IBM documentation, whose link I give at the bottom of this post. I use:

Short column name	Description
INSTALLED	Indicates if the feature is installed or not
EXPIR_DATE	Date the license will expire If null there is no expiration date
GRACE_PRD	Date the grace periods expires If null there is no grace period or the grace period has passed
LICPGM	Product identifier
LIC_TERM	Indicates whether the authorization exists until the next version, release, or modification level
RLS_LVL	Version, release, and modification of the product
FEATURE	Feature number of the product
PROC_GROUP	Processor group of this IBM i
LABEL	Product description

If I put that in a SQL select statement I can see all licensed products with their expiration dates.

SELECT INSTALLED,EXPIR_DATE AS EXPIRES,GRACE_PRD AS GRACE, LICPGM,LIC_TERM,RLS_LVL,FEATURE,PROC_GROUP AS PROC_GRP, CAST(LABEL AS VARCHAR(100) CCSID 37) AS DESCRIPTION FROM QSYS2.LICENSE_INFO

I renamed some of the columns, using the AS, to reduce the "width" of the results shown below. I have to use a CAST with the LABEL column as it is in CCSID 1200, and the IBM i I am using uses CCSID 37.

INSTALLED EXPIRES GRACE LICPGM LIC_TERM RLS_LVL FEATURE YES - 70 5770SS1 V7R3M0 V7R3M0 5050 NO - 0 5770SS1 V7 V7R3M0 5051 NO - 0 5770SS1 V7 V7R3M0 5052 YES - 70 5770SS1 V7R3M0 V7R3M0 5103 YES - 70 5770SS1 V7R3M0 V7R3M0 5112 PROC_GRP DESCRIPTION P05 IBM i P05 IBM i P05 IBM i P05 Media and Storage Extensions P05 PSF for IBM i 1-55 IPM Printer Support

As the example results above have an expiration date, column EXPIRES, of null these products do not expire. But what about any products I have that will expire in the next year?

I can modify the SQL Select I used above to become to:

SELECT INSTALLED,EXPIR_DATE AS EXPIRES,GRACE_PRD AS GRACE, LICPGM,LIC_TERM,RLS_LVL,FEATURE,PROC_GROUP AS PROC_GRP, CAST(LABEL AS VARCHAR(100) CCSID 37) AS DESCRIPTION FROM QSYS2.LICENSE_INFO WHERE EXPIR_DATE BETWEEN (CURRENT_DATE - 30 DAYS) AND (CURRENT_DATE + 1 YEAR)

The WHERE clause with a BETWEEN allows me to select all products that have expired in the last 30 days and will expire in the next year.

INSTALLED EXPIRES GRACE LICPGM LIC_TERM RLS_LVL FEATURE NO 09/30/2017 0 9999XXX V1 V1R0M0 9999 PROC_GRP DESCRIPTION P05 Added for example

I can create a job that will take this output, format it into an email, using the SNDSMTPEMM command, and send it to me. This way I will be forewarned and avoid having products expiring.

IBM does provide a procedure, LICENSE_EXPIRATION_CHECK, to send messages to the QSYSOPR message queue for every installed product that will expire in the number of days passed:

CALL SYSTOOLS.LICENSE_EXPIRATION_CHECK(365)

Notice that I said "installed product" this means that there has to be a value of YES in the installed column. My example product does not get picked up by this procedure as it is not "installed", i.e. it has a value of NO.

 

I have Vikas Anand to thank for sending me a copy of the email produced by the LICENSE_EXPIRATION_CHECK procedure.

Additional Message Information Message ID . . . . : CPI9E75 Severity . . . . . : 00 Message type . . . : Information Date sent . . . . : DD/DD/DD Time sent . . . . : HH:HH:HH Message . . . . : Grace period expires in 37 days on 08/09/17. Cause . . . . . : You received this message for one of the following reasons: -- The grace period for product 5770ST1, license term , feature 5050, expires in 37 days on 08/09/17. When the grace period expires, all users exceeding the usage limit are released, starting from the most recently added user, until the number of uses no longer exceeds the usage limit. -- The evaluation period for product 5770ST1, license term , feature 5050, expires in 37 days on 08/09/17. Recovery . . . : To be prepared before the day that the grace period expires, contact your software provider to assist you in receiving a new license key.

You can learn more about this from the IBM website:

• LICENSE_INFO View
• LICENSE_EXPIRATION_CHECK Procedure

 

This article was written for IBM i 7.3 TR2, and should work for 7.2 TR6.