Virtual DataPort Monitors¶
There are several monitors that are specific to Virtual DataPort servers (not for the other modules of the Denodo Platform):
The information provided by these monitors is aimed at:
Expert administrators and power users
Denodo tools like the Diagnostic & Monitoring Tool or the Automatic Summary Recommendations.
The data generated by the Virtual DataPort Queries Monitor and Virtual DataPort Query Data Sources Monitor is especially useful for FinOps monitoring and can be used to create custom reports using the Denodo Dashboard.
The following sections describe the information stored by these monitors.
Virtual DataPort Queries Monitor¶
It logs all the statements processed by Virtual DataPort, not just queries. It is specific to Virtual DataPort and cannot be used to monitor other Denodo servers.
The output is logged to denodo-monitor/logs/vdp-queries.log and in table request_notification if a JDBC database is configured.
The following table describes the information generated by this monitor:
Column name |
Description |
|---|---|
ServerName |
Logical name of the logged server. The default value is vdp. |
Host |
Host name or IP address of the Virtual DataPort server is being monitored. |
Port |
JMX port number of the Virtual DataPort server is being monitored. |
Id |
Identifier of the query executed by the client in the Virtual DataPort server. This identifier is unique for each SessionId. |
Database |
Name of the Virtual DataPort database where the query was executed. |
UserName |
Name of the Virtual DataPort user that executed the query. |
NotificationType |
Type of notification that triggered the query execution. It can be one of the following values: startRequest or endRequest. |
SessionId |
Identifier of the session where the query was executed. This identifier is unique for each ServerName. |
StartTime |
Date and time when the query started the execution. |
EndTime |
Date and time when the query finished the execution. |
Duration |
Duration of the query execution in milliseconds. |
WaitingTime |
Time in milliseconds that the query spent waiting to be executed. |
NumRows |
Number of rows returned by the query. |
State 1 |
Status of the query execution. |
Completed |
Indicates whether the query execution has finished or not. It can be one of the following values: true or false. |
Cache |
Indicates whether the query was executed using the cache or not. It can be one of the following values: true or false. |
Query |
VQL query executed in the Virtual DataPort server. |
RequestType 2 |
Type of request executed by the Virtual DataPort server. |
Elements |
Elements used in the query: views| base views| stored procedures| etc. |
UserAgent |
User-agent of the client that executed the query. |
AccessInterface |
Name of the access interface used by the client to execute the query. |
ClientIP |
IP address of the client that executed the query. |
TransactionId |
Unique identifier of the transaction where the query was executed. |
WebServiceName |
Name of the web service that executed the query. |
EstimatedQueryCost |
When the cost optimizer is enabled and view statistics are available, this value is the estimated cost calculated by the optimizer for the whole query execution. |
GlobalSecurityPoliciesApplied |
(Denodo 9): Global Security Policies applied to the query. |
Virtual DataPort Query Data Sources Monitor¶
While Virtual DataPort Queries Monitor collects general information about a query, the Query Data Sources Monitor provides details about the access to the different data sources including the number of rows read from the data source, the response time, and the cloud provider (if any) where the data source is located. This information can then be used for two things:
Performance monitoring: to identify bottlenecks as it is possible to check what data sources are taking more time and identify operations that were not pushed to a data source among other things.
FinOps monitoring: it is possible to specify the cloud provider and region hosting the data sources (available for JDBC, custom, JSON, XML and delimited files), the Denodo servers and the clients accessing Denodo. This information is included in this monitor along with other data that can be relevant to make cost estimations on the cloud usage. For instance, the estimated number of bytes read in the data source or the number of rows transferred and the estimated size of each row. This allows to answer queries like what is the estimated amount of data transferred from a specific cloud provider or who are the teams causing more data traffic from a certain cloud provider and region.
The output is logged to denodo-monitor/logs/vdp-querydatasources.log and in table query_datasources_notification if a JDBC database is configured.
This log contains information about the three actors that participate in a query:
Data consumer sending the query. It includes properties identifying the client like ClientIP, ClientProvider or UserAgent.
The Virtual DataPort server. It includes properties like ServerName, Host or VDPProvider.
The data source Virtual DataPort needs to access to gather data or insert data. e.g., DataSourceName, DataSourceProvider.
In addition, the information included in this log can be clasified in:
Northbound: Properties related to the connection between the client and Denodo. e.g., QueryId, QueryStartTime, QueryResultRows.
Southbound: Properties related to the connection between Denodo and the data source. e.g., StartTime, Query, NumRowsToVDP.
The following table offers a detailed description of all properties included in the log.
Column name |
Description |
|---|---|
ServerName |
Logical name of the logged server. The default value is vdp. |
Host |
Host name or IP address of the Virtual DataPort server is being monitored. |
Port |
JMX port number of the Virtual DataPort server is being monitored. |
VDPProvider |
Name of the infrastructure provider (On-premise, AWS, Azure, Google Cloud, Alibaba, etc.) where the Virtual DataPort server is running. |
VDPRegion |
Name of the region of the infrastructure provider where the Virtual DataPort server is running. |
SessionId |
Identifier of the session where the query was executed. This identifier is unique for each ServerName |
QueryId |
Identifier of the query executed by the client in the Virtual DataPort server. This identifier is unique for each SessionId. |
QueryState 1 |
Status of the request executed in the Virtual DataPort server. |
RequestType 2 |
Type of request executed by the Virtual DataPort server. |
QueryStartTime |
Date and time when the VQL query started the execution in the Virtual DataPort server. |
QueryEndTime |
Date and time when the VQL query finished the execution in the Virtual DataPort server. |
DatabaseName |
Name of the Virtual DataPort database where the query was executed. |
UserName |
Name of the Virtual DataPort user that executed the query. |
AccessInterface |
Name of the access interface used by the client to execute the query in the Virtual DataPort server. |
UserAgent |
User agent of the client that executed the query. |
ClientIP |
IP address of the client that executed the query. |
ClientProvider |
Name of the infrastructure provider (On-premise, AWS, Azure, Google Cloud, Alibaba, etc.) where the client is running. |
ClientRegion |
Name of the region of the infrastructure provider where the client is running. |
QueryResultRows |
Number of rows returned by the query. |
EstimatedQueryResultRowSize |
Estimated size in bytes of a row returned by the query. It contains the average row size based on a sample of ten rows. |
DataSourceDatabaseName |
Database name of the data source that the Virtual DataPort server accessed to execute the VQL query. |
DataSourceName |
Name of the data source that the Virtual DataPort server accessed to execute the VQL query. |
DataSourceType 3 |
Type of the data source that the Virtual DataPort server accessed to execute the VQL query. |
DataSourceAdapter |
Data source connector that the Virtual DataPort server used to execute the VQL query. For example, Oracle, DB2, Snowflake, Databricks, etc. |
StartTime |
Date and time when the query started the execution in the data source. |
EndTime |
Date and time when the query finished the execution in the data source. |
ResponseTime |
Time it took for the Virtual DataPort server to receive the first row of the result of the query it delegated to the data source. |
Query |
Query executed in the data source. |
State |
Status of the query executed in the data source. |
Exception |
Exception thrown by the data source when executing the query. |
DataSourceProvider |
Name of the infrastructure provider (On-premise, AWS, Azure, Google Cloud, Alibaba, etc.) where the data source is running. |
DataSourceRegion |
Name of the region of the infrastructure provider where the data source is running. |
NumRowsToVDP |
Number of rows returned by the data source to the Virtual DataPort server. |
EstimateRowSizeToVDP |
Estimated size in bytes of a row returned by the data source to the Virtual DataPort server. It contains the average row size based on a sample of ten rows. |
NumRowsFromVDP |
Number of rows the Virtual DataPort server inserted in the data source. |
EstimateRowSizeFromVDP |
Estimated size in bytes of a row the Virtual DataPort server inserted in the data source. |
NoDelegationCauses |
Causes why the Virtual DataPort server did not delegate the query to the data source. |
MemoryLimitReached |
Indicates whether the Virtual DataPort server reached the memory limit when executing the query in the data source. Possible values: true or false. |
NestedJoinRightAccess |
Indicates whether the Virtual DataPort server executed a nested join. Possible values: true or false. |
NestedTotalRightAccesses |
Number of times the Virtual DataPort server executed a nested join. |
EstimatedNumRowsReadInSource |
Estimated number of rows read in the data source. |
EstimatedNumBytesReadInSource |
Estimated number of bytes read in the data source. |
EstimatedSourceCost |
When the cost optimizer is enabled and view statistics are available, this value represents estimated cost calculated by the optimizer for the fragment of the query that is delegated to this data source. |
- 1(1,2)
Possible values: OK, STOPPED, ERROR, PROCESSING, ROW_LIMIT_REACHED, CONNECTION_ERROR, THREAD_TIMEOUT, QUERY_TIMEOUT, LICENSE_QUERY_TIMEOUT, INIT, WITHOUT_RESULT, NO_CREATED_ACCESS, TOO_MANY_WORKS_TIMEOUT, PARSE_ERROR, INVALID_ROUTE, INTERRUPTED_CONNECTION, INTERFACE_NOT_IMPLEMENTED, ENOUGH_ROWS_RETRIEVED, INCOMPATIBLE_QUERY_VIEW, CACHE_LOAD_ERROR, UNKNOWN.
- 2(1,2)
Possible values: SELECT VIEW, SELECT BASE VIEW, CALL PROCEDURE, SELECT SUMMARY VIEW, INSERT, INSERT INTO, UPDATE, DELETE, CREATE REMOTE TABLE, CREATE SUMMARY RECOMMENDATIONS, SELECT INTO, QUERY WRAPPER, UNKNOWN.
- 3
Possible values: JDBC, ODBC, VDB, WS, XML, DF, CUSTOM, LDAP, JSON, SAPERP, SAPBW, SAPBWBAPI, OLAP, ESSBASE, SALESFORCE
Virtual DataPort Cache Monitor¶
It logs the activity of the processes that load the Virtual DataPort cache.
The output is logged to denodo-monitor/logs/vdp-loadcacheprocesses.log.
Virtual DataPort Connections Monitor¶
It logs information about opened and closed connections, user account that opened/closed the connection, the interface it used (JDBC, ODBC, web service, etc.), from which IP, etc.
The output is logged to denodo-monitor/logs/vdp-connections.log.
Virtual DataPort Data Sources Monitor¶
Every certain number of seconds (determined by the interval property), it logs information about
all the data sources of the Virtual DataPort server. In addition, it can
ping periodically some data sources and register the result.
The output is logged to denodo-monitor/logs/vdp-datasources.log.
Virtual DataPort Query Blocks Monitor¶
When enabling this monitor, also enable the “Queries Monitor”. This monitor logs details of the statements processed by Virtual DataPort. The Summary Recommendations Tool uses this information combined with the information of the Virtual DataPort Queries Monitor.
The output is logged to denodo-monitor/logs/vdp-queryblocks.log.
Logging Headers
Every time the Denodo Monitor starts monitoring or has to acquire a new connection to the server, the following header is included in the logs generated by the Server and Virtual DataPort monitors:
Logging started at: 2020-02-03T15:29:17.411. Denodo Monitor 7.0 started at: 2020-02-03T15:29:16.925. VDP Server 7.0 update 20190903 started at: 2020-02-03T14:36:24.260
This header provides timestamp information about:
Logging. Instant when the monitor started logging. It is different among monitors due to their different polling intervals.
Denodo Monitor. Instant when the Denodo Monitor started monitoring. It is the same value for all the monitors.
Virtual DataPort Server. Instant when Virtual DataPort Server was launched.
When the logs contain several logging headers, you can detect important events by comparing their values:
If Virtual DataPort Server start time changes, then Virtual DataPort Server was restarted.
If Denodo Monitor start time changes, then Denodo Monitor was restarted.
If both dates remain the same, then the connection with the server was lost.