Teiid - Scalable Information Integration

Once you have a "VDB" built it can be deployed/removed in Teiid runtime in different ways.

http://<host>:<port>/admin-console

deploy [--all-server-groups] /path/to/my.vdb

Apart from deploying the VDB, the user is also responsible for providing all the necessary dependent libraries, configuration for creating the data sources that are needed by the models (schemas) defined in "META-INF/vdb.xml" file inside your VDB. For example, if you are trying to integrate data from Oracle RDBMS and File sources in your VDB, then you are responsible for providing the JDBC driver for the Oracle source and any necessary documents and configuration that are needed by the File Translator.

Data source instances may be used by only one VDB, or may be shared with as many VDBs or other applications as makes since for your deployments. Consider sharing connections to sources that have heavy-weight and resource constrained connections.

With the exception of JDBC sources, other supported data sources have a corresponding JCA connector (.rar) files in "<jboss-install>/standalone/deployments"" directory. Either directly edit the standalone-teiid.xml or use CLI or Admin API to create the required data sources by the VDB. Example configurations are provided for all the sources in "<jboss-install>/docs/teiid/datasources" directory. Note that in the domain mode, you must either use CLI or admin-console or AdminShell to configure.

Some data sources may contain passwords or other sensitive information. See the WIKI article EncryptingDataSourcePasswords to not store passwords in plain text.

Once the VDB and its dependencies are deployed, then client applications can connect using the JDBC API. If there are any errors in the deployment, a connection attempt will not be successful and a message will be logged. You can use the admin-console tool or check the log files for errors and correct them before proceeding.

<!-- If susbsytem is already defined, only copy the contents under it and edit to suit your needs -->
<subsystem xmlns="urn:jboss:domain:resource-adapters:1.0">
    <resource-adapters>
        <resource-adapter>
            <archive>teiid-connector-file.rar</archive>
            <transaction-support>NoTransaction</transaction-support>
            <connection-definitions>
                <connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" 
                        jndi-name="java:/fileDS" 
                        enabled="true" 
                        use-java-context="true" 
                        pool-name="file-ds">
                        
                       <!-- Directory where the data files are stored -->
                      <config-property name="ParentDirectory">/home/rareddy/testing/</config-property>
                      
                      <!-- Optional properties -->
                      
                      <!-- Set FileMapping to redirect specific relative paths (case sensitive) to alternative locations.
                           The string value specifies a map in the format key=value(,key=value)*
                           -->
                      <!-- <config-property name="FileMapping">file1.txt=fileX.txt,file2.txt=fileY.txt</config-property> -->
                      
                      <!-- Set AllowParentPaths to false to disallow .. in paths.
                           This prevent requesting files that are not contained in the parent directory -->
                      <config-property name="AllowParentPaths">true</config-property>
                </connection-definition>
            </connection-definitions>
        </resource-adapter>
    </resource-adapters>
</subsystem>

<!-- If susbsytem is already defined, only copy the contents under it and edit to suit your needs -->
<subsystem xmlns="urn:jboss:domain:resource-adapters:1.0">
    <resource-adapters>
        <resource-adapter>
            <archive>teiid-connector-ws.rar</archive>
            <transaction-support>NoTransaction</transaction-support>
            <connection-definitions>
                <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" 
                        jndi-name="java:/wsDS" 
                        enabled="true" 
                        use-java-context="true" 
                        pool-name="ws-ds">
                        
                    <config-property name="EndPoint">http://somewhere.com</config-property>
                </connection-definition>
            </connection-definitions>
        </resource-adapter>
    </resource-adapters>
</subsystem>

<beans xmlns="http://www.springframework.org/schema/beans"
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		xmlns:http-conf="http://cxf.apache.org/transports/http/configuration"
		xsi:schemaLocation="http://cxf.apache.org/transports/http/configuration
		http://cxf.apache.org/schemas/configuration/http-conf.xsd
		http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans.xsd">

	<http-conf:conduit name="{http://teiid.org}configName.http-conduit">
		<http-conf:client ConnectionTimeout="120000" ReceiveTimeout="240000"/>
	</http-conf:conduit>
</beans>

<!-- If susbsytem is already defined, only copy the contents under it and edit to suit your needs -->
<subsystem xmlns="urn:jboss:domain:resource-adapters:1.0">
    <resource-adapters>
        <resource-adapter>
            <archive>teiid-connector-ws.rar</archive>
            <transaction-support>NoTransaction</transaction-support>
            <connection-definitions>
                <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" 
                        jndi-name="java:/wsDS" 
                        enabled="true" 
                        use-java-context="true" 
                        pool-name="ws-ds">
                        
                      <config-property name="EndPoint">http://somewhere.com</config-property>
                      <config-property name="ConfigFile">${jboss.server.home.dir}/server/default/conf/xxx-jbossws-cxf.xml</config-property>
                      <config-property name="ConfigName">port_x</config-property>
                      <config-property name="SecurityType">WSSecurity</config-property>
                </connection-definition>
            </connection-definitions>
        </resource-adapter>
    </resource-adapters>
</subsystem>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:jaxws="http://cxf.apache.org/jaxws"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
          http://www.springframework.org/schema/beans/spring-beans.xsd
          http://cxf.apache.org/jaxws
          http://cxf.apache.org/schemas/jaxws.xsd">

    <jaxws:client name="{http://teiid.org}port_x" 
        createdFromAPI="true">
        <jaxws:outInterceptors>
            <bean class="org.apache.cxf.binding.soap.saaj.SAAJOutInterceptor"/>
            <ref bean="Timestamp_Request"/>
        </jaxws:outInterceptors>
    </jaxws:client>

    <bean 
        class="org.apache.cxf.ws.security.wss4j.WSS4JOutInterceptor"
        id="Timestamp_Request">
        <constructor-arg>
            <map>
                <entry key="action" value="Timestamp"/>
            <map>
        </constructor-arg>
    </bean>
    
</beans>

<!-- If susbsytem is already defined, only copy the contents under it and edit to suit your needs -->
<subsystem xmlns="urn:jboss:domain:resource-adapters:1.0">
    <resource-adapters>
        <resource-adapter>
            <archive>teiid-connector-ws.rar</archive>
            <transaction-support>NoTransaction</transaction-support>
            <connection-definitions>
                <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" 
                        jndi-name="java:/wsDS" 
                        enabled="true" 
                        use-java-context="true" 
                        pool-name="ws-ds">
                        
                      <config-property name="EndPoint">http://somewhere.com</config-property>
                      <config-property name="ConfigFile">${jboss.server.home.dir}/server/default/conf/xxx-jbossws-cxf.xml</config-property>
                      <config-property name="ConfigName">port_y</config-property>
                </connection-definition>
            </connection-definitions>
        </resource-adapter>
    </resource-adapters>
</subsystem>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:jaxws="http://cxf.apache.org/jaxws"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
          http://www.springframework.org/schema/beans/spring-beans.xsd
          http://cxf.apache.org/jaxws
          http://cxf.apache.org/schemas/jaxws.xsd">

    <jaxws:client name="{http://teiid.org}port_y" 
        createdFromAPI="true">
        <jaxws:features>
            <bean class="org.apache.cxf.feature.LoggingFeature"/>
        </jaxws:features>
    </jaxws:client>
    
</beans>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:http-conf="http://cxf.apache.org/transports/http/configuration"
       xsi:schemaLocation="http://cxf.apache.org/transports/http/configuration
                           http://cxf.apache.org/schemas/configuration/http-conf.xsd
                           http://www.springframework.org/schema/beans
                             http://www.springframework.org/schema/beans/spring-beans.xsd">

  <http-conf:conduit name="{http://teiid.org}port_z.http-conduit">
     <!-- WARNING ! disableCNcheck=true should NOT be used in production -->
     <http-conf:tlsClientParameters disableCNcheck="true" />

  </http-conf:conduit>
</beans>

<!-- If susbsytem is already defined, only copy the contents under it and edit to suit your needs -->
<subsystem xmlns="urn:jboss:domain:resource-adapters:1.0">
    <resource-adapters>
        <resource-adapter>
            <archive>teiid-connector-salesforce.rar</archive>
            <transaction-support>NoTransaction</transaction-support>
            <connection-definitions>
                <connection-definition class-name="org.teiid.resource.adapter.salesforce.SalesForceManagedConnectionFactory" 
                        jndi-name="java:/sfDS" 
                        enabled="true" 
                        use-java-context="true" 
                        pool-name="sf-ds">
                        
                      <config-property name="URL">https://test.salesforce.com/services/Soap/u/10.0</config-property>
                      <config-property name="username">username</config-property>
                      <config-property name="password">password</config-property>
                </connection-definition>
            </connection-definitions>
        </resource-adapter>
    </resource-adapters>
</subsystem>

<!-- If susbsytem is already defined, only copy the contents under it and edit to suit your needs -->
<subsystem xmlns="urn:jboss:domain:resource-adapters:1.0">
    <resource-adapters>
        <resource-adapter>
            <archive>teiid-connector-ldap.rar</archive>
            <transaction-support>NoTransaction</transaction-support>
            <connection-definitions>
                <connection-definition class-name="org.teiid.resource.adapter.ldap.LDAPManagedConnectionFactory" 
                        jndi-name="java:/ldapDS" 
                        enabled="true" 
                        use-java-context="true" 
                        pool-name="ldap-ds">
                        
                      <config-property name="LdapAdminUserDN">cn=x,ou=y,dc=z</config-property>
                      <config-property name="LdapAdminUserPassword">password</config-property>
                      <config-property name="LdapUrl">ldap://ldapServer:389</config-property>
                </connection-definition>
            </connection-definitions>
        </resource-adapter>
    </resource-adapters>
</subsystem>

VDB Versioning is a feature that allows multiple versions of a VDB to be deployed at the same time with additional support to determine which version will be used. When a user connects to Teiid the desired VDB version can be set as a connection property (See the Client Developers Guide). If a specific version is set, then only that VDB may be connected to. If no version is set, then the deployed VDBs are searched for the appropriate version. This feature helps support more fluid migration scenarios.

Setting the version can either be done in the vdb.xml, which is useful for dynamic vdbs, or through a naming convention of the deployment file - vdbname.version.vdb, e.g. marketdata.2.vdb. The deployer is responsible for choosing an appropriate version number. If there is alreay a VDB name/version that matches the current deployment, then connections to the previous VDB will be terminated and its cache entries will be flushed. Any new connections will then be made to the new VDB.

Once deployed a VDB has an updatable property called connection type, which is used to determine what connections can be made to the VDB. The connection type can be one of:

The connection type may be changed either through the AdminConsole or the AdminAPI.

VDBs from prior release contain an older configuration file version that is no longer supported. Note - XML and File based sources from previous releases have changed, and require manual changes to the VDB.

JDBC clients may use simple passwords to authenticate a user.

Typically a user name is required, however user names may be considered optional if the identity of the user can be discerned by the password credential alone. In any case it is up to the configured security domain to determine whether a user can be authenticated. If you need authentication, the administrator must configure a LoginModule to be used with Teiid. See below for more information on how configure the Login module in JBoss AS.

Authorization covers both administrative activities and data roles. A data role is a collection of permissions (also referred to as entitlements) and a collection of entitled principals or groups. With the deployment of a VDB the deployer can choose which principals and groups have which data roles.

At a transport level Teiid provides built-in support for JDBC over SSL or just sensitive message encryption when SSL is not in use.

Passwords in configuration files however are by default stored in plain text. If you need these values to be encrypted, please see encrypting passwords for instructions on encryption facilities provided by the container.

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.

Teiid can be configured with multiple named application policies that group together relevant LoginModules. Each of these application policy (or domain) names can be used to fully qualify user names to authenticate only against that domain. The format for a qualified name is username@domainname. The security-domain attribute under the transport/authentication element in the <jboss-install>/standalone/configuration/standalone-teiid.xml file should be used set the comma separated list of desired domains. The securityDomains property defaults to a single domain named teiid-security.

If a user name is not fully qualified, then the installed domains will be consulted in order until a domain successfully or unsuccessfully authenticates the user.

If no domain can authenticate the user, the login attempt will fail. Details of the failed attempt including invalid users, which domains were consulted, etc. will be in the server log with appropriate levels of severity.

        
         <subsystem xmlns="urn:jboss:domain:security:1.1">
            <security-domains>
                <security-domain name="teiid-security" cache-type="default">
                    <authentication>
                        <login-module code="UsersRoles" flag="required">
                            <module-option name="usersProperties" value="teiid-security-users.properties"/>
                            <module-option name="rolesProperties" value="teiid-security-roles.properties"/>
                        </login-module>
                    </authentication>
                </security-domain>
            </security-domains>
        </subsystem>

# A users.properties file for use with the UsersRolesLoginModule
# username=password

fred=password
george=password
...

# A roles.properties file for use with the UsersRolesLoginModule
# username=role1,role2,...

data_role_1=fred,sally
data_role_2=george

<transport name="jdbc" protocol="teiid" socket-binding="teiid-jdbc"/>
        <authentication security-domain="teiid-security" krb5-domain="krb5-domain"/>
</transport>

<!--login module that negotiates the login conext for kerberos --> 
         <subsystem xmlns="urn:jboss:domain:security:1.1">
            <security-domains>
                <security-domain name="krb5-domain" cache-type="default">
                    <authentication>
                        <login-module code="Kerberos" flag="required">
                            <module-option name="storeKey">true</module-option>
                            <module-option name="useKeyTab">true</module-option>
                            <module-option  name="principal">demo@EXAMPLE.COM</module-option>
                            <module-option  name="keyTab">path/to/krb5.keytab</module-option>
                            <module-option name="doNotPrompt">true</module-option>
                            <module-option name="debug">false</module-option>
                        </login-module>         
                    </authentication>        
                </security-domain>
                <!-- teiid's default security domain, replace this with your own if needs to be any other JAAS domain  -->
                <security-domain name="teiid-security" cache-type="default">
                    <authentication>
                        <login-module code="UsersRoles" flag="required">
                            <module-option name="usersProperties" value="teiid-security-users.properties"/>
                            <module-option name="rolesProperties" value="teiid-security-roles.properties"/>
                        </login-module>
                    </authentication>
                </security-domain>
            </security-domains>
        </subsystem>

JAVA_OPTS = "$JAVA_OPTS -Djava.security.krb5.realm=EXAMPLE.COM -Djava.security.krb5.kdc=kerberos.example.com -Djavax.security.auth.useSubjectCredsOnly=false"

Client {
    com.sun.security.auth.module.Krb5LoginModule required
    useTicketCache=true
    storeKey=true
    useKeyTab=true 
    keyTab="/path/to/krb5.keytab" 
    doNotPrompt=false 
    debug=false
    principal="demo@EXAMPLE.COM";
};

-Djava.security.krb5.realm=EXAMPLE.COM
-Djava.security.krb5.kdc=kerberos.example.com
-Djavax.security.auth.useSubjectCredsOnly=false
-Dsun.security.krb5.debug=false
-Djava.security.auth.login.config=/path/to/client.conf

authenticationType=KRB5;jaasName=Client;kerberosServicePrincipleName=demo@EXAMPLE.COM

<subsystem xmlns="urn:jboss:domain:security:1.1">
            <security-domains>
                <security-domain name="my-security-domain" cache-type="default">
                    <authentication>
                        <login-module code="UsersRoles" flag="required">
                            <module-option name = "password-stacking">useFirstPass</module-option>
                            <module-option name="usersProperties">props/teiid-security-users.properties</module-option>
                            <module-option name="rolesProperties">props/teiid-security-roles.properties</module-option>
                        </login-module>
                        
                        <login-module code="org.jboss.resource.security.CallerIdentityLoginModule" flag="required">
                            <module-option name = "password-stacking">useFirstPass</module-option>
                            <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS</module-option>
                        </login-module>
                    
                    </authentication>
                </security-domain>
                </security-domains>
                

<security-domain>teiid-security</security-domain>

<subsystem xmlns="urn:jboss:domain:security:1.1">
            <security-domains>
                <security-domain name="my-security-domain" cache-type="default">
                    <authentication>
        
                        <login-module code="UsersRoles" flag="required">
                            <module-option name = "password-stacking">useFirstPass</module-option>
                            <module-option name="usersProperties">teiid-security-users.properties</module-option>
                            <module-option name="rolesProperties">teiid-security-roles.properties</module-option>
                        </login-module>
                        
                        <login-module code="org.teiid.jboss.RoleBasedCredentialMapIdentityLoginModule" flag="required">
                            <module-option name = "password-stacking">useFirstPass</module-option>
                            <module-option name="credentialMap">teiid-credentialmap.properties</module-option>
                            <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS</module-option>
                        </login-module>            
                    
                    </authentication>
                </security-domain>
             </security-domains>

<security-domain>teiid-security</security-domain>

<login-module code="org.teiid.jboss.RoleBasedCredentialMapIdentityLoginModule" flag="required">
    <module-option name = "password-stacking">useFirstPass</module-option>
    <module-option name="credentialMap">props/teiid-credentialmap.properties</module-option>
    <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS</module-option>
    
   <!-- below properties are only required when passwords are encrypted -->
   <module-option name = "pbealgo">PBEWithMD5AndDES</module-option>
   <module-option name = "pbepass">testPBEIdentityLoginModule</module-option>
   <module-option name = "salt">abcdefgh</module-option>
   <module-option name = "iterationCount">19</module-option>
</login-module>

The Teiid's configuration file(s) standalone-teiid.xml/domain-teiid.xml, contain transports defined for access to Teiid, and transport contain the properties to configure SSL for socket per transport.

<ssl mode="login" authentication-mode="1-way" ssl-protocol="SSLv3" keymanagement-algorithm="algo"
         enabled-cipher-suites="SSL_RSA_WITH_RC4_128_MD5,SSL_RSA_WITH_RC4_128_SHA">
            <keystore name="cert.keystore" password="passwd" type="JKS"/>
            <truststore name="cert.truststore" password="passwd"/>
        </ssl>

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 standalone-teiid/domin-teiid.xml configuration file.

All the logs produced by Teiid are prefixed by "org.teiid". This makes it extremely easy to control of of Teiid logging from a single context. Note however that changes to the log configuration file require a restart to take affect

Command logging captures executing commands in the Teiid System. Both user commands (that have been submitted to Teiid) and data source commands (that are being executed by the connectors) are tracked through command logging.

To enable command logging to the default log location, simply enable the DETAIL level of logging for the org.teiid.COMMAND_LOG context.

To enable command logging to an alternative file location, configure a separate file appender for the DETAIL logging of the org.teiid.COMMAND_LOG context. An example of this is shown below and can also be found in the standalone-teiid.xml distributed with Teiid.

<periodic-rotating-file-handler name="COMMAND_FILE">
                <level name="INFO"/>
                <formatter>
                    <pattern-formatter pattern="%d{HH:mm:ss,SSS} %-5p [%c] (%t) %s%E%n"/>
                </formatter>
                <file relative-to="jboss.server.log.dir" path="command.log"/>
                <suffix value=".yyyy-MM-dd"/>
            </periodic-rotating-file-handler>
    
   <logger category="org.teiid.COMMAND_LOG">
      <level name="INFO"/>
      <handlers>
        <handler name="COMMAND_FILE"/>
      </handlers>
   </logger>

See the Developer's Guide to develop a custom logging solution if file based, or any other built-in Log4j, logging is not sufficient.

Audit logging captures important security events. This includes the enforcement of permissions, authentication success/failures, etc.

To enable audit logging to the default log location, simply enable the DETAIL level of logging for the org.teiid.AUDIT_LOG context.

To enable audit logging to an alternative file location, configure a separate file appender for the DETAIL logging of the org.teiid.AUDIT_LOG context. See the Developer's Guide to develop a custom logging solution if file based, or any other built-in Log4j, logging is not sufficient.

The BufferManager is responsible for tracking both memory and disk usage by Teiid. Configuring the BufferManager properly is one of the most important parts of ensuring high performance. See the file for all BufferManager settings.

The Teiid engine uses batching to reduce the number of memory rows processed at a given time. The batch sizes may be adjusted to larger values if few clients will be accessing the Teiid server simultaneously.

The max-reserve-kb setting determines the total size in kilobytes of batches that can be held by the BufferManager in memory. This number does not account for persistent batches held by soft (such as index pages) or weak references. The default value of -1 will auto-calculate a typical max based upon the max heap available to the VM. The auto-calculated value assumes a 64bit architecture and will limit buffer usage to 50% of the first gigabyte of memory beyond the first 300 megabytes (which are assumed for use by the AS and other Teiid purposes) and 75% of the memory beyond that.

The BufferManager automatically triggers the use of a canonical value cache if enabled when more than 25% of the reserve is in use. This can dramatically cut the memory usage in situations where similar value sets are being read through Teiid, but does introduce a lookup cost. If you are processing small or highly similar datasets through Teiid, and wish to conserve memory, you should consider enabling value caching.

The max-processing-kb setting determines the total size in kilobytes of batches that can be used by active plans regardless of the memory held based on max-reserve-kb. The default value of -1 will auto-calculate a typical max based upon the max heap available to the VM and max active plans. The auto-calculated value assumes a 64bit architecture and will limit processing batch usage to 10% of memory beyond the first 300 megabytes (which are assumed for use by the AS and other Teiid purposes).

In systems where large intermediate results are normal (scrolling cursors or sorting over millions of rows) you can consider increasing the max-processing-kb and decreasing the max-reserve-kb so that each request has access to an effectively smaller buffer space.

Each intermediate result buffer, temporary LOB, and temporary table is stored in its own set of buffer files, where an individual file is limited to max-file-size megabytes. Consider increasing the storage space available to all such files max-buffer-space if your installation makes use of internal materialization, makes heavy use of SQL/XML, or processes large row counts.

-XX:MaxDirectMemorySize=12g -XX:+UseLargePages

Socket threads are configured for each transport. They handle NIO non-blocking IO operations as well as directly servicing any operation that can run without blocking. For longer running operations, the socket threads queue with work the query engine.

The query engine has several settings that determine its thread utilization. max-threads sets the total number of threads available for query engine work (processing plans, transaction control operations, processing source queries, etc.). You should consider increasing the maximum threads on systems with a large number of available processors and/or when it's common to issue non-transactional queries with that issue a large number of concurrent source requests. max-active-plans, which should always be smaller than max-threads, sets the number of the max-threads that should be used for user query processing. Increasing the max-active-plans should be considered for workloads with a high number of long running queries and/or systems with a large number of available processors. If memory issues arise from increasing the max threads and the max active plans, then consider decreasing the processor/connector batch sizes to limit the base number of memory rows consumed by each plan. thread-count-for-source-concurrency, which should always be smaller than max-threads, sets the number of concurrently executing source queries per user request. Setting this value to 1 forces serial execution of all source queries by the processing thread. The default value is computed based upon 2*max-threads/max-active-plans. Using the respective default values, this means that each user request would be allowed 6 concurrently executing source queries. If the default calculated value is not applicable to your workload, for example if you have queries that generate more concurrent long running source queries, you should adjust this value.

Caching can be tuned for cached result (including user query results and procedure results) and prepared plans (including user and stored procedure plans). Even though it is possible to disable or otherwise severely constrain these caches, this would probably never be done in practice as it would lead to poor performance.

Cache statistics can be obtained through the Admin Console or Adminshell. The statistics can be used to help tune cache parameters and ensure a hit ratio.

Plans are currently fully held in memory and may have a significant memory footprint. When making extensive use of prepared statements and/or virtual procedures, the size of the plan cache may be increased proportionally to number of gigabytes intended for use by Teiid.

While the result cache parameters control the cache result entries (max number, eviction, etc.), the result batches themselves are accessed through the BufferManager. If the size of the result cache is increased, you may need to tune the BufferManager configuration to ensure there is enough buffer space.

Result set and prepared plan caches have their entries invalidated by data and metadata events. By default these events are captured by running commands through Teiid. See the Developers Guide for further customization. Teiid stores compiled forms of update plans or trigger actions with the prepared plan, so it is recommended to leave the max-staleness of the prepared plan cache set to 0 so that metadata changes, for example disabling a trigger, may take effect immediately. The default staleness for result set caching is 60 seconds to improve efficiency with rapidly changing sources. Consider decreasing this value to make the result set cache more consistent with the underlying data. Even with a setting of 0 full transactional consistency is not guaranteed.

Teiid separates the configuration of its socket transports for JDBC ans ODBC. Typical installations will not need to adjust the default thread and buffer size settings. The default input output buffer sizes are set to 0, which will use the system default. Before adjusting this value keep in mind that each JDBC, ODBC will create a new socket connection. Setting these values to a large buffer size should only be done if the number of client is constrained. All JDBC/ODBC socket operations are non-blocking, so setting the number of max-threads higher than the maximum effective parallelism of the machine should not result in greater performance. The default value 0 for JDBC socket threads will set the max to the number of available processors.

LOBs and XML documents are streamed from the Teiid Server to the Teiid JDBC API.   Normally, these values are not materialized in the server memory - avoiding potential out-of-memory issues. When using style sheets, or XQuery, whole XML documents must be materialized on the server. Even when using the XMLQuery or XMLTable functions and document projection is applied, memory issues may occur for large documents.

LOBs are broken into pieces when being created and streamed.  The maximum size of each piece when fetched by the client can be configured with the "lob-chunk-size-in-kb" property in the <jboss-install>/standalone/configuration/standalone-teiid.xml file. The default value is 100 KB. When dealing with extremely large LOBs, you may consider increasing this value to decrease the amount of round-trips to stream the result. Setting the value too high may cause the server or client to have memory issues.

Source LOB values are typically accessed by reference, rather than having the value copied to a temporary location. Thus care must be taken to ensure that source LOBs are returned in a memory-safe manner.

When using Teiid in a development environment, you may consider setting the max-source-rows-allowed property in the <jboss-install>/standalone/configuration/standalone-teiid.xml file to reasonably small level value (e.g. 10000) to prevent large amounts of data from being pulled from sources. Leaving the exception-on-max-source-rows set to true will alert the developer through an exception that an attempt was made to retrieve more than the specified number of rows.

Here are the steps to follow to install Teiid

The AdminShell tooling provides scripting based programming environments that enable user to access, monitor and control a Teiid Server. Both the command line and graphical console tools are built on functionality provide by the Groovy ( http://groovy.codehaus.org/ ) project. The AdminShell tools can be used in ad-hoc scripting mode or to run pre-defined scripts.

To learn the basics of Groovy take a look at their documents and tutorials on their website.

Basic knowledge of the Java programming language and types is required in order to effectively design and develop scripts using the AdminShell. To learn Java language find learning resources at http://java.sun.com.

You can learn about the Teiid AdminAPI either using “adminHelp()” function or by using the JavaDocs.

AdminShell is a specialized version of Groovy which works in several different modes: interactive shell, graphical console, or script run mode. In interactive shell mode (launched via adminshell), the user can invoke connect to a live Teiid system and issue any ad-hoc commands to control the system. The interactive buffer can be used to develop a script and the interactive session input and output can be captured into a log file, more on this later in the document.

In graphical mode (lanched via adminshell-console), the user can develop and run scripts using a text editor that supports syntax highlighting.

In the script run mode, the user can execute/play back previously developed scripts. This mode especially useful to automate any testing or to perform any repeated configurations/migrations changes to a Teiid system.

adminHelp();

adminHelp("deployVDB");

/*
 *Deploy a VDB from file
 */
void deploy(
    String /* file name */)
  throws AdminException
  throws FileNotFoundException

sqlHelp();

   
println "xxx";   // print something to console

adminHelp();     // shows all the available admin commands; 

sql = connect(); // get an extended Groovy Sql connection using connection.properties file

sql.execute(<SQL>); // run any SQL command. 

connectAsAdmin(); // connect as admin; no need have the vdb name. SQL commands will not work under this connection

println getConnectionName();  // returns the current connection name

useConnection(<connection name>); // switches to using the given connection settings

disconnect();   // disconnects the current connection in the context
        

To execute a script file "foo.groovy" in a directory "some/directory" in the interactive comamnd line tool, execute as following

. some/directory/foo.groovy

"foo.groovy" is read into current context of the shell as if you typed in the whole document. If your script only contained method calls, you can explicitly invoke the call to execute.

Full execute syntax may also be used, and is required outside of the interactive command line tool:

evaluate("some/directory/foo.groovy" as File) 

To execute the same file without entering interactive mode, run

./adminshell.sh . some/directory/foo.groovy

Parameters can be passed in as Java System properties. For example

./adminshell.sh -Dparam=value . some/directory/foo.groovy

Inside the script file, you can access these properties using System.getProperty

value = System.getProperty(“param”); // will return "value"

During the interactive mode, input is recorded in a history file. This file can be accessed via the up arrow in the interactive shell.

User can also capture the commands entered during a interactive session to their own script file by using “startRecording” and “stopRecording” commands. For example,

record start directory/filename.txt
<commands and script ..>
record stop

All input and output between the start and stop are captured in the “directory/filename.txt” file. This gives the user an option to capture only certain portions of the interactive session and to later refine a script out of recorded file.

The file "connection.properties" in the installation directory of the AdminShell defines the default connection properties with which user can connect to Teiid system. The following properties can be defined using this file

jdbc.user=user
jdbc.password=user
jdbc.url=jdbc:teiid:admin@mm://localhost:31000;

admin.host=localhost
admin.port=9999
admin.user=admin
admin.password=admin
      

A call to "connect()" or "connectionAsAdmin()" without any input parameters, will connect to the Teiid system using the properties defined in properties file. However, a user can always pass in parameters in the connect method to connect to a same or different server than one mentioned in the “connection.properties”.  Look all the all the different connect methods using the “adminHelp()” method.  

Note that it is not secure to leave the passwords in clear text, as defined above. Please take necessary measures to secure the properties file, or do not use this feature and always pass in password interactively or some other secure way.

Note: At any given time user can be actively connected to more than one system or have more than one connection to same system.  To manage the connections correctly each connection is created given a unique connection name. To learn more about this look at Handling Multiple Connections.

Using AdminShell, a user can actively manage more than one connection to a single or multiple Teiid systems. For example, two separate connections can be maintained, one to the development server and one to the integration server at the same time. This is possible because AdminShell supports a feature called named connections.

Every time a connection is made, the connection has an explicit or an implicitly assigned name.   If another connect command is executed then a new connection is made with a unique name and execution will be switched to use the new connection.   The previous connection will be held as it is in its current state, and will not be closed.

You can use the following command to find out the current connection's name

name = getConnectionName();    
      

Knowing the names of the connection that user is working with is important to switch the active connection to a previous connection. To switch the active connection, use the following command and supply the name of the connection to be used

useConnection("name");    
      

If user supplies the same name as the active connection as they are currently participating in, then this operation will simply return with out any modifications. There is no limitation the number of simultaneous connections.

The following shows an example of using and switching between two connections.

// creates a connection 
connectAsAdmin();	        	

//capture the connection name
conn1 = getConnectionName();

deploy("file.vdb")

// creates a second connection 
connectAsAdmin();		

conn2 = getConnectionName();

deploy("file.vdb")

// switch the connection to "conn1"
useConnection(conn1);	

// close the connection in the "conn1"
disconnectAll();

The interactive shell uses a special shell interpretter and therefore has different behavior than just writting a script in Groovy. See the Groovy Shell Documentation for more on its usage. Notable differences: