ACTIVE_DB_CONNECTIONS table function

The ACTIVE_DB_CONNECTIONS table function returns a result table that contains one row for each DRDA, DDM, and Db2® Mirror connection. For these connections, the specified job has either an application requester or application server role.

If a connection is no longer active on the remote system, it might still appear in the result of this table function. For this situation, the ERROR column will have a value of YES.

Authorization: The caller must be the same user profile that is running the specified job-name, or the caller must have *JOBCTL special authority, or be authorized to the QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage identifier.

The schema is QSYS2.

job-name: An expression that contains the qualified job name for which connection information is to be returned. The special value of '*' indicates the current job.

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. ACTIVE_DB_CONNECTIONS table function
Column Name	Data Type	Description
CONNECTION_USAGE	VARCHAR(21)	The connection usage of the local server (job-name) side of the connection. APPLICATION REQUESTER The remote job is the application requester, often called the client. APPLICATION SERVER The remote job is the application server, often called the server.
REMOTE_HOST_NAME	VARCHAR(255)	The name of the remote system for this connection.
REMOTE_JOB_NAME	VARCHAR(28)	The qualified job name of the remote job that is connected to job-name. Contains the null value if the remote server is not an IBM® i.
REMOTE_USER	VARCHAR(255)	User name for the remote connection.
CLIENT_RDB	VARCHAR(18)	The RDB name of the application requester. Can contain the null value if the connection is only used for a DDM file operation.
SERVER_RDB	VARCHAR(18)	The RDB name of the application server. Can contain the null value if the connection is only used for a DDM file operation.
CONNECTION_TIME	TIMESTAMP	Timestamp for when the connection was established.
CONNECTION_TIME_UTC	TIMESTAMP	Timestamp for when the connection was established, in Universal Time Coordinated (UTC).
CONNECTION_TYPE	VARCHAR(11)	The type of connection established between job-name and REMOTE_JOB_NAME. DB2® MIRROR This connection is a Db2 Mirror connection. OPTICONNECT This connection is an OptiConnect connection. SNA This connection is an SNA connection. TCP/IP This connection is a TCP/IP connection.
NRG_NAME	VARCHAR(15)	The Network Redundancy Group (NRG) used for this connection. Contains the null value if CONNECTION_TYPE is not DB2 MIRROR.
CLIENT_PORT	INTEGER	The client host port number.
CLIENT_ADDRESS	VARCHAR(45)	IP address of the client. This is shown in IPv6 address format.
SERVER_PORT	INTEGER	The server host port number.
SERVER_ADDRESS	VARCHAR(45)	IP address of the server. This is shown in IPv6 address format.
SNA_DEVICE_NAME	VARCHAR(10)	The name of the device used to communicate with the remote location. Contains the null value if CONNECTION_TYPE is not SNA.
SNA_MODE_NAME	VARCHAR(8)	The name of the mode to be used to communicate between the local location and remote location. Contains the null value if CONNECTION_TYPE is not SNA.
SNA_LOCAL_LOCATION_NAME	VARCHAR(8)	Indicates the local location name by which this system is identified to the system on which the RDB is located. Contains the null value if CONNECTION_TYPE is not SNA.
SNA_REMOTE_LOCATION_NAME	VARCHAR(8)	The name of the remote location where the remote file exists. Contains the null value if CONNECTION_TYPE is not SNA.
SNA_REMOTE_NETWORK_ID	VARCHAR(8)	Indicates the remote network identifier of the system on which the RDB is located. Contains the null value if CONNECTION_TYPE is not SNA.
SNA_TRANSACTION_PROGRAM_ NAME	VARCHAR(19)	Indicates the transaction program name (TPN) specified in the RDB directory entry. The value is returned as a hex string in the form X'aabbcc'. Contains the null value if CONNECTION_TYPE is not SNA.
SSL	VARCHAR(3)	Indicates whether this connection uses SSL. NO The connection does not use SSL. YES The connection uses SSL.
THREE_PART_NAMING	VARCHAR(3)	Indicates whether this connection was established using an SQL statement with 3-part naming. NO This connection was not established using an SQL statement with 3-part naming. YES This connection was established using an SQL statement with 3-part naming. Contains the null value if CONNECTION_USAGE is APPLICATION SERVER.
XA	VARCHAR(3)	Indicates whether this connection is used for XA. NO This is not an XA connection. YES This is an XA connection. Contains the null value if CONNECTION_USAGE is APPLICATION SERVER.
ACTIVATION_GROUP_NUMBER	BIGINT	Activation group number for job making the connection. Contains the null value if CONNECTION_USAGE is APPLICATION SERVER.
ACTIVATION_GROUP_NAME	VARCHAR(10)	Activation group name for job making the connection. Contains the null value if the activation group has no name or if CONNECTION_USAGE is APPLICATION SERVER.
THREAD_ID	BIGINT	The thread ID for the connection. Contains the null value if CONNECTION_USAGE is APPLICATION SERVER.
SCOPE	VARCHAR(16)	Scope of the connection. ACTIVATION GROUP The connection is scoped to the activation group. JOB The connection is scoped to the job. Contains the null value if CONNECTION_USAGE is APPLICATION SERVER.
COMMIT_PROTOCOL	VARCHAR(9)	The commitment control protocol used for this connection. ONE-PHASE The connection uses one-phase commitment control. TWO-PHASE The connection uses two-phase commitment control. Contains the null value if CONNECTION_USAGE is APPLICATION SERVER.
REMOTE_CLASS	VARCHAR(255)	The class of the remote server. Some common values are: QAS Db2 for i QDB2 Db2 for z/OS® QDB2/xxx Db2 for LUW
REMOTE_RELEASE	VARCHAR(255)	The release level of the remote server.
ERROR	VARCHAR(3)	Indicates whether the connection is currently in an error state. For example, a communication failure occurred. NO The connection is not in an error state. YES The connection is in an error state.
SUSPEND_PROCESSING_START	TIMESTAMP	The timestamp, in Universal Time Coordinated (UTC), when the most recent Db2 Mirror suspend state change processing started for this job. The timestamp is not cleared when the state change processing is complete. Contains the null value if no prior suspend state change has occurred for this job.
SUSPEND_PROCESSING_END	TIMESTAMP	The timestamp, in Universal Time Coordinated (UTC), when the most recent Db2 Mirror suspend state change processing ended for this job. The timestamp is not cleared when the state change processing is complete. Contains the null value if no prior suspend state change has occurred for this job.

Example

List all the connections that are active for a specific job.

SELECT * FROM TABLE (QSYS2.ACTIVE_DB_CONNECTIONS('900239/SKALSKY/QPADEV000F'));