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.

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.

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.

  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.