Start of change

CERTIFICATE_INFO table function

The CERTIFICATE_INFO table function returns a result table that contains information about server or Certificate Authority (CA) certificates.

This information is similar to what is returned by the Retrieve Certificate Information (QYCURTVCI, QycuRetrieveCertificateInfo) API.

Authorization: The caller must provide the password for the certificate store. In addition, the caller must have *ALLOBJ and *SECADM special authorities.

Read syntax diagramSkip visual syntax diagram CERTIFICATE_INFO ( CERTIFICATE_STORE_PASSWORD => certificate-store-password,CERTIFICATE_STORE => certificate-store)
The schema is QSYS2.
certificate-store-password
An expression that contains the password for the specified certificate store. It is recommended that the password be passed as a variable, not as a string visible as clear text.
The following value can be used instead of specifying the password.
*NOPWD
The certificate store password will be retrieved from the stashed password file.
certificate-store
A character or graphic string expression that indicates the certificate store from which a list of certificates is to be retrieved. The value can either be a fully qualified Integrated File System (IFS) directory path and file name of the certificate store, starting with a leading forward slash (/), or one of the following special values. If the file name does not identify a certificate store, no rows are returned. If certificate-store is not specified, *SYSTEM is the default.
*OBJECTSIGNING
The *OBJECTSIGNING certificate store.
*SIGNATUREVERIFICATION
The *SIGNATUREVERIFICATION certificate store.
*SYSTEM
The *SYSTEM certificate store.

The result of the function is a table containing rows with the format shown in the following table. All the columns are nullable.

Table 1. CERTIFICATE_INFO table function
Column Name Data Type Description
CERTIFICATE_LABEL VARCHAR(256) The label for the certificate.
SERIAL_NUMBER VARCHAR(64) Serial number.
VALIDITY_START TIMESTAMP(0) The beginning date of the validity period.
VALIDITY_END TIMESTAMP(0) The ending date of the validity period.
TRUSTED VARCHAR(3) Indicates if the certificate is trusted.
NO
The certificate is not trusted.
YES
The certificate is trusted.
KEY_SIZE INTEGER The size of the key, in bytes.
PRIVATE_KEY VARCHAR(3) Indicates if the certificate has a private key.
NO
The certificate does not have a private key.
YES
The certificate has a private key.
PRIVATE_KEY_LABEL VARCHAR(64) The private key label.

Contains the null value if PRIVATE_KEY is NO or if PRIVATE_KEY_STORAGE_LOCATION is SOFTWARE.
PRIVATE_KEY_STORAGE_LOCATION VARCHAR(19) Where the key is stored.
HARDWARE
The key is stored in hardware.
HARDWARE ENCRYPTION
The key is stored in hardware encryption.
SOFTWARE
The key is stored is software.

Contains the null value if PRIVATE_KEY is NO.
DIGITAL_SIGNATURE VARCHAR(3) The certificate has the digital signature extension.
NO
The certificate does not have the digital signature extension.
YES
The certificate has the digital signature extension.
NONREPUDIATION VARCHAR(3) Indicates if the certificate has the nonrepudiation extension.
NO
The certificate does not have the nonrepudiation extension.
YES
The certificate has the nonrepudiation extension.
KEY_ENCIPHERMENT VARCHAR(3) Indicates if the certificate has the key encipherment extension.
NO
The certificate does not have the key encipherment extension.
YES
The certificate has the key encipherment extension.
DATA_ENCIPHERMENT VARCHAR(3) Indicates if the certificate has the data encipherment extension.
NO
The certificate does not have the data encipherment extension.
YES
The certificate has the data encipherment extension.
KEY_AGREEMENT VARCHAR(3) Indicates if the certificate has the key agreement extension
NO
The certificate does not have the key agreement extension.
YES
The certificate has the key agreement extension.
KEY_CERTIFICATE_SIGNATURE VARCHAR(3) Indicates if the certificate has the key certificate signature extension.
NO
The certificate does not have the key certificate signature extension.
YES
The certificate has the key certificate signature extension.
CRL_SIGNATURE VARCHAR(3) Indicates if the certificate has the Certificate Revocation List (CRL) signature extension.
NO
The certificate does not have the CRL signature extension.
YES
The certificate has the CRL signature extension.
CRL_LOCATION VARCHAR(50) The CRL location.

Contains the null value if no value is available.
ENCIPHER_ONLY VARCHAR(3) Indicates if the certificate has the encipher only extension.
NO
The certificate does not have the encipher only extension.
YES
The certificate has the encipher only extension.
DECIPHER_ONLY VARCHAR(3) Indicates if the certificate has the decipher only extension.
NO
The certificate does not have the decipher only extension.
YES
The certificate has the decipher only extension.
LDAP_SERVER_NAME VARCHAR(900) The LDAP server name.

Contains the null value if no value is available.
IP_ADDRESS_COUNT INTEGER The number of addresses in the IP_ADDRESSES column. Currently, only one IP address is returned.

Contains the null value if no IP addresses are available.
IP_ADDRESSES VARCHAR(45) The IP address.

Contains the null value if no value is available.
DOMAIN_NAME_COUNT INTEGER The number of domain names in the DOMAIN_NAMES column. Currently, only one domain name is returned.

Contains the null value if no domain names are available.
DOMAIN_NAMES VARCHAR(256) The domain name.

Contains the null value if no value is available.
EMAIL_ADDRESS VARCHAR(256) The email address.

Contains the null value if no value is available.
CRYPTOGRAPHIC_DEVICE_COUNT INTEGER The number of cryptographic devices.
CRYPTOGRAPHIC_DEVICES VARCHAR(109) A list of cryptographic device descriptions. Each entry is ten characters long. A single blank separates entries.

Contains the null value if CRYTOGRAPHIC_DEVICE_COUNT is 0.
SUBJECT_COMMON_NAME VARCHAR(256) The subject's common name. The SUBJECT set of columns define information about the end-entity that is being described for the certificate.

Contains the null value if no value is available.
SUBJECT_ORGANIZATIONAL_UNIT VARCHAR(256) The subject's organizational unit.

Contains the null value if no value is available.
SUBJECT_ORGANIZATION VARCHAR(256) The subject's organization.

Contains the null value if no value is available.
SUBJECT_LOCALITY VARCHAR(128) The subject's locality.

Contains the null value if no value is available.
SUBJECT_STATE_PROVINCE VARCHAR(128) The subject's state or province.

Contains the null value if no value is available.
SUBJECT_POSTAL_CODE VARCHAR(16) The subject's postal code.

Contains the null value if no value is available.
SUBJECT_COUNTRY_REGION VARCHAR(3) The subject's country or region.

Contains the null value if no value is available.
ISSUER_COMMON_NAME VARCHAR(256) The issuer's common name. The ISSUER set of columns define information about the Certificate Authority that signed the end-entity certificate.

Contains the null value if no value is available.
ISSUER_ORGANIZATIONAL_UNIT VARCHAR(256) The issuer's organizational unit.

Contains the null value if no value is available.
ISSUER_ORGANIZATION VARCHAR(256) The issuer's organization.

Contains the null value if no value is available.
ISSUER_LOCALITY VARCHAR(128) The issuer's locality.

Contains the null value if no value is available.
ISSUER_STATE_PROVINCE VARCHAR(128) The issuer's state or province.

Contains the null value if no value is available.
ISSUER_POSTAL_CODE VARCHAR(16) The issuer's postal code.

Contains the null value if no value is available.
ISSUER_COUNTRY_REGION VARCHAR(3) The issuer's country or region.

Contains the null value if no value is available.

Example

Retrieve all the certificates for the *SYSTEM certificate store that will be expired within the next month. Use a password that has been set in a global variable.
  • CREATE VARIABLE MYLIB.SYSTEM_CERT_PW VARCHAR(30);

SET MYLIB.SYSTEM_CERT_PW = 'cert_pwd';

SELECT * FROM TABLE(QSYS2.CERTIFICATE_INFO(CERTIFICATE_STORE_PASSWORD=> MYLIB.SYSTEM_CERT_PW))
   WHERE VALIDITY_END < CURRENT DATE + 1 MONTH;
End of change