Query gets modified by Tibco Data Virtualization(TDV/Composite) Studio at runtime by adding RTRIM(upper) in the Query which can cause performance issues. Shows different query in TDV Studio and different when viewed in database SQL run history.
Article ID: KB0081341
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | ALL |
Description
The query gets modified by TIBCO Data Virtualization(TDV/Composite) Studio shows different query in TDV Studio and different when viewed in database SQL run history ( adds RTRIM(upper) in the TDV query). When a user has a large database and query which runs very fast when executing directly against the DB using Toad/SQL Developer, it may not return results or can take hours in TDV to run due to the query getting converted in TDV view. This may happen if the user has indexes built on the column used in joins and when the query gets changed in TDV. It does a full scan of the tables and makes it slow.
Symptoms:
Query running for longer time in TDV than directly running in the database client.
Symptoms:
Query running for longer time in TDV than directly running in the database client.
Issue/Introduction
Query gets modified by Tibco Data Virtualization(TDV/Composite) Studio at runtime by adding RTRIM(upper) in the Query which can cause performance issues. Shows different query in TDV Studio and different when viewed in database SQL run history.
Environment
All Supported Operating Systems
Resolution
1). Modify the query as per the values in the data source like the following:
SELECT {option IGNORE_TRAILING_SPACES="False", CASE_SENSITIVE="True" }
*
FROM
/shared/"ViewName"
The values for flags “IGNORE_TRAILING_SPACES” and “CASE_SENSITIVE” should match the values you see in the data source, .e.g., if the values in datasource created for these flags are IGNORE_TRAILING_SPACES="False", CASE_SENSITIVE="True" , then the values in the query should be same.
2). Modify the configuration setting for Case Sensitivity and Ignore trailing spaces from Administration >> Configuration >> TDV Server >> SQL Engine >> SQL Language same as that is set in your data source.
Once the configuration settings are modified , go to the data source and refresh the Resource capabilities to apply the changes and then try to run the SQL again.
SELECT {option IGNORE_TRAILING_SPACES="False", CASE_SENSITIVE="True" }
*
FROM
/shared/"ViewName"
The values for flags “IGNORE_TRAILING_SPACES” and “CASE_SENSITIVE” should match the values you see in the data source, .e.g., if the values in datasource created for these flags are IGNORE_TRAILING_SPACES="False", CASE_SENSITIVE="True" , then the values in the query should be same.
2). Modify the configuration setting for Case Sensitivity and Ignore trailing spaces from Administration >> Configuration >> TDV Server >> SQL Engine >> SQL Language same as that is set in your data source.
Once the configuration settings are modified , go to the data source and refresh the Resource capabilities to apply the changes and then try to run the SQL again.
Feedback
Yes
No
Powered by
