Why is a query with a DATETIMEOFFSET type data, not pushed to the SQL Server database in TIBCO Data Virtualization?
Article ID: KB0070305
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | All supported versions. |
Description
If a SQL Server database is queried from the TIBCO Data Virtualization, the query might not get pushed to the database and instead all the execution might happen in TDV itself. Thus, resulting in higher execution time.
On checking the query execution plan, a No Push Reason as below can be observed:
The cause for the same and how to resolve it is explained below.
On checking the query execution plan, a No Push Reason as below can be observed:
No Push Reason: Cannot push Selectable because the data source SqlServer is not case sensitive and our system is case sensitive and selectable is not wrapped with UPPER or LOWER function
The cause for the same and how to resolve it is explained below.
Issue/Introduction
This article explains the reason and outlines the steps for resolving the 'No Push Reason' that is observed when using datatype DATETIMEOFFSET in TIBCO Data Virtualization.
Environment
All supported environments.
Resolution
Whenever a SQL Server table with a column of type DATETIMEOFFSET is imported into TIBCO Data Virtualization, TDV converts the column to datatype VARCHAR(34). This can be verified by looking at the table definition in the Studio.
Now, the problem is whenever a comparison is carried out using a query, TDV compares both values in the query as string values and applies case-sensitivity and white-space trimming logic to them (which is set in Studio > Configuration) without taking into consideration that neither of those values are actual string values. TDV doesn't support the DATETIMEOFFSET datatype so casting to another datatype also doesn't work.
So, the resolution here would be to use a query hint which will change the case sensitivity and trailing white spaces for this query to match with the SQL Server's set value.
Example of this is as below:
Now, the problem is whenever a comparison is carried out using a query, TDV compares both values in the query as string values and applies case-sensitivity and white-space trimming logic to them (which is set in Studio > Configuration) without taking into consideration that neither of those values are actual string values. TDV doesn't support the DATETIMEOFFSET datatype so casting to another datatype also doesn't work.
So, the resolution here would be to use a query hint which will change the case sensitivity and trailing white spaces for this query to match with the SQL Server's set value.
Example of this is as below:
SELECT {OPTION CASE_SENSITIVE="false", IGNORE_TRAILING_SPACES="true" } * FROM table1 WHERE column1 = ’xyz’; Feedback
Yes
No
Powered by
