Wednesday, August 2, 2017

Checking Software License expiration dates using SQL

checking softwatre 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:

 

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

2 comments:

  1. I love that SQL access IBM is allowing, this should feed into AI control....doh

    ReplyDelete

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.