February 2017 - teiid-issues - Jboss List Archives

[JBoss JIRA] (TEIID-4398) Write a utility to convert a .VDB with Index file into -vdb.xml or DDL format

by Bram Gadeyne (JIRA)

[ https://issues.jboss.org/browse/TEIID-4398?page=com.atlassian.jira.plugin... ] Bram Gadeyne commented on TEIID-4398: ------------------------------------- Thank you [~rareddy], I've tried converting our vdb file but I get an error: D:\teiid9.2.0\bin>teiid-convert-vdb.bat D:\workspaceTeiid\IZ\vdb3.24.vdb Exception in thread "main" org.teiid.deployers.VirtualDatabaseException: TEIID40106 VDB vdb3 deployment failed. Override translators are not allowed in embedded mode. at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:771) at org.teiid.runtime.EmbeddedServer.deployVDBZip(EmbeddedServer.java:764) at org.teiid.runtime.util.ConvertVDB.convert(ConvertVDB.java:111) at org.teiid.runtime.util.ConvertVDB.main(ConvertVDB.java:70) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.jboss.modules.Module.run(Module.java:330) at org.jboss.modules.Main.main(Main.java:505) Press any key to continue . . . Indeed we are using an override translator for sybase to set the property JtdsDriver=true; I've then created a copy of our vdb file that points to the regular sybase translator and removed the override translator. Then the process generates indeed a clear ddl file. Can I suggest to add " > output.ddl' to the documentation and to remove the pause statement at the end of the script? The script generates the DDL file on STDOUT but if you add > output.ddl it does not print the "Press any key to continue..." in the console but in the output.ddl file so to the user it is not clear that the script is just waiting for a user to press a key. > Write a utility to convert a .VDB with Index file into -vdb.xml or DDL format > ----------------------------------------------------------------------------- > > Key: TEIID-4398 > URL: https://issues.jboss.org/browse/TEIID-4398 > Project: Teiid > Issue Type: Task > Components: Build/Kits > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Fix For: 9.2 > > Attachments: teiid-convert-vdb.bat, teiid-convert-vdb.sh > > > Write a command line utility and provide in "bin" directory to convert the Designer based .vdb file with index metadata into -vdb.xml file and/or newer DDL format. > This can be used to migrate the older VDBs mach easier to newer formats without use of Designer tooling. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4619) left join returns wrong results

by Bram Gadeyne (JIRA)

[ https://issues.jboss.org/browse/TEIID-4619?page=com.atlassian.jira.plugin... ] Bram Gadeyne commented on TEIID-4619: ------------------------------------- Sure, that's ok! > left join returns wrong results > ------------------------------- > > Key: TEIID-4619 > URL: https://issues.jboss.org/browse/TEIID-4619 > Project: Teiid > Issue Type: Bug > Affects Versions: 9.0.4, 9.0.5 > Reporter: Bram Gadeyne > Assignee: Steven Hawkins > Priority: Critical > Fix For: 9.2.1 > > Attachments: correct_result.txt, enclosed_queryplan.txt, query1_enclosed_plan.txt, query1_plan.txt, query2_plan.txt, teiid_reduced_case.txt, wrong_result.txt > > > I have the following situation. > I have a temporary table #tmp_admissions that contains 8047 rows. > In this first query there are 66290 results. However if I only look at the lines for infectionid 880 then there are only 16 lines. > {code:sql} > select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber, > cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime, > abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype, > lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts > from #tmp_admissions adm > join cos2_links l on l.admissionid = cast(adm.patientid as string) > join cos2_link_culture lc on lc.linkid = l.id > left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts > left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber > left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts > {code} > This query does almost the same but returns 30 rows (and is correct). > {code:sql} > select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber, > cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime, > abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype, > lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts > from cos2_links l > join cos2_link_culture lc on lc.linkid = l.id > left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts > left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber > left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts > where l.infectionid = 880 > {code} > cos2_link_culture contains 2 rows for this infectionid. The left join statements should result in 15 rows for both rows. However the left join results in the first query for the first row are null and to my understanding ignored. I'll attach the query plans for both queries. > I should note that there is a one to many relation between infection and admission so therefore infectionid is for the same admission. > Strangely enough if you enclode the first query in a group by query and count the rows it does indeed return 2 times 15 for the specific groups (see enclosed_queryplan.txt). -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4738) Change JDG Materialization to use Upsert

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-4738?page=com.atlassian.jira.plugin... ] Van Halbert commented on TEIID-4738: ------------------------------------ After further discussions, this jira could be closed. The current 3 cache materialization will be kept for backwards compatibility and will be needed until the new design is implemented and matures. This option to use UPSERT is in Teiid 9.3 and can be configured in an vdb.xml file. A teiid designer jira has been logged to provide the tooling. > Change JDG Materialization to use Upsert > ---------------------------------------- > > Key: TEIID-4738 > URL: https://issues.jboss.org/browse/TEIID-4738 > Project: Teiid > Issue Type: Enhancement > Components: JDG Connector > Reporter: Van Halbert > Assignee: Van Halbert > Fix For: 9.3 > > > Change materialization to use the new Upsert feature. > This will enable the removing of the Alias and Staging caches. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4738) Change JDG Materialization to use Upsert

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-4738?page=com.atlassian.jira.plugin... ] Work on TEIID-4738 stopped by Van Halbert. ------------------------------------------ > Change JDG Materialization to use Upsert > ---------------------------------------- > > Key: TEIID-4738 > URL: https://issues.jboss.org/browse/TEIID-4738 > Project: Teiid > Issue Type: Enhancement > Components: JDG Connector > Reporter: Van Halbert > Assignee: Van Halbert > Fix For: 9.3 > > > Change materialization to use the new Upsert feature. > This will enable the removing of the Alias and Staging caches. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4758) Permanent materialization load failure is when target source goes down

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-4758?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-4758: ------------------------------------- They are done scheduled timeline, if the previous load fails it retries in one minute again. > Permanent materialization load failure is when target source goes down > ---------------------------------------------------------------------- > > Key: TEIID-4758 > URL: https://issues.jboss.org/browse/TEIID-4758 > Project: Teiid > Issue Type: Bug > Components: Server > Affects Versions: 8.12 > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Fix For: 9.3 > > > During the external materialization load, if the target cache database goes offline, the materialization job stops, but the {{Status}} table is left in {{LOADING}} state, which will never recover when the target cache database comes back up again. > This situation is observed when JDG is used in OpenShift along with JDV However, behavior can occur in standalone situations too. The system should resilient and must recover in this situation. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4766) MySQL LEFT JOIN performance issue with external materialization management

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4766?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-4766: ---------------------------------- Fix Version/s: 9.3 9.2.1 Summary: MySQL LEFT JOIN performance issue with external materialization management (was: Improve LEFT JOIN performance queries when using MySQL 5 Translator) Updated the title to further reflect the issue. > MySQL LEFT JOIN performance issue with external materialization management > -------------------------------------------------------------------------- > > Key: TEIID-4766 > URL: https://issues.jboss.org/browse/TEIID-4766 > Project: Teiid > Issue Type: Enhancement > Affects Versions: 9.1.2 > Environment: * MySql 5.6.35 > * CentOs 7 > * Teiid 9.1.2 > * WildFly 10 > Reporter: Pedro Inácio > Assignee: Steven Hawkins > Fix For: 9.3, 9.2.1 > > Attachments: MySqlQueryExplainPlan.png, TeiidQueryExplainPlan.png, server.log > > > For better understanding the performance problem, the description of the problem and possible enhancement will be done through a real example. > Having two tables defined in vdb: > * vodafone_nl > * numbering_plan > each having respectively: 1155 rows and 1,473,213 rows. > And also having each of these tables externally materialized in MySql in tables: > * vodafone_nl_cache > * numbering_plan_cache > The vodafone_nl table specification: > {code:sql} > CREATE VIEW vodafone_nl ( > mcc varchar(5), > mnc varchar(5), > ... > INDEX (mcc,mnc) > ) > ... > {code} > The numbering_plan table specification: > {code:sql} > CREATE TABLE numbering_plan ( > mobile_country_code varchar(5), > mobile_network_code varchar(5), > ... > INDEX (mobile_country_code,mobile_network_code) > ) > ... > {code} > The vodafone_nl_cache table specification: > {code:sql} > CREATE TABLE vodafone_nl ( > mcc varchar(5), > mnc varchar(5), > ... > INDEX (mcc,mnc) > ) > ... > {code} > The numbering_plan_cache table specification: > {code:sql} > CREATE TABLE numbering_plan_cache ( > mobile_country_code varchar(5), > mobile_network_code varchar(5), > ... > INDEX (mobile_country_code,mobile_network_code) > ) > ... > {code} > And having the translator defined as > {code:xml} > <translator name="mysql-override" type="mysql5"> > <property name="SupportsNativeQueries" value="true"/> > </translator> > {code} > When executing the following query in a Client: > {code:sql} > SELECT COUNT(*) > FROM > VodafoneNl.vodafone_nl AS vnl > LEFT JOIN NumberingPlan.numbering_plan AS np > ON (np.mobile_country_code = vnl.mcc) AND (np.mobile_network_code = vnl.mnc) > {code} > Teiid Server will transform it in the following query: > {code:sql} > SELECT COUNT(*) AS c_0 > FROM `mnom`.`vodafone_nl_cache` AS g_0 > LEFT OUTER JOIN ( > SELECT g_1.`mobile_country_code` AS c_0, g_1.`mobile_network_code` AS c_1 > FROM `mnom`.`numbering_plan_cache` AS g_1) AS v_0 > ON v_0.c_0 = g_0.`mcc` AND v_0.c_1 = g_0.`mnc` > LIMIT 200 > {code} > This query will take 22 seconds in our system. > If we do an explain statement in MySqlWorkbench we observe the following: > (please refer to TeiidQueryExplainPlan.png image) > There are two Full Index Scans, one returning 1155 rows and a second returning 1452482 rows followed by a Non-Unique Key Lookup. > If the exact same query is run directly in MySql the system only takes 0.984 seconds to respond. > {code:sql} > SELECT COUNT(*) > FROM > vodafone_nl_cache AS vnl > LEFT JOIN numbering_plan_cache AS np > ON (np.mobile_country_code = vnl.mcc) AND (np.mobile_network_code = vnl.mnc) > {code} > If we do an explain statement in MySqlWorkbench we observe the following: > (please refer to MySqlQueryExplainPlan.png image) > There is one Full Index Scan, returning 1155 rows followed by a Non-Unique Key Lookup. > Between the two queries there is a difference of 21 seconds. > So it is necessary to improve the way Teiid Server converts a LEFT JOIN in MySQL to boost performance. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4769) Queries fail against SYS.Datatypes with predicates on columns other than name

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4769?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4769. ----------------------------------- Fix Version/s: 9.2 Resolution: Done Corrected the columns that represent the name index. > Queries fail against SYS.Datatypes with predicates on columns other than name > ----------------------------------------------------------------------------- > > Key: TEIID-4769 > URL: https://issues.jboss.org/browse/TEIID-4769 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 9.2 > > > The datatiermanager logic is incorrect for datatypes. Any sargable predicate on a column other than the pk name, for exampl: > select * from SYS.DataTypes where isStandard = true > will fail with an index out of bounds exception as we're allowing the predicate to be used effectively against the name index. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4758) Permanent materialization load failure is when target source goes down

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-4758?page=com.atlassian.jira.plugin... ] Van Halbert commented on TEIID-4758: ------------------------------------ As you indicated, when the source goes down, the VDB may still be in active state. And assuming no VDB is being deployed, what would trigger the rescheduling? > Permanent materialization load failure is when target source goes down > ---------------------------------------------------------------------- > > Key: TEIID-4758 > URL: https://issues.jboss.org/browse/TEIID-4758 > Project: Teiid > Issue Type: Bug > Components: Server > Affects Versions: 8.12 > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Fix For: 9.3 > > > During the external materialization load, if the target cache database goes offline, the materialization job stops, but the {{Status}} table is left in {{LOADING}} state, which will never recover when the target cache database comes back up again. > This situation is observed when JDG is used in OpenShift along with JDV However, behavior can occur in standalone situations too. The system should resilient and must recover in this situation. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4758) Permanent materialization load failure is when target source goes down

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-4758?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-4758: ------------------------------------- Just deployment of the VDB in ACTIVE state. No resource adapters are consulted. > Permanent materialization load failure is when target source goes down > ---------------------------------------------------------------------- > > Key: TEIID-4758 > URL: https://issues.jboss.org/browse/TEIID-4758 > Project: Teiid > Issue Type: Bug > Components: Server > Affects Versions: 8.12 > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Fix For: 9.3 > > > During the external materialization load, if the target cache database goes offline, the materialization job stops, but the {{Status}} table is left in {{LOADING}} state, which will never recover when the target cache database comes back up again. > This situation is observed when JDG is used in OpenShift along with JDV However, behavior can occur in standalone situations too. The system should resilient and must recover in this situation. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4398) Write a utility to convert a .VDB with Index file into -vdb.xml or DDL format

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-4398?page=com.atlassian.jira.plugin... ] Ramesh Reddy updated TEIID-4398: -------------------------------- Comment: was deleted (was: Looks like the DDL is not being wrapped in properly in CDATA section, this needs to be fixed.) > Write a utility to convert a .VDB with Index file into -vdb.xml or DDL format > ----------------------------------------------------------------------------- > > Key: TEIID-4398 > URL: https://issues.jboss.org/browse/TEIID-4398 > Project: Teiid > Issue Type: Task > Components: Build/Kits > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Fix For: 9.2 > > Attachments: teiid-convert-vdb.bat, teiid-convert-vdb.sh > > > Write a command line utility and provide in "bin" directory to convert the Designer based .vdb file with index metadata into -vdb.xml file and/or newer DDL format. > This can be used to migrate the older VDBs mach easier to newer formats without use of Designer tooling. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

7 years, 10 months

1
0
0 / 0

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues February 2017