CERTIFICATE_USAGE_INFO view

The CERTIFICATE_USAGE_INFO view returns information about registered applications that use certificates and the associated certificate information.

The information returned is similar to the detail available through the Retrieve Certificate Usage Information (QYCDRCUI, QycdRetrieveCertUsageInfo) API.

This view only returns data when Option 34 (Digital Certificate Manager) of the IBM i operating system is installed.

Authorization: The caller must have *ALLOBJ and *SECADM special authorities.

The following table describes the columns in the view. The system name is CERT_USAGE. The schema is QSYS2.

Table 1. CERTIFICATE_USAGE_INFO view
Column Name	System Column Name	Data Type	Description
DESCRIPTION	APP_DESCR	VARCHAR(50) Nullable	The description for the application. This is the value of MESSAGE_TEXT. If no value is available for MESSAGE_TEXT, contains the first 50 characters of TEXT_DESCRIPTION. Contains the null value if no text description is available.
APPLICATION_ID	APP_ID	VARCHAR(100)	The ID of the application.
APPLICATION_TYPE	APP_TYPE	VARCHAR(14)	The type of application. CLIENT Client application. OBJECT SIGNING Object signing application. SERVER Server application.
CERTIFICATE_STORE	CERT_STORE	VARCHAR(1024) CCSID 1208 Nullable	The path name where the certificate is stored. Can contain the following special values: SYSTEM The certificate is stored in the system certificate store. OBJECTSIGNING The certificate is stored in the object signing certificate store. Contains the null value if no certificates are assigned to this application.
CERTIFICATE_LABEL_COUNT	CERT_CNT	INTEGER	The number of certificate labels in CERTIFICATE_LABELS. Up to four can be returned.
CERTIFICATE_LABELS	CERT_LABEL	VARCHAR(1027) CCSID 1208 Nullable	A list of certificate labels. This list is returned as an array within a JSON object. The array is identified by CERT_LABELS. Each entry in the JSON array identifies a certificate label. Contains the null value if CERTIFICATE_LABEL_COUNT is 0.
MANAGED_CA_TRUSTS	CA_TRUSTS	VARCHAR(3)	Whether the application wants the system to manage the list of CA certificates the application trusts. NO The application will manage the list of CA certificates it trusts. YES The system will manage the list of CA certificates trusted by the application. The application determines which CA certificates are in the list.
TLS_PROTOCOL_COUNT	PROTO_CNT	INTEGER	The number of entries in the TLS_PROTOCOLS list.
TLS_PROTOCOLS	PROTOCOLS	VARCHAR(142) CCSID 1208 Nullable	The TLS protocol versions supported by this application. This list is returned as an array within a JSON object. The array is identified by PROTOCOLS. Each entry in the JSON array identifies a TLS protocol version. Up to 10 values can be returned in the list. *PGM Use the runtime value that was set by the underlying application and its configuration, do not override. SSLV3 Secure Sockets Layer version 3.0 is supported. TLSV1.0 Transport Layer Security version 1.0 is supported. TLSV1.1 Transport Layer Security version 1.1 is supported. TLSV1.2 Transport Layer Security version 1.2 is supported. TLSV1.3 Transport Layer Security version 1.3 is supported. Contains the null value if TLS_PROTOCOL_COUNT is 0.
TLS_CIPHER_COUNT	CIPHER_CNT	INTEGER	The number of entries in the TLS_CIPHERS list.
TLS_CIPHERS	CIPHERS	VARCHAR(2592) CCSID 1208 Nullable	The list of cipher suites that are supported by this application. This list is returned as an array within a JSON object. The array is identified by CIPHERS. Up to 64 values can be returned in the list. PGM Use the runtime value that was set by the underlying application and its configuration, do not override. AES_128_GCM_SHA256* Use Advanced Encryption Standard (AES) cipher with Galois/Counter Mode (GCM) and 128 bit keys. Use Secure Hash Algorithm 256 (SHA256) for generating the message authentication code (MAC). AES_256_GCM_SHA384 Use AES cipher with GCM and 256 bit keys. Use SHA384 for generating the MAC. CHACHA20_POLY1305_SHA256 Use the ChaCha stream cipher with 20 rounds, 96-bit nonce, and 256 bit keys with Poly1305 authenticator. Use SHA256 for generating the MAC. ECDHE_ECDSA_AES_128_CBC_SHA256 Use the ECDHE key exchange algorithm with the ECDSA public key algorithm with the AES cipher with CBC and 128 bit keys. Use SHA256 for generating the MAC. ECDHE_ECDSA_AES_128_GCM_SHA256 Use the Elliptic Curve Diffie-Hellman Ephemeral (ECDHE) key exchange algorithm with the Elliptic Curve Digital Signature Algorithm (ECDSA) with the AES cipher with GCM and 128 bit keys. Use SHA256 for generating the MAC. ECDHE_ECDSA_AES_256_CBC_SHA384 Use the ECDHE key exchange algorithm with the ECDSA public key algorithm with the AES cipher with CBC and 256 bit keys. Use SHA384 for generating the MAC. ECDHE_ECDSA_AES_256_GCM_SHA384 Use the ECDHE key exchange algorithm with the ECDSA signature algorithm with the AES cipher with GCM and 256 bit keys. Use SHA384 for generating the MAC. ECDHE_ECDSA_CHACHA20_POLY1305_SHA256 Use the ECDHE key exchange algorithm with the ECDSA public key algorithm with the ChaCha stream cipher with 20 rounds, 96-bit nonce, and 256 bit keys with Poly1305 authenticator. Use SHA256 for generating the MAC. ECDHE_ECDSA_NULL_SHA Use the Elliptic Curve Diffie-Hellman Ephemeral (ECDHE) key exchange algorithm with the ECDSA signature algorithm but do not use any cipher. Use the Secure Hash Algorithm 1 (SHA-1) for generating the MAC. ECDHE_ECDSA_RC4_128_SHA Use the ECDHE key exchange algorithm with the ECDSA public key algorithm with the Rivest Cipher 4 (RC4) cipher and 128 bit keys. Use SHA-1 for generating the MAC. ECDHE_ECDSA_3DES_EDE_CBC_SHA Use the ECDHE key exchange algorithm with the ECDSA public key algorithm with the Triple Data Encryption Standard (3DES) cipher with the encrypt/decrypt/encrypt (EDE) and CBC modes and 168 bit keys. Use SHA-1 for generating the MAC.
TLS_CIPHERS (continued)			ECDHE_RSA_AES_128_CBC_SHA256 Use the ECDHE key exchange algorithm with the RSA public key algorithm with the AES cipher with CBC and 128 bit keys. Use SHA256 for generating the MAC. ECDHE_RSA_AES_128_GCM_SHA256 Use the ECDHE key exchange algorithm with the Rivest Shamir Adleman (RSA) public key algorithm with the AES cipher with GCM and 128 bit keys. Use SHA256 for generating the MAC. ECDHE_RSA_AES_256_CBC_SHA384 Use the ECDHE key exchange algorithm with the RSA public key algorithm with the AES cipher with CBC and 256 bit keys. Use SHA384 for generating the MAC. ECDHE_RSA_AES_256_GCM_SHA384 Use the ECDHE key exchange algorithm with the RSA public key algorithm with the AES cipher with GCM and 256 bit keys. Use SHA384 for generating the MAC. ECDHE_RSA_CHACHA20_POLY1305_SHA256 Use the ECDHE key exchange algorithm with the RSA public key algorithm with the ChaCha stream cipher with 20 rounds, 96-bit nonce, and 256 bit keys with Poly1305 authenticator. Use SHA256 for generating the MAC. ECDHE_RSA_NULL_SHA Use the ECDHE key exchange algorithm with the RSA public key algorithm but do not use any cipher. Use SHA-1 for generating the MAC. ECDHE_RSA_RC4_128_SHA Use the ECDHE key exchange algorithm with the RSA public key algorithm with the RC4 cipher and 128 bit keys. Use SHA-1 for generating the MAC. ECDHE_RSA_3DES_EDE_CBC_SHA Use the ECDHE key exchange algorithm with the RSA public key algorithm with the 3DES cipher with the EDE and CBC modes and 168 bit keys. Use SHA-1 for generating the MAC. RSA_AES_128_CBC_SHA Use the RSA public key algorithm with the AES cipher with CBC and 128 bit keys. Use SHA-1 for generating the MAC. RSA_AES_128_CBC_SHA256 Use the RSA public key algorithm with the AES cipher with CBC and 128 bit keys. Use SHA256 for generating the MAC. RSA_AES_128_GCM_SHA256 Use the RSA public key algorithm with the AES cipher with GCM and 128 bit keys. Use SHA256 for generating the MAC. RSA_AES_256_CBC_SHA Use the RSA public key algorithm with the AES cipher with CBC and 256 bit keys. Use SHA-1 for generating the MAC. RSA_AES_256_CBC_SHA256 Use the RSA public key algorithm with the AES cipher with CBC and 256 bit keys. Use SHA256 for generating the MAC.
TLS_CIPHERS (continued)			RSA_AES_256_GCM_SHA384 Use the RSA public key algorithm with the AES cipher with GCM and 256 bit keys. Use SHA384 for generating the MAC. RSA_DES_CBC_SHA Use the RSA public key algorithm with the Data Encryption Standard (DES) cipher with CBC mode and 56 bit keys. Use SHA-1 for generating the MAC. RSA_EXPORT_RC2_CBC_40_MD5 Use the RSA public key algorithm with the Rivest Cipher 2 (RC2) cipher with CBC mode and 40 bit keys. Use MD5 for generating the MAC. RSA_EXPORT_RC4_40_MD5 Use the RSA public key algorithm with the RC4 cipher and 40 bit keys. Use MD5 for generating the MAC. RSA_NULL_MD5 Use the RSA public key algorithm but do not use any cipher. Use MD5 for generating the MAC. RSA_NULL_SHA Use the RSA public key algorithm but do not use any cipher. Use SHA-1 for generating the MAC. RSA_NULL_SHA256 Use the RSA public key algorithm but do not use any cipher. Use SHA256 for generating the MAC. RSA_RC4_128_MD5 Use the RSA public key algorithm with the RC4 cipher and 128 bit keys. Use message digest algorithm 5 (MD5) for generating the MAC. RSA_RC4_128_SHA Use the RSA public key algorithm with the RC4 cipher and 128 bit keys. Use SHA-1 for generating the MAC. RSA_3DES_EDE_CBC_SHA Use the RSA public key algorithm with the 3DES cipher with the EDE and CBC modes and 168 bit keys. Use SHA-1 for generating the MAC.
TLS_KEY_EXCHANGE_ALGORITHM_COUNT	KEY_CNT	INTEGER	The number of entries in the TLS_KEY_EXCHANGE_ALGORITHMS list.
TLS_KEY_EXCHANGE_ALGORITHMS	KEY_ALGS	VARCHAR(608) CCSID 1208 Nullable	The TLS signature algorithms for key exchange supported by this application. This list only has meaning when a TLS version 1.2 or newer protocol is negotiated. This list is returned as an array within a JSON object. The array is identified by KEY_ALGS. Up to 32 values can be returned in the list. *PGM Use the runtime value that was set by the underlying application and its configuration, do not override. ECDSA_SHA1 ECDSA with SHA1 ECDSA_SHA224 ECDSA with SHA224 ECDSA_SHA256 ECDSA with SHA256 ECDSA_SHA384 ECDSA with SHA384 ECDSA_SHA512 ECDSA with SHA512 ED25519 ED25519 RSA_PSS_SHA256 RSA_PSS_SHA256 RSA_PSS_SHA384 RSA_PSS_SHA384 RSA_PSS_SHA512 RSA_PSS_SHA512 RSA_SHA1 RSA with SHA1 RSA_SHA224 RSA with SHA224 RSA_SHA256 RSA with SHA256 RSA_SHA384 RSA with SHA384 RSA_SHA512 RSA with SHA512 Contains the null value if TLS_KEY_EXCHANGE_ALGORITHM_COUNT is 0.
TLS_CERTIFICATE_ALGORITHM_COUNT	ALG_CNT	INTEGER	The number of entries in the TLS_CERTIFICATE_ALGORITHMS list.
TLS_CERTIFICATE_ALGORITHMS	CERT_ALGS	VARCHAR(608) CCSID 1208 Nullable	The TLS signature algorithms for a certificate supported by this application. Note: This column applies when a TLS version 1.2 or newer protocol is negotiated This list is returned as an array within a JSON object. The array is identified by CERT_ALGS. Up to 32 values can be returned in the list. *PGM Use the runtime value that was set by the underlying application and its configuration, do not override. ECDSA_SHA1 ECDSA with SHA1 ECDSA_SHA224 ECDSA with SHA224 ECDSA_SHA256 ECDSA with SHA256 ECDSA_SHA384 ECDSA with SHA384 ECDSA_SHA512 ECDSA with SHA512 ED25519 ED25519 RSA_MD5 RSA with MD5 RSA_PSS_SHA256 RSA_PSS_SHA256 RSA_PSS_SHA384 RSA_PSS_SHA384 RSA_PSS_SHA512 RSA_PSS_SHA512 RSA_SHA1 RSA with SHA1 RSA_SHA224 RSA with SHA224 RSA_SHA256 RSA with SHA256 RSA_SHA384 RSA with SHA384 RSA_SHA512 RSA with SHA512 Contains the null value if TLS_CERTIFICATE_ALGORITHMS_COUNT is 0.
TLS_SESSION_CACHE_TIME_TO_LIVE	TIMETOLIVE	VARCHAR(5) Nullable	The length of time, in seconds, previously established TLS handshake session attributes remain valid for use in establishing a new secure connection. 1-86400 Number of seconds the TLS handshake attributes cache entry will remain valid. *PGM Use the runtime value that was set by the underlying application and its configuration, do not override. Contains the null value if no caching is done for TLS handshake attributes.
PERFORM_OCSP_CHECKING	OCSP_CHECK	VARCHAR(17)	The current value for the perform Online Certificate Status Protocol (OCSP) checking indicator. *PGM Use the runtime value that was set by the underlying application and its configuration, do not override. DISABLED OCSP certificate revocation checking using AIA certificate extension information is disabled. ENABLED OCSP certificate revocation checking using Authority Information Access (AIA) certificate extension information is enabled. STAPLING OCSP certificate revocation checking using AIA certificate extension information and OCSP stapling are enabled. The client requests OCSP stapling and the server supports the certificate status_request extension. STAPLING REQUIRED OCSP certificate revocation checking using AIA certificate extension information and OCSP stapling are enabled and OCSP stapling is required by the client. If the client does not receive a stapled OCSP response and the server's certificate extensions indicate it must staple, the secure connection fails. On the server, this value has the same meaning as a value of STAPLING.
OCSP_URL	OCSP_URL	VARCHAR(128) CCSID 1208	The URL of the OCSP responder to query during certificate validation. url-value The URL to use. DISABLE Do not use the runtime URL value that may have been set by the underlying application. No URL value will be used. PGM Use the runtime value that was set by the underlying application and its configuration, do not override.
CLIENT_AUTHENTICATION_REQUIRED	CLIENT_AUT	VARCHAR(3)	Whether client authentication is required. NO Client authentication is not required. YES Client authentication is required.
EXTENDED_RENEGOTIATION_ CRITICAL_MODE	CRIT_MODE	VARCHAR(16)	Value for the extended renegotiation critical mode processing. Note: This column applies to TLSv1.2 and prior protocols. It does not apply to TLSv1.3 and newer protocols. *PGM Use the runtime value that was set by the underlying application and its configuration, do not override. RFC5746 DISABLED RFC 5746 critical mode disabled. RFC5746 ENABLED RFC 5746 critical mode enabled.
PERFORM_CRL_PROCESSING	CRL_PROC	VARCHAR(3)	Whether Certificate Revocation List (CRL) processing is performed. NO CRL processing is not performed when the certificate associated with the application is validated. If the certificate has been added to a CRL, it will still be considered valid. YES CRL processing is performed when the certificate associated with the application is validated. If the certificate has been added to a CRL, it will not be valid.
MESSAGE_FILE_LIBRARY	MSGF_LIB	VARCHAR(10) Nullable	The name of the library in which the application description message file resides. Can contain the special value of *LIBL. Contains the null value when a message file and message identifier are provided for the application description, when there is no message information, or if the message is not available.
MESSAGE_FILE	MSGF	VARCHAR(10) Nullable	The name of the message file that contains the application description message ID. Contains the null value when a message file and message identifier are provided for the application description, when there is no message information, or if the message is not available.
MESSAGE_ID	MSGID	CHAR(7) Nullable	The message identifier for the application description. Contains the null value when a message file and message identifier are provided for the application description, when there is no message information, or if the message is not available.
MESSAGE_TEXT	MSG_TEXT	VARGRAPHIC(330) CCSID 1200 Nullable	The first level text for MESSAGE_ID, retrieved from MESSAGE_FILE. Contains the null value when a message file and message identifier are provided for the application description, when there is no message information, or if the message is not available.
TEXT_DESCRIPTION	TEXT	VARCHAR(50) Nullable	The text for the application description. Contains the null value when a message file and message identifier are provided for the application description or there is no application text description.
APPLICATION_USER_PROFILE	APP_USER	VARCHAR(10) Nullable	The name of the user profile associated with the application. Contains the null value if there is no user profile associated with the application.
EXIT_PROGRAM_LIBRARY	EXITPGMLIB	VARCHAR(10) Nullable	The name of the library in which the exit program resides. Contains the null value if there is no exit program.
EXIT_PROGRAM	EXITPGM	VARCHAR(10) Nullable	The name of the exit program associated with the application. Contains the null value if there is no exit program.
THREADSAFE	THREADSAFE	VARCHAR(3) Nullable	The thread safety status of the exit program entry. NO The exit program entry is not threadsafe. YES The exit program entry is threadsafe. Contains the null value if there is no exit program or if the threadsafe status of the exit program entry is not known.
MULTITHREADED_JOB_ACTION	JOB_ACTION	VARCHAR(7)	The action to take when calling an exit program in a multithreaded job. MSG Run the exit program in the current multithreaded job, and send informational message CPI3C80. NORUN Do not run the exit program in the current multithreaded job, but send informational message CPI3C80. RUN Run the exit program in the current multithreaded job. SYSVAL Use the QMLTTHDACN system value to determine the action to take.
QMLTTHDACN_SYSTEM_VALUE	QMLTTHDACN	VARCHAR(3)	Whether the QMLTTHDACN system value was used in determining the multithreaded job action. NO The QMLTTHDACN system value was not used to determine the multithreaded job action. YES The QMLTTHDACN system value was used to determine the multithreaded job action.
SERVER_NAME_INDICATION	SNI	VARCHAR(128) Nullable	Server name indication (SNI) value. Contains the null value if there is no SNI.
SPECIAL_INDICATORS	SPEC_IND	VARCHAR(16) Nullable	Special indicators. Contains the null value if there are no special indicators.

Example

Retrieve a list of applications that are not using the *PGM value for ciphers or TLS protocols. This means the system defaults have been changed for secure connections used by those applications.
```
SELECT * FROM QSYS2.CERTIFICATE_USAGE_INFO 
  WHERE TLS_PROTOCOLS <> '{"PROTOCOLS":["*PGM"]}' OR 
        TLS_CIPHERS <> '{"CIPHERS":["*PGM"]}';  
```