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.
- 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.
- 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.
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.
|
KEY_SIZE | INTEGER | The size of the key, in bytes. |
PRIVATE_KEY | VARCHAR(3) | Indicates if 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.
Contains the null value if PRIVATE_KEY is NO. |
DIGITAL_SIGNATURE | VARCHAR(3) | The certificate has the digital signature extension.
|
NONREPUDIATION | VARCHAR(3) | Indicates if the certificate has the nonrepudiation extension.
|
KEY_ENCIPHERMENT | VARCHAR(3) | Indicates if the certificate has the key encipherment extension.
|
DATA_ENCIPHERMENT | VARCHAR(3) | Indicates if the certificate has the data encipherment extension.
|
KEY_AGREEMENT | VARCHAR(3) | Indicates if the certificate has the key agreement extension
|
KEY_CERTIFICATE_SIGNATURE | VARCHAR(3) | Indicates if the certificate has the key certificate signature extension.
|
CRL_SIGNATURE | VARCHAR(3) | Indicates if the certificate has the Certificate Revocation List
(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.
|
DECIPHER_ONLY | VARCHAR(3) | Indicates if 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;