Monitoring queries and sessionsΒΆ

Kognitio provides tools which allow database admins to view currently active sessions and queries in a system, along with associated information such as the user identities running each query, and other information. These tools are described below.

$f5

A prepared query is provided to view the status of current sessions and queries. This can be accessed in command-line tools such as wxsubmit or wxbashtools, by running $f5; as SQL

# use wxsubmit to connect to a system from Linux, then run $f5 as SQL
wxadmin@mysystem-node1:~> wxsubmit -s localhost sys
>$f5;
Session|User           |Client          |Status          |Dur  |Work GB  |S
     56| SYS           | office-laptop1   | idle           | -   | -       |
==========================================================================
select u.NAME, sc.PASSWD_CHANGE_PRD, (current_date - cast(u
__________________________________________________________________________
     58| SYS           | office-laptop2   | idle           | -   | -       |
==========================================================================
select   cast(to_char(cs.session,'9999999') as varchar(7))

Or from Console, by pressing ctrl+F5, then running the SQL that appears.

In both cases, the query will produce a row for each current query, along with the user running the query, the client machine the user connected from, and other information.

$f9

A prepared query is provided to view the number of connected sessions from each client machine. This can be accessed in command-line tools such as wxsubmit or wxbashtools, by running $f9; as SQL

wxadmin@mysystem-node1:~> wxsubmit -s localhost sys
>$f9;
Server Node                     |Port |Conn |Client
mysystem-node1                  |6550 |    2|office-laptop1
mysystem-node1                  |6550 |    1|mysystem-node1 *
                          Total |     |    3|ssl: 0 / nossl: 3
---------------------------------------
Total Open Connections          |     |    3|ssl: 0 / nossl: 3
---------------------------------------

Or from Console, by pressing ctrl+F9, then running the SQL that appears. An alternative way to access the same information in Console is via the menus view > reports > open connections report.

In both cases, the query will produce a row for each client hostname that is currently connected to the system, along with the number of connections it has, the port it is connecting on, and the server node that the connection was routed through.

Concurrency Monitor

Kognitio Console provides a concurrency monitor, which shows a bar chart of current sessions, including sessions awaiting locks, idle sessions, and all sessions. It can be accessed in Console via the menus view > reports > concurrency monitor.

System Tables

Kognitio stores session and query information in various system tables. These system tables are the basis for tools such as $f5, $f9 and the concurrency monitor, but the tables can also be accessed directly, for example with a custom select query. Some of these tables are described below:

Table

Description

ipe_allcursessions

Contains a row for each active session. Columns include the user identity running the session, the connect time, the last query ran, whether the session has a current query running, and the client hostname, IP that the session connected from.

ipe_allcurtrans

Contains a row for each currently active transaction. Columns are similar to those in ipe_allcursessions

ipe_alllogin

This is a historical table which contains a row for each time a session logged in or out

ipe_command

This is a historical table which contains a row for each complete (i.e. committed or rolled back) query