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.

 

Caution: 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 integer A unique identifier for the connection on a specific Transaction Engine (TE). To uniquely identify the connection across multiple TEs, combine CONNID with NODEID.
OPEN integer The number of open statements created using 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 integer The identifier for the TE to which the client is connected.
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 with "where ID = this_value".
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
  • 5 — WRITE COMMITTED
  • 7 — CONSISTENT READ
  • 8 — SERIALIZABLE

Note: With the release of NuoDB 3.2, the WRITE COMMITTED isolation level has been deprecated.

CLIENTHOST string

Specifies the IP address for the connecting client. As of NuoDB 2.3.2, the TE will supply this with information from the socket it uses for communicating with the client.

Prior to NuoDB 2.3.2, you could set this with a connection property called clientHost.

CLIENTPROCESSID string Specifies the process ID (pid) for the connecting client. This may be set automatically by the driver (as of NuoDB 2.3.2 this is true for 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.
AUTOCOMMITSPMODE boolean ON or OFF. See SET AUTOCOMMIT_SP_MODE at SET.
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.

Indexes

None

Example

The following example shows two rows in SYSTEM.CONNECTIONS. The second row represents the "SELECT * FROM system.connections" query itself. The first row represents a SQL statement executing on another client with two parameters in the query. We use SET OUTPUT VERTICAL because there are many columns in SYSTEM.CONNECTIONS and it is more readable if displayed this way.

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 parameters entered for the query were 'A%' and 16, etc. The AUTOCOMMITFLAGS is set to 3 which means autocommit is on but deferred on read operations until the result set is closed or the next operation occurs. The transaction isolation level is SERIALIZABLE.

SET OUTPUT VERTICAL;

SELECT * FROM system.connections;
==================================== Row #1 ====================================
SQLSTRING: select * from hugetable where lastname like ? and id > ?;
COUNT: 1
RUNTIME: 2016795
USER: DBA
SCHEMA: USER
NUMPARAM: 2
PARAMS: 0/string/A% 1/string/16 
CONNID: 21474836521
OPEN: 2
HANDLE: 9
OPENRESULTS: 1
NODEID: 5
EXECID: 166020696839479623685
TRANSID: 4548485
TRANSRUNTIME: 2016689
AUTOCOMMITFLAGS: 7
ISOLATIONLEVEL: 8
CLIENTHOST: 127.0.0.1
CLIENTPROCESSID: 38483
CLIENTINFO: nuosql
AUTOCOMMITSPMODE: FALSE
ROLLBACKMODE: procedure
EXECUTIONSTACK: 
CREATED: 2018-04-19 10:26:15.616959
LASTEXECUTED: 2018-04-19 10:39:53.891211
==================================== 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
NUMPARAM: 0
PARAMS: 
CONNID: 21474836522
OPEN: 2
HANDLE: 1
OPENRESULTS: 1
NODEID: 5
EXECID: 18446744254098178053
TRANSID: 4548357
TRANSRUNTIME: 9214479
AUTOCOMMITFLAGS: 5
ISOLATIONLEVEL: 8
CLIENTHOST: 127.0.0.1
CLIENTPROCESSID: 38872
CLIENTINFO: nuosql
AUTOCOMMITSPMODE: FALSE
ROLLBACKMODE: procedure
EXECUTIONSTACK: Procedure "SAMPLE.PROC" line 1: insert into sample.b select msleep(1000) from dual
Procedure "EMPLOYEES.PROC2" line 1: call sample.proc()
Trigger "EMPLOYEES.TITLES.TRIG1" line 1: call proc2(NEW.f1)
line 1: execute immediate 'insert into employees.titles values (?), (?), (?);' using values 1, 2, x
CREATED: 2018-04-19 10:38:18.631389
LASTEXECUTED: 2018-04-19 10:39:46.691568
==================================== Row #3 ====================================
SQLSTRING: SELECT * FROM system.connections;
COUNT: 4
RUNTIME: 159
USER: DBA
SCHEMA: USER
NUMPARAM: 0
PARAMS: 
CONNID: 21474836523
OPEN: 1
HANDLE: 1
OPENRESULTS: 0
NODEID: 5
EXECID: 18446744258393145349
TRANSID: 4548613
TRANSRUNTIME: 81
AUTOCOMMITFLAGS: 1
ISOLATIONLEVEL: 8
CLIENTHOST: 127.0.0.1
CLIENTPROCESSID: 38873
CLIENTINFO: nuosql
AUTOCOMMITSPMODE: FALSE
ROLLBACKMODE: procedure
EXECUTIONSTACK: 
CREATED: 2018-04-19 10:38:23.287194
LASTEXECUTED: 2018-04-19 10:39:55.906553