Wednesday, December 14, 2011

Source Tables Made Easy

For our Teiid Designer 7.6 release we introduced a couple of new ways to create new source tables in your relational models.




If you have an existing model that needs a new table, select the model and choose the "New Child > Base Table ..." context action to launch the New Relational Table Wizard dialog. This wizard contains the options necessary to construct a relational table complete with columns, unique keys and foreign key definitions.





Another new way to create tables is to import an XML input file which you can generate using the new RelationalModel.xsd schema.  Launch the Import wizard and select Teiid Designer's "Designer Text File >> Source or View Models" option.  Then choose the "Relational Model (XML Format)" import type.

Select Next> to get to the file selection page. Browse to select your XML file and select a target model location for your new tables.






When finished your new tables will be displayed in the model's package diagram.




Happy Modeling....

Teiid Designer Team

Tuesday, December 6, 2011

Teiid Designer 7.6 Final Released

We are pleased to announce Teiid Designer 7.6 is now available.  Feature highlights include:
  • XML Source Data File Importer - generate relational model xml externally and import to create your relational model.
  • New Relational Table Wizard - Create a new relational table complete with column definition, keys and references in one wizard.
  • New XML Format Relational Model Importer - generate relational model xml externally and import to create your relational model.
  • New Model Extension Definition (MED) Framework - Provides more flexibility to add custom metadata to your models which enables your translators to be more flexible for accessing and exposing your source data. Framework includes a MED Registry View and MED editor.
For more see Teiid Designer 7.6 What's New...

See the release notes for more details on the 74 issues addressed.

Download it today....

The Teiid Designer Team

Tuesday, November 29, 2011

Teiid 7.6 Final Released

We are pleased to announce Teiid 7.6 is now available.  Highlights of 7.6 features:
  • Procedure language features - Added support for compound/block statements, BEGIN [[NOT] ATOMIC], loop/block labels, and the leave statement. See the reference for more.
  • File Enhancements - the file translator can now optionally (via the ExceptionIfFileNotFound property) throw an exception if the path refers to a file that doesn't exist. The file resource adapter can be configured to map file names and can prevent parent path .. references. See the Admin Guide or the file-ds.xml template for more.
  • TEXTTABLE Enhancements - TEXTTABLE can now parse fixed width files that do not use a row delimiter and can optionally produce fixed values that haven't been trimmed.
  • Temp table transactions - Internal materialized views and temp table usage from a session and within procedures can take advantage of greater transaction support.
  • Buffering Improvements
    • Added the ability to inline memory based or small lobs.
    • Added tracking of the memory held by soft references and general batch overhead. This ensures more efficient cache/memory utilization when dealing with 10s of millions or more of batches/pages.
    • Also switched to a concurrent LFRU algorithm that significantly reduces writes and read misses with temporary tables.
    • Added a memory buffer to better handle file storage as fixed blocks. The memory buffer may optionally be configured as off-heap for better large memory performance - see the Admin Guide for more.
    • Serialization was improved for both internal batches and client/server batches.
  • GSSAPI - both the Teiid JDBC client/server and the ODBC pg backend can now support GSSAPI for single sign-on.
  • Server-side Query Timeouts - default query timeouts can be configured at both the VDB (via the query-timeout VDB property) and entire server (via the teiid-jboss-beans.xml queryTimeout property).
  • Native Queries - added the ability to specify native query SQL for JDBC physical tables and stored procedures via extension metadata. See the Reference for more.
  • View removal hint - the NO_UNNEST hint now also applies to FROM clause views and subqueries. It will instruct the planner to not perform view flattening.
  • Non-blocking statement execution - Teiid JDBC extensions TeiidStatement and TeiidPreparedStatement can be used to submit queries against embedded connections with a callback to process results in a non-blocking manner.
  • NON_STRICT limit hint - the NON_STRICT hint can be used with unordered limits to tell the optimizer to not inhibit push operations even if the results will not be consistent with the logical application of the limit.
  • Source Hints - user and transformation queries can specify a meta source hint, e.g. SELECT /*+ sh my-oracle:'leading' */ * FROM TBL. The hint information will be passed to the translator. The Oracle translator will by default treat the source hint as an Oracle hint. See the Reference and Developers Guide for more.
  • Hive Translator - Hive translator has been added as a technology preview.
There were 86 total issues addressed.  See the release notes for more details.  Download today, give it try, and vote for or log features you want in upcoming releases.

The Teiid Team

Wednesday, November 16, 2011

Teiid 7.6 CR1

7.6 CR1 is now available.  It fixes issues from the previous betas and contains the following new features: 
  • View removal hint - the NO_UNNEST hint now also applies to FROM  clause views and subqueries. It will instruct the planner to not perform view flattening.
  • Non-blocking statement execution - Teiid JDBC extensions TeiidStatement and TeiidPreparedStatement can be used to submit queries against embedded connections with a callback to process results in a non-blocking manner.
  • NON_STRICT limit hint - the NON_STRICT hint can be used with unordered limits to tell the optimizer to not inhibit push operations even if the results will not be consistent with the logical application of the limit.
  • Source Hints - user and transformation queries can specify a meta source hint, e.g. SELECT /*+ sh my-oracle:'leading' */ * FROM TBL. The hint information will be passed to the translator. The Oracle translator will by default treat the source hint as an Oracle hint.  See the Reference and Developers Guide for more.
  • Hive Translator - Hive translator has been added as a technology preview.
Please note that the weather web services example is no longer operational as the national weather service has modified their procedures.  We will have a replacement for the final release.

The Teiid Team

Thursday, October 27, 2011

Teiid 7.6 Beta1

7.6 Beta1 is now available.  It contains an overhaul of the memory management system, which includes: 
  • A Fixed Memory Buffer - A serialization buffer will be allocated to increase disk through-put.  It is also managed as a second tier cache.  You may optionally configure the size and set it to use off-heap memory - which is especially useful for dealing with larger amounts (typically 32BG+) of memory from the VM.  The memory buffer also allows for better disk usage based upon fixed allocation blocks vs. the old Teiid scheme which used a file per table/tuple buffer.
  • Concurrent LRFU - the main eviction algorithm was further refined to be concurrent and to better handle table work loads.
  • Numerous Performance Improvements - batch size estimation, serialization, and value type resolution were all improved.
You may also now configure query timeout defaults at both the server and per-VDB level.

Now is a great time to provide feedback on any issues or minor enhancements before it's too late for the release.

The Teiid Team

Tuesday, October 4, 2011

Teiid 7.6 Alpha1

7.6 Alpha1 is now available and includes the following notable features since 7.5:
  • Procedure language features - Added support for compound/block statements, BEGIN [[NOT] ATOMIC], loop/block labels, and the leave statement. See the reference for more.
  • File Enhancements - the file translator can now optionally (via the ExceptionIfFileNotFound property) throw an exception if the path refers to a file that doesn't exist. The file resource adapter can be configured to map file names and can prevent parent path .. references. See the Admin Guide or the file-ds.xml template for more.
  • TEXTTABLE Enhancements - TEXTTABLE can now parse fixed width files that do not use a row delimiter and can optionally produce fixed values that haven't been trimmed.
  • Temp table transactions - Internal materialized views and temp table usage from a session and within procedures can take advantage of greater transaction support.
  • Buffering Improvements - Added the ability to inline memory based or small lobs and added tracking of the memory held by soft references. Also switched to a LFRU algorithm that significantly reduces writes and read misses with temporary tables.
  • GSSAPI - both the Teiid JDBC client/server and the ODBC pg backend can now support GSSAPI for single sign-on. (this is in the documentation, but not yet in the release notes as it has yet to be tested).
For even more detailed information on these or any pending issues, check out the JIRA issues here.  Please note that development of Teiid 8 is happening concurrently with the 7.6 development and will incorporate the 7.6 work as well.

We are also looking for anyone who wants to volunteer to help validate the GSSAPI feature.

Thanks,

The Teiid Team

Sunday, August 21, 2011

Teiid 7.5 Final Released

We are pleased to announce Teiid 7.5 is now available.  Highlights of 7.5 features:
  • 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.
There were 93 total issues addressed.  See the release notes for more details.  Download today, give it try, and vote for or log features you want in upcoming releases.

The Teiid Team

Wednesday, August 10, 2011

Teiid 7.5 CR1

Adding on more features to 7.5 Beta 2, 7.5 CR1 is released today.  This marks end of new feature work for 7.5. We are planning a quick turn around and releasing 7.5 Final after some testing within next week.

If you are using 7.4 or any prior versions of Teiid, we highly encourage you to download this version of Teiid and validate your test cases and submit any bugs through JIRA. We will try to fix any critical and blocker bugs before the 7.5 Final.

New features in 7.5 CR1

- LDAP Pagination (TEIID-1659)
- Support for 'substring', 'trim', 'extract' functions (TEIID-1694)

If you are looking for new features in Teiid, this would a great time to start requesting and discussing them out in community forums, so that we can plan them for either 7.6 release or 8.0 releases.

Thank you.

The Teiid Team

Saturday, July 30, 2011

Teiid 7.5 Beta2

7.5 Beta2 is now available with new features and numerous fixes since beta1.  Highlights include:
  • 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.
  • 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.
As always see the release notes for more. For even more detailed information on these or any pending issues, check out the JIRA issues here. Please check any of your issues that have been resolved and log new ones before it's too late to hit the release.

The Teiid Team

Tuesday, June 28, 2011

Teiid 7.5 Beta1

7.5 Beta1 is now available. While mainly a fix release, there have been notable features since 7.4:
  • 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.
As always see the release notes for more. For even more detailed information on these or any pending issues, check out the JIRA issues here. Please check any of your issues that have been resolved and log new ones before it's too late to hit the release.

The Teiid Team

Friday, May 13, 2011

Teiid 7.4 Final Released

We are pleased to announce Teiid 7.4 is now available.  Highlights of 7.4 features:
  • Virtual procedure out params - virtual procedures can now have RETURN/OUT/INOUT parameters to return values.
  • OLAP - OLAP translator is now part of the Teiid kit using OLAP4J
  • Multi-source procedures - multi-source handling was expanded to cover stored procedure execution. See the Reference for more.
  • Function Support - additional system functions were made available.
    • uuid was added to generate type 4 UUIDs and the Hibernate dialect was updated to support the GUIDGenerator.
    • array_get was added to extract values from java.sql.Array or java array values.
    • array_length was added to get the length of java.sql.Array or java array values.
  • ARRAYTABLE - the ARRAYTABLE table function was added to simplify array value extraction into a tabular format.
  • Ingres - Ingres database translator is now available to use as supported source under Teiid.
  • Optional Join Enhancements - the optional join hint no longer requires the use of ANSI joins and can will not remove optional bridging tables that are used by two other tables that are required.
  • InterSystems Cache - InterSystems Cache database translator is now available to use as supported source under Teiid.
  • userRequestSourceConcurrency - was added to control the number of concurrent source queries allowed for each user request.
  • Memory Management Improvements - maxReserveBatchColumns and maxProcessingBatchesColumns will by default be determined automatically and will more reliably prevent memory issues. See the admin guide for more.
  • Subquery optimization control - added the MJ and NO_UNNEST hints and the org.teiid.subqueryUnnestDefault system property to control the optimization of subqueries to traditional joins or to a merge join implementation of a semijoin or antijoin.
  • Local connection threads - local connection calling threads will be used to process work rather than using an engine thread. This helps decouple the configuration of maxThreads.
  • Dependent Join Improvements - several major improvements were made to increase performance and develop better plans.
    • Improved Planning - the decision to create a dependent join is now considered earlier in planning and is much more effective for dependent joins involving multiple unrelated independent tables.
    • IN predicate splitting - the planner can now split large dependent IN predicates into multiple IN predicates, which is controlled by the translator property MaxDepdendentInPredicates. This allows for much larger dependent joins to be performed as a single query.
    • Dependent query parallelization - when multiple dependent queries are still required, then they will be run in parallel (up to MaxUserSourceRequestConcurrency), rather than sequentially.
    • Cost based back-off - for cost based dependent joins if the number of independent values is too large, then the join will be performed as normal.
  • Enhanced Sort Join - the partitioned merge join was replaced with an enhanced sort join. The enhanced sort join will use the actual row counts from each side of the relation to perform a index based join if one side is small enough, a partial sort of the larger side and a repeated merge join if the tuples are unbalanced but one side is not small enough to form an index, or a standard sort merge join if the tuples are balanced.
  • JDK1.5 JDBC Client JAR - A retro-translated Teiid client JDBC jar now available to use with JDK 1.5 VM. Note only the JDBC API is supported, not the Admin API, or retrieving query plans as XML.
  • Security Improvements - UDF and pushdown functions can now be protected with data roles. Also the CommandContext can provide the Subject for custom security checks in UDFs.
  • Cache Invalidation - Prepared plan and result set caches have will invalidate entries based upon metadata and data changes respectively. See the cache configuration maxStaleness setting and the Admin and Developer Guides for more.
  • Runtime Updates of Metadata - ALTER statements have been added to change view/procedure/INSTEAD OF trigger (update procedure) definitions. A CREATE TRIGGER statement is also available to add an INSTEAD OF trigger (update procedures) to views. System procedures were added to set extension metadata and stat values. By default all effects of metadata updates happen only on running vdbs across the cluster. To make the changes persistent see the Developers Guide Runtime Updates section.
There were numerous issues addressed as well.  See the release notes for more details.  Download today, give it try, and vote for or log features you want in upcoming releases.

The Teiid Team

Tuesday, May 3, 2011

Teiid 7.4 CR1

7.4 CR1 is now available. Notable features since beta3:
  • Security Improvements - UDF and pushdown functions can now be protected with data roles. Also the CommandContext can provide the Subject for custom security checks in UDFs.
  • Cache Invalidation - Prepared plan and result set caches have will invalidate entries based upon metadata and data changes respectively. See the cache configuration maxStaleness setting and the Admin and Developer Guides for more.
  • Runtime-updates of Metadata - ALTER statements have been added to change view/procedure/INSTEAD OF trigger (update procedure) definitions. A CREATE TRIGGER statement is also available to add an INSTEAD OF trigger (update procedures) to views. System procedures were added to set extension metadata and stat values. By default all effects of metadata updates happen only on running vdbs across the cluster. To make the changes persistent see the Developers Guide Runtime Updates section.
The pg ODBC layer has also been further solidified, see https://issues.jboss.org/browse/TEIID-1460 which implies that Teiid should be consumable from additional platforms.
As always see the release notes for more. For even more detailed information on these or any pending issues, check out the JIRA issues here.
Please check any of your issues that have been resolved and ensure there are no lurking critical/blocker issues.

The Teiid Team

Thursday, April 7, 2011

Teiid 7.4 Beta2

7.4 Beta2 is now available. Notable features since beta1:
  • improved dependent join processing through in predicate splitting and parallization
  • threading improvements for the ODBC transport and local connections
  • the more scalable enhanced sort join replaces the partitioned sort join
As always see the release notes for more. For even more detailed information on these or any pending issues, check out the JIRA issues here.

Please check any of your issues that have been resolved and log new ones before it's too late to hit the release.

The Teiid Team

Wednesday, March 23, 2011

Teiid 7.4 Beta1

7.4 Beta1 is now available. From 7.3 we've added a host of important features including:
  • improved dependent/optional join planning
  • improved memory management
  • subquery optimization control
  • support for multi-source procedures
  • the uuid/array_get/array_length system functions
  • the arraytable table function
  • control over source query concurrency
  • virtual procedure out/return/in_out params (requires a pending Teiid Designer 7.4 milestone)
There is also new source connectivity with an OLAP translator and Ingres/InterSystems Cache JDBC translator variants.

As always see the release notes for more. For even more detailed information on these or any pending issues, check out the JIRA issues here.

Please check any of your issues that have been resolved and log new ones before it's too late to hit the release.

The Teiid Team

Tuesday, February 1, 2011

Materializied Views In Teiid

Teiid supports various kinds of caching

  • Caching of final results of a user query, called "result set" caching.
  • Caching contents of a virtual table, called "materialized views".
This article is a practical "How To" guide for users who want to use "Materialized Views" in their use-case to improve the query performance. For more information about caching in the Teiid please consult "Caching Guide".

http://community.jboss.org/docs/DOC-16397


Thank you for using Teiid.

Monday, January 31, 2011

Teiid 7.3 Final Released

We are pleased to announce Teiid 7.3 is now available. Highlights of 7.3 features:

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

There were numerous issues addressed as well. See the release notes for more details. Download today, give it try, and vote for or log features you want in upcoming releases.

The Teiid Team

Note: the upcoming Teiid Designer 7.3 will be required to use the updatable view enhancements.

Wednesday, January 19, 2011

Teiid 7.3 CR1 Released

7.3 CR1 is now available for download. From last release of 7.3 Beta1, support for auto-increment IDs for temp tables is added. Also couple caching issues has been resolved when the JBoss AS being run in the "all" profile. When using H2 as the source "boolean" data type was being imported incorrectly, that has been fixed along with few other issues. For more information check out the JIRA issues here.

If you have been putting off 7.3 version so far and working with 7.2-Final, this is time for you to check your usecase with 7.3 version and report any regressions that we might have missed, so that we can fix them before the 7.3 Final is released.

Thank you.

Teiid Team

Tuesday, January 11, 2011

Teiid 7.3 Beta 1

7.3 Beta 1 refines the delegating translator, changes the default user credentials to user/user, and provides even more Salesforce fixes. See the release notes and updated guides for more. Expect a candidate release soon, so now is great time to validate your fixes and features.

The Teiid Team