Open topic with navigation
A pseudo table.
SYSTEM.LOCALCONNECTIONS is similar to the pseudo table
SYSTEM.CONNECTIONS, except it only lists connections to the same Transaction Engine (TE) to which the current client is connected. The
SYSTEM.LOCALCONNECTIONS pseudo table does not include some columns that are defined for the
SYSTEM.CONNECTIONS pseudo table.
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.
SQL statement being executed. If the connection is currently active,
but no statement is executing, the
|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
Parameter string — A string representing the parameters specified in the query. The string representing each parameter is of the format:
Each parameter string is separated by a space. For example:
NUMPARAM PARAMS --------- ---------------------------- 2 0/string/'hello' 1/integer/16
|CONNID||integer||An identifier for the connection on a specific Transaction Engine (TE).|
|OPEN||integer||The number of open statements created using this connection.|
identifier, unique only to the specific connection, that represents the
|OPENRESULTS||integer||The number of open result sets for this connection.|
|TRANSID||bigint||The identifier for the transaction being run on the connection. This value can be used to query
|TRANSRUNTIME||bigint||The duration of the current open transaction on the connection, specified in microseconds.|
A bitwise mapping of the following values
Specifies the transaction isolation level for the connection:
Note: With the release of NuoDB 3.2, WRITE COMMITTED functionality has been deprecated.
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
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
|CLIENTINFO||string||Specifies the client information for the connecting client. This must be given as the client connection property
|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.|
The following example shows three rows in
SYSTEM.LOCALCONNECTIONS. 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.LOCALCONNECTIONS and it is more readable if displayed this way. The second row represents an example of how a SQL multi-statement might appear in the table with the executionstack providing information about what is being executed. The third row represents the
SELECT * FROM system.localconnections query itself.
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 (or 2.0 seconds), the SQL user is
DBA and the current schema is
USER, and the parameters entered for the query were
AUTOCOMMITFLAGS is set to 7 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
SET OUTPUT VERTICAL;
SELECT * FROM system.localconnections;==================================== 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 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 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.localconnections; COUNT: 4 RUNTIME: 159 USER: DBA SCHEMA: USER NUMPARAM: 0 PARAMS: CONNID: 21474836523 OPEN: 1 HANDLE: 1 OPENRESULTS: 0 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