When the table has a column with char datatype with 'MS SQLServer' database then it is observed that the adapter will pass the query statement where the char datatype is converted to nvarchar which results in full table scan rather than the usage of index
Article ID: KB0076229
Updated On:
| Products | Versions |
|---|---|
| TIBCO ActiveMatrix BusinessWorks Plug-in for Database | 7.2.x |
Description
When the table has a column with char datatype with 'MS SQLServer' database then it is observed that the adapter will pass the query statement where the char datatype is converted to nvarchar which results in full table scan rather than the usage of index because of datatype mismatch impacting the performance of the database.
TIBCO ActiveMatrix Database Adapter is implemented to construct the SQL statement, as it will not alter the table column datatype. The AESchema type is mapped to the JDBC datatype for which the JDBC driver can map the java String datatype to the Char datatype without any issue.
Cause:
========
The reason for the data type conversion is the TIBCO supplement JDBC driver that sends a String input parameter to the database in Unicode format, however, the columns defined in Char serve for storing ANSI data. That is why the driver issued the declare statement to implicitly concert all the Char data type to nvarchar.
TIBCO ActiveMatrix Database Adapter is implemented to construct the SQL statement, as it will not alter the table column datatype. The AESchema type is mapped to the JDBC datatype for which the JDBC driver can map the java String datatype to the Char datatype without any issue.
Cause:
========
The reason for the data type conversion is the TIBCO supplement JDBC driver that sends a String input parameter to the database in Unicode format, however, the columns defined in Char serve for storing ANSI data. That is why the driver issued the declare statement to implicitly concert all the Char data type to nvarchar.
Issue/Introduction
The char data type of the table changes to nvarchar when a table with char datatype is configured with TIBCO ActiveMatrix Database Adapter
Environment
Product: TIBCO ActiveMatrix BusinessWorks Adapter for Database
Version: 7.x
Product:TIBCO Database Drivers Supplement
Version:2.0.4 and above
OS: All Supported Operating Systems
Resolution
Resolution:
==========
The TIBCO JDBC driver provides a connection parameter "StringInputParameterType" for 'MS SQLServer' database which is used to determine whether the driver sends String input parameters to the database in Unicode or the default character encoding of the database.
If set to "StringInputParameterType=nvarchar", the driver sends String input parameter to the database in Unicode.
If set to "StringInputParameterType=varchar", the driver sends String input parameters to the database in the default character encoding of the database. This value can improve performance because the server does not need to convert Unicode characters to the default encoding.
Set the property like,
jdbc:tibcosoftwareinc:sqlserver://<Host_Name>:<PORT>;databaseName=<Database_name>;StringInputParameterType=varchar
==========
The TIBCO JDBC driver provides a connection parameter "StringInputParameterType" for 'MS SQLServer' database which is used to determine whether the driver sends String input parameters to the database in Unicode or the default character encoding of the database.
If set to "StringInputParameterType=nvarchar", the driver sends String input parameter to the database in Unicode.
If set to "StringInputParameterType=varchar", the driver sends String input parameters to the database in the default character encoding of the database. This value can improve performance because the server does not need to convert Unicode characters to the default encoding.
Set the property like,
jdbc:tibcosoftwareinc:sqlserver://<Host_Name>:<PORT>;databaseName=<Database_name>;StringInputParameterType=varchar
Feedback
Yes
No
Powered by
