AUDIT_JOURNAL_SM table function

The AUDIT_JOURNAL_SM table function returns rows from the audit journal that contain information from the SM (Systems Management Change) journal entries.

Every audit journal table function shares a common authorization requirement and a common set of parameters. These are described in AUDIT JOURNAL table function common information.

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. AUDIT_JOURNAL_SM table function
Column Name	Data Type	Description
The first columns returned by this table function are from the common audit journal entry header. See Common columns returned from the audit journal entry header for the column definitions. After the common columns are the following columns that describe the entry specific data for the SM audit journal entry.
ENTRY_TYPE	CHAR(1)	The type of entry. B Backup list changed C Automatic cleanup options D DRDA F HFS file system M Change DDM TCP/IP Attributes (CHGDDMTCPA) CL command N Network file operation O Backup options changed P Power® on/off schedule S System reply list T Access path recovery times changed
ENTRY_TYPE_DETAIL	VARCHAR(200)	Descriptive text that corresponds to the entry type.
ACCESS_TYPE	VARCHAR(8)	The type of access. ADD Add CHANGE Change DELETE Delete DISPLAY Display REMOVE Remove RETRIEVE Retrieve or receive Contains the null value when information is not available.
REPLY_SEQUENCE_NUMBER	INTEGER	Sequence number of the action. Contains the null value if ENTRY_TYPE is not S or if ACCESS_TYPE is DISPLAY.
REPLY_MESSAGE_ID	VARCHAR(7)	Message ID associated with the action. Can contain the special value *ANY. Contains the null value if ENTRY_TYPE is not S.
FILE_SYSTEM_NAME	VARCHAR(10)	Name of the file system. Contains the null value if ENTRY_TYPE is not F.
BACKUP_OPTION	VARCHAR(8)	The backup option that was changed. Values are DAILY, WEEKLY, and *MONTHLY. Contains the null value if ENTRY_TYPE is not O.
BACKUP_LIST	VARCHAR(4)	The name of the backup list that was changed. Values are FLD and LIB. Contains the null value if ENTRY_TYPE is not B.
NETWORK_FILE	VARCHAR(10)	The name of the network file that was used. Contains the null value if ENTRY_TYPE is not N.
NETWORK_FILE_MEMBER	VARCHAR(10)	The name of the member of the network file. Contains the null value if ENTRY_TYPE is not N or if the information is not available.
NETWORK_FILE_NUMBER	INTEGER	The number of the network file. Contains the null value if ENTRY_TYPE is not N.
NETWORK_FILE_OWNER	VARCHAR(10)	The name of the user profile that owns the network file. Contains the null value if ENTRY_TYPE is not N.
NETWORK_FILE_ORIGINATOR	VARCHAR(8)	The name of the user profile that originated the network file. Contains the null value if ENTRY_TYPE is not N.
NETWORK_FILE_ADDRESS	VARCHAR(8)	The address that originated the network file. Contains the null value if ENTRY_TYPE is not N.
RDB_NAME	VARCHAR(18)	Name of the relational database. When ENTRY_TYPE is D, can contain the following special values: ALL All entries in the RDB directory were removed. ALLRMT All entries except the *LOCAL entry in the RDB directory were removed. Contains the null value if ENTRY_TYPE is not D or M.
RDB_ALIAS	VARCHAR(18)	The RDB alias name. This value may be *NONE. Contains the null value if ENTRY_TYPE is not D.
REMOTE_LOCATION_TYPE	VARCHAR(4)	The remote location type. IP The RDB is found using a host name or an internet address over a TCP/IP connection. SNA The RDB is accessed using a Systems Network Architecture (SNA) address and protocol. Contains the null value if ENTRY_TYPE is not D or if the information is not available.
REMOTE_LOCATION	VARCHAR(254)	The remote location name of the system on which the RDB is located. remote-location-name The remote location name is in one of the following formats: SNA remote location name (LU name). SNA remote network identifier and remote location name separated by a period. IPv4 address in dotted decimal form. IPv6 address in colon hexadecimal form. IP host domain name. ARDPGM The RDB is accessed by using the Application Requester Driver (ARD) program. LOCAL The system database on this system. LOOPBACK This value is an alias for the IP address of the host system. MIRROR The RDB is accessed on the other system for a Db2® Mirror relationship. Contains the null value if ENTRY_TYPE is not D or if the information is not available.
REMOTE_PORT_OR_SERVICE	VARCHAR(14)	The relational database entry port number or service name that is used at the remote location to communicate with the system on which the RDB is located. If DRDA was specified, the Distributed Relational Database Architecture™ (DRDA) port 446 is shown. Contains the null value if ENTRY_TYPE is not D, REMOTE_LOCATION_TYPE is not IP, or if the information is not available.
PREFERRED_AUTHENTICATION	VARCHAR(10)	The preferred authentication method on a connection request. ENCRYPTED User ID and encrypted password. ENCUSRPWD Encrypted user ID and encrypted password. KERBEROS Authentication occurs using Kerberos. USRENCPWD User ID and encrypted password USRID User ID only. USRIDPWD User ID and password. Contains the null value if ENTRY_TYPE is not D or if the information is not available.
LOWER_AUTHENTICATION	VARCHAR(11)	Whether an authentication method lower than what was specified for the preferred method will be accepted during negotiation with the server. ALWLOWER Allow negotiation of a lower authentication method. NOALWLOWER Do not allow negotiation of a lower authentication method. Contains the null value if ENTRY_TYPE is not D or if the information is not available.
ENCRYPTION_ALGORITHM	VARCHAR(4)	The encryption algorithm to be used initially on the connection request. AES Advanced Encryption Standard (AES) is to be used initially. DES Data Encryption Standard (DES) is to be used initially. Contains the null value if ENTRY_TYPE is not D, REMOTE_LOCATION_TYPE is not *IP, or if the information is not available.
SECURE_CONNECTION	VARCHAR(5)	Whether Transport Layer Security (TLS) is to be used on a DDM/DRDA TCP/IP connection request. NONE TLS is not used. SSL TLS is used. TLS TLS is used. Contains the null value if ENTRY_TYPE is not D, REMOTE_LOCATION_TYPE is not IP, or if the information is not available.
APPC_DEVICE_DESCRIPTION	VARCHAR(10)	The Advanced Program-to-Program Communications (APPC) device description on this system that is used with this RDB entry. Can contain the special value: LOC If APPC is being used, the system determines which device description is used. Contains the null value if ENTRY_TYPE is not D, REMOTE_LOCATION_TYPE is not SNA, or if the information is not available.
LOCAL_LOCATION	VARCHAR(8)	The local location name by which this system is identified to the system on which the RDB is located. Can contain one of the following special values: LOC If APPC is being used, the system determines which local location name is used. NETATR The LCLLOCNAME value specified in the system network attributes is used. Contains the null value if ENTRY_TYPE is not D, REMOTE_LOCATION_TYPE is not *SNA, or if the information is not available.
REMOTE_NETWORK_ID	VARCHAR(8)	The remote network identifier of the system on which the RDB is located. Can contain one of the following special values: LOC If APPC is being used, the system determines which remote network identifier is used. NETATR The remote network identifier specified in the network attributes is used. NONE No remote network identifier is used. Contains the null value if ENTRY_TYPE is not D, REMOTE_LOCATION_TYPE is not SNA, or if the information is not available.
REMOTE_MODE	VARCHAR(8)	The mode name to use with the remote location name to communicate with the system on which the RDB is located. Can contain one of the following special values: NETATR The mode in the network attributes is used. BLANK A mode name of all blanks is used. Contains the null value if ENTRY_TYPE is not D, REMOTE_LOCATION_TYPE is not SNA, or if the information is not available.
TRANSACTION_PROGRAM	VARCHAR(8) FOR BIT DATA	The name of the transaction program to use with the RDB entry. transaction-program-name The transaction program name is in one of the following formats: A 4-byte hexadecimal name. For example, X'07F6C4C2'. An 8-byte character name. If DRDA was specified, the Distributed Relational Database Architecture (DRDA) transaction program name, X'07F6C4C2'. Contains the null value if ENTRY_TYPE is not D, REMOTE_LOCATION_TYPE is not SNA, or if the information is not available.
ARD_LIBRARY	VARCHAR(10)	The library containing the Application Requester Driver (ARD) program. This value can be LIBL or CURLIB. Contains the null value if the special value DRDA was specified on the command, if ENTRY_TYPE is not D, if REMOTE_LOCATION is not ARDPGM, or if the information is not available.
ARD_PROGRAM	VARCHAR(10)	The ARD program to be called to process SQL requests directed to the RDB. Contains the null value if the special value DRDA was specified on the command, if ENTRY_TYPE is not D, if REMOTE_LOCATION is not ARDPGM, or if the information is not available.
PREV_REMOTE_LOCATION_TYPE	VARCHAR(4)	The previous value for the remote location type. Contains the null value if ENTRY_TYPE is not D or if the previous value is not available.
PREV_REMOTE_LOCATION	VARCHAR(254)	The previous value for the remote location name of the system on which the RDB is located. Contains the null value if ENTRY_TYPE is not D or if the previous value is not available.
PREV_REMOTE_PORT_OR_SERVICE	VARCHAR(14)	The previous value for the relational database entry port number or service name that is used at the remote location to communicate with the system on which the RDB is located. Contains the null value if ENTRY_TYPE is not D, if REMOTE_LOCATION_TYPE is not *IP, or if the previous value is not available.
PREV_PREFERRED_AUTHENTICATION	VARCHAR(10)	The previous value for the preferred authentication method on a connection request. Contains the null value if ENTRY_TYPE is not D or if the previous value is not available.
PREV_LOWER_AUTHENTICATION	VARCHAR(11)	The previous value for whether an authentication method lower than what was specified for the preferred method will be accepted during negotiation with the server. Contains the null value if ENTRY_TYPE is not D or if the previous value is not available.
PREV_ENCRYPTION_ALGORITHM	VARCHAR(4)	The previous value for the encryption algorithm to be used initially on the connection request. Contains the null value if ENTRY_TYPE is not D, if REMOTE_LOCATION_TYPE is not *IP, or if the previous value is not available.
PREV_SECURE_CONNECTION	VARCHAR(5)	The previous value for whether Transport Layer Security (TLS) is to be used on a DDM/DRDA TCP/IP connection request. Contains the null value if ENTRY_TYPE is not D, if REMOTE_LOCATION_TYPE is not *IP, or if the previous value is not available.
PREV_APPC_DEVICE_DESCRIPTION	VARCHAR(10)	The previous value for the Advanced Program-to-Program Communications (APPC) device description on this system that is used with this RDB entry. Contains the null value if ENTRY_TYPE is not D, if the previous value is not available, or REMOTE_LOCATION_TYPE is not *SNA.
PREV_LOCAL_LOCATION	VARCHAR(8)	The previous value for the local location name by which this system is identified to the system on which the RDB is located. Contains the null value if ENTRY_TYPE is not D, if the previous value is not available, or REMOTE_LOCATION_TYPE is not *SNA.
PREV_REMOTE_NETWORK_ID	VARCHAR(8)	The previous value for the remote network identifier of the system on which the RDB is located. Contains the null value if ENTRY_TYPE is not D, if the previous value is not available, or REMOTE_LOCATION_TYPE is not *SNA.
PREV_REMOTE_MODE	VARCHAR(8)	The previous value for the mode name to use with the remote location name to communicate with the system on which the RDB is located. Can contain one of the following special values: Contains the null value if ENTRY_TYPE is not D, if the previous value is not available, or REMOTE_LOCATION_TYPE is not *SNA.
PREV_TRANSACTION_PROGRAM	VARCHAR(8) FOR BIT DATA	The previous value for the name of the transaction program to use with the RDB entry. Contains the null value if ENTRY_TYPE is not D, if the previous value is not available, or REMOTE_LOCATION_TYPE is not *SNA.
PREV_ARD_LIBRARY	VARCHAR(10)	The previous value for the library containing the Application Requester Driver (ARD) program. This value may be LIBL or CURLIB. Contains the null value if ENTRY_TYPE is not D, if REMOTE_LOCATION is not *ARDPGM, or if the previous value is not available.
PREV_ARD_PROGRAM	VARCHAR(10)	The previous value for the ARD program to be called to process SQL requests directed to the RDB. Contains the null value if ENTRY_TYPE is not D, if REMOTE_LOCATION is not *ARDPGM, or if the previous value is not available.
AUTOSTART_SERVER	VARCHAR(4)	Whether the DDM server is automatically started. This value was set using the Change DDM TCP/IP Attributes (CHGDDMTCPA) CL command. NO Do not automatically start the DDM server. YES Automatically start the DDM server. Contains the null value if ENTRY_TYPE is not M or if the information is not available.
LOWEST_AUTHENTICATION_METHOD	VARCHAR(10)	The lowest level of password security required. This value was set using the Change DDM TCP/IP Attributes (CHGDDMTCPA) CL command. ENCRYPTED User ID and encrypted password. Same as USRENCPWD. ENCUSRPWD Encrypted user ID and encrypted password. KERBEROS Authentication occurs using Kerberos. NO User ID only. Same as USRID. USRID User ID only. USRENCPWD User ID and encrypted password USRIDPWD User ID and password. VLDONLY User ID only but if a password is sent on the request, it must be valid. YES User ID and password. Same asUSRIDPWD. Contains the null value if ENTRY_TYPE is not M or D with an ACCESS_TYPE of ADD, CHANGE, REMOVE, or RETRIEVE, ENTRY_TYPE is D and REMOTE_LOCATION is not *LOCAL, or if the information is not available.
LOWEST_ENCRYPTION_ALGORITHM	VARCHAR(4)	The lowest encryption algorithm allowed on an incoming connection request. This value was set using the Change DDM TCP/IP Attributes (CHGDDMTCPA) CL command. AES Advanced Encryption Standard (AES) allowed. DES Data Encryption Standard (DES) or higher encryption algorithm allowed. Contains the null value if ENTRY_TYPE is not M or D with an ACCESS_TYPE of ADD, CHANGE, REMOVE, or RETRIEVE, ENTRY_TYPE is D and REMOTE_LOCATION is not *LOCAL, or if the information is not available.
PREV_AUTOSTART_SERVER	VARCHAR(4)	The previous value for whether the DDM server is automatically started. Contains the null value if ENTRY_TYPE is not M or if the previous value is not available.
PREV_LOWEST_AUTHENTICATION_ METHOD	VARCHAR(10)	The previous value for the lowest level of password security required. Contains the null value if ENTRY_TYPE is not M or if the previous value is not available.
PREV_LOWEST_ENCRYPTION_ ALGORITHM	VARCHAR(4)	The previous value for the lowest encryption algorithm allowed on an incoming connection request. Contains the null value if ENTRY_TYPE is not M or if the previous value is not available.

Example

List the DRDA configuration changes related to authentication made this month.


SELECT RDB_NAME, PREFERRED_AUTHENTICATION, LOWER_AUTHENTICATION, ENCRYPTION_ALGORITHM, SECURE_CONNECTION FROM TABLE(
  SYSTOOLS.AUDIT_JOURNAL_SM(  
      STARTING_TIMESTAMP => CURRENT TIMESTAMP - 1 MONTH
  )
)
WHERE ENTRY_TYPE = 'D';