# Teiid 9.1.3 Release Notes

Teiid 9.1.3 adds performance, integration features, and WildFly 10 support.

## Overview

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

## Highlights

- TEIID-2715 OData Metadata support for providing the metadata extension properties for OData.
- TEIID-4243 BatchUpdateException support in JDBC to indicate what part of batch succeeds when a failure occurs.
- TEIID-4191 NO QUOTE option for TEXTAGG.
- TEIID-4200 Moved to WildFly 10 as host environment
- TEIID-4332 Improved NDV calculations for costing.
- TEIID-4284 Salesforce Bulk API for SELECT queries.
- TEIID-4378 TEIID-4376 Clob enhancements to support concat and use in EXECUTE IMMEDIATE.
- TEIID-4100 OData expand support for siblings and nested expands.
- TEIID-4393 Additional Geospatial Functions to more fully support the SQL-MM specification.
- TEIID-4421 Embedded Translator Refinements to allow for functioning that is similar to the server.
- TEIID-3630 Merge/Upsert pushdown is now supported.

## Compatibility Issues

- TEIID-3754OData Version 2 support is removed. Please use OData V4. Note that there are many changes in specification with V4 Vs V2 but specification is more stable.
- 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-2904 The createMetadataProcessor method on JDBCExcutionFactory has been deprecated. Use getMetadataProcessor instead.
- TEIID-3159 The SAP Netweaver Gateway translator (sap-nw-gateway) has been renamed to just SAP Gateway (sap-gateway). Usage of sap-nw-gateway is deprecated.
- TEIID-2694 In the autogenerated web service, if a procedure is designed for POST method, and one of its IN/INOUT parameters is either a LOB or VARBINARY then that service can only invoked using "multipart/form-data". This allows user to send large binary files for processing in Teiid
- TEIID-3814 In the autogenerated web service, the model name in the path is now case sensitive.
- TEIID-3754 The OData2 web service layer has been deprecated and will be removed in a future release. You should migrate to the OData v4 service layer instead.
- TEIID-4205 By default, the wrapping begin/commit of a UseDeclareFetch cursor will be ignored as Teiid does not require a transaction. Set the org.teiid.honorDeclareFetchTxn system property to false to revert to the old behavior which honored the transaction.
- TEIID-4240 The usage of ; delimited statements for materialization scripts has been deprecated. An anonymous procedure block should be used instead if multiple statements are needed.
- TEIID-4228 Precision and scale values greater than 32767 are deprecated.

#### from 9.0

- TEIID-4400 XML Document Models have been deprecated. OData or SQL/XML should be used instead.
- TEIID-4317 ExecutionFactory.initCapabilities will always be called - either during start if isSourceRequiredForCapabilities returns false, or later if true.
- TEIID-4346 The excel-odbc translator has been removed. Please use the excel translator instead.
- TEIID-4332 Due to costing logic changes plans may be different that in previous releases. Please raise an issue is you feel a plan is not appropriate.
- TEIID-4421 Removed the deprecated EmbeddedServer.addTranslator(ExecutionFactory) method.
- TEIID-4442 Removed the interpretation of the security-domain setting for the session service as a comma separated list of domains. Also added the USER(boolean) function to control if the USER function returns a name with the security domain. Finally the DatabaseMetaData and CommandContext getUserName will both return the simple user name without the domain.
- TEIID-4228 Precision/scale will now be set consistently. Values reported from JDBC/OData/ODBC metadata may be different if your current metadata declares a bigdecimal type with default precision.
- TEIID-4423 Uncorrelated subqueries will be treated as deterministic regardless of functions used within them. Prior releases treated most uncorrelated subqueries as non-deterministic if they contained a non-deterministic function.
- TEIID-4501 The salesforce-34 resource adapter defaults to the version 34 api rather than version 22 api.

#### from 8.x

- TEIID-3462 Semantic versioning requires the VDB version to be a string, rather than an integer field. This affects several public classes including CommandLogMessage, VDB, Session, EventListener, VDBImport, ExecutionContext, and MetadataRepository. Any custom command logging or materialization status tables will need the version field updated as well.
- TEIID-4147 ODBC type handling will now report the type name as the PostgreSQL type rather than the Teiid type.
- TEIID-3601 changed the rowCount field on CommandLogMessages from Integer to Long.
- TEIID-3752 the admin assignToModel method was removed
- TEIID-3684 RoleBasedCredentialMapIdentityLoginModule removed, consider using alternative login modules with roles to restrict access to VDB
- TEIID-2476 The AuthorizationValidator and PolicyDecider interfaces had minor changes - see their javadocs for new/altered methods
- TEIID-3503 To better isolate dependencies a separate teiid-jboss-admin jar was created from classes in teiid-admin - most notably AdminFactory was moved there.
- TEIID-4206 TranslatorProperty annotations on methods without setters must have the readOnly attribute as true.

#### from 8.11

- TEIID-3553 Ambiguous OData v2 entity set and function names will throw an exception rather than resolving to the first found.
- TEIID-3515 MAKEIND was added as a reserved word.
- TEIID-3576 the waitForLoad connection property has been deprecated.
- TEIID-2813 a source end event will be sent to the command log when an error occurs rather than being omitted.
- TEIID-3736 string literals values matching the date format can be directly resolved as timestamps.
- TEIID-3727 The version 22 salesforce translator and resource adapter have been deprecated.

#### from 8.10

- TEIID-3380/TEIID-3663 The SecurityHelper interface has changed to allow for easier control over GSS authentication
- TEIID-3372 DDL and DDL-FILE metadata repositories have deprecating using the respective ddl and ddl-file model properties.
- TEIID-3390 temporary lobs are now cleaned up when the result set is closed - even for local connections.

#### from 8.9

- TEIID-3210 Added supportsCompareCriteriaOrderedExclusive, which defaults to supportsCompareCriteriaOrdered, to specifically support < and > pushdown.
- TEIID-3282 Changed the WEEK function to compute the ISO 8601 by default (org.teiid.iso8601Week=true) and ensured pushdowns do the same. Changed the dayOfWeek function to be unaffected by the iso8601Week setting.

#### 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 qualification 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.

## Configuration Issues

See the Admin Guide for more on configuration and installation.

#### from 9.0

- TEIID-4129 in order to prevent invalid results from a sort/merge join, the sort operation will undergo additional checks. If org.teiid.assumeMatchingCollation is false (the default) and a translator does not specify a collationLocale, then the sort for a sort/merge join will not be pushed. Teiid defaults to the Java UCS-2 collation, which may not match the default collation for sources, particular tables, or columns. You may set the system property org.teiid.assumeMatchingCollation true to restore the old default behavior or selectively update the translators to report a collationLocale matching org.teiid.collationLocale (UCS-2 if unset).

#### from 8.x

- TEIID-2754 view are reported as VIEW table type in the metadata. Use the connection property reportAsViews=false to restore the old behavior.
- TEIID-3753 org.teiid.widenComparisonToString now defaults to false.
- TEIID-3669 there is now a single session service. Common configuration properties need to be consolidated. With TEIID-3790 this also means that you may want to change the default of trust-all-local to false to restrict local pass-through connections. Also the VDB REST passthrough-auth property is no longer used.
- TEIID-3797 the embedded transport is now known as the local transport.

#### from 8.12

- TEIID-3859 the "native" 9999 management port is no longer used. AdminShell will default to the http 9990 management port instead.

#### from 8.11

- TEIID-3594 User query command log entries are now logged at the INFO level on the org.teiid.COMMAND_LOG context. This allows command logging of just the user query events by setting the logging level to INFO. The level will default to WARN in the standard install or to DEBUG when running the auditcommand scripts.

#### from 8.9

- TEIID-3192 The CXF config is no longer a valid option for the Salesforce resource adapter. Please log an issue if there is feature from the CXF config that you were using that is not present on the new resource adapter.

#### from 8.8

- TEIID-3177 ODBC connections will be required to be secure based upon the SSL mode setting. If the mode is enabled, then the client must request an SSL connection. If the mode is login, then the client must use GSS authentication. To revert to the prior behavior, the system property org.teiid.ODBCRequireSecure can be set to false.

#### 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.

## Other Issues

- 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-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.
- TEIID-2998 - Google spreadsheets containing all string data do not detect their row data and labels correctly on the Google backend.
- TEIID-3070 - Netty threads may inappropriately take up CPU resources. This affects most EAP releases. Upgrade the AS version of Netty to 3.6.10.Final to address this issue.
- TEIID-3289 - The timestamp to string conversion performed in MySQL will produce a string with all of the trailing zeros (up to 6) for the fractional seconds. This differs from the expected Teiid/Java format.
- 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.
- TEIID-2998 - Google spreadsheets containing all string data do not detect their row data and labels correctly on the Google backend.
- TEIID-3070 - Netty threads may inappropriately take up CPU resources. This affects most EAP releases. Upgrade the AS version of Netty to 3.6.10.Final to address this issue.
- TEIID-3289 - The timestamp to string conversion performed in MySQL will produce a string with all of the trailing zeros (up to 6) for the fractional seconds. This differs from the expected Teiid/Java format.
- TEIID-3779 - There are a host of Phoenix issues that Teiid is currently not working around for HBase access. If you hit any of these, please let us know so that we can work with the Phoenix community to get it resolved. Generally Phoenix has issues with subquery evaluation and certain datatypes, such as char and timestamp.
  - TEIID-3772 TEIID-3769 TEIID-3766 are not likely to occur and generate an exception.
  - TEIID-3774 is unlikely but can return inaccurate results.
  - TEIID-3768 affects correlated subquery comparison using an aggregate of a char value and can return inaccurate results.
- TEIID-3808 - The Informix driver handling of timezone information is inconsistent - even if the databaseTimezone translator property is set. Consider ensuring that the Informix server and the application server are in the same timezone.
- TEIID-3805 - SAP Hana returns an empty string rather than null for the substring function when the from index is larger than the string length.
- TEIID-3816 - Informix can return incorrect results for subquery comparisons involving a boolean value and a subquery that has only a single row. If you encounter such a scneario and need Teiid to compensate, then please open an issue.

## Thirdparty Library Updates

The following components have been updated:

#### From 8.12.x

- gdata-core and gdata-spreadsheet were replace and updated by gdata core 1.47.1

#### From 8.12.3

- jts2geojson support was updated to 0.7

#### From 8.11

- AWS support was updated to 1.10.11

#### From 8.10

- The MongoDB client was upgraded to 2.13.1
- OData4J support was switched to the OReva fork version 0.8.1

#### From 8.9

- JTS 1.13 was added.

#### From 8.8

- Saxon was upgraded from 9.2.1.5 to 9.5.1-6
- Groovy (and related components) was upgraded from 1.7.4 to 2.3.6

#### From 8.5

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

#### From 8.2

- json-simple was removed.

## Detailed Release Notes

Detailed Release Notes - Teiid - Version 9.1.3

## 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.