Checking for expired certificates

IBM i uses certificates for various functions, and certificates will expire. I wanted to find a way where I could check the certificate store for any certificates that would be expiring soon. Whatever method I wanted needed to be simple so that I could move to other partitions too.

Fortunately, there is a SQL Table Function that will give me this information, CERTIFICATE_INFO. It has two parameters:

1. CERTIFICATE_STORE_PASSWORD:  Password for the certificate store. Rather than providing a password I can use the value '*NOPWD', which will retrieve the certificate password from the stashed password file. If a password is used it is recommended it is passed as a variable, so that people cannot see what it is.
2. CERTIFICATE_STORE:  The name of the certificate store. There are three special values that can be used as well:
   • *OBJECTSIGNING:  The object signing certificate store
   • *SIGNATUREVERIFICATION:  The signature verification certificate store
   • *SYSTEM:  The system certificate store. This is the default value the certificate store parameter is not used

To use this Table Function you must have *ALLOBJ and *SECADM authority.

Regular readers know that I always recommend if this is the first time you are using this Table Function you want to see all the columns. To do that I would use the following statement:

01 SELECT * 02 FROM TABLE(QSYS2.CERTIFICATE_INFO( 03 CERTIFICATE_STORE_PASSWORD => '*NOPWD'))

Line 3: '*NOPWD' will retrieve the certificate password.

As I have not given a certificate store the default value, '*SYSTEM', is used.

As I want to discover which certificates are going to expire in the, let us say, next six months I would only want to use a few of the available columns. My SQL Statement would be:

01 SELECT CERTIFICATE_LABEL, 02 VALIDITY_START, 03 VALIDITY_END, 04 ISSUER_ORGANIZATION 05 FROM TABLE(QSYS2.CERTIFICATE_INFO( 06 CERTIFICATE_STORE_PASSWORD => '*NOPWD')) 07 WHERE DATE(VALIDITY_END) BETWEEN CURRENT_DATE AND (CURRENT_DATE + 6 MONTHS) 08 ORDER BY VALIDITY_END

Line 1: CERTIFICATE_LABEL is the label for the certificate.

Line 2: VALIDITY_START the beginning date of the validity period.

Line 3: VALIDITY_END the ending date of the validity period.

Line 4: ISSUER_ORGANIZATION is the organization that issued the certificate.

Line 6: I am using the '*NOPWD' for the password.

Line 7: As the Validity End is a timestamp I am using the DATE scalar function to convert it to a date value. Then I want to select all certificates that expire in any date that is in the range of today to six months in the future.

Line 8: I want my results sorted by the date the certificates expire.

The results are:

CERTIFICATE_LABEL VALIDITY_START VALIDITY_END ISSUER_ORGANIZATION ----------------------- ------------------- ------------------- ---------------------------- QIBM_HTTP_SERVER_ADMIN3 2024-09-12 16:50:52 2025-09-13 16:50:52 IBM Web Administration for i Amazon S3 2015-10-21 20:00:00 2025-10-18 20:00:00 Amazon splat.frankeni.com_2025 2025-01-31 19:00:00 2026-03-04 18:59:59 DigiCert Inc

If I wanted to hide the password I would place it in a SQL global variable:

01 CREATE OR REPLACE VARIABLE MYLIB.GBLVAR01 02 VARCHAR(1024) ; 03 SET MYLIB.GBLVAR01 = '*NOPWD' ; 04 VALUES MYLIB.GBLVAR01 ;

Lines 1 and 2: I created the global variable, deliberately using a vague name for it. The certificate store password can be up to 1,024 characters. As I have not given it a default value, its default is null.

Line 3: I used the Set statement to update the global variable with the value '*NOPWD'.

Line 4: I use the Values statement to display the contents of the global variable:

00001 -------- *NOPWD

I can change my previous statement using CERTIFICATE_INFO to use the global variable for the password:

01 SELECT CERTIFICATE_LABEL, 02 VALIDITY_START, 03 VALIDITY_END, 04 ISSUER_COMMON_NAME, 05 ISSUER_ORGANIZATION 06 FROM TABLE(QSYS2.CERTIFICATE_INFO( 07 CERTIFICATE_STORE_PASSWORD => MYLIB.GBLVAR01)) 08 WHERE DATE(VALIDITY_END) BETWEEN CURRENT_DATE AND (CURRENT_DATE + 6 MONTHS) 09 ORDER BY VALIDITY_END

Line 7: I use the global variable for the password.

The results are the same as I showed before.

If I wanted to see the object signing certificates information I would need to add the CERTIFICATE_STORE parameter:

01 SELECT CERTIFICATE_LABEL, 02 VALIDITY_START, 03 VALIDITY_END, 04 ISSUER_COMMON_NAME, 05 ISSUER_ORGANIZATION 06 FROM TABLE(QSYS2.CERTIFICATE_INFO( 07 CERTIFICATE_STORE_PASSWORD => MYLIB.GBLVAR01 08 CERTIFICATE_STORE => '*OBJECTSIGNING' 09 )) 10 WHERE DATE(VALIDITY_END) BETWEEN CURRENT_DATE AND (CURRENT_DATE + 1 YEAR) 11 ORDER BY VALIDITY_END

Line 8: I added the certificate store parameter for the object signing certificates.

Which returns:

CERTIFICATE_LABEL VALIDITY_START VALIDITY_END ISSUER_ORGANIZATION ----------------------- ------------------- ------------------- ---------------------------- SRIEDMUE_2025 2025-08-18 14:24:02 2026-08-19 14:24:02 IBM Web Administration for i

Finally I want to check the signature verification certificates:

01 SELECT CERTIFICATE_LABEL, 02 VALIDITY_START, 03 VALIDITY_END, 04 ISSUER_COMMON_NAME, 05 ISSUER_ORGANIZATION 06 FROM TABLE(QSYS2.CERTIFICATE_INFO( 07 CERTIFICATE_STORE_PASSWORD => MYLIB.GBLVAR01 08 CERTIFICATE_STORE => '*SIGNATUREVERIFICATION' 09 )) 10 WHERE DATE(VALIDITY_END) BETWEEN CURRENT_DATE AND (CURRENT_DATE + 1 YEAR) 11 ORDER BY VALIDITY_END

When I executed this statement I received an error informing me that this certificate store does not exist.

SQL State: 42704 Vendor Code: -443 Message: [SQL0443] CERTIFICATE STORE NOT FOUND

I created a SQL View to display all this information in one set of results:

01 CREATE OR REPLACE VIEW MYLIB.CERTIFICATION_EXPIRATION 02 FOR SYSTEM NAME "CERTEXPIR" AS 03 (SELECT CERTIFICATE_LABEL, 04 VALIDITY_START, 05 VALIDITY_END, 06 ISSUER_ORGANIZATION 07 FROM TABLE(QSYS2.CERTIFICATE_INFO( 08 CERTIFICATE_STORE_PASSWORD => '*NOPWD', 09 CERTIFICATE_STORE => '*SYSTEM')) 10 WHERE DATE(VALIDITY_END) BETWEEN CURRENT_DATE AND (CURRENT_DATE + 6 MONTHS) 11 UNION ALL 12 SELECT CERTIFICATE_LABEL, 13 VALIDITY_START, 14 VALIDITY_END, 15 ISSUER_ORGANIZATION 16 FROM TABLE(QSYS2.CERTIFICATE_INFO( 17 CERTIFICATE_STORE_PASSWORD => '*NOPWD', 18 CERTIFICATE_STORE => '*OBJECTSIGNING')) 19 WHERE DATE(VALIDITY_END) BETWEEN CURRENT_DATE AND (CURRENT_DATE + 1 YEAR) ;

Line 1: I am giving this View a long SQL name.

Line 2: I need to give this a short system name too.

Lines 3 - 10: The statement for the system certificates.

Line 11: Using the UNION ALL I combine the results from the above SQL statement with what follows.

Lines 12 - 20: The statement for the object signing certificates.

After executing this statement the View is created. I can then use the following SQL statement to see which certificates expire soon:

01 SELECT * 02 FROM CERTIFICATION_EXPIRATION 03 ORDER BY VALIDITY_END

Which returns:

CERTIFICATE_LABEL VALIDITY_START VALIDITY_END ISSUER_ORGANIZATION ----------------------- ------------------- ------------------- ---------------------------- QIBM_HTTP_SERVER_ADMIN3 2024-09-12 16:50:52 2025-09-13 16:50:52 IBM Web Administration for i Amazon S3 2015-10-21 20:00:00 2025-10-18 20:00:00 Amazon splat.frankeni.com_2025 2025-01-31 19:00:00 2026-03-04 18:59:59 DigiCert Inc SRIEDMUE_2025 2025-08-18 14:24:02 2026-08-19 14:24:02 IBM Web Administration for i

SQL to rescue, again, to give me what I want with a simple SQL statement.

 

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