ACTIVE_DB_CONNECTIONS table function
The ACTIVE_DB_CONNECTIONS table function returns a result table that contains one row for each DRDA and DDM 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: To invoke this function, the caller must be the same user profile that is running the specified job-name, or the caller must have *JOBCTL user special authority, or QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage authority.
- 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.
Column Name | Data Type | Description |
---|---|---|
CONNECTION_USAGE | VARCHAR(21) | The connection usage of the local server (job-name) side
of the connection.
|
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_TYPE | VARCHAR(11) | The type of connection established between job-name and REMOTE_JOB_NAME.
|
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.
|
THREE_PART_NAMING | VARCHAR(3) | Indicates whether 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.
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.
Contains the null value if CONNECTION_USAGE is APPLICATION SERVER. |
COMMIT_PROTOCOL | VARCHAR(9) | The commitment control protocol used for this connection.
Contains the null value if CONNECTION_USAGE is APPLICATION SERVER. |
REMOTE_CLASS | VARCHAR(255) | The class of the remote server. Some common values are:
|
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.
|
Example
- List all the connections that are active for a specific
job.
SELECT * FROM TABLE (QSYS2.ACTIVE_DB_CONNECTIONS('900239/SKALSKY/QPADEV000F'));