Goal

This document explains how to use the predefined LOGCONTROLLER stored procedure and some useful log categories.  

Content

Denodo Virtual DataPort uses the logging library Apache Log4j to log the activity of the Virtual DataPort serverl. Log4j allows defining several categories on which the log level will be specified independently.

By default, the Virtual DataPort Server logs are stored in <DENODO_HOME>/logs/vdp and the Administration Tool logs in <DENODO_HOME>/logs/vdp-admin. The configuration of Log4j of the Virtual DataPort server is controlled by the file log4j2.xml (<DENODO_HOME>/conf/vdp) and for the Administration Tool by the file log4j.xml (<DENODO_HOME>/conf/vdp-admin). Refer to article Log column details to understand the content of the logs.

 

You can change the log level of a category in the VDP server without restarting it by calling the stored procedure “LogController”. This stored procedure changes the log level for a certain log category of the logging engine used by Virtual DataPort at run time. The changes performed by this procedure are lost when the Server is stopped. Administration privileges are required to call this stored procedure.

 

Syntax: LogController (<logCategory>, <logLevel>)

• logCategory – the category of the log. The package of the Java classes to log information from. You can see some of the lists of log categories in log4j2.xml file.
• logLevel – type of message to be logged for the category: ‘TRACE', 'DEBUG', 'INFO', ‘WARN', 'ERROR', 'FATAL'. 

Log categories

The log categories have a hierarchical structure and when a log level is set for a category, all its subcategories will also be set to the same log level.

 

Though all the classes come under the “com.denodo” category, it is not advisable to set the log level at this category as this would print all the log information of its subcategories, which in addition to occupying more space, could also delay the debugging process.

This document contains some useful scenarios categorized as Northbound Connections, Southbound Connections, Authentication, Cache and Performance that can be used during a troubleshooting process. Note that, after making changes to the log levels, it is recommended and important to revert back to the default level (i.e) ERROR after the troubleshooting is complete. In case you forgot the log levels set for different categories you can use the Predefined Stored Procedure GET_ACTIVE_LOGGERS that returns information about the log categories that have been set in the Virtual DataPort server.

Northbound Connections

Enable vdp-requests log

The vdp-requests.log will log information about all the query requests received by the Virtual DataPort. To set the log category for logging the requests, execute the below from the VQL shell

CALL LOGCONTROLLER('com.denodo.vdp.requests', 'INFO')

Enable vdp-queries.log

The vdp-queries.log contains the VQL requests with more detailed information such as which user has executed what query, the total duration of the query, etc., and just like, vdp-requests.log, it has to be manually enabled by executing the below from the VQL shell.

CALL LOGCONTROLLER('com.denodo.vdb.management.mbeans.request.RequestsManagementInfo', 'INFO')

Enabling logs for ODBC connections

When querying the Virtual DataPort from an ODBC client, the below combination of log categories can be set to debug any issues/ errors.

 

CALL LOGCONTROLLER('com.denodo.vdp.requests','INFO'); CALL LOGCONTROLLER('com.denodo.vdb.vdbinterface.server.odbc','TRACE')

Enabling OAuth logs for REST/SOAP WS published from Virtual DataPort

The following log category can be set to diagnose problems when accessing the web services deployed in Denodo using OAuth2.0 authentication.

CALL LOGCONTROLLER('com.denodo.vdb.security.OauthAuthenticator', 'DEBUG')

Southbound Connections

To see the SQL sentence Denodo sends to a JDBC data source

The below log category can be set to log the prepared SQL sentence as seen in the execution trace of a view/ query execution.

CALL LOGCONTROLLER('com.denodo.vdb.engine.wrapper.raw.jdbc.adapter.DriverAdapter', 'DEBUG')

Enabling error tuples log in Delimited Files Sources

The below log category can be set to log more information about query execution against a DF data source.

CALL LOGCONTROLLER('com.denodo.vdb.engine.wrapper.raw.df.DataExtractor.contentMatcher', 'INFO')

HTTP route

This log category can be set to debug HTTP data sources in order to see more details about the HTTP request such as headers etc.,

CALL LOGCONTROLLER('httpclient.wire','TRACE')

To get the Data Source access information

This log category can be used to get the data source access information. Replace [datasourcetype] with any of the below:

arn, df, essbase, gs, html (itpilot), jdbc, json, ldap, custom, odbc, olap, salesforce, sapbwbapi, saperp, ws or xml

CALL LOGCONTROLLER ('com.denodo.vdb.engine.wrapper.raw.[datasourceType]','DEBUG')

Authentication and Security

Debugging LDAP Configuration

Refer to Debugging the LDAP configuration section from article How to configure a VDP database with LDAP authentication and LDAP authentication best practices for debugging steps

SSL related troubleshooting

First step is to add the property ‘-Djavax.net.debug=all’ to the JVM configuration. To do that,

• Stop the Virtual Dataport Server
• Append the following property to the JVM configuration of Virtual DataPort Server using the Denodo Platform Control Center by going to Configure > JVM options > Virtual DataPort server or by editing the file <DENODO_HOME>/conf/vdp/VDBConfiguration.properties at the property java.env.DENODO_OPTS_START

-Djavax.net.debug=all

• Execute the <Denodo_Home>/bin/regenerateFiles.sh (.bat for Windows)
• Restart the Virtual DataPort Server.
• Then, open the VQLShell and execute the following log category

CALL LOGCONTROLLER('com.denodo.util.logging.JavaConsoleLogging', 'DEBUG')

Kerberos troubleshooting

Kerberos debug modes for Northbound, Southbound and most common issues are addressed in the article Kerberos configuration and troubleshooting.

Privilege auditing

To debug the permissions assigned to a view for each user and role the following log category can be enabled:

CALL LOGCONTROLLER('com.denodo.vdb.security.queryPrivileges', 'DEBUG')

Cache

Enable Cache logs

Use the following log category to enable cache logs

CALL LOGCONTROLLER('com.denodo.vdb.cache', 'DEBUG')

Bulk Data Load

Enable the following log category if Virtual Dataport runs into error during Bulk Data load. Execute the cache load query with necessary CONTEXT clauses after setting this log category.

CALL LOGCONTROLLER('com.denodo.vdb.util.tablemanagement.sql.insertion', 'TRACE')

Table insertion

This combination of log categories can be set to debug the cache table insertion errors.

CALL LOGCONTROLLER('com.denodo.vdb.util.tablemanagement.TableManager', 'DEBUG') CALL LOGCONTROLLER('com.denodo.vdb.util.tablemanagement.sql.insertion', 'DEBUG')

Cache maintenance task

To debug any issues with the cache maintenance task, use the below log category

CALL LOGCONTROLLER('com.denodo.vdb.cache.CacheMaintainerTask', 'DEBUG')

Performance

Execution trace

To see the execution trace of the queries executed on the VDP server the following log category can be enabled. For more information see Execution Trace of a Statement.

CALL LOGCONTROLLER('com.denodo.vdp.traces', 'DEBUG')

Cost-based optimizer

For debugging the statistics generation process while gathering statistics for JDBC base view, the below log category can be set.

CALL LOGCONTROLLER('com.denodo.util.jdbc.sql','TRACE')

Insufficient resources

This log category can be used to debug the scenarios where it says insufficient resources to execute an operation. For example, java.lang.NoClassDefFoundError: Could not initialize class

CALL LOGCONTROLLER('com.denodo.vdb.interpreter.execution.Action', 'WARN')

Memory usage of a query execution

This log category can be set to log information about the memory usage of a query execution that can be useful in assessing the performance of a query. For example, in case, you are executing a query with a memory limit = 2000MB, you can retrieve information like,

Search method plan ready and selected to run: sm-0  

checkMemoryUsage - Max memory: 2.0 GB  

checkMemoryUsage - Query size: 0 B  

checkMemoryUsage - SwapGroups: 5  

checkMemoryUsage - SwapSizes: [671088640, 637534208, 385875968, 201326592, 134217728]  

checkMemoryUsage - Max swap size: 640.0 MB  

checkMemoryUsage - Total queues: 945.0 MB  

checkMemoryUsage - Query max size: 1.5 GB  

checkMemoryUsage - Query memory usage below threshold  

CALL LOGCONTROLLER('com.denodo.vdb.catalog.view.View','DEBUG')

Cost estimation

This combination of log categories can be set when cost optimization is applied during a view execution and the factual details about the optimizations applied.

CALL LOGCONTROLLER('com.denodo.vdb.engine.costoptimizer.LeafCostCalculator','DEBUG') CALL LOGCONTROLLER('com.denodo.vdb.engine.costoptimizer.CostOptimizerQueryVisitor','DEBUG')

Transformations candidates costs

To see the final transformations candidates costs only applied to a query, use the log category

CALL LOGCONTROLLER('com.denodo.vdb.catalog.view.visitor.TreeViewTransformer','DEBUG')

Virtual layer and other scenarios

VCS

To debug any issues related to the Version control system, use the below log category

CALL LOGCONTROLLER('com.denodo.vdb.interpreter.execution.vcs', 'DEBUG')

SWAP

To get information about swapping use the below log category

CALL LOGCONTROLLER('com.denodo.vdb.engine.swap', 'DEBUG')

Stored Procedures

To enable stored procedure logs  use the below log category

CALL LOGCONTROLLER('com.denodo.vdb.engine.storedprocedure','DEBUG' )

Compiling classes

When using the WSDL2Java Axis library, the WSDL file is translated into Java classes, which encapsulates the SOAP operations and messages. For obtaining the errors while compiling these classes, you can enable the following logging category,

CALL LOGCONTROLLER('com.denodo.util.compile', 'DEBUG')

SOAP Requests / Responses

Denodo obtains tuples from a web service response (i.e., from the objects obtained from Apache Axis library/ from the underlying SOAP response message). For obtaining information about SOAP requests/responses, you can enable the following logging categories,

CALL LOGCONTROLLER('org.apache.axis.client','DEBUG') CALL LOGCONTROLLER('org.apache.axis.transport.http','DEBUG')

Derby locks

Derby attempts to prevent two JVMs from accessing a database at one time by use of a db.lck file. In scenarios, where you face issues with the booting up of this database, you can debug the issue by setting the below log category

CALL LOGCONTROLLER('com.denodo.vdp.derby','DEBUG')

References

Virtual DataPort Administration Guide: Configuring the Logging Engine

Advanced VQL Guide: LOGCONTROLLER