[stavroskalamat]

Does

[TEIID-3196] Remove Teradata join pushdown restriction - JBoss Issue Tracker 

mean that Teiid support for Teradata outer joins will be back because according to Teradata docs (see below URL)

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch02.033.022.html

outer joins are supported?

Thanks

sk

[rareddy]

Yes, TEIID-3196 an effort to turn the "outer join" support that in teradata translator. See fixed in field for which release this will be available.

[stavroskalamat]

That's great news. Really looking forward to it.

Should I expect a performance benefit  if I query the physical model (blue xmis), instead of the VBL ?

Any credit for this thought goes to the  Redhat engineer who recommended it in the past. 

Thanks

sk

[rareddy]

What it means is when Teiid sees query with outer join, instead of rewriting it, it will get pushed to source as is. Yes, that may increase the performance in few situations.

[stavroskalamat]

I was asked to explore the JBOSS capabilities and find out whether it supports pushdown optimization (we've already discussed part of that) but also if it supports inline macros for scripts created in the Data Virtualization layer before they get submitted to a data source. Any idea about that ?

[rareddy]

Macros you mean user defined functions?

[stavroskalamat]

I believe there's a point of confusion in the question and that's between inline functions and macros. I may be wrong but I think Java doesn't support macros and if I recall correctly, in Java all optimization takes place in the JVM so when it comes to inline processes all work is done at runtime. Those asked the Q. may think that the translator can handle a script the way JVM will perform inlining. I'll request a clarification. and re-port.

[stavroskalamat]

As far as best practices on writing SQL from dbVis against a VDB, does anything else come to mind along with the following.

Follow standard SQL code practices

      Finish a statement with a ;

      Use fully qualified database names

      Avoid returning all fields by using .*

      Use either an inner join or a where condition to define a join between tbls

      Prefer CAST & COALESCE as they are standard functions

Implement indexes if there’s a need but they are not present

      use materialized views on stale or less frequently updated data sets

      whenever the query optimizer relies on a field’s index ordering and the field is used in a where condition avoid manipulation on that field in the Where, e.g., select t1.fld1, t2.fld2 from t1 inner join t2 on t1.fld1=t2.fld2 where t1.fld1 = 3 (avoid manipulation like: where Right(t1.fld1,5) = 2

Use the newest teiid jdbc driver to ensure the latest translator is in use.

Make sure the Teiid technical documentation you’ll refer to for assistance is in sync with the version of Teiid currently used.

Internal materialized views and temp table usage from a session and within procedures can take advantage of greater transaction support.

If a developer has access to Dev Studio then studying Query Plans and logs will help troubleshooting sluggish performance

[shawkins]

> Avoid returning all fields by using .*

Ideally you'd avoid all star qualifications.

> Prefer CAST & COALESCE as they are standard functions

Most of the Teiid system functions have good push down support. The exceptions are xml, security, json, and parse/format functions.

> Use the newest teiid jdbc driver to ensure the latest translator is in use.

The driver will not influence the translators, but it is a good idea to use the latest.

> does anything else come to mind

Probably the most important is utilizing plans to understand execution even before performance is an issue and to make sure table cardinality and column stats are set to assist Teiid in making good planning decisions.

[stavroskalamat]

In an effort to improve the execution time of our scripts we followed the valid advice of a Redhat engineer, who volunteers his time to consult when he can, to override the translator. 

We overrode only a few of the properties shown below (Translators - Teiid 8.10 (draft) - Project Documentation Editor)

Name	Description	Default
Immutable	Set to true to indicate that the source never changes.	false
RequiresCriteria	Set to true to indicate that source SELECT/UPDATE/DELETE queries require a where clause.	false
SupportsOrderBy	Set to true to indicate that the ORDER BY clause is supported.	false
SupportsOuterJoins	Set to true to indicate that OUTER JOINs are supported.	false
SupportsFullOuterJoins	If outer joins are supported, true indicates that FULL OUTER JOINs are supported.	false
SupportsInnerJoins	Set to true to indicate that INNER JOINs are supported.	false
SupportedJoinCriteria	If joins are supported, defines what criteria may be used as the join criteria. May be one of (ANY, THETA, EQUI, or KEY).	ANY
MaxInCriteriaSize	If in criteria are supported, defines what the maximum number of in entries are per predicate. -1 indicates no limit.	-1
MaxDependentInPredicates	If in criteria are supported, defines what the maximum number of predicates that can be used for a dependent join. Values less than 1 indicate to use only one in predicate per dependent value pushed (which matches the pre-7.4 behavior).	-1
DirectQueryProcedureName	if the direct query procedure is supported on the translator, this property indicates the name of the procedure.	native
SupportsDirectQueryProcedure	Set to true to indicate the translator supports the direct execution of commands	false
ThreadBound	Set to true to indicate the translator's Executions should be processed by only a single thread

These properties were : SupportsOrderBy, SupportsInnerJoins and SupportsDirectQueryProcedure

we overrode them on the teradata translator by setting them all to true

We paid close attention to the case sensitivity of the properties names when we were adding them to the translator override

then we re-deployed and re-executed the VDB

We re-ran the scripts but we observed no improvement at all, as if we didn't override any property

Any ideas why not ? Any recommendations on how to override the translator properly, if the steps above indicate an error on our end ?

Thanks in advance

sk

[shawkins]

>  These properties were : SupportsOrderBy, SupportsInnerJoins and SupportsDirectQueryProcedure

> we overrode them on the teradata translator by setting them all to true

SupportsOrderBy and SupportsInnerJoins are already true for Teradata. SupportsDirectQueryProcedure will only be relevant if you are issuing direct to source queries: teradata_model.native('some source sql')

> Any ideas why not ? Any recommendations on how to override the translator properly, if the steps above indicate an error on our end ?

You may done the above correctly (although for effectively no result).  First we'd need an idea of why you are overriding properties, then we can make sure there will be a difference with the properties changed.

[stavroskalamat]

Performance tuning is still work in progress so, which cases the translator override  helps or not, is yet TBD and requires further investigation.

The jpg attached shows the generic getTextFiles  function for a txt data source we created to expedite import tbls (instead of doing a separate import for each tbl and having it show as a different data source). That way I need to reproduce the VBL SQL transformations only and present the tbls to a VDB faster.

Meanwhile, more data came around, this time about 60 tbls in MS Access 2007/2010 format.

I'm going over the slow way (Way1) to load txt files (one tbl at a time), but the Q. is how can I connect to MS Access db directly (Way2) to bring it into my existing VDB as a  another data source ?

I'm under the gun to complete this ASAP so any doc. leads or any other type of advice via a response to this thread will be helpful.

Meanwhile on Way1 I produced the txt files with pipe (|) text  qualifiers but there are a lot of abnormal chars in some of the string flds of the tbls, such as  " (2ble quotes) that create problems rendering the data from the txt files.

I want  to use ~ (tilde) as txt qualifiers and re-produce the txt files to avoid the aforementioned.

Where can I tell Teiid what qualifiers I have specified in the txt file and how can I manually specify what txt qualifier I'm using  in the sql transformation of a VBL tbl like the one below ?

SELECT A.DeersID, A.ID, A.AUD_QuickSIN_Patient_ID, A.AUD_QuickSIN_Reg, A.AUD_QuickSIN_Rev, A.AUD_QuickSIN_Rev_Fast FROM (EXEC Flat_File_Sources.getTextFiles(

'.\NICoE_Data\vn_AUD_QuickSIN.txt'

)) AS f, TEXTTABLE(f.file COLUMNS DeersID string , ID string , AUD_QuickSIN_Patient_ID string , AUD_QuickSIN_Reg string , AUD_QuickSIN_Rev string , AUD_QuickSIN_Rev_Fast string  DELIMITER '|' HEADER) AS A

Going back to Way 2 :

will a DSN-less conn. be good for MS Access

DSN Less Connection - Teiid 8.10 (draft) - Project Documentation Editor

or

Configuring the Data Source Name (DSN) - Teiid 8.10 (draft) - Project Documentation Editor

is preferred ?

thanks in advance

[rareddy]

Stavros,

If I were you I will not do your WAY (1), that may be my back up solution rather than primary way of doing it, and you are discovering why? you need to export, then import using the File resource adapter and use TEXTTABLE etc.

WAY(2) is the way to go. MS Access provides ODBC access to the database. However the link you were looking at is wrong. The link you provided is ODBC access to Teiid, not ODBC access from Teiid to MS Access. Take look at the "docs/teiid/datasources/odbc" directory for some instructions. The instructions there are for Excel sheet, but you can do similar DSN for the MS Access and connect to it and import the tables.

Ramesh..

[stavroskalamat]

Glad a direct conn. (Way2) is an option

sorry for the URL error, you're right, I'm looking at setting up a conn. from Teiid to MS Access, not the reverse.

Are the following URLs those I should be looking at ?

http://grepcode.com/file/repository.jboss.org/nexus/content/repositories/releases/org.jboss.teiid/teiid/8.0.0.Final/jboss-as7/docs/teiid/datasources/odbc/odbc-ds.xml

http://grepcode.com/file/repository.jboss.org/nexus/content/repositories/releases/org.jboss.teiid/teiid/8.0.0.Final/jbos…

also, is the following True?

For ODBC datasource, we use JDBC <--> ODBC bridge driver provided by Oracle that comes with Sun JDK. So, the driver

is already installed and available.

If yes then I need only a DSN conn. to Access and an xml addition to standalone xml to introduce the new MS Access datasource to Teiid, correct ?

thanks again.

sk

[rareddy]

yes