Teiid 7.3.0.Final Release Notes

Teiid 7.3.0.Final adds updatable view, 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

• Subquery Optimization - added rewrite to INNER JOIN for applicable WHERE clause subqueries. Also added cost based SEMI and ANTI-SEMI join handling for applicable non-pushed WHERE and HAVING subqueries.
• Updatable Views
  • Added support to perform simple pass-through and more complicated updates through views by default.
  • Added support for "FOR EACH ROW" update procedure handling (similar to INSTEAD OF triggers in other DBMS's), which should be used instead of the deprecated TRANSLATE criteria approach.
• Temp table enhancements - added support for the SERIAL datatype, which is a not null INTEGER that auto-increments, and the ability to specify a column as NOT NULL.
• UDF - new API objects added to teiid-api to support user defined functions that are capable of pushdown to source.
• Unescape Function - a new system function, unescape, was added to handle common \ escaping in strings including octal, hex/unicode, \b, \r, \n, \f, and \t.
• Predicate Optimization - expanded optimizations for detecting always false conditions when using IS NULL, IN, or comparison predicates with literal values.
• Partition-wise Optimizations - Views defined by partitioned unions (union alls where each branch has a projected literal or an IN predicate that makes its values mutually exclusive from the other branches) can be used in aggregation or joins and the optimizer will take advantage of the partitioning information. For example, when a partitioned union is joined against another partitioned union, the optimizer will reorganize the join of unions into a union of joins.
• Delegate Translator - A new translator base class was added that is capable of delegating all the calls to another configured translator.
• JDBC Reauthentication - Teiid connections (defined by the org.teiid.jdbc.TeiidConnection interface) now support the changeUser method to reauthenticate a given connection.
• Lob Caching - Lobs are allowed to cache to disk as part of ResultSet caching. Distributed lob caching is not allowed.

Compatibility Issues

• TRANSLATE/HAS CRITERIA has been deprecated. An alternative approach to update procedures will be introduced in a subsequent version.
• 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.

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.

from 6.2

• The connector API has changed substantially. Custom connectors need to be retargeted to the new Translator API. See TEIID-1003 and the Developer's Guide for more information.
• Connector bindings are no longer valid. Use the Designer Tooling or see the AdminShell download for a migration utility.
• Calling out to web services and xml processing has been dramatically simplified and improved, but is a breaking change from prior releases. XML/Relational processing has been replaced by XMLTABLE, retrieving XML from file has been replaced by the File Translator/Resource Adapter, and web service calls have been replaced by the WS Translator/Resource Adapter. See TEIID-1118, TEIID-1114, and the Reference guide for more information.
• The direct usage of XQuery has been replaced with the XMLQuery function.
• The text connector file processing has been replaced with the TEXTTABLE function. See TEIID-1102 and the Reference guide for more information
• Double quoted values are now parsed as identifiers by default. See TEIID-145 and the Reference guide for more information.
• The system information schema has changed from System to SYS. The tables and table structures have also changed. See TEIID-871 and the Reference guide for more information.
• Client control over query plans has changed. OPTION PLANONLY, DEBUG, and SHOWPLAN are no longer allowed. This behavior should not be controlled by the SET statement, SET SHOWPLAN (ON|OFF|DEBUG) and SET NOEXEC (ON|OFF). Usage of the client PlanNode class will also need to be updated.
• Teiid specific JDBC client interfaces and methods have changed. In general these custom features have mostly been moved to other more standard locations or been removed. See TEIID-1020 and the Client Developers guide for more information.
• Teiid's reserved words have changed. Many of the Teiid specific reserved words have become non-reserved words. All of SQL Foundation and SQL/MED 2003, and SQL/XML from SQL 2006 reserved words have been added as reserved words. It is generally required to double quote all reserved words. It is advisable to double quote all non-reserved words as well. The use of the INPUT qualifier for procedure inputs has been deprecated in favor of INPUTS. The use of the USING qualifier for dynamic SQL inputs has been deprecated in favor of DVARS.
• The AdminApi has been redesigned for use with the new Translator/JCA split and for deployment in JBoss AS.
• The AdminShell is based on Groovy. Most BeanShell syntax is also valid in Groovy, but there are extensive changes in the AdminAPI that may necessitate rewriting scripts.
• OPTION NO CACHE <table list> will now only by-pass cache for the given tables at their immediate view layer. Previously all caches would have transitively been skipped. See TEIID-900 for more information.
• Nan and +-Infinity are no longer allowed as numerical results by default. This may be changed via the system property org.teiid.allowNanInfinity.
• The scale of BigDecimal values is now adjusted to allow for more intuitive results. Conversion from floating numeric types will preserve the approximate scale and division results will allow for additional scale. See TEIID-159 for more information.
• The 7.0 client is not compatible with 6.x or vice versa. It is expected that 7.0 clients will be compatible with 7.x releases for standard JDBC operations.

Configuration Issues

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.

from 6.2

• A JBoss AS instance is now required. Embedded mode is also currently not supported.
• Engine configuration is now handled through the admin console or deploy/teiid/teiid-jboss-beans.xml
• Logging is now done through the container's log4j instance. Any Teiid specific logging changes, see the conf/jboss-teiid-log4j.xml, need to be incorporated into the master jboss-log4j.xml.

Other Issues

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

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.

From 6.2

• Start time is now significantly longer due to the container deployment. However many operations can be performed on configruation files that will trigger a redeployment without the need for a restart.
• Inversion of parse, format, and convert system functions is not used if it can be narrowing. In situations where a predicate has the form: parseTime(column, 'format') = {t 'time value'} may lead to reduced performance, since the parseTime function cannot be pushed down. This should be addressed in future releases.

Thirdparty Library Updates

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.

From 6.2

• Netty was upgraded to 3.1.5
• Saxon was upgraded to 9.1.0.8
• A 5.1 container's version of Arjuna is newer than what was previously included in Teiid 6.x.
• Various other dependencies have been removed as they are no longer needed or now provided by the container.

Detailed Release Notes

Bug

• [TEIID-89] - Data roles should account for the ability to create temp tables
• [TEIID-1350] - Teiid 7.2 in JBoss EAP cluster (with clustered cache on) will not deploy VDBs
• [TEIID-1357] - Provide an example of an ODBC ds file to be used with a JDBC-ODBC bridge connection
• [TEIID-1360] - with clause not processed in views
• [TEIID-1361] - Salesforce IN predicate handling
• [TEIID-1362] - salesforce push down queries won't get resolved correctly
• [TEIID-1370] - Error Code:107 Message:'The column prefix 'custsale' does not match with a table name or alias name used in the query - But query is valid
• [TEIID-1372] - Change the default Teiid user from "admin" to something else
• [TEIID-1373] - Document that 'OID' column in the system tables is not durable across different versions of the same VDB.
• [TEIID-1381] - pushdown validation not accurate for all update
• [TEIID-1387] - Restrict access of default port 31000 to JDBC and 31443 to Admin connections
• [TEIID-1390] - Partial multi-byte char values misread in InputStreamReader
• [TEIID-1391] - Changes to the use of SSL when connecting with the Teiid Driver are ignored after a successful connection
• [TEIID-1395] - XMLPARSE error message references XMLSERIALIZE
• [TEIID-1396] - XML_PLAN output to server log too verbose for DEBUG level logging
• [TEIID-1399] - Adding ORDER BY to SELECT COUNT(*) FROM ... returns org.teiid.core.TeiidProcessingException: null to client - Needs clearer message
• [TEIID-1401] - QueryRewriter issue with certain UDFs
• [TEIID-1402] - SELECTing data from salesforce with a WHERE condition which includes ID results in a NPE
• [TEIID-1404] - The teiid runtime jar overrides commons logging configuration
• [TEIID-1406] - Salesforce connector does not parse {ts'2003-03-11 11:42:10.5'} - gets mismatched character ' ' expecting ':'
• [TEIID-1407] - Client code fails while inserting "lobs" in few situations
• [TEIID-1408] - SalesForce.com - Unable to get managed connection - java.lang.NullPointerException
• [TEIID-1410] - MALFORMED Query when trying to execute a query with OUTER JOINs which only contains elements from JOINed source
• [TEIID-1411] - Incorrect IN behavior when using a SELECT clause as IN values
• [TEIID-1414] - Execution of a Prepared Statement may receive LanguageBridgeFactory AssertionError
• [TEIID-1416] - Admin console shows no information about sessions
• [TEIID-1418] - Metadata loaded incorrectly
• [TEIID-1420] - Adminshell getTransactions() fails - exception = org.teiid.adminapi.AdminComponentException:
• [TEIID-1422] - Limit pushing can result in inconsistent xml results
• [TEIID-1426] - NPE when SELECTing NULL columns from JOINed tables
• [TEIID-1427] - Class not found in Teiid JON agent
• [TEIID-1428] - ODBC connection fails if the TeiidDriver is not registered.
• [TEIID-1434] - Example dynamicvdb-portfolio customer-schema.sql INSERT INTO HOLDINGS has PRODUCT_ID references that don't exist in PRODUCT table causing sql to fail
• [TEIID-1435] - SQL type boolean imported as object for dynamic vdbs
• [TEIID-1439] - Adminshell/API - sequence of datasource creation/VDB deployment and queries results in deployment error
• [TEIID-1441] - java.lang.NullPointerException - on queries involving lookup (temp table cache does not have buffermanager set on it)
• [TEIID-1442] - clearCache("QUERY_SERVICE_RESULT_SET_CACHE") - in ER7 - totalEntries is not reset to zero
• [TEIID-1443] - SalesForce connector execution of procedure fails with NPE
• [TEIID-1444] - Jopr Plugin has description for refreshing Materialized Views that's awkward: "Refresh a given any Materialized View for this VDB"
• [TEIID-1445] - ConcurrentModificationException during the startup of the Server
• [TEIID-1446] - Error during startup when CXF is installed
• [TEIID-1451] - Teiid does not handle failover correctly
• [TEIID-1456] - Processing errors after the first batch has been returned may not be reported

Feature Request

• [TEIID-146] - Translate Criteria enhancement
• [TEIID-192] - Add the ability to switch the identity on connections
• [TEIID-211] - subquery optimization
• [TEIID-229] - Support update of the view column using a column
• [TEIID-231] - Ability to support Connector Level UDF functions in the System
• [TEIID-1227] - Lob values should be cachable
• [TEIID-1253] - Allow for cache scope to be set in the cache hint
• [TEIID-1267] - Add support for non-pushdown subquery evaluation on update statements
• [TEIID-1330] - API request: Delegating Translator
• [TEIID-1338] - Add transitive join conditions across sources
• [TEIID-1349] - Better default update procedure handling
• [TEIID-1351] - Replace translate criteria
• [TEIID-1376] - Add support for pushing joins through a union
• [TEIID-1385] - Add a way to support escaped strings
• [TEIID-1389] - Add additional detection for conflicting criteria to help optimize query plan
• [TEIID-1412] - Teiid should take care of salesforce query limits by itself
• [TEIID-1421] - Support better scrolling of cached results
• [TEIID-1433] - Temp tables should support count(*)
• [TEIID-1440] - Add support for not null and auto increment for temp table columns

Quality Risk

• [TEIID-1368] - "Each" should be a parser keyword

Documentation and Help

• 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. Browse Licenses

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.