INFORMATION.SCHEMA.CURRENTCONNECTIONS

view class INFORMATION.SCHEMA.CURRENTCONNECTIONS

SQL Table Name: INFORMATION_SCHEMA.CURRENT_CONNECTIONS

View Query:

 
	SELECT pq2.Pid, 
		   pq2.NameSpace, 
		   pq2.UserName, 
		   pq2.Roles, 
		   pq2.LoginRoles, 
		   pq2.Routine, 
		   pq2.InTransaction, 
		   pq2.ClientNodeName, 
		   pq2.ClientIPAddress, 
		   pq2.ClientExecutableName, 
		   pq2.MemoryAllocated, 
		   pq2.MemoryUsed, 
		   pq2.MemoryPeak, 
		   pq2.StatementId,
		   information_schema.getStatement(pq2.StatementId,pq2.NameSpace) as Statement
	FROM (select Pid,
			     NameSpace, 
			     UserName, 
			     Roles, 
			     LoginRoles, 
			     Routine, 
			     InTransaction, 
			     ClientNodeName, 
			     ClientIPAddress, 
			     ClientExecutableName, 
			     MemoryAllocated, 
			     MemoryUsed, 
			     MemoryPeak,
			     cast($listget($listget(AppFrameInfo),4) as varchar(32)) as StatementId 
			FROM %SYS.ProcessQuery pq1
			WHERE JobType = 28
		) pq2

Query analysis:

Col	Schema	Table	Col Name	Col Label	Datatype	ODBC Type Code	Length	Precision	Scale
1	%SYS	ProcessQuery	Pid	Pid	%Library.Integer	4	4	10	0
2	%SYS	ProcessQuery	NameSpace	NameSpace	%Library.String	12	50	50	0
3	%SYS	ProcessQuery	UserName	UserName	%Library.String	12	160	160	0
4	%SYS	ProcessQuery	Roles	Roles	%Library.String	12	50	50	0
5	%SYS	ProcessQuery	LoginRoles	LoginRoles	%Library.String	12	50	50	0
6	%SYS	ProcessQuery	Routine	Routine	%Library.String	12	50	50	0
7	%SYS	ProcessQuery	InTransaction	InTransaction	%Library.Integer	4	4	10	0
8	%SYS	ProcessQuery	ClientNodeName	ClientNodeName	%Library.String	12	50	50	0
9	%SYS	ProcessQuery	ClientIPAddress	ClientIPAddress	%Library.String	12	50	50	0
10	%SYS	ProcessQuery	ClientExecutableName	ClientExecutableName	%Library.String	12	50	50	0
11	%SYS	ProcessQuery	MemoryAllocated	MemoryAllocated	%Library.Integer	4	4	10	0
12	%SYS	ProcessQuery	MemoryUsed	MemoryUsed	%Library.Integer	4	4	10	0
13	%SYS	ProcessQuery	MemoryPeak	MemoryPeak	%Library.Integer	4	4	10	0
14				StatementId	%Library.String	12	32	32	0
15				Statement	%Library.String	12	262144	262144	0

Returns one row for each xDBC server process currently running on this instance

Column Descriptions:

Pid - Process ID. Process ID ($J) given to the process by the O/S, decimal form on all platforms.

Username - $Username of process.

Roles - Roles a process currently has.

LoginRoles - Roles a process has when it initially logs in.

Routine - Name of the routine which the process is currently executing.

InTransaction - In a transaction. If 0, the process is not in a transaction. If >0, the process has executed a tstart command, is in a transaction, and the value is the offset in the journal file where the transaction has started.

ClientNodeName - Node name of the client which initiated the connection. It is passed down to the process as part of the initial connection message. This property may be set by the end-user if they are managing their own connections.

ClientIPAddress - IP address of the client which initiated the connection. It is passed down to the process as part of the initial connection message. This property may be set by the end-user if they are managing their own connections.

ClientExecutableName - The name of the Executable or DLL on the client which initiated the connection. It is passed down to the process as part of the initial connection message. This property may be set by the end-user if they are managing their own connections.

MemoryAllocated - Maximum amount of memory in KB that the process is allowed to use. This property requires a mailbox message to be sent to the process being examined.

MemoryUsed - Memory used in KB (Current $s). Current amount of memory the process has used in KB. This property requires a mailbox message to be sent to the process being examined.

MemoryPeak - Peak memory allocated in KB. This property requires a mailbox message to be sent to the process being examined.

StatementId - Statement hash ID from the SQL Statement Index (if the process is currently executing an SQL Statement)

Statement - SQL Statement Text currently being executed (if the caller has permission for this statement).