s-Server Features for Managing and Monitoring

<< Click to Display Table of Contents >>

Navigation:  Administering SQLstream Blaze > Monitoring s-Server and Components Overview >

s-Server Features for Managing and Monitoring

Previous pageReturn to chapter overviewNext page

This section describes system views and system procedures useful to a system administrator for managing and monitoring a running SQLstream system.

All system views and system procedures described here are located in the schema SYS_BOOT.MGMT. The code for the system procedures is in the java package net.sf.farrago.syslib). The SQL command ALTER SYSTEM can also modify various system parameters. For more details, see the topic ALTER SYSTEM in the s-Server Streaming SQL Reference Guide.

Monitoring views

View

Description

SESSIONS_VIEW

Displays all active sessions.

Each row describes a session, giving a unique session id, and

such attributes as the jdbc connection URL, the user, the transaction status, etc.

STATEMENTS_VIEW

Displays all executing statements. Each statement has a row,

which gives a unique statement id, and such attributes as the containing session

(as its session id, from the sessions_view), the start time, the SQL being executed,

the current value of any dynamic parameters, etc.

SESSION_PARAMETERS_VIEW

Displays private parameter settings for the current session

REPOSITORY_PROPERTIES_VIEW

Displays properties of the MDR repository used to store Farrago's catalog.

OBJECTS_IN_USE_VIEW

Displays which objects are in use by which statements.

PAGE_COUNTS_VIEW

Displays statistics about tables, columns, and indexes

ROW_COUNTS_VIEW

Displays statistics about tables, columns, and indexes

HISTOGRAMS_VIEW

Displays statistics about tables, columns, and indexes

HISTOGRAM_BARS_VIEW

Displays statistics about tables, columns, and indexes

SEQUENCES_VIEW

Displays the state of generated columns

Scripts

Some of these views are used in scripts, such as the support scripts with extension .sql found in the SQLSTREAM_HOME/support/sql directory for use in querying your database schemas and configurations. Other examples of scripts appear in the demo subdirectories of the SQLSTREAM_HOME/demo directory.

In SQLline you can use the !run command to execute any such script, using the following template:

!run <support script name>.sql

 

For more details on using SQLline, see the topic SQLline in the s-Server Getting Started Guide.

Most script names describe what they do, and some scripts use the views named above. Here are three examples:

!run showSessions.sql

This command uses the SESSIONS_VIEW in running the following command:

select id, system_user_name, session_user_name,url from sys_boot.mgmt.sessions_view;

!run showStatements.sql

This command uses the STATEMENTS_VIEW in running the following command:

select * from sys_boot.mgmt.statements_view;

select * from table(sys_boot.mgmt.session_parameters());

This command uses the SESSION_PARAMETERS_VIEW in running the following command:

select * from sys_boot.mgmt.statements_view;

 

The SQLSTREAM_HOME/support/sql directory includes the following support scripts:

showFennelConfig.sql

showFarragoConfig.sql

showForeignServers.sql

showForeignStreams.sql

showProcedureColumns.sql

showProcedures.sql

showPumps.sql

showSchemas.sql

showSessionParams.sql

showSessions.sql

showStatements.sql

showSystemInfo.sql

showTableColumns.sql

showTableColumnsAll.sql

showUsers.sql

Management UDXs (user defined transforms)

UDX

Description

REPOSITORY_INTEGRITY_VIOLATIONS()

Checks for integrity violations in the MDR repository.

THREADS

Lists threads running in the JVM.

UDX THREAD_STACK_ENTRIES

Dumps the stack for each thread

UDX SYSTEM_INFO

Retrieves information about the environment in which

SQLstream is running (e.g., environment variables,

system properties, and OS and hardware information).

UDX PERFORMANCE_COUNTERS

Monitors performance counters such as cache hit ratios.

Utility functions

Function

Description

SLEEP

Can be used to introduce an artificial delay into a SQL statement.

Management Procedures

Procedure

Description

FLUSH_CODE_CACHE

Discards all entries from the global code cache.

STAT_SET_ROW_COUNT

Synthesizes an artificial row count for a table

STAT_SET_PAGE_COUNT

Synthesizes an artificial page count for an index

STAT_SET_COLUMN_HISTOGRAM

Synthesizes an artificial histogram for a column

KILL_STATEMENT

An administrator can kill an executing statement by calling the system procedure

KILL_STATEMENT(statement_id) which terminates a single statement, specified by id

(as seen in STATEMENTS_VIEW).

 

Killing a statement aborts its execution and frees all resources allocated to the statement. A corresponding JDBC Statement will be invalidated, and any JDBC ResultSet will be terminated (with an SQLException to indicate that the statement was killed from outside.)

KILL_STATEMENT_MATCH

KILL_STATEMENT_MATCH(sql fragment) terminates all statements whose

sql commands contain the provided text.

KILL_SESSION

An administrator can kill an active session by calling the system procedure KILL_SESSION(id).

The session is specified by the id that appears in the sessions_view.

Killing a session kills all statements executing in the session, aborts any transactions, and frees all allocated resources. A corresponding jdbc Connection is invalidated: any further use gets an SQLException.

(However, if the optional boolean cancel_only parameter is specified as true for any of the kill calls, then the effect is just to request an asynchronous cancel and return immediately; no wait or resource release is performed, and the session or statement remains valid.)