JBoss.orgCommunity Documentation

Teiid - Scalable Information Integration

Teiid Client Developer's Guide

7.6

Legal Notice

1. Connecting to Teiid Server
1.1. Driver Connection
1.1.1. URL Connection Properties
1.2. Datasource Connection
1.3. Standalone Application
1.3.1. Driver Connection
1.3.2. Datasource Connection
1.4. JBoss AS Data Source
1.4.1. DataSource Connection
1.4.2. Driver based connection
1.4.3. Local JDBC Connection
1.5. Using Multiple Hosts
1.5.1. Fail Over
1.5.2. Load Balancing
1.5.3. Advanced Configuration
1.6. Reauthentication
2. Prepared Statements
3. Teiid extensions to the JDBC API
3.1. Statement Extensions
3.2. Execution Properties
3.3. SET Statement
3.4. SHOW Statement
3.5. Transaction Statements
3.6. Partial Results Mode
3.7. XML extensions
3.7.1. Document formatting
3.7.2. Schema validation
3.8. Non-blocking Statement Execution
4. Transactions with JDBC
4.1. Local Transactions
4.1.1. Turning Off Local Transactions
4.2. Request Level Transactions
4.2.1. Multiple Insert Batches
4.3. Using Global Transactions
4.4. Restrictions
4.4.1. Application Restrictions
4.4.2. Enterprise Information System Support
5. SSL Client Connections
5.1. Default Security
5.2. SSL Modes
5.3. Client SSL Settings
5.3.1. Option 1: Java SSL properties
5.3.2. Option 2: Teiid Specific Properties
6. Using Teiid with Hibernate
6.1. Limitations
6.2. Configuration
7. ODBC Support
7.1. Installing the ODBC Driver Client
7.1.1. Microsoft Windows
7.1.2. Other *nix Platform Installations
7.2. Configuring the Data Source Name (DSN)
7.2.1. Windows Installation
7.2.2. Other *nix Platform Installations
7.3. DSN Less Connection
A. Unsupported JDBC Methods
A.1. ResultSet Limitations
A.2. Unsupported Classes and Methods in "java.sql"
A.3. Unsupported Classes and Methods in "javax.sql"
B. Generating Self Signed Certificate with Keytool
B.1. Creating private/public key pair:
B.2. Extracting the public key
B.3. Creating the Truststore

The Teiid JDBC API provides Java Database Connectivity (JDBC) access to any Virtual Database (VDB) deployed on a Teiid Server. The Teiid JDBC API is compatible with the JDBC 4.0 specification; however, it does not fully support all methods. Advanced features, such as updatable result sets or SQL3 data types, are not supported.

Java client applications connecting to a Teiid Server will need to use Java 1.6 JDK. Previous versions of Java are not supported.

Before you can connect to the Teiid Server using the Teiid JDBC API, please do following tasks first.

  1. Install the Teiid Server. See the "Admin Guide" for instructions.

  2. Build a Virtual Database (VDB). You can either build a "Dynamic VDB" (Designer not required), or you can use the Eclipse based GUI tool Designer. Check the "Reference Guide" for instructions on how to build a VDB. If you do not know what VDB is, then start with this document.

  3. Deploy the VDB into Teiid Server. Check "Admin Guide" for instructions.

  4. Start the Teiid Server (JBoss AS), if it is not already running.

Now that you have the VDB deployed in the Teiid Server, client applications can connect to the Teiid Server and issue SQL queries against deployed VDB using Teiid's JDBC API. If you are new to JDBC, see Java's documentation about JDBC. Teiid ships with teiid-7.6-client.jar in the "jboss-install/server/<profile>/lib" directory.

Main classes in the client JAR:

  • org.teiid.jdbc.TeiidDriver - allows JDBC connections using the DriverManager class.

  • org.teiid.jdbc.TeiidDatasource - allows JDBC connections using the DataSource or XADataSource class. You should use this class to create managed or XA connections.

Once you have established a connection with the Teiid Server, you can use standard JDBC API classes to interrogate metadata and execute queries.

Use org.teiid.jdbc.TeiidDriver as the driver class.

Use the following URL format for JDBC connections:

jdbc:teiid:<vdb-name>@mm[s]://<host>:<port>;[prop-name=prop-value;]*

URL Components

  1. <vdb-name> - Name of the VDB you are connecting to. Optionally VDB name can also contain version information inside it. For example: "myvdb.2", this is equivalent to supplying the "version=2" connection property defined below. However, use of vdb name in this format and the "version" property at the same time is not allowed.

  2. mm - defines Teiid JDBC protocol, mms defines a secure channel (see the SSL chapter for more)

  3. <host> - defines the server where the Teiid Server is installed. If you are using IPv6 binding address as the host name, place it in square brackets. ex:[::1]

  4. <port> - defines the port on which the Teiid Server is listening for incoming JDBC connections.

  5. [prop-name=prop-value] - additionally you can supply any number of name value pairs separated by semi-colon [;]. All supported URL properties are defined in the connection properties section. Property values should be URL encoded if they contain reserved characters, e.g. ('?', '=', ';', etc.)

The following table shows all the supported connection properties that can used with Teiid JDBC Driver URL connection string, or on the Teiid JDBC Data Source class.

Table 1.1. Connection Properties

Property NameTypeDescription
ApplicationName String Name of the client application; allows the administrator to identify the connections
FetchSize int Size of the resultset; The default size if 500. <=0 indicates that the default should be used.
partialResultsMode boolean Enable/disable support partial results mode. Default false. See the partial results section.
autoCommitTxn String Only applies only when "autoCommit" is set to "true". This determines how a executed command needs to be transactionally wrapped inside the Teiid engine to maintain the data integrity.
  • ON - Always wrap command in distributed transaction

  • OFF - Never wrap command in distributed transaction

  • DETECT (default)- If the executed command is spanning more than one source it automatically uses distributed transaction.

Transactions with JDBC for more information.
disableLocalTxn boolean If "true", the autoCommit setting, commit and rollback will be ignored for local transactions. Default false.
user String User name
Password String Credential for user
ansiQuotedIdentifiers boolean Sets the parsing behavior for double quoted entries in SQL. The default, true, parses dobuled quoted entries as identifiers. If set to false, then double quoted values that are valid string literals will be parsed as string literals.
version integer Version number of the VDB
resultSetCacheMode boolean ResultSet caching is turned on/off. Default false.
autoFailover boolean If true, will automatically select a new server instance after a communication exception. Default false. This is typically not needed when connections are managed, as the connection can be purged from the pool.
SHOWPLAN String (typically not set as a connection property) Can be ON|OFF|DEBUG; ON returns the query plan along with the results and DEBUG additionally prints the query planner debug information in the log and returns it with the results. Both the plan and the log are available through JDBC API extensions. Default OFF.
NoExec String (typically not set as a connection property) Can be ON|OFF; ON prevents query execution, but parsing and planning will still occur. Default OFF.
PassthroughAuthentication boolean Only applies to "local" connections. When this option is set to "true", then Teiid looks for already authenticated security context on the calling thread. If one found it uses that users credentials to create session. Teiid also verifies that the same user is using this connection during the life of the connection. if it finds a different security context on the calling thread, it switches the identity on the connection, if the new user is also eligible to log in to Teiid otherwise connection fails to execute.
useCallingThread boolean Only applies to "local" connections. When this option is set to "true" (the default), then the calling thread will be used to process the query. If false, then an engine thread will be used.
QueryTimeout integer Default query timeout in seconds. Must be >= 0. 0 indicates no timeout. Can be overriden by Statement.setQueryTimeout. Default 0.
useJDBC4ColumnNameAndLabelSemantics boolean

A change was made in JDBC4 to return unaliased column names as the ResultSetMetadata column name. Prior to this, if a column alias were used it was returned as the column name. Setting this property to false will enable backwards compatibility when JDBC3 and older support is still required. Defaults to true.

jaasName String

JAAS configuration name. Only applies when configuring a GSS authentication. See the Admin Guide for configuration required for GSS.

kerberosServicePrincipleName String

Kerberos authenticated principle name. Only applies when configuring a GSS authentication. See the Admin Guide for configuration required for GSS


To use a data source based connection, use org.teiid.jdbc.TeiidDataSource as the data source class. The TeiidDataSource is also an XADatasource. Teiid DataSource class is also Serializable, so it possible for it to be used with JNDI naming services.

All the properties (except for version, which is known on TeiidDataSource as DatabaseVersion) defined in the connection propertieshave corresponding "set" methods on the org.teiid.jdbc.TeiidDataSource. Properties that are assumed from the URL string have addtional "set" methods, which are described in the following table.


Teiid can be configured as a JDBC data source in the JBoss Application Server to be accessed from JNDI or injected into your JEE applications. Deploying Teiid as data source in JBoss AS is exactly same as deploying any other RDBMS resources like Oracle or DB2.

Defining as data source is not limited to JBoss AS, you can also deploy as data source in Glassfish, Tomcat, Websphere, Weblogic etc servers, however their configuration files are different than JBoss AS. Consult the respective documentation of the environment in which you are deploying.

A special case is if the Teiid instance you are connecting to is in the same VM as the JBoss AS instance. If that matches you deployment, then follow the Section 1.4.3, “Local JDBC Connection” instructions

Installation Steps

  1. If Teiid is not installed in the AS instance, copy the teiid-7.6-client.jar into <jboss-install>/server/<profile>/lib directory.

  2. Create a "<datasource name>-ds.xml" file in <jboss-install>/server/<profile>/deploy directory. Based on the type of deployment (XA, driver, or local), the contents of the file will be different. See the following sections for more.

The data source will then be accessable through the JNDI name specified in the -ds.xml file.

A group of Teiid Servers in the same JBoss AS cluster may be connected using failover and load-balancing features. To enable theses features in their simplest form, the client needs to specify multiple host name and port number combinations on the URL connection string.


If you are using a DataSource to connect to Teiid Server, use the "AlternateServers" property/method to define the failover servers. The format is also a comma separated list of host:port combinations.

The client will randomly pick one the Teiid server from the list and establish a session with that server. If that server cannot be contacted, then a connection will be attempted to each of the remaining servers in random order. This allows for both connection time fail-over and random server selection load balancing.

Post connection load balancing can be utilized in one of two ways. First if you are using TeiidDataSource and the Connections returned by Teiid PooledConnections have their close method called, then a new server instance will be selected automatically. However when using driver based connections or even when using TeiidDataSource in a connection pool (such as JBoss AS), the automatic load balancing will not happen. Second you can explicitly trigger load balancing through the use of the set statement:

SET NEWINSTANCE TRUE

Typically you will not need want to issue this statement manually, but instead use it as the connection test query on your DataSource configuration.


Teiid by default maintians a pool of extra socket connections that are reused. For load balancing, this reduces the potential cost of switching a connection to another server instance. The default setting is to maintain 16 connections (see org.teiid.sockets.maxCachedInstances in the client jar's teiid-client-settings.properties file. If you're client is connecting to large numbers of Teiid instances and you're using post connection time load balancing, then consider increasing the number of cached instances. You may either set an analogous system property or create another version of teiid-client-settings.properties file and place it into the classpath ahead of the client jar.

Teiid provides a standard implementation of java.sql.PreparedStatement. PreparedStatements can be very important in speeding up common statement execution, since they allow the server to skip parsing, resolving, and planning of the statement. See the Java documentation for more information on PreparedStatement usage.

PreparedStatement Considerations

  • It is not necessary to pool client side Teiid PreparedStatements, since Teiid performs plan caching on the server side.

  • The number of cached plans is configurable (see the Admin Guide), and are purged by the least recently used (LRU).

  • Cached plans are not distributed through a cluster. A new plan must be created for each cluster member.

  • Plans are cached for the entire VDB or for just a particular session. The scope of a plan is detected automatically based upon the functions evaluated during it's planning process.

  • Stored procedures executed through a CallableStatement have their plans cached just as a PreparedStatement.

  • Bind variable types in function signatures, e.g. "where t.col = abs(?)" can be determined if the function has only one signature or if the function is used in a predicate where the return type can be determined. In more complex situations it may be necessary to add a type hint with a cast or convert, e.g. upper(convert(?, string)).

The Teiid statement extension interface, org.teiid.jdbc.TeiidStatement, provides functionality beyond the JDBC standard. To use the extension interface, simply cast or unwap the statement returned by the Connection. The following methods are provided on the extension interface:


The Teiid Server supports a "partial results" query mode. This mode changes the behavior of the query processor so the server returns results even when some data sources are unavailable.

For example, suppose that two data sources exist for different suppliers and your data Designers have created a virtual group that creates a union between the information from the two suppliers. If your application submits a query without using partial results query mode and one of the suppliers’ databases is down, the query against the virtual group returns an exception. However, if your application runs the same query in “partial results” query mode, the server returns data from the running data source and no data from the data source that is down.

When using "partial results" mode, if a source throws an exception during processing it does not cause the user’s query to fail. Rather, that source is treated as returning no more rows after the failure point. Most commonly, that source will return 0 rows.

This behavior is most useful when using UNION or OUTER JOIN queries as these operations handle missing information in a useful way. Most other kinds of queries will simply return 0 rows to the user when used in partial results mode and the source is unavailable.

For each source that is excluded from the query, a warning will be generated describing the source and the failure. These warnings can be obtained from the Statement.getWarnings() method. This method returns a SQLWarning object but in the case of "partial results" warnings, this will be an object of type org.teiid.jdbc.PartialResultsWarning class. This class can be used to obtain a list of all the failed sources by name and to obtain the specific exception thrown by each resource adaptor.

Partial results mode is off by default but can be turned on for all queries in a Connection with either setPartialResultsMode("true") on a DataSource or partialResultsMode=true on a JDBC URL. In either case, partial results mode may be toggled later with a set statement.



JDBC query execution can indefinitely block the calling thread when a statement is executed or a resultset is being iterated. In some situations you may wish to have your calling threads held in these blocked states. When using embedded connections, you may optionally use the org.teiid.jdbc.TeiidStatement and org.teiid.jdbc.TeiidPreparedStatement interfaces to execute queries with a callback org.teiid.jdbc.StatementCallback that will be notified of statement events, such as an available row, an exception, or completion. Your calling thread will be free to perform other work. The callback will be executed by an engine processing thread as needed. If your results processing is itself blocking and you want query processing to be concurrent with results processing, then your callback should implement onRow handling in a multi-threaded manner to allow the engine thread to continue.


Note

The non-blocking logic is limited to statement execution only. Other JDBC operations, such as connection creation or batched executions do not yet have non-blocking options.

The Teiid JDBC API supports three types of transactions from a client perspective – global, local, and request level. All are implemented by the Teiid Server as XA transactions. See the JTA specification for more on XA Transactions.

The Connection class uses the "autoCommit" flag to explicitly control local transactions. By default, autoCommit is set to "true", which indicates request level or implicit transaction control. example of how to use local transactions by setting the autoCommit flag to false.


This example demonstrates several things:

  1. Setting autoCommit flag to false. This will start a transaction bound to the connection.

  2. Executing multiple updates within the context of the transaction.

  3. When the statements are complete, the transaction is committed by calling commit().

  4. If an error occurs, the transaction is rolled back using the rollback() method.

Any of the following operations will end a local transaction:

  1. Connection.setAutoCommit(true) – if previously set to false

  2. Connection.commit()

  3. Connection.rollback()

  4. A transaction will be rolled back automatically if it times out.

Request level transactions are used when the request is not in the scope of a global or local transaction, which implies "autoCommit" is "true". In a request level transaction, your application does not need to explicitly call commit or rollback, rather every command is assumed to be its own transaction that will automatically be committed or rolled back by the server.

The Teiid Server can perform updates through virtual tables. These updates might result in an update against multiple physical systems, even though the application issues the update command against a single virtual table. Often, a user might not know whether the queried tables actually update multiple sources and require a transaction.

For that reason, the Teiid Server allows your application to automatically wrap commands in transactions when necessary. Because this wrapping incurs a performance penalty for your queries, you can choose from a number of available wrapping modes to suit your environment. You need to choose between the highest degree of integrity and performance your application needs. For example, if your data sources are not transaction-compliant, you might turn the transaction wrapping off (completely) to maximize performance.

You can set the transaction mode as a property when you establish the Connection or on a per-query basis using the execution properties. For more information on execution properties, see the section “Execution Properties”

Global or client XA transactions allow the Teiid JDBC API to participate in transactions that are beyond the scope of a single client resource. For this use the Teiid DataSource Class for establishing connections.

When the DataSource is used in the context of a UserTransaction in an application server, such as JBoss, WebSphere, or Weblogic, the resulting connection will already be associated with the current XA transaction. No additional client JDBC code is necessary to interact with the XA transaction.


With the use of global transactions multiple Teiid XAConnections may participate in the same transaction. It is important to note that the Teiid JDBC XAResource "isSameRM" method only returns "true", if connections are made to the same server instance in a cluster. If the Teiid connections are to different server instances then transactional behavior may not be the same as if they were to the same cluster member. For example, if the client transaction manager uses the same XID for each connection, duplicate XID exceptions may arise from the same physical source accessed through different cluster members. If the client transaction manager uses a different branch identifier for each connection, issues may arise with sources that lock or isolate changes based upon branch identifiers.

This chapter will shows you various security configurations that can be used with Teiid in securing your data access. Note that data level security called as "data roles" are explained in Reference Guide. This chapter pertains to transport level security.

By default all JDBC/Admin sensitive (non-data) messages between client and server are encrypted using a Diffy-Hellman key that is negotiated per connection. This encryption is controlled by clientEncryptionEnabled property in JdbcSslConfiguration and AdminSslConfiguration sections in the <jboss-install>/server/<profile>/deploy/teiid/teiid-jboss-beans.xml file.

If you are using a socket connection, then you may need to secure the channel more completely - especially if using ODBC, which currently only supports plain text authentication.

The following sections define the properties required for each SSL mode. Note that when connecting to Teiid Server with SSL enabled, you MUST use the "mms" protocol, instead of "mm" in the JDBC connection URL, for example

jdbc:teiid:<myVdb>@mms://<host>:<port>

There are two different sets of properties that a client can configure to enable 1-way or 2-way SSL.

Use this option for anonymous mode or when the above "javax" based properties are already in use by the host process. For example if your client application is a Tomcat process that is configured for https protocol and the above Java based properties are already in use, and importing Teiid-specific certificate keys into those https certificate keystores is not allowed.

In this scenario, a different set of Teiid-specific SSL properties can be set as system properties or defined inside the "teiid-client-settings.properties" file. The "teiid-client-settings.properties" file can be found inside the "teiid-7.6-client.jar" file at the root. Extract this file, or make a copy, change the property values required for the chosen SSL mode, and place this file in the client application's classpath before the "teiid-7.6-client.jar" file.

SSL properties and definitions inside the "teiid-client-settings.properties" are shown below.

########################################
# SSL Settings
########################################

#
# The key store type.  Defaults to JKS
#

org.teiid.ssl.keyStoreType=JKS

#
# The key store algorithm, defaults to 
# the system property "ssl.TrustManagerFactory.algorithm"
#

#org.teiid.ssl.algorithm=

#
# The classpath or filesystem location of the
# key store.
# 
# This property is required only if performing 2-way
# authentication that requires a specific private 
# key.
#

#org.teiid.ssl.keyStore=

#
# The key store password (not required)
#

#org.teiid.ssl.keyStorePassword=

#
# The classpath or filesystem location of the
# trust store.
# 
# This property is required if performing 1-way
# authentication that requires trust not provided
# by the system defaults.
#

#org.teiid.ssl.trustStore=

#
# The trust store password (not required)
#

#org.teiid.ssl.trustStorePassword=

#
# The cipher protocol, defaults to SSLv3
#

org.teiid.ssl.protocol=SSLv3

#
# Whether to allow anonymous SSL
# (the TLS_DH_anon_WITH_AES_128_CBC_SHA cipher suite)
# defaults to true
#

org.teiid.ssl.allowAnon=true



For the most part, interacting with Teiid VDBs (Virtual Databases) from Hibernate is no different from working with any other type of data source.  First you must place Teiid JDBC API client JAR file and Teiid's hibernate dialect JAR in Hibernate’s classpath.  These files can be found in <jboss-install>/server/<profile>/lib directory.

These JAR files have the org.teiid.dialect.TeiidDialect and org.teiid.jdbc.TeiidDriver and org.teiid.jdbc.TeiidDataSource classes.

You then configure Hibernate (via hibernate.cfg.xml) as follows:

  1. Specify the Teiid driver class in the "connection.driver_class" property:

    <property name="connection.driver_class">
         org.teiid.jdbc.TeiidDriver
    </property>

  2. Specify the URL for the VDB in the "connection.url" property (replacing terms in angle brackets with the appropriate values):

    <property name="connection.url">
    	jdbc:teiid:<vdb-name>@mm://<host>:<port>;user=<user-name>;password=<password>
    </property>

    Note

    Be sure to use a Section 1.4.3, “Local JDBC Connection” if Hibernate is in the same VM as the application server.

  3. Specify the Teiid dialect class in the “dialect” property:

    <property name="dialect">
    	org.teiid.dialect.TeiidDialect
    </property>

    Alternatively, if you put your connection properties in hibernate.properties instead of hibernate.cfg.xml, they would look like this:

    hibernate.connection.driver_class=org.teiid.jdbc.TeiidDriver
    hibernate.connection.url=jdbc:teiid:<vdb-name>@mm://<host>:<port>
    hibernate.connection.username=<user-name>
    hibernate.connection.password=<password>
    hibernate.dialect=org.teiid.dialect.TeiidDialect

Note also that since your VDBs will likely contain multiple source and view models with identical table names, you will need to fully qualify table names specified in Hibernate mapping files:

<class name="<Class name>" table="<Source/view model name>.[<schema name>.]<Table name>">
	...
</class>

Open DataBase Connectivity (ODBC) is a standard database access method developed by the SQL Access group in 1992. ODBC, just like JDBC in Java, allows consistent client access regardless of which database management system (DBMS) is handling the data. ODBC uses a driver to translate the application's data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant -- that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.

Teiid can provide ODBC access to deployed VDBs in the Teiid runtime through PostgreSQL's ODBC driver. This is possible because Teiid has specialized handling that allows it emulate a PostgreSQL server and respond appropriate to expected metadata queries.

Note

By default, ODBC on the Teiid is enabled and running on on port 35432.

Before an application can use ODBC, you must first install the ODBC driver on same machine that the application is running on and then create Data Source Name (DSN) that represents a connection profile for your Teiid VDB.

Warning

Teiid currently only supports plain text passward authentication for ODBC. If the client/server are not configured to use SSL, the password will be sent in plain text over the network. If you need secure passwords in transit and are not using SSL, then consider installing a security domain that will accept safe password values from the client (for example encrypted or hashed).

A PostgreSQL ODBC driver needed to make the ODBC connection to Teiid is not bundled with the Teiid distribution. The appropriate driver needs be downloaded directly from the PostgreSQL web site. We have tested with 8.04.200 version of the ODBC driver.

For all other platforms other than Microsoft Windows, the ODBC driver needs built from the source files provided. Download the ODBC driver source files from the PostgreSQL download site. Untar the files to a temporary location. For example: "~/tmp/pgodbc". Build and install the driver by running the commands below.

Note

You should use super user account or use "sudo" command for running the "make install" command.

                % tar -zxvf psqlodbc-xx.xx.xxxx.tar.gz
                % cd psqlodbc-xx.xx.xxxx
                % ./configure
                % make
                % make install
            

Some *nix distributions may already provide binary forms of the appropriate driver, which can be used as an alternative to building from source.

Once you have installed the ODBC Driver Client software on your workstation, you have to configure it to connect to a Teiid Runtime. Note that the following instructions are specific to the Microsoft Windows Platform.

To do this, you must have logged into the workstation with administrative rights, and you need to use the Control Panel’s Data Sources (ODBC) applet to add a new data source name.

Each data source name you configure can only access one VDB within a Teiid System. To make more than one VDB available, you need to configure more than one data source name.

Follow the below steps in creating a data source name (DSN)

  1. From the Start menu, select Settings > Control Panel.

  2. The Control Panel displays. Double click Administrative Tools.

  3. Then Double-click Data Sources (ODBC).

  4. The ODBC Data Source Administrator applet displays. Click the tab associated with the type of DSN you want to add.

  5. The Create New Data Source dialog box displays. In the Select a driver for which you want to set up a data source table, select PostgreSQL Unicode.

  6. Click Finish

  7. The PostgreSQL ODBC DSN Setup dialog box displays.


  8. In the Data Source Name edit box, type the name you want to assign to this data source.

    In the Database edit box, type the name of the virtual database you want to access through this data source.

    In the Server edit box, type the host name or IP address of your Teiid runtime. If connecting via a firewall or NAT address, the firewall address or NAT address should be entered.

    In the Port edit box, type the port number to which the Teiid System listens for ODBC requests. By default, Teiid listenes for ODBC requests on port 35432

    In the User Name and Password edit boxes, supply the user name and password for the Teiid runtime access.

    Provide any description about the data source in the Description field.

  9. Click on the Datasource button, you will see this below figure. Configure options as shown.


    Click on "page2" and make sure the options are selected as shown


  10. Click "save" and you can optionally click "test" to validate your connection if the Teiid is running.

You have configured a Teiid's virtual database as a data source for your ODBC applications. Now you can use applications such as Excel, Access to query the data in the VDB

Before you can access Teiid using ODBC on any *nix platforms, you need to either install a ODBC driver manager or verify that one already exists. As the ODBC Driver manager Teiid recommends unixODBC. If you are working with RedHat Linux or Fedora you can check the graphical "yum" installer to search, find and install unixODBC. Otherwise you can download the unixODBC manager here. To install, simply untar the contents of the file to a temporary location and execute the following commands as super user.

                ./configure
                make
                make install 
            

Check unixODBC website site for more information, if you run into any issues during the installation.

Now, to o verify that PostgreSQL driver installed correctly from earlier step, execute the following command

            odbcinst -q -d
            

That should show you all the ODBC drivers installed in your system. Now it is time to create a DSN. Edit "/etc/odbc.ini" file and add the following

                [<DSN name>]
                Driver = /usr/lib/psqlodbc.so
                Description = PostgreSQL Data Source
                Servername = <Teiid Host name or ip>
                Port = 35432 
                Protocol = 7.4-1
                UserName = <user-name> 
                Password = <password>
                Database = <vdb-name>
                ReadOnly = no
                ServerType = Postgres
                ConnSettings = 
                UseServerSidePrepare=1
                ByteaAsLongVarBinary=1
                Optimizer=0
                Ksqo=0
                Debug=0
                Fetch = 10000
                # enable below when dealing large resultsets
                #UseDeclareFetch=1                
            

Note that you need "sudo" permissions to edit the "/etc/odbc.ini" file. For all the available configurable options that you can use in defining a DSN can be found here on postgreSQL ODBC page.

Once you are done with defining the DSN, you can verify your DSN using the following command

                    isql <DSN-name> [<user-name> <password>] < commands.sql            
            

where "commands.sql" file contains the SQL commands you would like to execute.

Based upon the JDBC in JDK 1.6, this appendix details only those JDBC methods that Teiid does not support. Unless specified below, Teiid supports all other JDBC Methods.

Those methods listed without comments throw a SQLException stating that it is not supported.

Where specified, some listed methods do not throw an exception, but possibly exhibit unexpected behavior. If no arguments are specified, then all related (overridden) methods are not supported. If an argument is listed then only those forms of the method specified are not supported.

Table A.1. Connection Properties

Class nameMethods
Array Not Supported
Blob
getBinaryStream(long, long) - throws SQLFeatureNotSupportedException
setBinaryStream(long) - - throws SQLFeatureNotSupportedException
setBytes - - throws SQLFeatureNotSupportedException
truncate(long) - throws SQLFeatureNotSupportedException
                            
CallableStatement
getArray - throws SQLFeatureNotSupportedException
getBigDecimal(String parameterName)- throws SQLFeatureNotSupportedException
getBlob(String parameterName)- throws SQLFeatureNotSupportedException
getBoolean(String parameterName)- throws SQLFeatureNotSupportedException
getByte(String parameterName)- throws SQLFeatureNotSupportedException
getBytes(String parameterName)- throws SQLFeatureNotSupportedException
getCharacterStream(String parameterName)- throws SQLFeatureNotSupportedException
getClob(String parameterName)- throws SQLFeatureNotSupportedException
getDate(String parameterName, *)- throws SQLFeatureNotSupportedException
getDouble(String parameterName)- throws SQLFeatureNotSupportedException
getFloat(String parameterName)- throws SQLFeatureNotSupportedException
getInt(String parameterName)- throws SQLFeatureNotSupportedException
getLong(String parameterName)- throws SQLFeatureNotSupportedException
getNCharacterStream - throws SQLFeatureNotSupportedException
getNClob - throws SQLFeatureNotSupportedException
getNString - throws SQLFeatureNotSupportedException
getObject(int parameterIndex, Map&lt;String, Class&lt;?&gt;&gt; map) - throws SQLFeatureNotSupportedException
getObject(String parameterName) - throws SQLFeatureNotSupportedException
getRef - throws SQLFeatureNotSupportedException
getRowId - throws SQLFeatureNotSupportedException
getShort(String parameterName) - throws SQLFeatureNotSupportedException
getSQLXML(String parameterName) - throws SQLFeatureNotSupportedException
getString(String parameterName) - throws SQLFeatureNotSupportedException
getTime(String parameterName, *) - throws SQLFeatureNotSupportedException
getTimestamp(String parameterName, *) - throws SQLFeatureNotSupportedException
getURL(String parameterName) - throws SQLFeatureNotSupportedException
registerOutParameter - ignores
registerOutParameter(String parameterName, *) - throws SQLFeatureNotSupportedException 
setAsciiStream - throws SQLFeatureNotSupportedException
setBigDecimal(String parameterName, BigDecimal x)- throws SQLFeatureNotSupportedException
setBinaryStream(String parameterName, *) - throws SQLFeatureNotSupportedException
setBlob(String parameterName, *)- throws SQLFeatureNotSupportedException
setBoolean(String parameterName, boolean x) - throws SQLFeatureNotSupportedException
setByte(String parameterName, byte x) - throws SQLFeatureNotSupportedException
setBytes(String parameterName, byte[] x) - throws SQLFeatureNotSupportedException
setCharacterStream - throws SQLFeatureNotSupportedException
setClob(String parameterName, *) - throws SQLFeatureNotSupportedException
setDate(String parameterName, *) - throws SQLFeatureNotSupportedException
setDouble(String parameterName, double x) - throws SQLFeatureNotSupportedException
setFloat(String parameterName, float x) - throws SQLFeatureNotSupportedException
setLong(String parameterName, long x) - throws SQLFeatureNotSupportedException
setNCharacterStream - throws SQLFeatureNotSupportedException
setNClob - throws SQLFeatureNotSupportedException
setNString - throws SQLFeatureNotSupportedException
setNull - throws SQLFeatureNotSupportedException
setObject(String parameterName, *) - throws SQLFeatureNotSupportedException
setRowId(String parameterName, RowId x) - throws SQLFeatureNotSupportedException
setSQLXML(String parameterName, SQLXML xmlObject) - throws SQLFeatureNotSupportedException
setShort(String parameterName, short x) - throws SQLFeatureNotSupportedException
setString(String parameterName, String x) - throws SQLFeatureNotSupportedException
setTime(String parameterName, *) - throws SQLFeatureNotSupportedException
setTimestamp(String parameterName, *) - throws SQLFeatureNotSupportedException
setURL(String parameterName, URL val) - throws SQLFeatureNotSupportedException
                            
Clob
getCharacterStream(long arg0, long arg1) - throws SQLFeatureNotSupportedException
setAsciiStream(long arg0) - throws SQLFeatureNotSupportedException
setCharacterStream(long arg0) - throws SQLFeatureNotSupportedException
setString - throws SQLFeatureNotSupportedException
truncate - throws SQLFeatureNotSupportedException
                            
Connection
createArrayOf - throws SQLFeatureNotSupportedException
createBlob - throws SQLFeatureNotSupportedException
createClob - throws SQLFeatureNotSupportedException
createNClob - throws SQLFeatureNotSupportedException
createSQLXML - throws SQLFeatureNotSupportedException
createStatement(int resultSetType,int resultSetConcurrency, int resultSetHoldability) - throws SQLFeatureNotSupportedException
createStruct(String typeName, Object[] attributes) - throws SQLFeatureNotSupportedException
getClientInfo - throws SQLFeatureNotSupportedException
prepareCall(String sql, int resultSetType,int resultSetConcurrency, int resultSetHoldability) - throws SQLFeatureNotSupportedException
prepareStatement(String sql, int autoGeneratedKeys) - throws SQLFeatureNotSupportedException
prepareStatement(String sql, int[] columnIndexes) - throws SQLFeatureNotSupportedException
prepareStatement(String sql, String[] columnNames) - throws SQLFeatureNotSupportedException
releaseSavepoint - throws SQLFeatureNotSupportedException
rollback(Savepoint savepoint) - throws SQLFeatureNotSupportedException
setHoldability - throws SQLFeatureNotSupportedException
setSavepoint - throws SQLFeatureNotSupportedException
setTypeMap - throws SQLFeatureNotSupportedException
                            
DatabaseMetaData
getAttributes - throws SQLFeatureNotSupportedException
getClientInfoProperties  - throws SQLFeatureNotSupportedException
getFunctionColumns - throws SQLFeatureNotSupportedException
getFunctions - throws SQLFeatureNotSupportedException
getRowIdLifetime - throws SQLFeatureNotSupportedException
                            
NClob Not Supported
PreparedStatement
execute(String sql) - throws SQLException
executeQuery(String sql) - throws SQLException
executeUpdate(String sql) - throws SQLException
setArray - throws SQLFeatureNotSupportedException
setNCharacterStream - throws SQLFeatureNotSupportedException
setNClob - throws SQLFeatureNotSupportedException
setRef - throws SQLFeatureNotSupportedException
setRowId - throws SQLFeatureNotSupportedException
setUnicodeStream - throws SQLFeatureNotSupportedException
                            
Ref Not Implemented
ResultSet
deleteRow - throws SQLFeatureNotSupportedException
getArray - throws SQLFeatureNotSupportedException
getAsciiStream - throws SQLFeatureNotSupportedException
getHoldability - throws SQLFeatureNotSupportedException
getNCharacterStream - throws SQLFeatureNotSupportedException
getNClob - throws SQLFeatureNotSupportedException
getNString - throws SQLFeatureNotSupportedException
getObject(*, Map&lt;String, Class&lt;?&gt;&gt; map) - throws SQLFeatureNotSupportedException
getRef - throws SQLFeatureNotSupportedException
getRowId - throws SQLFeatureNotSupportedException
getUnicodeStream - throws SQLFeatureNotSupportedException
getURL - throws SQLFeatureNotSupportedException
insertRow - throws SQLFeatureNotSupportedException
moveToInsertRow - throws SQLFeatureNotSupportedException
refreshRow - throws SQLFeatureNotSupportedException
rowDeleted - throws SQLFeatureNotSupportedException
rowInserted - throws SQLFeatureNotSupportedException
rowUpdated - throws SQLFeatureNotSupportedException
setFetchDirection - throws SQLFeatureNotSupportedException
update*  - throws SQLFeatureNotSupportedException
                            
RowId Not Supported
Savepoint not Supported
SQLData Not Supported
SQLInput not Supported
SQLOutput Not Supported
Statement
execute(String, int)
execute(String, int[])
execute(String, String[])
executeUpdate(String, int)
executeUpdate(String, int[])
executeUpdate(String, String[])
getGeneratedKeys()
getResultSetHoldability()
setCursorName(String)
                            
Struct Not Supported

To generate a self-signed certificate, you need a program called “keytool”, which is supplied with any version of the Java SDK. The instructions below walk through the creation of both the key store and the trust store files for a 1-way SSL configuration with the security keys.