IBM Support

Teradata connector stage: Transaction mode affects case sensitivity of literals in SQL queries

Troubleshooting


Problem

In the Teradata Connector stage, there is property for "Transaction mode" with a choice of values, either ANSI (default) or Teradata, this set the session mode for the connection,

ANSI session mode is a state in which transaction processing semantics adhere to the rules defined by the ANSI SQL: 2011 specification. Teradata (or BTET) session mode is a state in which transaction processing follows a set of rules defined by Teradata. Teradata session mode provides a means for conducting transaction processing by legacy applications.

In ANSI session mode, each transaction consists of one or more requests, each of which can consist of one or more SQL statements in ANSI session mode.

Multistatement requests are treated as a single atomic unit; either all the work done by all the statements in a multi statement request is committed or none of it is.

Apart from transaction semantics, you can write SQL code with explicit specifications to override defaults so that it performs identically in both ANSI and Teradata session modes.

The default for literals in sql query statement is that they are case-sensitive in ANSI mode and case-insensitive in Teradata mode.

Symptom

A query such as
Select case when 'A' = 'a' then 'Case-insensitive' else 'Case-sensitive' END
Returns 'Case-sensitive' if run with ANSI session mode and 'Case-insensitive' if run with Teradata session mode.

Document Location

Worldwide

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Component":"Teradata Connector","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"11.5;11.7;11.7.1","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

Modified date:
25 April 2025

UID

ibm11282516

Page Feedback