_IV_TABLE_COLUMNS and _IV_VIEWED_TABLE_COLUMNS System Queries

_IV_LOGGED_USERS System Query

The system query _iv_logged_users result contains one row for each client thread on the server. One thread exists for each connected user. System threads are executed by the server itself and separate threads are executed by the CALL DETACHED command.

When viewing threads in the Monitor windows (Clients and threads tab) thread types are displayed in different colors. Normal client threads are black, independent SQL procedures threads are red and system threads are blue.

These columns are present in this query result:

Column name	Type	Contents
`Login_name`	`CHAR(31)`	Name of the user that is connected (for system threads DB_ADMIN or ANONYMOUS)
`Client_number`	`INT`	Internal number of the client (from the `Client_number` function). Corresponds with the `%c` switch in the log rules (function `_sqp_define_log`).
`Sel_schema_name`	`CHAR(31)`	Name of the opened schema
`State`	`INT`	State of the client process (see below, normal client state = 4)
`Connection`	`INT`	Client connection method (0 = direct, 1 = TCP/IP, 4 = HTTP-tunnel)
`Net_address`	`CHAR(30)`	Network address and the port number of the client (empty for direct access clients)
`Detached`	`BOOLEAN`	A flag describing a separate thread (executed by the `CALL DETACHED` statement)
`Worker_thread`	`BOOLEAN`	A flag describing a system thread that does not process a client request or a separate thread (detached)
`Own_connection`	`BOOLEAN`	A flag describing thread ownership based on who called this query
`Transaction_open`	`BOOLEAN`	A flag describing that the thread has an open transaction
`Isolation_level`	`INT`	The level of transaction isolation set for the client (1 = read committed, 3 = serializable)
`Sql_options`	`INT`	The sum of SQL compatibility flags set for this client by the `Set_sql_option` function or taken from the console settings
`Lock_waiting_timeout`	`INT`	The wait limit for locks placed by other clients (in tenths of seconds), -1 = forever
`Comm_encryption`	`INT`	Greater than zero if the thread belongs to a client whose communication with the server is encrypted, unsecured communication is marked as `Plain`
`Session_number (Session)`	`INT`	A unique identifier of a client thread. Unlike the client number in the `client_number` column, these numbers do not repeat when the 602SQL Server is running. Corresponds with the `%e` switch in the log rules (function `_sqp_define_log`).
`Profiled_thread (Profiled)`	`BOOLEAN`	Profiling is enabled for each thread explicitly, if it is enabled for all threads globally, this value equals FALSE
`Thread_name`	`CHAR(63)`	A thread name set by the `_sqp_set_thread_name` procedure; "common" clients are marked automatically (e.g. GUI client, ODBC client, debug client)
`Req_proc_time (Processing)`	`INT`	A time spending by processing actual request
`Proc_sql_stmt (SQL statement)`	`CLOB`	If a thread request was an SQL statement, its text is here, empty in other cases

The State column values have the following meaning:

0	The thread is running.
1	The thread is waiting for a memory page lock.
2	The thread is waiting to release a page in the drive cache.
3	The thread is waiting for a record lock.
4	The thread is waiting for another client request (normal state).
5	The thread is sending a request result.
6	The thread is receiving a new client request.
7	The thread is terminating.
8	The thread is waiting for a semaphore (from the `Wait_for_semaphore` function).
9	The thread is sleeping (from the `Sleep` function).
10	The thread is waiting for an event (from the `Wait_for_event` event).
(>100)	In certain situations values are >100. This signals an internal state. These values can only be interpreted by Software602 Technical Support.

A user thread who executed this query has a state value equal to 0 (> 100 is not an error).

Example:

Display users (not working threads) that are connected to the Property application:

SELECT Login_name
FROM  _iv_logged_users
WHERE sel_schema_name = 'Property' AND NOT worker_thread

System Queries

_IV_TABLE_COLUMNS and _IV_VIEWED_TABLE_COLUMNS System Queries