Translators

Teiid uses the Teiid Connector Architecture (TCA), which provides a robust mechanism for integrating with external systems. The TCA defines a common client interface between Teiid and an external system that includes metadata as to what SQL constructs are available for pushdown and the ability to import metadata from the external system.

A Translator is the heart of the TCA and acts as the bridge logic between Teiid and an external system.

Refer to the Teiid Developers Guide for details on developing custom Translators and JCA resource adapters for use with Teiid.

Tip	The TCA is not the same as the JCA, the JavaEE Connector Architecture, although the TCA is designed for use with JCA resource adapters.

A Translator is typically paired with a particular JCA resource adapter. In instances where pooling, environment dependent configuration management, advanced security handling, etc. are not needed, then a JCA resource adapter is not needed. The configuration of JCA ConnectionFactories for needed resource adapters is not part of this guide, please see the Teiid Administrator Guide and the kit examples for configuring resource adapters for use in WildFly.

Translators can have a number of configurable properties. These are broken down into execution properties, which determine aspects of how data is retrieved, and import settings, which determine what metadata is read for import.

The execution properties for a translator typically have reasonable defaults. For specific translator types, such as the Derby translator, base execution properties are already tuned to match the source. In most cases the user will not need to adjust their values.

Table 1. **Base execution properties - shared by all translators**
Name	Description	Default
Immutable	Set to `true` to indicate that the source never changes. The transactional capability is reported as NONE, and update commands will fail.	false
RequiresCriteria	Set to `true` to indicate that source SELECT/UPDATE/DELETE queries require a where clause.	false
SupportsOrderBy	Set to `true` to indicate that the ORDER BY clause can be used.	false
SupportsOuterJoins	Set to `true` to indicate that OUTER JOINs can be used.	false
SupportsFullOuterJoins	If `SupportsOuterJoins` is set to `true`, `true` indicates that FULL OUTER JOINs can be used.	false
SupportsInnerJoins	Set to `true` to indicate that INNER JOINs can be used.	false
SupportedJoinCriteria	If join capabilities are enabled, defines the criteria that can be used as the join criteria. May be one of (ANY, THETA, EQUI, or KEY).	ANY
MaxInCriteriaSize	If the use of `IN` criteria is enabled, specifies the maximum number of `IN` entries per predicate. `-1` indicates no limit.	-1
MaxDependentInPredicates	If the use of `IN` criteria is enabled, defines what the maximum number of predicates that can be used for a dependent join. Values less than 1 indicate to use only one `IN` predicate per dependent value pushed (which matches the pre-7.4 behavior).	-1
DirectQueryProcedureName	If `SupportsDirectQueryProcedure` is set to `true` for the translator, this property indicates the name of the procedure.	native
SupportsDirectQueryProcedure	Set to `true` to indicate that direct execution of commands is available for the translator.	false
ThreadBound	Set to `true` to indicate the translator’s Executions should be processed by only a single thread	false
CopyLobs	If `true`, then returned large object (LOB) data (clob, blob, sql/xml) is copied by the engine in a memory safe manner. Use this option if the source does not provide memory safe LOBS or if you want to disconnect LOBS from the source connection.	false
TransactionSupport	The highest level of transaction capability. Used by the engine as a hint to determine if a transaction is needed for `autoCommitTxn=DETECT` mode. Can be one of XA, NONE, or LOCAL. If XA or LOCAL then access under a transaction will be serialized.	XA

Note	Only a subset of the available metadata can be set through execution properties on the base ExecutionFactory. All methods are available on the BaseDelegatingExecutionFactory.

There are no base importer settings.

Override execution properties

For all translators, you can override Execution Properties in the main vdb file.

Example: Overriding a translator property

CREATE FOREIGN DATA WRAPPER "oracle-override" TYPE oracle OPTIONS (RequiresCriteria 'true');

CREATE SERVER ora FOREIGN DATA WRAPPER "oracle-override" OPTIONS ("resource-name" 'java:/oracle');

CREATE SCHEMA ora SERVER ora;

SET SCHEMA ora;

IMPORT FROM SERVER ora INTO ora;

Or as an XML vdb:

<model name="ora">
     <source name="ora" translator-name="oracle-override" connection-jndi-name="java:/oracle"/>
</model>

<translator name="oracle-override" type="oracle">
     <property name="RequiresCriteria" value="true"/>
</translator>

The preceding example overrides the oracle translator and sets the RequiresCriteria property to true. The modified translator is only available in the scope of this VDB. As many properties as desired may be overriden together.

See also VDB Definition.

Parameterizable native queries

In some situations the teiid_rel:native-query property and native procedures accept parameterizable strings that can positionally reference IN parameters. A parameter reference has the form `$integer, for example, $1. Note that one-based indexing is used and that only IN parameters may be referenced. Dollar-sign integer is therefore reserved, but may be escaped with another $`, for example, $$1. The value will be bound as a prepared value or a literal is a source specific manner. The native query must return a result set that matches the expectation of the calling procedure.

For example the native-query select c from g where c1 = $1 and c2 = '$$1' results in a JDBC source query of select c from g where c1 = ? and c2 = '$1', where ?` will be replaced with the actual value bound to parameter 1.

General import properties

Several import properties are shared by all translators.

When specifying an importer property, it must be prefixed with importer.. For example, importer.tableTypes.

Name Description Default

Name	Description	Default
autoCorrectColumnNames	Replace any usage of `.` in a column name with `_` as the period character is not valid in Teiid column names.	true
renameDuplicateColumns	If true, rename duplicate columns caused by either mixed case collisions or `autoCorrectColumnNames` replacing `.` with `_`. A suffix `_n` where `n` is an integer will be added to make the name unique.	false
renameDuplicateTables	If true, rename duplicate tables caused by mixed case collisions. A suffix `_n` where `n` is an integer will be added to make the name unique.	false
renameAllDuplicates	If true, rename all duplicate tables, columns, procedures, and parameters caused by mixed case collisions. A suffix `_n` where `n` is an integer will be added to make the name unique. Supersedes the individual rename duplicate options.	false
nameFormat	Set to a Java string format to modify table and procedure names on import. The only argument will be the original name Teiid name. For example use `prod_%s` to prefix all names with `prod_`.

autoCorrectColumnNames

Replace any usage of . in a column name with _ as the period character is not valid in Teiid column names.

true

renameDuplicateColumns

If true, rename duplicate columns caused by either mixed case collisions or autoCorrectColumnNames replacing . with _. A suffix _n where n is an integer will be added to make the name unique.

false

renameDuplicateTables

If true, rename duplicate tables caused by mixed case collisions. A suffix _n where n is an integer will be added to make the name unique.

false

renameAllDuplicates

If true, rename all duplicate tables, columns, procedures, and parameters caused by mixed case collisions. A suffix _n where n is an integer will be added to make the name unique. Supersedes the individual rename duplicate options.

false

nameFormat

Set to a Java string format to modify table and procedure names on import. The only argument will be the original name Teiid name. For example use prod_%s to prefix all names with prod_.

Translators

Translators

results matching ""

No results matching ""