October 2014 - teiid-issues - Jboss List Archives

[JBoss JIRA] (TEIID-2605) Optimization substitutes wrong column in where clause

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2605?page=com.atlassian.jira.plugin... ] Steven Hawkins closed TEIID-2605. --------------------------------- > Optimization substitutes wrong column in where clause > ----------------------------------------------------- > > Key: TEIID-2605 > URL: https://issues.jboss.org/browse/TEIID-2605 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.1 > Environment: z/OS > Reporter: Jeff Hayes > Assignee: Steven Hawkins > Attachments: query_plan.txt, views.xml > > > Optimization results in a query with a different column in the WHERE clause producing an empty result set. > Full query plan is attached but the beginning and ending queries are shown below. Note that the subject column of the IN clause is SCOPEID but optimization changes it to AUTHID for some reason. > USER COMMAND: > SELECT * FROM SECURITY.SCPXREF AS CHORUS_B WHERE (CHORUS_B.SYSID = 'DE29') AND ((CHORUS_B.SCOPEID IN (SELECT SN5.SCOPEID FROM SECURI > TY.SCPNEXT AS SN5 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN4.SCOPEID FROM SECURITY.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND > (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURI > TY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))))) OR (CHORUS_B.SCOPEID IN (SELECT SN4.SCOPEID FROM SECURIT > Y.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND > (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))) OR (CHORUS_B.SC > OPEID IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.S > CPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))) OR (CHORUS_B.SCOPEID IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEX > T AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))) OR (CHORUS_B.SCOPEID = 'CHRDEPT1')) > OPTIMIZATION COMPLETE: > PROCESSOR PLAN: > AccessNode(10) output=[x.sysid AS sysid, x.scopeid AS authid, x.authid AS scopeid, x.authtype AS authtype] SELECT g_0.SYSID, g_0.SCO > PEID, g_0.AUTHID, g_0.AUTHTYPE FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPXREF AS g_0 WHERE (g_0.SYSID = 'DE29') AND ((g_0.AUTHID IN > (SELECT g_1.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_1 WHERE (g_1.SYSID = 'DE29') AND (g_1.NEXTREC IN (SELECT g_2 > .SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_2 WHERE (g_2.SYSID = 'DE29') AND (g_2.NEXTREC IN (SELECT g_3.SCOPEID FR > OM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_3 WHERE (g_3.SYSID = 'DE29') AND (g_3.NEXTREC IN (SELECT g_4.SCOPEID FROM SECURITY > _CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_4 WHERE (g_4.SYSID = 'DE29') AND (g_4.NEXTREC = 'CHRDEPT1'))))))))) OR (g_0.AUTHID IN (SELECT > g_5.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_5 WHERE (g_5.SYSID = 'DE29') AND (g_5.NEXTREC IN (SELECT g_6.SCOPEI > D FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_6 WHERE (g_6.SYSID = 'DE29') AND (g_6.NEXTREC IN (SELECT g_7.SCOPEID FROM SECU > RITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_7 WHERE (g_7.SYSID = 'DE29') AND (g_7.NEXTREC = 'CHRDEPT1'))))))) OR (g_0.AUTHID IN (SELE > CT g_8.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_8 WHERE (g_8.SYSID = 'DE29') AND (g_8.NEXTREC IN (SELECT g_9.SCOP > EID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_9 WHERE (g_9.SYSID = 'DE29') AND (g_9.NEXTREC = 'CHRDEPT1'))))) OR (g_0.AUTH > ID IN (SELECT g_10.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_10 WHERE (g_10.SYSID = 'DE29') AND (g_10.NEXTREC = 'C > HRDEPT1'))) OR (g_0.AUTHID = 'CHRDEPT1')) > The view definitions are shown below: > <view name="SCPNEXT"> > <columns> > <column name="sysid" type="varchar"/> > <column name="scopeid" type="varchar"/> > <column name="nextrec" type="varchar"/> > </columns> > <definition> > #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0) > #set ($count = 0) > #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'")) > #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0) > #set ($count = $count + 1) > #end > #end > #if ($count == 0) > SELECT t.sysid, t.scopeid, t.nextrec > FROM ( > #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'")) > SELECT n.sysid, n.scopeid, n.nextrec > FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPNEXT n > #if( $velocityHasNext ) UNION #end > #end > ) AS t > #end > #end > </definition> > </view> > <view name="SCPXREF"> > <columns> > <column name="sysid" type="varchar"/> > <column name="authid" type="varchar"/> > <column name="scopeid" type="varchar"/> > <column name="authtype" type="varchar"/> > </columns> > <definition> > #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0) > #set ($count = 0) > #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'")) > #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0) > #set ($count = $count + 1) > #end > #end > #if ($count == 0) > SELECT sysid, scopeid, authid, authtype > FROM ( > #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'")) > SELECT x.sysid, x.scopeid, x.authid, x.authtype > FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPXREF x > #if( $velocityHasNext ) UNION #end > #end > ) AS t > #end > #end > </definition> > </view> > I guess the best way to test this is to define these views and run the input query with SHOWPLAN=DEBUG and see if the AUTHID substitution is occurring. > Thanks! -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

22 hours, 18 minutes

2
1
0 / 0

[JBoss JIRA] (TEIID-3193) Teiid not removing/setting the correct TCCL before calling getMetadata on translator

by Michael Farwell (JIRA)

Michael Farwell created TEIID-3193: -------------------------------------- Summary: Teiid not removing/setting the correct TCCL before calling getMetadata on translator Key: TEIID-3193 URL: https://issues.jboss.org/browse/TEIID-3193 Project: Teiid Issue Type: Bug Components: Common Affects Versions: 8.8 Reporter: Michael Farwell Assignee: Steven Hawkins Fix For: 8.10 Ramesh helped me debug a class loading issue that I'm experiencing with a custom translator and asked me to open this ticket based on his findings. The forum link is specified below and contains all of the necessary information. Ramesh's words: "OK, I believe the geoTools is using the TCCL to load the schema, thus the error. However, the error is on the Teiid side not correctly removing/setting the correct TCCL before we call "getMetadata" call on the translator. You can add JIRA for this we will fix it." -- This message was sent by Atlassian JIRA (v6.3.1#6329)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3192) Migrate Salesforce connectivity to sf partner api jars

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3192?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-3192: --------------------------------------- Added the initial commit. This keeps our api version at 22, so I moved the force wsc back to 22 from 26 for consistency. From here we can look at adding additional config parameters as needed to replace whatever the cxf config could have been used for. The dynamic metadata does look the same between the old and the new version - except for some of the native types, which are reported differently such as _boolean rather than boolean. I confirmed querying against all tables (although two tables marked as queryable through the api are actually not at api version 22) and updates. The bulk update logic was changed to use a single job and add all batches to that job rather than serially processing - which should improve performance. I was able to confirm that batching now works and there is a corresponding performance improvement even for moderately sized results. A sample with 9000 small result rows through CXF was taking approximately ~8 seconds, vs. ~4.5 seconds through the partner api. > Migrate Salesforce connectivity to sf partner api jars > ------------------------------------------------------ > > Key: TEIID-3192 > URL: https://issues.jboss.org/browse/TEIID-3192 > Project: Teiid > Issue Type: Component Upgrade > Components: Salesforce Connector > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.10 > > > There are several deficiencies in using CXF for salesforce connectivity - we are not batching correctly, there is additional CXF overhead, and we have to maintain our own generated java of the api. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3192) Migrate Salesforce connectivity to sf partner api jars

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3192?page=com.atlassian.jira.plugin... ] Steven Hawkins edited comment on TEIID-3192 at 10/31/14 11:29 AM: ------------------------------------------------------------------ A couple of issues I have also noticed: Insert should not strip quotes from string values, and bulk insert cannot handle literal values - insert into foo (a, b) values (?, 1), single row insert doesn't handle timestamp formatting, join results also don't handle timestamp values in the child, and bulk insert returns a single int rather than individual codes. was (Author: shawkins): A couple of issues I have also noticed: Insert should not strip quotes from string values, and bulk insert cannot handle literal values - insert into foo (a, b) values (?, 1), single row insert doesn't handle timestamp formatting, and join results also don't handle timestamp values in the child. > Migrate Salesforce connectivity to sf partner api jars > ------------------------------------------------------ > > Key: TEIID-3192 > URL: https://issues.jboss.org/browse/TEIID-3192 > Project: Teiid > Issue Type: Component Upgrade > Components: Salesforce Connector > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.10 > > > There are several deficiencies in using CXF for salesforce connectivity - we are not batching correctly, there is additional CXF overhead, and we have to maintain our own generated java of the api. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3192) Migrate Salesforce connectivity to sf partner api jars

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3192?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-3192: --------------------------------------- A couple of issues I have also noticed: Insert should not strip quotes from string values, and bulk insert cannot handle literal values - insert into foo (a, b) values (?, 1), single row insert doesn't handle timestamp formatting, and join results also don't handle timestamp values in the child. > Migrate Salesforce connectivity to sf partner api jars > ------------------------------------------------------ > > Key: TEIID-3192 > URL: https://issues.jboss.org/browse/TEIID-3192 > Project: Teiid > Issue Type: Component Upgrade > Components: Salesforce Connector > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.10 > > > There are several deficiencies in using CXF for salesforce connectivity - we are not batching correctly, there is additional CXF overhead, and we have to maintain our own generated java of the api. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3191) Teiid global temporary table error with first invocation

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-3191?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-3191: ------------------------------------------------ Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1158508|https://bugzilla.redhat.com/show_bug.cgi?id=1158508] from ASSIGNED to MODIFIED > Teiid global temporary table error with first invocation > -------------------------------------------------------- > > Key: TEIID-3191 > URL: https://issues.jboss.org/browse/TEIID-3191 > Project: Teiid > Issue Type: Bug > Affects Versions: 8.7.1 > Reporter: Jan Stastny > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.9 > > Attachments: nativeSQL.vdb > > > When working with global temporary table in teiid an error occurs while accessing the temp table for the first time. > Error: org.teiid.metadata.Column cannot be cast to org.teiid.query.metadata.TempMetadataID > All the following invocations of the global temporary table run fine. > Global temporary table is defined through Teiid designer within my vdb. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3192) Migrate Salesforce connectivity to sf partner api jars

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3192?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-3192: --------------------------------------- The initial target of the migration will be the version 22 of the api. We can look changing our supported version with a different issue. > Migrate Salesforce connectivity to sf partner api jars > ------------------------------------------------------ > > Key: TEIID-3192 > URL: https://issues.jboss.org/browse/TEIID-3192 > Project: Teiid > Issue Type: Component Upgrade > Components: Salesforce Connector > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.10 > > > There are several deficiencies in using CXF for salesforce connectivity - we are not batching correctly, there is additional CXF overhead, and we have to maintain our own generated java of the api. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3191) Teiid global temporary table error with first invocation

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3191?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-3191. ----------------------------------- Fix Version/s: 8.7.1 8.9 Resolution: Done Corrected the metadata check for the pk, which assumed a traditional temp table rather than the initial metadata entry. > Teiid global temporary table error with first invocation > -------------------------------------------------------- > > Key: TEIID-3191 > URL: https://issues.jboss.org/browse/TEIID-3191 > Project: Teiid > Issue Type: Bug > Affects Versions: 8.7.1 > Reporter: Jan Stastny > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.9 > > Attachments: nativeSQL.vdb > > > When working with global temporary table in teiid an error occurs while accessing the temp table for the first time. > Error: org.teiid.metadata.Column cannot be cast to org.teiid.query.metadata.TempMetadataID > All the following invocations of the global temporary table run fine. > Global temporary table is defined through Teiid designer within my vdb. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3192) Migrate Salesforce connectivity to sf partner api jars

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3192?page=com.atlassian.jira.plugin... ] Steven Hawkins reassigned TEIID-3192: ------------------------------------- Assignee: Steven Hawkins (was: Mark Drilling) > Migrate Salesforce connectivity to sf partner api jars > ------------------------------------------------------ > > Key: TEIID-3192 > URL: https://issues.jboss.org/browse/TEIID-3192 > Project: Teiid > Issue Type: Component Upgrade > Components: Salesforce Connector > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.10 > > > There are several deficiencies in using CXF for salesforce connectivity - we are not batching correctly, there is additional CXF overhead, and we have to maintain our own generated java of the api. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3192) Migrate Salesforce connectivity to sf partner api jars

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3192: ------------------------------------- Summary: Migrate Salesforce connectivity to sf partner api jars Key: TEIID-3192 URL: https://issues.jboss.org/browse/TEIID-3192 Project: Teiid Issue Type: Component Upgrade Components: Salesforce Connector Reporter: Steven Hawkins Assignee: Mark Drilling Fix For: 8.10 There are several deficiencies in using CXF for salesforce connectivity - we are not batching correctly, there is additional CXF overhead, and we have to maintain our own generated java of the api. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

10 years, 11 months

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues October 2014