Teiid 7.5.0.Final Release Notes

Teiid 7.5.0.Final adds XML streaming, additional ANSI SQL, 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

• Window function support - Teiid and pushdown support was added for SQL 2003 OLAP window functions. The analytical ranking functions RANK, DENSE_RANK, and ROW_NUMBER were also added. See the reference for more.
• Additional ANSI support - Teiid and pushdown support was added for SUBSTRING using FROM/FOR syntax, TRIM, EXTRACT, SIMILAR TO, and LIKE_REGEX. See the reference for more.
• Subquery optimization control - added the DJ hint to indicate that a subquery should be the independent side of a dependent join.
• MAKEIND Hint - The MAKEIND hint can be used to indicate that the other side of the join should be made dependent.
• ODBC SSL - added support for SSL encrypted ODBC connections.
• Reauthentication Statement - SET SESSION AUTHORIZATION can now be used to perform a reauthentication via JDBC or ODBC.
• Pluggable Authorization - an alternative PolicyDecider can be defined in the teiid-jboss-beans.xml file to customize authorization decisions.
• Streaming XQuery - in situations where document projection applies if the XMLQUERY/XMLTABLE path expressions meet certain conditions, then the incoming document will not only be projected, but the independent subtrees will be processed without loading the entire document. This allows for nearly arbitrarily large XML documents to be processed. See the Reference for more.
• Logging Procedures - added SYSADMIN.isLoggable and SYSADMIN.logMsg to aid in debugging procedure logic.
• ANSI OFFSET/FETCH FIRST - instead of the limit clause, a standard OFFSET and/or FETCH FIRST/NEXT clause can be used to limit results.
• ODBC Cursors - added the capability to use "UseDeclareFetch" with ODBC. This enables users to read results in batches, which is especially useful when dealing with large row counts.
• Internal Materialized Views
  • Distributed Refresh - When a internal materialized view is refreshed at one node, an event is issued to all other nodes in the cluster to asynchronously fetch the new contents.
  • Sync at Start - When a node is restarted in a cluster, at end of start cycle Teiid will asynchronously fetch the cached internal materialized view contents from other nodes.
• LDAP Pagination - the ldap translator now supports pagination for retrieving large results from directory servers like Active Directory and OpenLDAP.

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.
• Support for using the FROM clause post item hints MAKEDEP/MAKENOTDEP has been deprecated. Use the pre item comment hint syntax instead, e.g. /*+ MAKEDEP */ tbl

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.

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

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

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

• 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-1648 - Teradata lobs can only be consistently read from an embedded client (with the default option to use the calling thread) and in single threaded mode (transactional or userRequestSourceConcurrency=1). Usage of Teradata lobs under any other configuration is unlikely to work.

Thirdparty Library Updates

From 7.4

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

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-97] - add proper SQLState for timeout/cancel
• [TEIID-1443] - SalesForce connector execution of procedure fails with NPE
• [TEIID-1528] - ruleimplementjoin strategy may generate an order by that does not use a relevant index
• [TEIID-1591] - Error thrown when attempting to use clustered cache in teiid 7.4
• [TEIID-1592] - EventDistributor is not initialized correctly
• [TEIID-1593] - Creating a temp table gets error that Cannot create temporary table "dddd". A table with the same name already exists.
• [TEIID-1595] - User without required role is able to explicitly create a temporary table
• [TEIID-1597] - Index metadata issues
• [TEIID-1603] - BQT Query not returning expected row count
• [TEIID-1605] - Unable to call a function that is defined within a package in oracle
• [TEIID-1612] - Tried a custom log appended, but the LoggingEvent.getMessage() returns org.teiid.logging.LogMessage and not org.teiid.logging.CommandLogMessage
• [TEIID-1613] - Cannot define an implicit temp table with columns and a query value expression
• [TEIID-1614] - Embedded connections using the calling thread can hang
• [TEIID-1616] - ORDER BY not ordering query results on Teradata query: SELECT DoubleNum AS E FROM VQT.U5 ORDER BY E
• [TEIID-1617] - Incorrect rewrite of subquery
• [TEIID-1618] - Designer (JBDS 4.1 CR1) not able to preview: NPE
• [TEIID-1621] - Unexpected grouping for SELECT queries on views that include a GROUP BY
• [TEIID-1623] - ODBC numeric and string precision issues
• [TEIID-1625] - SalesForce INCLUDE function not being pushed to source
• [TEIID-1626] - Subsequent usage of a statement cannot be timed out
• [TEIID-1628] - Mondrian not returning the correct row values for the "ROW" dimention
• [TEIID-1630] - XQT queries failing with: [TeiidProcessingException] 1054: Error Code:1054 Message:Source: Error Code:1054 Message:'Unknown column 'c_2' in 'order clause'
• [TEIID-1631] - ODBC SQLDescribeCol does not work, When used with the Prepare Statement
• [TEIID-1634] - For file connector-based queries, please provide a DEBUG warning if no files are found
• [TEIID-1635] - Error resolving a procedure invocation w/o a resultset in a procedure
• [TEIID-1639] - Unable to build on a file system that isn't formatted to support case-sensitivity
• [TEIID-1640] - QueryTimeout connection parameter does not cancel long-running queries
• [TEIID-1641] - In org.teiid.jdbc.StatementImpl, as there have been no batched updates the batchedUpdates member hasn't been instantiated so clearBatch() throws a NPE.
• [TEIID-1642] - ModeShape translator needs to be changed to move the supported functions to the pushdown functions so that the function def. file isn't needed to be updated
• [TEIID-1645] - SELECT COUNT(*) FROM VQT.Agg3 is expecting a result of 1 but 50 is being returned
• [TEIID-1647] - TeiidComponentException from generated web service
• [TEIID-1650] - hsql does not generally support catalog identifier parts
• [TEIID-1651] - Due to a change in the JDBC spec on how the use of "AS" in the select statement will now be returned in the getColumnLabel of the ResultSetMetadata, Teiid must provide backwards support
• [TEIID-1654] - CLONE - EDS - Node returns 0 rows for cached view when other node had it loaded and leaves cluster
• [TEIID-1657] - CLONE - EDS - refreshMatView does not work correctly for an internal materialized view when clustering is enabled
• [TEIID-1658] - teiid-jdbc-template-ds.xml jca example is in trunk, but also needs to be on 7.4.x branch
• [TEIID-1660] - EDS BQT Queries failing on SqlServer 2005 with SQLServerException "Type DATE is not a defined system type"
• [TEIID-1661] - Not all expression types are validated for grouping columns
• [TEIID-1662] - Teiid fails when calling databasemetadata.getTables and passing in a empty array for table types
• [TEIID-1663] - Local Transactions not working
• [TEIID-1664] - Teiid - Teradata queries containing LTRIM and RTRIM on the BQT booleanvalue column are returning unexpected BQT results
• [TEIID-1676] - Decomposed aggregates results can be wrong
• [TEIID-1677] - Exists subqueries will unnest by default
• [TEIID-1678] - Order by may be applied incorrectly with pushed aggregate
• [TEIID-1681] - Limit not working as expected
• [TEIID-1682] - Sort optimization with grouping does not preserve descending/null ordering
• [TEIID-1683] - Error Code:0 Message: Remote org.teiid.core.TeiidException:Error Code: 0 Message:Symbol EMAIL.EMAILDOCUMENT.MAPPINGCLASSES.EMAIL_1.id is specified with an unknown group context
• [TEIID-1686] - Assertion failure when using a plan result buffer
• [TEIID-1688] - Derby is inappropriately picking up capabilities
• [TEIID-1690] - NPE with xmltable in view
• [TEIID-1695] - CLONE - java.lang.ClassNotFoundException: nu.xom.ParsingException via Import > Teiid Designer > WSDL into Relational Source Model
• [TEIID-1696] - Issues with function schema qualification
• [TEIID-1697] - CLONE - java.io.NotSerializableException: java.io.PrintWriter when SHOWPLAN is DEBUG in a clustered configuration
• [TEIID-1698] - + is not properly handled in like patterns
• [TEIID-1704] - if where clause contains more than one equal condition on the same column, no rows are returned for the count query
• [TEIID-1706] - JOIN between two text file data sources extremely slow
• [TEIID-1708] - Teiid Designer Preview Feature does not refresh if a transformation is changed on a procedure
• [TEIID-1713] - default xmlnamespace fails to match root element when streaming
• [TEIID-1717] - NPE with texttable join
• [TEIID-1718] - Object type is not handled well in ODBC
• [TEIID-1719] - Executing an SQL statement over ODBC interface does not respond
• [TEIID-1721] - ODBC metadata handling does not account for update statements
• [TEIID-1722] - NPE with lookup function

Enhancement

• [TEIID-1586] - Increase audit information
• [TEIID-1587] - Minimize final project columns
• [TEIID-1601] - When the webservice translator cannot reach the endpoint url, the exception message should include the endpoint
• [TEIID-1602] - Add support for like_regex and other regex functions
• [TEIID-1611] - Add reauthentication support for ODBC
• [TEIID-1637] - In the Reference guide - Query Timeouts section - add a reference to the Client Developers guide
• [TEIID-1652] - ODBC Data Row results should be batched
• [TEIID-1656] - Add pushdown support for grouping expression over a UNION
• [TEIID-1671] - Add Translator for Netzza
• [TEIID-1691] - Improve xml staging table planning
• [TEIID-1694] - Add additional ansi sql function support
• [TEIID-1712] - Support different length records in files for modeling

Feature Request

• [TEIID-993] - Enhance XML processing to execute relational queries in parallel when possible
• [TEIID-1448] - XMLTABLE processing should support streaming when possible
• [TEIID-1460] - Have teiid emulate a native postgresql database connection
• [TEIID-1585] - Make payload available in ExecutionFactory.getConnection
• [TEIID-1607] - Provide a data role injection point where a different implementation could be used for role validation
• [TEIID-1615] - Add features to support relate planning
• [TEIID-1620] - Fetch keyword not handled
• [TEIID-1632] - Provide something like an ECHO statement in the procedure language that can assist a user in debugging their procedure logic
• [TEIID-1653] - Support Cursoring over ODBC protocol ( support UseDeclareFetch = 1)
• [TEIID-1659] - Support pagination of LDAP results when retrieving from Active Directory
• [TEIID-1665] - Optimize SELECT of all constants
• [TEIID-1667] - Add support for sql olap functionality
• [TEIID-1689] - Load internal mateirlized view cached contents when node joins the cluster
• [TEIID-1692] - Include an example of the LOOKUP function in the Ref Guide

Quality Risk

• [TEIID-1619] - Deprecate MAKEDEP/MAKENOTDEP keyword hints
• [TEIID-1624] - Remove the buffer space configuration settings that deal with batch columns
• [TEIID-1715] - Simplify ODBC buffers

Task

• [TEIID-1594] - Add to the sandox the example Coherence translator and connector
• [TEIID-1655] - CLONE - Error messages displayed in the transformation editor are often too generic or too long to be useful
• [TEIID-1693] - Update Teiid Admin Console Documentation

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.

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.