Logging, Monitoring, and Notifications

Logging

the CData Virtuality Server includes an advanced logging system. The log files are located in the <dvserver>/standalone/log/ folder:

boot.log, which contains bootstrapping messages
server.log, which contains the full log except bootstrapping messages

The <dvserver>/standalone/configuration/logging.properties file and the <subsystem xmlns="urn:jboss:domain:logging:1.1"> section in <dvserver>/standalone/configuration/dvserver-standalone.xml can be used to configure the logging behaviour. Changes in dvserver-standalone.xml overwrite properties in logging.properties. For more details, please refer to Logging.

Logging Translator and User Query CPU Time

A Java virtual machine implementation may support measuring the CPU time for the current thread. The CData Virtuality queries are working within separated threads. So we can measure the CPU time between starting and finishing a query (note that an error or a cancelled query will also have some finishing point) between two points of a working thread. The CPU time provided by a JVM interface has nanosecond precision.

To start logging the CPU time for every query, add the org.teiid.COMMAND_LOG log category by doing one of the following

Edit the <subsystem xmlns="urn:jboss:domain:logging:1.1"> section in <dvserver>/standalone/configuration/dvserver-standalone.xml:

XML

<logger category="org.teiid.COMMAND_LOG">
    <level name="DEBUG"/>
</logger>

or

Run the following CLI command:

SQL

CALL SYSADMIN.executeCli(script => '/subsystem=logging/logger=org.teiid.COMMAND_LOG:add(level=DEBUG)');;

Logging SSH Tunnel Usage

SSH Tunnel usage may be logged and monitored by adding an SSHTunnelHandler logger using the following CLI command:

SQL

CALL SYSADMIN.executeCli(script => '/subsystem=logging/logger=com.datavirtuality.dv.core.teiid.sshtunnel.SSHTunnelHandler:add(level=DEBUG)') ;;

Logging of the `ALTER VIEW` command

To turn on the logging of the ALTER VIEW command, run the following CLI command:

CODE

CALL SYSADMIN.executeCli('/subsystem=logging/logger= com.datavirtuality.dv.core.teiid.common.ViewProcDependencyGraphHandler:add(level=TRACE)') ;;
CALL SYSADMIN.executeCli('/subsystem=logging/logger=com.datavirtuality.dv.core.teiid.view.AlterViewPlan:add(level=TRACE)') ;;
CALL SYSADMIN.executeCli('/subsystem=logging/logger=com.datavirtuality.dv.core.teiid.view.ViewDefinitionHandler:add(level=TRACE)') ;;

System Tables for Monitoring

As it is possible to access the system tables and procedures via JDBC or ODBC, most monitoring systems can easily check them using simple SQL queries against system tables. In this section, we list tables that are particularly useful for this.

`SYSADMIN.ScheduleJobRun`

This table can get information on failed jobs and long-running queries.

Checking for Failed Jobs

To check for failed jobs, look for status = 'failed' and get the failureReason.

Checking for Long-running Queries

You can check if queries are running longer than a specified time using the following call (in this example, we are checking for queries running for more than 60 seconds):

SQL

SELECT sqlCommand, startTime, endTime, status, failureReason FROM SYSADMIN.ScheduleJobRun WHERE TIMESTAMPDIFF(SQL_TSI_SECOND, startTime, endTime) > 60

For other time intervals to check, please refer to the chapter Date and Time Functions, section TIMESTAMPDIFF.

`SYSADMIN.Queries`

This table monitors all queries that have been issued against CData Virtuality Server. This table, like the one in the previous example, can be used to check for long-running queries:

SQL

SELECT query, startTime, endTime, status, failureReason FROM SYSADMIN.Queries WHERE TIMESTAMPDIFF(SQL_TSI_SECOND, startTime, endTime) > 60

For other time intervals to check, please refer to the chapter Date and Time Functions, section TIMESTAMPDIFF.

`SYSADMIN.getAllDataSourcesMetadataDiff()`

This table returns metadata differences for all data sources, thus enabling you to check if Source Systems have changed the metadata. For more information, please refer to System Procedures for details.

Process Monitoring

For monitoring if the server is running, you have two possibilities:

You can write the process ID to a file, start the server (please note that you will have to edit the start script in <dvserver>/bin/dvserver.[bat|sh] for that), and grep for the process ID;
Or you can try to connect the server and query a predefined test view returning TRUE or any status information needed by the monitoring tool.