JBoss.orgCommunity Documentation

Teiid - Scalable Information Integration

Teiid Developer's Guide


Legal Notice

1. Developing For Teiid
1.1. Introduction to the Teiid Connector Architecture
1.2. Do You Need a New Translator?
1.2.1. Custom Translators
1.3. Do You Need a New Resource Adapter?
1.3.1. Custom Resource Adapters
1.4. Other Teiid Development
2. Developing JEE Connectors
2.1. Using the Teiid Framework
2.1.1. Define Managed Connection Factory
2.1.2. Define the Connection Factory class
2.1.3. Define the Connection class
2.1.4. XA Transactions
2.1.5. Define the configuration properties in a "ra.xml" file
2.2. Packaging the Adapter
2.3. Deploying the Adapter
3. Translator Development
3.1. Extending the ExecutionFactory Class
3.1.1. ConnectionFactory
3.1.2. Connection
3.1.3. Configuration Properties
3.1.4. Initializing the Translator
3.1.5. TranslatorCapabilities
3.1.6. Execution (and sub-interfaces)
3.1.7. Metadata
3.1.8. Logging
3.1.9. Exceptions
3.1.10. Default Name
3.2. Connections to Source
3.2.1. Obtaining connections
3.2.2. Releasing Connections
3.3. Executing Commands
3.3.1. Execution Modes
3.3.2. ResultSetExecution
3.3.3. Update Execution
3.3.4. Procedure Execution
3.3.5. Asynchronous Executions
3.3.6. Bulk Execution
3.3.7. Command Completion
3.3.8. Command Cancellation
3.4. Command Language
3.4.1. Language
3.4.2. Language Utilities
3.4.3. Runtime Metadata
3.4.4. Language Visitors
3.4.5. Translator Capabilities
3.5. Large Objects
3.5.1. Data Types
3.5.2. Why Use Large Object Support?
3.5.3. Handling Large Objects
3.5.4. Inserting or Updating Large Objects
3.6. Packaging
3.7. Deployment
4. Extending The JDBC Translator
4.1. Capabilities Extension
4.2. SQL Translation Extension
4.3. Results Translation Extension
4.4. Adding Function Support
4.4.1. Using FunctionModifiers
4.5. Installing Extensions
5. User Defined Functions
5.1. UDF Definition
5.2. Source Supported UDF
5.3. Non-pushdown Support for User-Defined Functions
5.3.1. Java Code
5.3.2. Post Code Activities
5.4. Installing user-defined functions
6. AdminAPI
6.1. Connecting
6.2. Admin Methods
7. Logging
7.1. Customized Logging
7.1.1. Command Logging API
7.1.2. Audit Logging API
8. Login Modules
8.1. Built-in LoginModules
8.2. Custom LoginModules
A. ra.xml file Template
B. Advanced Topics
B.1. Security Migration From Previous Versions

A Translator is used to:

A Resource Adapter:

Given a combination of a Translator + Resource Adapter, one can connect any EIS system to Teiid for their data integration needs.

Teiid provides several translators for common enterprise information system types. If you can use one of these enterprise information systems, you do not need to develop a custom one.

Teiid offers the following translators:

High-level Translator development procedure:

  1. ???Create a new or reuse an existing Resource Adapater for the EIS system, to be used with this Translator

  2. Implement the required classes defined by the Translator API.

    • Create an ExecutionFactory – Extend the org.teiid.translator.ExecutionFactory class

    • Create relevant Executions (and sub-interfaces) – specifies how to execute each type of command

  3. Define the template for exposing configuration properties.

  4. Deploy your Translator.

    • Deploy a Virtual Database (VDB) utilizing your Translator.

  5. Execute queries via Teiid.

This guide covers how to do each of these steps in detail. It also provides additional information for advanced topics, such as streaming large objects. For sample Translator code, please check the Teiid source code

As mentioned above, for every Translator that needs to gather data from external source systems, it requires a resource adapter.

High-level Resource Adapter development procedure:

  1. Understand the JEE Connector specification to have basic idea about what JCA connectors are how they are developed and packaged.

  2. Gather all necessary information about your Enterprise Information System (EIS). You will need to know:

    • API for accessing the system

    • Configuration and connection information for the system

    • Expectation for incoming queries/metadata

    • The processing constructs, or capabilities, supported by information system.

    • Required properties for the connection, such as URL, user name, etc.

  3. Base classes for all of the required supporting JCA SPI classes are provided by the Teiid API. The JCA CCI support is not provided from Teiid, since Teiid uses the Translator API as it's common client interface. You will want to extend:

    • BasicConnectionFactory – Defines the Connection Factory

    • BasicConnection – represents a connection to the source.

    • BasicResourceAdapter – Specifies the resource adapter class

  4. Package your resource adapter.

  5. Deploy your resource adapter.

This guide covers how to do each of these steps in detail. It also provides additional information for advanced topics, such as transactions. For a sample resource adapter code, please check the Teiid Source code

Teiid is highly extensible in other ways:

This chapter examines how to use facilities provided by the Teiid API to develop a JEE JCA Connector. Please note that these are standard JEE JCA connectors, nothing special needs to be done for Teiid. As an aid to our Translator developers, we provided a base implementation framework. If you already have a JCA Connector or some other mechanism to get data from your source system, you can skip this chapter.

If you are not familiar with JCA API, please read the JCA 1.5 Specification. There are lot of online tutorials on how to design and build a JCA Connector. The below we show you to build very simple connector, however building actual connector that supports transactions, security can get much more complex.

Check out the following links Connectors on JBoss

If you are going to use the Teiid framework for developing a JCA connector, follow these steps. The required classes are in org.teiid.resource.api package. Please note that Teiid framework does not make use JCA's CCI framework, only the JCA's SPI interfaces.

If you EIS source can participate in XA transactions, then on your Connection object, override the "getXAResource()" method and provide the "XAResource" object for the EIS system. Also, You need to extend the "BasicResourceAdapter" class and provide implementation for method "public XAResource[] getXAResources(ActivationSpec[] specs)" to participate in crash recovery.

Note that, only when the resource adapters are XA capable, then Teiid can make them participate in a distributed transactions. If they are not XA capable, then source can participate in distributed query but will not participate in the transaction. Transaction semantics at that time defined by how you defined "-ds.xml" file. i.e. with local-tx or no-tx

Define a "ra.xml" file (sample shown in the appendix-a) in "META-INF" directory of your RAR file. For every configuration property defined inside the ManagedConnectionFactory class, define the following XML configuration fragment inside the "ra.xml" file. These properties are used by user to configure instance of this Connector inside a Container. Also, during the startup the Container reads these properties from this file and knows how to inject provided values in the "-ds.xml" file into a instance of "ManagedConnectionFactory" to create the Connection.

    <description>{$display:"${display-name}",$description:"${description}", $allowed="${allowed}", $required="${true|false}", $defaultValue="${default-value}"}</description>

The format and contents of "<description>" element may be used as extended metadata for tooling. The special format must begin and end with curly braces e.g. {...}. This use of the special format and all properties is optional. Property names begin with '$' and are separated from the value with ':'. Double quotes identifies a single value. A pair of square brackets, e.g. [...], containing comma separated double quoted entries denotes a list value.

Extended metadata properties

  • $display: Display name of the property

  • $description: Description about the property

  • $required: The property is a required property; or optional and a default is supplied

  • $allowed: If property value must be in certain set of legal values, this defines all the allowed values

  • $masked: The tools need to mask the property; Do not show in plain text; used for passwords

  • $advanced: Notes this as Advanced property

  • $editable: Property can be modified; or read-only

Note that all these are optional properties; however in the absence of this metadata, Teiid tooling may not work as expected.

Once all the required code is developed, it is time to package them into a RAR artifact, that can be deployed into a Conainer. A RAR artifact is lot more similar to a WAR. To put together a RAR file it really depends upon build system you are using.

Make sure that the RAR file, under its "META-INF" directory has the "ra.xml" file. If you are using maven see http://maven.apache.org/plugins/maven-rar-plugin/In the root of the RAR file, you can embed the JAR file containing your connector code and any dependent library JAR files.

Once the RAR file is built, deploy it by copying the RAR file into "deploy" directory of JBoss AS's choosen profile. Typically the server does not need to be restarted when a new RAR file is being added. Alternatively, you can also use "admin-console" a web based monitoring and configuration tool to deploy this file into the container.

Once the Connector's RAR file is deployed into the JBoss container, now you can start creating a instance of this connector to be used with your Translator. Creating a instance of this Connector is no different than creating a "Connection Factory" in JBoss AS. Again, you have have two ways you can create a "ConnectionFactory".

A component called the Connector Manager is controlling access to your translator. This chapter reviews the basics of how the Connector Manager interacts with your translator while leaving reference details and advanced topics to be covered in later chapters.

A custom translator must extend org.teiid.translator.ExecutionFactory to connect and query an enterprise data source. This extended class must provide a no-arg constructor that can be constructed using Java reflection libraries. This Execution Factory need define/override following elements.

Every software program requires some external configuration, that defines ways user can alter the behavior of a program. If this translator needs configurable properties define a variable for every property as an attribute in the extended "ExecutionFactory" class. Then define a "get" and "set" methods for each of them. Also, annotate each "get" method with @TranslatorProperty annotation and provide the metadata about the property. For example, if you need a property called "foo",

private String foo = "balh";

@TranslatorProperty(display="Foo property", description="description about Foo") 
public String getFoo() {
    return foo;

public void setFoo(String value) {
    return this.foo = value;

by providing the annotation on these properties, the Teiid tooling will automatically interrogate and provide graphical way to configure your Translator. Only java primitive (int), primitive object wrapper (java.lang.Integer), or Enum types are supported as Translator properties. The default value will be derived from calling the getter, if available, on a newly constructed instance. All properties should have a default value. If there is no applicable default, then the property should be marked in the annotation as required. Initialization will fail if a required property value is not provided.

The @TranslatorProperty defines the following metadata that you can define about your property

Teiid sends commands to your Translator in object form. These classes are all defined in the "org.teiid.language" package. These objects can be combined to represent any possible command that Teiid may send to the Translator. However, it is possible to notify Teiid that your Translator can only accept certain kinds of constructs via the capabilities defined on the "ExecutionFactory" class. See the section on using Translator Capabilities for more information.

The language objects all extend from the LanguageObject interface. Language objects should be thought of as a tree where each node is a language object that has zero or more child language objects of types that are dependent on the current node.

All commands sent to your Translator are in the form of these language trees, where the root of the tree is a subclass of Command. Command has several sub-interfaces, namely:

  • QueryExpression

  • Insert

  • Update

  • Delete

  • BatchedUpdates

  • Call

Important components of these commands are expressions, criteria, and joins, which are examined in closer detail below. Also see the Teiid JavaDocs for more on the classes and interfaces described here.

This section covers utilities available when using, creating, and manipulating the language interfaces.

Teiid uses a library of metadata, known as "runtime metadata” for each virtual database that is deployed in Teiid. The runtime metadata is a subset of metadata as defined by models in the Teiid models that compose the virtual database.  While builing your VDB in the Designer, you can define what called "Extension Model", that defines any number of arbitary properties on a model and its objects. At runtime, using this runtime metadata interface, you get access to those set properties defined during the design time, to define/hint any execution behavior.

Translator gets access to the RuntimeMetadata interface at the time of Excecution creation. Translators can access runtime metadata by using the interfaces defined in org.teiid.metadata package.  This package defines API representing a Schema, Table, Columns and Procedures, and ways to navigate these objects.

The API provides a language visitor framework in the org.teiid.language.visitor package.  The framework provides utilities useful in navigating and extracting information from trees of language objects.

The visitor framework is a variant of the Visitor design pattern, which is documented in several popular design pattern references.  The visitor pattern encompasses two primary operations: traversing the nodes of a graph (also known as iteration) and performing some action at each node of the graph.  In this case, the nodes are language interface objects and the graph is really a tree rooted at some node.  The provided framework allows for customization of both aspects of visiting.

The base AbstractLanguageVisitor class defines the visit methods for all leaf language interfaces that can exist in the tree.  The LanguageObject interface defines an acceptVisitor() method – this method will call back on the visit method of the visitor to complete the contract.  A base class with empty visit methods is provided as AbstractLanguageVisitor.  The AbstractLanguageVisitor is just a visitor shell – it performs no actions when visiting nodes and does not provide any iteration.

The HierarchyVisitor provides the basic code for walking a language object tree.  The HierarchyVisitor performs no action as it walks the tree – it just encapsulates the knowledge of how to walk it.  If your translator wants to provide a custom iteration that walks the objects in a special order (to exclude nodes, include nodes multiple times, conditionally include nodes, etc) then you must either extend HierarchyVisitor or build your own iteration visitor.  In general, that is not necessary.

The DelegatingHierarchyVisitor is a special subclass of the HierarchyVisitor that provides the ability to perform a different visitor’s processing before and after iteration.  This allows users of this class to implement either pre- or post-order processing based on the HierarchyVisitor.  Two helper methods are provided on DelegatingHierarchyVisitor to aid in executing pre- and post-order visitors.  

The ExecutionFactory class defines all the methods that describe the capabilities of a Translator. These are used by the Connector Manager to determine what kinds of commands the translator is capable of executing. A base ExecutionFactory class implements all the basic capabilities, which says your translator does not support any cpabilities. Your extended ExecutionFactory class must override the the necessary methods to specify which capabilities your translator supports.  

The following table lists the capabilities that can be specified in the ExecutionFactory class.

Table 3.2. Available Capabilities





Translator can support SELECT DISTINCT in queries.


Translator can support SELECT of more than just column references.


Translator can support Tables in the FROM clause that have an alias.


Translator can support inner and cross joins


AliasedGroups and at least on of the join type supports.

Translator can support a self join between two aliased versions of the same Table.


Translator can support LEFT and RIGHT OUTER JOIN.


Translator can support FULL OUTER JOIN.



Translator can support a named subquery in the FROM clause.


Not currently used - between criteria is rewriten as compound comparisions.


Translator can support comparison criteria with the operator "=”.


Translator can support comparison criteria with the operator ">” or "<".


Translator can support LIKE criteria.



Translator can support LIKE criteria with an ESCAPE character clause.



Translator can support IN predicate criteria.


Translator can support IN predicate criteria where values are supplied by a subquery.


Translator can support IS NULL predicate criteria.


Translator can support the OR logical criteria.


Translator can support the NOT logical criteria. IMPORTANT: This capability also applies to negation of predicates, such as specifying IS NOT NULL, "<=" (not ">"), ">=" (not "<"), etc.


Translator can support EXISTS predicate criteria.


Translator can support a quantified comparison criteria using the ALL quantifier.


Translator can support a quantified comparison criteria using the SOME or ANY quantifier.


Translator can support the ORDER BY clause in queries.



Translator can support the ORDER BY items that are not directly specified in the select clause.


Translator can support an explict GROUP BY clause.



Translator can support the HAVING clause.


Translator can support the AVG aggregate function.


Translator can support the COUNT aggregate function.


Translator can support the COUNT(*) aggregate function.


At least one of the aggregate functions.

Translator can support the keyword DISTINCT inside an aggregate function.  This keyword indicates that duplicate values within a group of rows will be ignored.


Translator can support the MAX aggregate function.


Translator can support the MIN aggregate function.


Translator can support the SUM aggregate function.


Translator can support the use of a subquery in a scalar context (wherever an expression is valid).


At least one of the subquery pushdown capabilities.

Translator can support a correlated subquery that refers to an element in the outer query.


Not currently used - simple case is rewriten as searched case.


Translator can support "searched” CASE expressions anywhere that expressions are accepted.


Translator support UNION and UNION ALL


Translator supports INTERSECT


Translator supports Except


Unions, Intersect, or Except

Translator supports set queries with an ORDER BY


Translator can support the limit portion of the limit clause


Translator can support the offset portion of the limit clause



Not currently used - non-element expressions in the group by create an inline view.


Translator supports INSERT statements with values specified by an QueryExpression.


Translator supports a batch of INSERT, UPDATE and DELETE commands to be executed together.


Translator supports updates with multiple value sets

Note that any pushdown subquery must itself be compliant with the Translator capabilities.

The method ExecutionFactory.getSupportedFunctions() can be used to specify which scalar functions the Translator supports.  The set of possible functions is based on the set of functions supported by Teiid.  This set can be found in the Reference documentation.  If the Translator states that it supports a function, it must support all type combinations and overloaded forms of that function.

There are also five standard operators that can also be specified in the supported function list: +, -, *, /, and ||.

The constants interface SourceSystemFunctions contains the string names of all possible built-in pushdown functions. Note that not all system functions appear in this list. This is because some system functions will always be evaluted in Teiid, are simple aliases to other functions, or are rewriten to a more standard expression.

This section examines how to use facilities provided by the Teiid API to use large objects such as blobs, clobs, and xml in your Translator.

The JDBC Translator can be extended to handle new JDBC drivers and database versions. This is one of the most common needs of custom Translator development. This chapter outlines the process by which a user can modify the behavior of the JDBC Translator for a new source, rather than starting from scratch.

To design a JDBC Translator for any RDMS that is not already provided by the Teiid, extend the org.teiid.translator.jdbc.JDBCExecutionFactory class in the "translator-jdbc" module. There are three types of methods that you can override from the base class to define the behavior of the Translator.

This extension must override the methods that begin with "supports" that describe translator capabilities. For all the available translator capabilities please see this.

The most common example is adding support for a scalar function – this requires both declaring that the translator has the capability to execute the function and often modifying the SQL Translator to translate the function appropriately for the source.

Another common example is turning off unsupported SQL capabilities (such as outer joins or subqueries) for less sophisticated JDBC sources.

The JDBCExcecutionFactory provides several methods to modify the command and the string form of the resulting syntax before it is sent to the JDBC driver, including:

See User Defined Functions for adding new functions to Teiid. This example will show you how to declare support for the function and modify how the function is passed to the data source.

Following is a summary of all coding steps in supporting a new scalar function:

  1. Override the capabilities method to declare support for the function (REQUIRED)

  2. Implement a FunctionModifier to change how a function is translated and register it for use (OPTIONAL)

There is a capabilities method getSupportedFunctions() that declares all supported scalar functions.

An example of an extended capabilities class to add support for the “abs” absolute value function:

package my.connector;
import java.util.ArrayList;
import java.util.List;

public class ExtendedJDBCExecutionFactory extends JDBCExecutionFactory {
    public List getSupportedFunctions() {
        List supportedFunctions = new ArrayList();
        return supportedFunctions;

In general, it is a good idea to call super.getSupportedFunctions() to ensure that you retain any function support provided by the translator you are extending.

This may be all that is needed to support a Teiid function if the JDBC data source supports the same syntax as Teiid. The built-in SQL translation will translate most functions as: “function(arg1, arg2, …)”.

In some cases you may need to translate the function differently or even insert additional function calls above or below the function being translated. The JDBC translator provides an abstract class FunctionModifier for this purpose.

During the start method a modifier instance can be registered against a given function name via a call to JDBCExecutionFactory.registerFunctionModifier.

The FunctionModifier has a method called translate. Use the translate method to change the way the function is represented.

In addition to building your own FunctionModifiers, there are a number of pre-built generic function modifiers that are provided with the translator.

To register the function modifiers for your supported functions, you must call the ExecutionFactory.registerFunctionModifier(String name, FunctionModifier modifier) method.

public class ExtendedJDBCExecutionFactory extends JDBCExecutionFactory
	public void start() {
	    // register functions.
	    registerFunctionModifier("abs", new MyAbsModifier()); 
	    registerFunctionModifier("concat", new AliasModifier(“concat2”)); 

Support for the two functions being registered (“abs” and “concat”) must be declared in the capabilities as well. Functions that do not have modifiers registered will be translated as usual.

Once you have developed an extension to the JDBC translator, you must install it into the Teiid Server. The process of packaging or deploying the extended JDBC translators is exactly as any other other translator. Since the RDMS is accessible already through its JDBC driver, there is no need to develop a resource adapter for this source as JBoss AS provides a wrapper JCA connector (DataSource) for any JDBC driver.

If you need to extends Teiid's scalar function library, then Teiid provides a means to define custom scalar functions or User Defined Functions(UDF). The following steps need to be taken in creating a UDF.

The FunctionDefinition.xmi file provides metadata to the query engine on User Defined Functions. See the Designer Documentation for more on creating a Function Definition Model.

Even pushdown required functions need to be added as a UDF to allow Teiid to properly parse and resolve the function. Pushdown scalar functions differ from normal user-defined functions in that no code is provided for evaluation in the engine. An exception will be raised if a pushdown required function cannot be evaluated by the appropriate source.

While Teiid provides an extensive scalar function library, it contains only those functions that can be evaluated within the query engine. In many circumstances, especially for performance, a user defined function allows for calling a source specific function.

For example, suppose you want to use the Oracle-specific functions score and contains:

The score and contains functions are not part of built-in scalar function library. While you could write your own custom scalar function to mimic their behavior, it's more likely that you would want to use the actual Oracle functions that are provided by Oracle when using the Oracle Free Text functionality.

In addition to the normal steps outlined in the section to create and install a function model (FunctionDefinitions.xmi), you will need to extend the appropriate connector(s).

In most circumstances the admin operations will be performed through the admin console or AdminShell tooling, but it is also possible to invoke admin functionality directly in Java through the AdminAPI.

All classes for the AdminAPI are in the client jar under the org.teiid.adminapi package.

The Teiid system provides a wealth of information via logging. To control logging level, contexts, and log locations, you should be familiar with log4j and the container's jboss-log4j.xml configuration file. Teiid also provides a <profile>/conf/jboss-teiid-log4j.xml containing much of information from chapter. Check out Admin Guide for more details about different Teiid contexts available.

If the default log4j logging mechanisms are not sufficient for your logging needs you may need a different appender - see the log4j javadocs. Note that log4j already provides quite a few appenders including JMS, RDBMS, and SMTP.

If you want a custom appender, follow the Log4J directions to write a custom appender. See instructions here. If you develop a custom logging solution, the implementation jar should be placed in the "lib" directory of the JBoss AS server profile Teiid is installed in.

The Teiid system provides a range of built-in and extensible security features to enable the secure access of data.  For details about how to configure the available security features check out Admin Guide.

LoginModules are an essential part of the JAAS security framework and provide Teiid customizable user authentication and the ability to reuse existing LoginModules defined for JBossAS. See JBossAS Security for general information on configuring security in JBossAS.

If your authentication needs go beyond the provided LoginModules, please consult the JAAS development guide. There are also numerous guides available.

If you are extending one of the built-in LoginModules, please see this.

This appendix contains an example of the ra.xml file that can be used as a template when creating a new Connector.

<?xml version="1.0" encoding="UTF-8"?>

<connector xmlns="http://java.sun.com/xml/ns/j2ee"

        ${license text}


            <!-- repeat for every configuration property -->
                <description>{$display:"${short-name}",$description:"${description}", $allowed:[${value-list}], $required:"${required-boolean}", $defaultValue:"${default-value}"}</description>
            <!-- use the below as is if you used the Connection Factory interface -->


${...} indicates a value to be supplied by the developer.