# Teiid ${project.version} Release Notes

Teiid ${project.version} adds performance and integration features.

## Overview

- Highlights
- Compatibility Issues
- Configuration Issues
- Other Issues
- Thirdparty Library Updates
- Detailed Release Notes
- Additional Docs and Help
- Licenses
- About JBoss

## Highlights

- TEIID-1070 Amazon SimpleDB support has been contributed by Radim Hopp. See the Admin and Reference Guides for more.
- TEIID-2761 Security Enhancements added support for a grant-all attribute on a role. Also added the ability to specify the security domain for a VDB. See the reference guide for more.
- TEIID-2780 Apache Solr Translator has been added. See the Admin and Reference Guides for more.
- TEIID-2809 Pattern based system property substitution has been added to VDB.XML files. Only supported if the DVB is being deployed JBoss EAP.
- TEIID-2844 Remove unauthorized columns from SELECT * can be enabled via the session property ignore_unauthorized_asterisk. See the Reference for more.
- TEIID-2859 TEXTTABLE FOR ORDINALITY a FOR ORDINALITY column can now be used with TEXTTABLE. See the Reference for more.
- TEIID-965 Apache POI based Excel connectivity has been added. See the reference for more.
- TEIID-2888 Designer VDB support in Embedded is now available. With a few caveats, Designer .INDEX based vdbs can now be used with Teiid Embedded. See the Embedded Guide for more.

## Compatibility Issues

- Support for named parameter syntax using param=value has been deprecated, since it is ambiguous with a comparison predicate boolean value expression. param=>value should be used instead.
- decodeinteger/decodestring have been deprecated. A CASE expression should be used instead.
- TEIID-2267 The custom appenders for command and audit logging has been changed, now they need to be developed for java.util.logging based Handler.
- TEIID-1979 The resource adaptors are now deployed through modules, and have shorter names as identifiers. Connection Factories created with previous versions must be re-configured.
- TEIID-2326 By default imported vdb internal materialized views will shared with the importing vdb.
- TEIID-2344 non-available JDBC sources in partial results mode or source with connection factories that require an ExecutionContext to obtain a connection will require manual setting of the database version metadata property. The affected sources are: db2, derby, oracle, postgresql, sqlserver, sybase, teiid
- TEIID-2477 Most of the JDBC translator static String version constants were replaced by org.teiid.translator.jdbc.Version constants. Use the .toString() method to obtain a version string if needed.
- TEIID-2527 The admin method assignToModel has been deprecated. See the updateSource, addSource, and removeSource methods instead.

#### from 8.6

- TEIID-2793 Searchability metadata will not prevent more complicated expressions from being pushed down.
- TEIID-2794 Schema scoped functions are checked for ambiguity. Schema qualifaciton may be needed to resolve properly.
- TEIID-2840 Internal materialized view ttl refresh is now blocking by default. To keep the old behavior of lazy invalidation, use the vdb property lazy-invalidation=true

#### from 8.5

- TEIID-2667 The jdbc importer importKeys parameter is now correctly defaulted to true.
- TEIID-2737 The 'native' procedure exposed by translators has been renames as the direct query feature. The related ExecutionFactory methods supportsNativeQueries and nativeQueryProcedure name have been deprecated and replaced with supportsDirectQueryProcedure and directQueryProcedureName.

#### from 8.4

- TEIID-2580 Both xpathValue and XMLTABLE will return null when retrieving the value for a single element marked with xis:nil="true".
- TEIID-2590 Both the source specific and the general hint if present will be included as the source hint for Oracle.
- TEIID-2603 TableStats and ColumnStats numeric values are held as Number, rather than Integer.
- TEIID-2613 The rowcount is reset to 0 after a non-update command statement is issued.
- TEIID-2422 using calendar based timestampdiff by default. See the Admin Guide for using the org.teiid.calendarTimestampDiff to control backwards compatibility.

#### from 8.3

- TEIID-2444 The deployment platform for Teiid has been changed to EAP 6.1.Alpha1, older or non-EAP deployments are not supported.
- TEIID-2429 Sorts over data sets over a single batch are not guaranteed to be sorted in a stable manor to improve performance. The sort will still be correct with respect to the sort keys.

#### from 8.2

- TEIID-2253 the multi-source implementation logic was significantly altered the following changes were introduced.
  - If not auto-populated, the multi-source column acts as a pseudo-column and will not be selectable via a wildcard SELECT * nor tbl.*
  - Multi-source inserts must specify a single source as their target.
  - The join planning behavior in multi-source mode was not consistent and did not work in all situations. To ensure consistency multi-source tables being joined together should specify a join predicate on the source name column - i.e. tbl1.source_name = tbl2.source_name. For backwards compatibility a the system property org.teiid.implicitMultiSourceJoin was introduced to control whether multi-source joins are effectively partitioned by source without a source_name predicate. The property defaults to true, the pre 8.3 behavior - but should be switched to false for later versions unless the issues with implicit join planning are addressed.
- TEIID-2317 byte[] char[] and java.util.Date instances returned as object values will be left in tact and not automatically converted to BinaryType, ClobType, and Timestamp respectively. The values may still be cast to those types.

#### from 8.1

- TEIID-2149 the subqueryUnnestDefault property no longer influences cost based decisions to treat subqueries as merge joins. In nearly all circumstances this is desirable, but may require the use of nounnest hint to prevent forming the join if desired.
- TEIID-2166 array_get will return null if the index is out of bounds rather than raising an error.
- TEIID-2175 for 8.0 and 8.1 clients the server will check if serialized date/time values fall outside of 32-bit value ranges (year 1900 - 9999 for dates and times between years 1901 and 2038) and throw an exception. The previous behavior was to truncate. The exception and the use of 32 bit serialization can be avoided by setting the system property org.teiid.longDatesTimes to true.
- TEIID-2184 to be consistent with the rest of Teiid's logic the system functions dayName and monthName will return values from the default locale, rather than only the English names. Use the system property org.teiid.enDateNames true to revert to the pre-8.2 behavior.
- TEIID-2187 the CONSTRAINT keyword is not correctly used in table DDL. It should be replaced with a comma from scripts to be compatible with 8.2. If desired, 8.2 now supports the CONSTRAINT keyword to provide a name for each constraint.
- TEIID-2181 system tables no longer contain valid OIDs. That responsibility has moved to the pg_catalog.
- TEIID-1386 the SQLState and errorCode reported by a TeiidSQLException will typically be from the top level nested SQLException. If there is also a nested TeiidException, the TeiidSQLException.teiidCode will be set to the TeiidException.getCode value and the TeiidSQLException.errorCode will be set to the integer suffix of the teiidCode if possible.
- TEIID-2226 All statements that return result sets that are executed as command statements in a procedure are validated against the expected resultset columns of the procedure. If the statement is not intended to be returnable, WITHOUT RETURN can be added to the end of the statement.
- TEIID-2235 The MetadataRepository.setNext method was removed and MetadataRepository was converted to an abstract class rather than an interface. Also if an instance of a DefaultMetadataRepository is used, it will only affect metadata already loaded in the repository chain.
- TEIID-2237 teiid_ is a reserved DDL namespace prefix and the MetadataFactory class no longer throws TranslatorExceptions, instead the unchecked MetadataException is thrown.
- TEIID-2243 by default Teiid will not pushdown the default null sort order of nulls low when no null sort order is specified. Set the system property org.teiid.pushdownDefaultNullOrder to true mimic the 8.1 and older release behavior.

#### from 8.0

- org.teiid.metadata.Schema holds FunctionMethods by uuid rather than name to accommodate overridden method signatures.
- MetadataFactory no longer extends Schema. Use the MetadataFactory.getSchema method to get the target Schema.
- DDL created VIRTUAL pushdown functions should be referenced in the ExecutionFactory.getSupportedFunctions by their full schema.function name.
- DDL functions/procedures defined without the VIRTUAL keyword are by default VIRTUAL. Use the FOREIGN keyword to indicate that they are source specific.
- FunctionMethod.getFullName returns the proper schema, not category qualified name.
- VDB.getUrl has been removed.
- VDB.Status now has four states - LOADING, ACTIVE, FAILED, REMOVED. To check for validity use the isValid method, rather than checking for the VALID state. FAILED deployments will still be accessible via the admin getVDB methods.
- The standalone and cli configuration files specify a setting for the teiid subsystem policy-decider-module. If a module is not specified, then data roles will not be checked.
- local connections specifying a VDB version will wait for their VDB to finish loading before allowing a connection, see the waitForLoad connection property for more.
- jsonToXml document elements will contain xsi:type attribute values of decimal and boolean respectively for number and boolean json values to allow for differentiation from string values.
- Result set cache entries can now have updatable set to false to indicate that updates should not purge the entry.
- Datatype default values have been corrected for Teiid built-in types. All datatypes are now nullable by default, only character string types are case sensitive, numeric types have radix 10, and length/precision/scale have been set appropriately.
- pg catalog and dynamic vdb created metadata will use a generated Teiid id rather than a random UUID.
- transport ssl config no longer uses the enabled attribute. Use mode=disabled to disable the usage of encryption.
- TEIID-2105 If a MetadataRepository throws a RuntimeException during load, that will be treated as a non-recoverable error and the VDB will have a FAILED status.
- TEIID-2105 It was an undocumented behavior that is a source did not specify a jndi connection that "java:/name" would be assumed. That is no longer the case. It the source needs a connection, then one must be specified.
- TEIID-2127 if ExecutionFactory.isSourceRequired returns true (the default) then not obtaining a connection will for an Execution will result in an error. If an ExecutionFactory does not use a source, then no connection-jndi-name should be specified and isSourceRequired should return false (see setSourceRequired). If isSourceRequired returns false and a connection-jndi-name is specified, then Teiid will still attempt to obtain a connection, but no exception will be thrown if a connection isn't available.
- TEIID-2138 the odbc layer will report standard_conforming_strings as on, rather than off to better reflect the string literal handling of Teiid.

#### from 7.x

- TRANSLATE/HAS CRITERIA has been removed. INSTEAD OF trigger actions should be used instead. ROWS_UPDATED, INPUTS, and INPUT are no longer procedure reserved words.
- 7.x or earlier VDBs with table cardinalities set the old unknown value 0 will now report that value as -1 via the metadata API or SYS.TABLES.
- Exact fixed point literals, e.g. 1.0, are now parsed as decimal/BigDecimal values as per the ANSI specification, rather than as double values. Also the AVG aggregate function will return an exact numeric (BigDecimal) value for integral types rather than returning a double value. Views that were projecting doubles from exact numeric literals or using AVG will need to be updated. There is also a new system property org.teiid.decimalAsDouble to enable the pre-8.0 behavior. The BigDecimal form of the AVG function now uses the same precision and scale logic as the division system function, rather than using a fixed scale of 9.
- BigDecimal division that returns a quotient that is equal to zero will have a scale of zero as well. Prior releases would typically return a zero value with a scale of 16.
- The env function no longer returns the value for the session id, the session_id() function should be used instead.
- The CommandContext no longer provides getEnvironmentProperties()
- Unaliased derived columns in the SELECT clause have different default names than prior releases. The name will be exprX where X is the SELECT clause position.
- The translator API facilities for iterator/bulk updates were combined and updated. Multi-valued literals were replaced by the Parameter class with an associated value iterator available on the BatchedCommand. The IteratorValueSource class was also removed.
- VARBINARY, OPTIONS, and OUT were added as reserved words.
- AbstractMetadataRecord and its sub-classes use a case-insensitive map for properties rather than a LinkedHashMap. Thus, property key lookups are now case-insensitive.
- Removed the unused ExecutionFactory methods supportsBetweenCriteria and supportsCaseExpression.
- allowCreateTemporaryTablesByDefault and allowFunctionCallsByDefault are now set to false. When data roles are in use the user must explicitly grant these options to the user on VDB

#### from 7.7

- parse/formatdate and parse/formattime are no longer pushdown functions. They are converted into parse/formattimestamp.
- SQLWarning exception chains attached to the ExecutionContext will be returned as a single TeiidSQLWarning rather than as individual TeiidSQLWarnings. See the TeiidSQLWarning javadocs for how to access the underlying SQLWarning chains.

#### from 7.5

- Leave was added as a reserved word.
- Lob inlining is incompatible with clients older than 7.6. If a 7.6 server will have older clients that use lobs connect to it, then the BufferService property inline-lobs should be set to false in the teiid-jboss-beans.xml file.
- Oracle translators assume they are using Oracle supplied drivers. If that is not the case, set the Oracle translator execution property oracleSuppliedDriver to false.
- Unordered limits are handled strictly by default. They are longer pushed through conditions, dup removal, or UNION not all - and cannot have conditions pushed through them. Use the NON_STRICT hint to change the behavior to Teiid 7.1 handling.

#### from 7.4

- OFFSET and LIKE_REGEX were added as reserved words.
- ColumnReference.getName will always return just the element name. Previously it inconsistently returned the qualified and unqualified form depending upon where the ColumnReference appeared.
- As per JDBC4, ResultSetMetadata.getColumnName will return the unaliased column name if available rather than return the alias. Set useJDBC4ColumnNameAndLabelSemantics to false to use the alias name as the column name.

#### from 7.3

- SYS.PROPERTIES has a new column, ClobValue, to get values exceeding the max string length
- SYS.COLUMNS has two new columns to get statistical information: DistinctCount and NullCount
- ARRAY_AGG is now a reserved word
- The use of an IN procedure parameter with the name "source_name" in a multi-source model, will now be treated the parameter that controls which source the procedure will execute against.
- Dynamic VDB functions injected via ExecutionFactory.getPushdownFunctions are now scoped to the SYS schema and have a fully qualified name that includes their source type. For example, instead of oracle_model.relate - which was only valid against the oracle_model source, there is now the SYS.oracle_sdo.relate function that is valid for all Oracle sources. Any fully-qualified reference to these functions will need updated.

#### from 7.2

- The default JDBC credentials are user/user - not admin/teiid
- Unordered limits are no longer pushed through conditions, dup removal, or UNION not all. This prevents the possibility of getting less results than the logical intent of the limit.

#### from 7.1

- Subqueries are no longer allowed to be SELECT INTO.
- INSERT/UPDATE/DELETE cannot be used to create implicit return cursors in non-update virtual procedures. You can instead use "UPDATE ...; SELECT VARIABLES.ROWCOUNT;".
- The SYSADMIN schema was created to hold procedures and tables that should not be generally accessible. SYS and pg_catalog are now always accessible - permissions do not apply to these schemas. The SYS.getBinaryVDBResource, SYS.getCharacterVDBResource, and SYS.getVDBResourcePaths have been replaced with the SYSADMIN.VDBResources table. The Matviews table and the refreshMatView/refreshMatViewRow procedures were also moved into SYSADMIN.
- Overwriting an existing VDB will cause old connections to be terminated. Production systems should rely on VDB versioning.
- The jdbc:metamatrix JDBC URL prefix is no longer accepted. Use jdbc:teiid instead.
- Model visibility no longer restricts access to tables and procedures. Setting visible to false will only hide entries from system tables. Data roles should be used to restrict data access.
- Admin API "getWorkManagerStats" methods renamed to "getWorkerPoolStats". Also, "setRuntimeProperty" and "getProcesses" methods were removed.
- By default the "ENV" system function is now turned off. To enable it, edit the teiid-jboss-beans.xml configuration file.
- The use of VARIABLES.ROWCOUNT is now reserved.
- Exec statements of the form "var = EXEC foo()" are only valid if the procedure foo has a return parameter.
- Cache hint now supports "scope" definition. For ex: /* cache(scope:user) */ select * from T1; This hint will override the computed scope based on the query.

#### from 7.0

- The term data policy was replaced with data role. The AdminAPI methods for adding role mappings have changed from addRoleToDataPolicy and removeRoleFromDataPolicy to addDataRoleMapping and removeDataRoleMapping respectively.

## Configuration Issues

See the Admin Guide for more on configuration and installation.

#### from 8.4

- TEIID-2512 the usage of the metadata element text as the "raw schema text" may not be appropriate in all situations. The ddl and ddl-file metadata repositories will check for the ddl and ddl-file model properties respectively.
- TEIID-2707 the org.teiid.joinPrefetchBatches property is no longer used.

#### from 8.3

- TEIID-2429 the default for maxProcessingKb has effectively doubled (the old default would use approaximately 4MB), while the maxReserveKb default has been reduced to 70% of the memory past the first gigabyte instead of 75%.
- TEIID-2445 the UseConnectorMetadata and supports-multi-source-bindings properties have been deprecated, but will still be respected if present. There is no equavalent to UserConnectorMetadata=true as it is always implied. UseConnectorMetadata=false has been replaced by cache-metadata=false, which can be placed at either the vdb or model level. supports-multi-source-bindings has been replaced by multisource, which no longer needs to be specified if more than one source is configured.
- TEIID-2510 the time-slice-in-millseconds has been corrected to be time-slice-in-milliseconds

#### from 8.1

- The connector batch size setting is no longer used. Instead a fetch size will be sent to the translator that is 2 times the working batch size or the non-pushed limit, whichever is less.

#### from 8.1

- The file translator now defaults to exceptionIfFileNotFound=true, you can set the translator property to false to preserve the old behavior of returning null.
- TEIID-2086 TEIID-2168 prepared plan and result set caches are now configured as infinispan caches. See the teiid cache container in the configuration. You may also control the transactional aspects of the result set cache on the resultset and resultset-repl caches via the configuration.
- TEIID-1241 the web services connector property ConfigName was deprecated in favor of EndPointName. There were also ServiceName, NamespaceUri, and Wsdl properties added, which are used to point the translator at a specific WSDL.

#### from 8.0

- teiid-security-users and teiid-security-roles properties files have been moved under the configuration directory of their respective deployment.

#### from 7.7

- The server is now dependent upon AS 7.x. The old configuration and deployment structure is no longer applicable.

#### from 7.4

- The configuration for the buffer service now defaults to 256/512 for processor and connector batch sizes respectively. The buffer service also has 4 new properties inline-lobs, memory-buffer-space, memory-buffer-off-heap, and max-storage-object-size. If you were explicitly setting the value for 'max-reserve-kb', you should consider lowering that value to account for the memory buffer and/or explicitly set the memory-buffer-space.
- The configuration for authorization has been moved off of the RuntimeEngineDeployer bean and onto separate AuthorizationValidator and PolicyDecider beans.
- The configuration for the buffer manager has been simplified to refer to memory sizes in KB, rather than batch columns.

#### from 7.3

- The default value for the JDBC dynamic vdb importer setting importer.useFullSchemaName is now true, which matches the expected behavior from the documentation.
- The prepared plan cache is now configured via the PreparedPlanCacheConfig bean, rather than through properties on the RuntimeEngineDeployer
- SocketConfiguration.maxSocketThreads will interpret a setting of 0 to mean use the system default of max available processors. Both the ODBC and JDBC transports now default to the 0 setting.
- maxReserveBatchColumns and maxProcessingBatchesColumns will interpret a setting of -1 to mean auto-calculate acceptable values given the max heap and other information. See the admin guide for more.
- The default for org.teiid.useValueCache has changed to false, since typical installations will not greatly benefit from the additional lookup cost.
- The property RuntimeEngineDeployer.allowFunctionCallsByDefault was added so that Teiid 7.4 behavior is compatible with Teiid 7.3. Set this property to false to require permissions for function calls when data roles are enabled.

#### from 7.2

- Temporary tables can now be restricted by data roles. Use the data-role attribute allow-create-temporary-tables to explicitly enable or disable the usage of temporary tables. There is also a allowCreateTemporaryTablesByDefault property in the teiid-jboss-beans.xml to control whether usage is allowed by default. For compatibility with prior 7.x releases, the default is to allow temporary table access.

#### from 7.1

- Teiid clients now allow the usage of anonymous SSL by default. This changes allows the admin port (default 31443) to use anonymous SSL by default, rather than just securing login traffic. Admin clients should therefore use the mms protocol instead of mm. This will encrypt all admin traffic and ensure that any passwords in configuration files will be encrypted in transit. See the Admin Guide to upgrade from anonymous SSL to 1-way or 2-way authentication. The config properties sslEnabled and clientEncryptionEnabled for SSLConfiguration beans have been combined to a single property mode, that can have the values disabled|login|enabled.
- Apache CXF is now expected to be used as the web services stack provider through JBossWS-CXF. See the Admin Guide for instructions on installing CXF for use with Teiid's Salesforce and web service connectors. The WS Resource Adapter's -ds.xml files should no longer use WSSecurityConfigURL and WSSecurityConfigName, rather they should be ConfigFile and ConfigName respectively. The property values should no longer refer to jboss-wsse-client.xml, but instead they should reference a CXF Spring configuration file and particular port configuration. See the Admin Guide for more on using CXF configuration files.
- The default for data role checking is now "true". However only VDBs with data roles will have roles enforced.
- The default prepared plan cache size was increased to 512, since it is targeted by internal plans as well.

#### from 7.0

- The property to enable data roles in teiid-jboss-beans.xml has changed from useEntitlements to useDataRoles.
- Rar file names no longer contain version numbers. -ds.xml files should be updated from connector-XXX-version.rar to teiid-connector-XXX.rar
- Code table relate configuration properties have been removed. Code tables are now implemented as materialized views.

## Other Issues

- TEIID-1170 - correlated subqueries are not allowed in UPDATEs or DELETEs against internal Teiid tables (internal materialized views or temporary tables).
- TEIID-1281 - Negative start indexing is not supported by DB2 and Derby databases. Usage of the Teiid SUBSTRING against these sources should not use negative start values.
- TEIID-1008 - Most versions of Oracle and MySQL do not support deeply nested correlated references. There is currently no workaround for this issue.
- For compatibility with the 7.0 release if a stored procedure parameter list begins with identifier=, then it will be parsed as a named parameter invocation even if the intent was to use a comparison predicate as the first parameter value. The workaround is to use nesting parens, e.g. call proc((identifier=value), ...), which clarifies that this is positional value. This workaround will not be needed in later releases.
- TEIID-1511 - When a VDB is deployed and undeployed a socket is left open, which may lead to "too manay open files". Check the defect comments for details.
- TEIID-2101 - Statement.setMaxRows is not used for cached result sets or when getting a result set from a CallableStatement procedure that returns parameters.
- TEIID-586 - Salesforce LIKE pushdown is case insensitive, while LIKE evaluated by Teiid is case sensitive unless an alternative collation is used. Care should be taken to ensure consistent results if mixed case values are being searched.
- TEIID-2836 - Data from DB2 on z/OS in EBCDIC may not be represented correctly at runtime. It is recommended that the values are converted to ASCII or another common character set.

#### from 7.0

- Fixed xsd type handling for SQL/XML and XML document models. xsd:date, xsd:dateTime, and xsd:time types will now all be displayed using the GMT timezone (Z). SQL types, such as timestamp, used for an XMLTABLE column will now expect their values to be in the form of the corresponding xsd type.

## Thirdparty Library Updates

The following components have been updated:

#### From 8.5

- the engine xom and the embedded jaxen dependency were replaced with module dependencies

#### From 8.2

- json-simple was removed.

#### From 7.4

- Saxon was upgraded to 9.2.1.5
- nux 1.6, and xom 1.2 were added.

#### From 7.1

- json-simple 1.1 was added.
- Netty was upgraded to 3.2.1

#### From 7.0

- Direct integration of JBossCache jars was removed.
- Netty was upgraded to 3.2.0
- JDOM was removed.

## Detailed Release Notes

Detailed Release Notes - Teiid - Version ${project.version}

## Documentation and Help

The Teiid community project is hosted on jboss.org. Documentation and help may be obtained from the local distribution under teiid-docs or the following locations.

- Online Documentation
- Wiki
- JIRA
- Forums

## Licenses

Teiid is licensed under the LGPL. The license texts for Teiid and the thirdparty components it uses may be found in the teiid-docs/licenses directory of the distribution.

## About JBoss, a division of Red Hat

JBoss, a division of Red Hat, is in the business of providing superior technical support to our customers. Our goal is to make Professional Open Source™ the SAFE CHOICE for you. We accomplish this by backing up our open source Java products with technical support services that are delivered by the core developers themselves. We can help you to train your staff and provide you with support at every stage of the application lifecycle - from development and integration through deployment and maintenance. Visit the JBoss Services page for more information.