Getting the SQL query delegated by a derived view

Applies to: Denodo 8.0 BETA , Denodo 7.0 , Denodo 6.0
Last modified on: 20 May 2020
Tags: Combination ODBC data sources JDBC data sources

Download document

You can translate the document:

Content

When working with views created on top of JDBC and ODBC data sources, the Virtual DataPort query engine will push down SQL queries to the data sources to retrieve the data requested in the queries.

The information about the SQL query delegated by a view to any of these data sources is available in the execution trace of a query.

Since Denodo 8.0, the execution trace is automatically retrieved for all queries executed by the Virtual DataPort Administration Tool or by the Design Studio. For older versions of the Denodo Platform, you need to add the TRACE clause manually or select the option "Execute with TRACE" in order to retrieve the trace.

Once a query finishes, in order to see the execution trace:

  1. Click on the "Execution Trace" tab.

  1. In the Execution Trace window, on the left side a tree with the execution plan is displayed.
  2. Both for JDBC and ODBC wrappers, the bottom node of the execution plan for the branch associated to those data sources will be a JDBC route type of node. Click on <Jdbc Route>.

  1. After selecting the node, on the right side of the execution trace windows, you will see Properties and Advanced Properties that you could expand by clicking on the “+” symbol. These properties show you information about the JDBC connection, including the query sent to the database next to the "SQLSentence" label.

Getting the SQL query of a query that has conditions

If your query is more complex and has conditions, depending on your data source configuration, it can happen that you see a SQL Sentence with question marks instead of the input values. The reason is that your data source configuration has the option Allow literal as parameter enabled. When you have that option enabled, the literals will be send as parameters and the provided values for the condition are listed separately in the parameters field below the SQL Sentence:

In such a case, you could either copy manually the different values from the below field into the SQL Sentence and insert them into the correct places. However, you could also temporarily change the setting of the data source in order to retrieve the full sql sentence without manually copying the values.

For this, you would need to use the Virtual DataPort Administration Tool in order to edit the Source Configuration of the data source. Follow these steps:

  1. Open the data source corresponding to the view in the Virtual DataPort Administration Tool.
  2. Navigate to Configuration > Source Configuration > Allow literal as parameter.
  3. Set that option to No and save the data source.
  4. Execute the query again.
  5. You should see now the full query with the provided values instead of the question marks:

  1. After copying the SQL Sentence, do not forget to switch the value of the property Allow literal as parameter back to Yes and to save the view.

References

Virtual DataPort Administration Guide: Execution Trace of a Statement

Virtual DataPort Administration Guide: Data Source Configuration Properties

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training