CONNECTIONS System Table Description

Description

A pseudo table, generated to present information about all current connections to the NuoDB database. Only users with the SYSTEM.DBA privilege can view all connections for all users. Users that have not been granted the SYSTEM.DBA privilege will only see the connections for their user.

Client applications should never contain code that accesses SYSTEM pseudo tables as they are subject to, and furthermore likely to, change from release to release.

Fields

Field Name Type Description

SQLSTRING

string

The SQL statement being executed. If the connection is currently active, but no statement is executing, the SQLSTRING is presented as an empty string.

COUNT

integer

A number n, indicating that this is the nth statement executed on this connection.

RUNTIME

bigint

The duration of the statement execution specified in microseconds.

USER

string

The SQL user for the database connection.

SCHEMA

string

The current SQL schema for the database connection.

NUMPARAM

integer

The number of SQL parameters specified in the query. This is 0 if no parameters were specified or if no statement is executing.

PARAMS

string

Parameter string — A string representing the parameters specified in the query. The string representing each parameter is of the format:

n/type/value

Where:

n is the number of the parameter starting at 0

type is the type of the variable, for example, integer, string.

value is the value passed in to the parameter.

Each parameter string is separated by a space. For example:

NUMPARAM            PARAMS
--------- ----------------------------
    2     0/string/'hello' 1/integer/

CONNID

bigint

A connection identifier which is unique across all connections in the database.

OPEN

integer

The number of open statements created using this connection.

MEMUSAGE

bigint

An estimate of the current memory usage by this connection.

HANDLE

integer

An identifier, unique only to the specific connection, that represents the executing statement. HANDLE is -1 when there is no statement running on the connection.

OPENRESULTS

integer

The number of open result sets for this connection.

NODEID

bigint

The identifier for the Transaction Engine (TE) to which the client is connected.

Deprecated in NuoDB 7.0 and later. Use STARTID instead.

STARTID

bigint

The identifier for the Transaction Engine (TE) to which the client is connected. See Start ID.

EXECID

numeric

A unique identifier generated for this SQL statement, specifically for use with the KILL STATEMENT command. EXECID will be 0 when HANDLE is -1, meaning that there is no statement running on the connection.

TRANSID

bigint

The identifier for the transaction being run on the connection. This value can be used to query SYSTEM.TRANSACTIONS by ID.

TRANSRUNTIME

bigint

The duration of the current open transaction on the connection, specified in microseconds.

AUTOCOMMITFLAGS

integer

A bitwise mapping of the following values

0x1

Autocommit is on.
0x2

Autocommit is deferred, meaning defer commit on read operations until the result set is closed or the next operation occurs.
0x4

Autocommit is suspended.

ISOLATIONLEVEL

integer

Specifies the transaction isolation level for the connection:

2

READ COMMITTED
4

REPEATABLE READ
7

CONSISTENT READ
8

SERIALIZABLE

CLIENTHOST

string

Specifies the IP address for the connecting client. The TE will supply this with information from the socket it uses for communicating with the client.

CLIENTPROCESSID

string

Specifies the process ID (pid) for the connecting client. This may be set automatically by the C++, .NET, and Python drivers. Otherwise, this must be given as the client connection property clientProcessID. See Connection Properties for more information.

CLIENTINFO

string

Specifies the client information for the connecting client. This must be given as the client connection property clientInfo. See Connection Properties for more information.

ROLLBACKMODE

string

Either TRANSACTION, PROCEDURE, or OFF. See SET ROLLBACK MODE at SET.

EXECUTIONSTACK

string

The execution stack column details all nested calls in the procedure currently running. The current executing statement, at the top of the stack, is shown at the top of this column.

CREATED

timestamp

The date and time when this connection was established with NUODB.

LASTEXECUTED

timestamp

The date and time when this connection last executed SQL.

SQLENGINE

string

The SQL Engine and the optimizer used by the connection.

COORDINATORCONNID

bigint

If the connection belongs to a distributed query worker, this field represents the connection identifier for the coordinator of the query. The value is NULL if the connection does not belong to a distributed query worker.

COORDINATORNODEID

integer

If the connection belongs to a distributed query worker, this field represents the identifier for the Transaction Engine (TE) where the query’s coordinator is running. The value is NULL if the connection does not belong to a distributed query worker.

Deprecated in NuoDB 7.0 and later. Use COORDINATORSTARTID instead.

COORDINATORSTARTID

integer

If the connection belongs to a distributed query worker, this field represents the identifier for the Transaction Engine (TE) where the query’s coordinator is running. The value is NULL if the connection does not belong to a distributed query worker.

COORDINATORHANDLE

integer

If the connection belongs to a distributed query worker, this field represents the handle of the executing statement for the query’s coordinator. The value is NULL if the connection does not belong to a distributed query worker.

It is formatted as a JSON object, and the metrics contained in the JSON object are described by the SYSTEM.STATEMENTMETRICSINFO table.

Indexes

None

Example

The following example shows two rows in SYSTEM.CONNECTIONS. The second row represents the SELECT SQLSTRING,COUNT,RUNTIME,USER,SCHEMA,ISOLATIONLEVEL FROM system.connections query itself. The first row represents a SQL statement executing on another client with two parameters in the query. Use SET OUTPUT VERTICAL to display the columns in SYSTEM.CONNECTIONS vertically.

In the row for the first query, we see this is the first statement run on this connection, the query has been running 2016795 microseconds, the SQL user is DBA and the current schema is USER, the transaction isolation level is SERIALIZABLE.

SET OUTPUT VERTICAL;

SELECT SQLSTRING,COUNT,RUNTIME,USER,SCHEMA,ISOLATIONLEVEL FROM system.connections;
==================================== Row #1 ====================================
SQLSTRING: select * from hugetable where lastname like ? and id > ?;
COUNT: 1
RUNTIME: 2016795
USER: DBA
SCHEMA: USER
ISOLATIONLEVEL: 8
==================================== Row #2 ====================================
SQLSTRING: var x = 0; while (x < 10) execute immediate 'insert into employees.titles values (?), (?), (?);' using values 1, 2, x; x = x + 1; end_while;
COUNT: 2
RUNTIME: 9214554
USER: DBA
SCHEMA: USER
ISOLATIONLEVEL: 8
==================================== Row #3 ====================================
SQLSTRING: SELECT SQLSTRING,COUNT,RUNTIME,USER,SCHEMA,ISOLATIONLEVEL FROM system.connections;
COUNT: 4
RUNTIME: 159
USER: DBA
SCHEMA: USER
ISOLATIONLEVEL: 8