-
1. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
rareddy Oct 18, 2010 11:17 AM (in response to meltedmetal)1 of 1 people found this helpfulS.Q,
As I mentioned in http://community.jboss.org/message/566899#566899 the column "SOURCE_NAME" is a runtime effect, after you create and deploy VDB and mark it as "multi-source". But what you are asking is at Design time. At design time, Designer does not know if you are going to mark this VDB as multi-source or not. So, it will never add this column automatically when you import metadata from your original source. You would need to manually add this column on your view table.
Howeever, if you deployed the VDB and configured it as "multi-source", and used that VDB as source of metadata for another VDB then you will this column in the metadata.
For clarity, I have updated the reference document "multi-source" section with the above details.
Ramesh..
-
2. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
meltedmetal Oct 19, 2010 3:19 AM (in response to rareddy)Hi, Ramesh
Thanks for your help!
I tried the two ways to let the column 'SOURCE_NAME' visible in Teiid Designer.
1) Add the column 'SOURCE_NAME' on view table manullly. But the vdb can't be deployed successfully.
The screen shot of Teiid Designer is below. The select transformation is not fully reconciled.
And yet, I have tried to deploy the vdb, but the log showed that deployment failed.
2) As you mentioned "you deployed the VDB and configured it as "multi-source", and used that VDB as source of metadata for another VDB then you will this column in the metadata." I have tried it too.
I imported the metadata from the Teiid VDB configured as "multi-source". However, the column "SOURCE_NAME" was not visible in the model table still.
More details:
a. VDB1 has a physical model "localdbModel" configured as "multi-source".
b. VDB1Model is imported from datasource VDB1. VDB2 includes the VDB1Model.
Then:
In SQuirreL SQL Client:
a. Connect to VDB1, the result of select statement of tables of model ''localdbModel" has the "SOURCE_NAME" column, but the metadata doesn't has it.
b. Connect to VDB2, the result of select statement of tables of model "VDB1Model" and the metadata of "VDB1Model" both don't have the column "SOURCE_NAME".
As you said, I think the column "SOURCE_NAME" should be visible in the metadata of table.
3) And I have tried that:
VDB3 has model "localdbModel" configured as "multi-source" and a view model "localdbViewModel" transformed by the model "localdbModel".
In the update transformation procedure of the view model "localdbViewModel", the below SQL is not resolved.
SELECT COUNT(*) FROM localdbModel.books.book WHERE SOURCE_NAME = "localdb"
The reason is
The query is parsable but not resolvable. Resolver Error: Element "SOURCE_NAME" is not defined by any relevant group.
Best Regards,
S.Q.
-
3. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
rareddy Oct 19, 2010 10:17 AM (in response to meltedmetal)S.Q,
Sorry, I was be wrong in suggesting to add to the view table, add the extra column on the "source" table itself that will solve the issue. Also, looks like the metadata not being visible when you stacked VDBs is a bug. I logged https://jira.jboss.org/browse/TEIID-1308.
Thanks
Ramesh..
-
4. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
meltedmetal Oct 20, 2010 5:46 AM (in response to rareddy)Hi, Ramesh
Thanks for your hints.
Adding the extra column on the "source" table itself lets me make some progress.
However, I have encountered some other problems.
Preconditions:
1). To describe problems simply and clearly, I use charts. In the charts, the Circle represent model, rectangle represent datasource.
2). All datasource has same tables and same columns. The tables are below:
Problems:
1). The structure of integration:(Please click the below chart to show big chart.)
Then:
In ws.vdb, below query is ok.
select * from localdb.books.book where source_name='localdb';
In finalVDB.vdb, below query is ok.
select * from ws_teiid_connection.localdb.books_book;
However, below query doesn't work.
select * from ws_teiid_connection.localdb.books_author where source_name='localdb';
The error is :
select * from ws_teiid_connection.localdb.books_book where source_name='localdb'; Error Code:Error Code:ERR.015.006.0033 Message:Unable to evaluate ?: No value was available Executing statement: [Prepared Values: ['localdb'] SQL: SELECT g_0.id, g_0.name, g_0.authorId, g_0.publishId, g_0.SOURCE_NAME FROM "books.book" AS g_0 WHERE g_0.SOURCE_NAME = ?] Message:Remote org.teiid.core.TeiidProcessingException: Error Code:Error Code:ERR.015.006.0033 Message:Unable to evaluate ?: No value was available Executing statement: [Prepared Values: ['localdb'] SQL: SELECT g_0.id, g_0.name, g_0.authorId, g_0.publishId, g_0.SOURCE_NAME FROM "books.book" AS g_0 WHERE g_0.SOURCE_NAME = ?] Message:Error Code:Error Code:ERR.015.006.0033 Message:Unable to evaluate ?: No value was available Executing statement: [Prepared Values: ['localdb'] SQL: SELECT g_0.id, g_0.name, g_0.authorId, g_0.publishId, g_0.SOURCE_NAME FROM "books.book" AS g_0 WHERE g_0.SOURCE_NAME = ?] Message:0 Elapsed Time: 0 hr, 0 min, 0 sec, 0 ms.
I don't kown what the reason is. I think the query could be execute correctly.
2). The structure of integration:
In the ws.vdb, the below query is ok.
select * from localdbViewModel.books.book where source_name='localdb';
However, the update statement fails.
update localdbViewModel.books.book set name = '3' where SOURCE_NAME='localdb';
The error is
update localdbViewModel.books.book set name = '3' where SOURCE_NAME='localdb'; Remote org.teiid.api.exception.query.QueryPlannerException: Left side of update expression must be an element that supports update: localdb.books.book.SOURCE_NAME Elapsed Time: 0 hr, 0 min, 0 sec, 0 ms.
What does the error mean?
Below sql statements are ok.
select * from localdb.books.book; select * from localdb.books.book where source_name='localdb'; select * from localdb.books.book where source_name='DFS'; select * from localdb.books.book where source_name='pDFS'; update localdb.books.book set name = '3' where SOURCE_NAME='localdb'; update localdb.books.book set name = '3' where SOURCE_NAME ='DFS'; update localdb.books.book set name = '4' where id = 1 AND SOURCE_NAME='localdb';
3) The structure of integration.
In the problem 1) and 2), the datasource DFS is a mysql JDBC connection. Now the datasource pDFS is a Teiid JDBC connection.
In this case:
I can update the records from datasource localdb by below statement:
update localdb.books.book set name = '3' where SOURCE_NAME='localdb';
However, I can not update the records from datasource pDFS by below statement and no error is printed.
update localdb.books.book set name = '3' where SOURCE_NAME ='pDFS';
By the way, I can update the records in direct connection to pDFS.
I think maybe some issues is in teiid driver or some else.
Thanks!
Best Regards,
S.Q.
-
5. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
rareddy Oct 20, 2010 11:56 AM (in response to meltedmetal)S.Q,
Great job on explanation, pictures are good.
1) I did not quite understand how the VDBs were stacked. finalVDB.vdb accesses ws.vdb? Final has the single source, that is of ws.vdb. How are the models layed out in the finalVDB.vdb? Your query needs to be wrt finalVDB's model structure. Overall, there seems to be seems some issue with prepared statement. .
2 & 3) Have you enabled and provided the update transformations on the view tables? Also what version of Teiid you are using. Use one of the latest like alpha2 and beta1 release.
Note that when you import the metadata in the Designer using one of the sources like 'localdb' in your case, it can some times include the fully qualified metadata, including the schema name. That may not match all the sources when you are dealing with multi-source scenario. I would advise to import them with "empty" source object name. You can choose this in Desinger's import wizard on the last page.
So, in this case you can say ( assuming your model name is 'books)
select * from books.book where source_name = 'localdb'
update books.book set name = '3' where source_name = 'localdb'
having the 'localdb' in your queries is clouding the intent of the multi-source as there is 'localdb' source and 'localdb' model.
Also, if you open up the command logging you can see what Teiid is sending to the sources, it may explain why you see no errors sometimes.
Ramesh..
-
6. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
meltedmetal Oct 22, 2010 6:38 AM (in response to rareddy)Hi, Remashe
Thanks for your active supports consistently.
Some updates of the above three problems:
For 1):
In your first reply, you mentioned "if you deployed the VDB and configured it as "multi-source", and used that VDB as source of metadata for another VDB then you will this column in the metadata."
In this case, ws.vdb is the vdb that configured as "multi-source", finalVDB.vdb is another vdb that uses ws.vdb as source of metadata and source of data.
Until now I understand your true meaning. finalVDB should only use ws.vdb as source of metadata and not use ws.vdb as source of data.
The VDBs stack only clouds the problem.
BTW, I have tried the VDBs stack on Teiid 7.2 beta 1, it still same error.
For 2):
The reason is in my update transformation procedure. It is ok now.
For 3):
I found that :
A VDB's select transformation is below:
SELECT * FROM sm_dfs.book WHERE id IN (SELECT refBookId FROM sm_provision.provison_book)
A update statement without where clause doesn't affect any records.
It should affect all records of the given table.
It happed on Teiid 7.1 and Teiid 7.2 beta 1.
Importing models with empty source name makes the query clear and simple.
Best Regards,
S.Q.
-
7. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
rareddy Oct 22, 2010 11:05 AM (in response to meltedmetal)S.Q,
Hi, Remashe
My Name is 'Ramesh'
For 1):
Until now I understand your true meaning. finalVDB should only use ws.vdb as source of metadata and not use ws.vdb as source of data.
The VDBs stack only clouds the problem.
BTW, I have tried the VDBs stack on Teiid 7.2 beta 1, it still same error.
Actually both, for metadata and data. I have logged this issue for the error https://jira.jboss.org/browse/TEIID-1308
we hope to fix this before 7.2 final, which is to be released before the end of this month.
For 3):
A update statement without where clause doesn't affect any records.It should affect all records of the given table.
It happed on Teiid 7.1 and Teiid 7.2 beta 1.
hmm. That should not be! if it is then it is bug. Let me verify.
Ramesh..
-
8. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
rareddy Oct 22, 2010 7:22 PM (in response to rareddy)S.Q,
I see the update happens correctly, however I am ending up with exception that is not returning the correct update count. I will look into that and fix before the next release. Are you seeing any exceptions? Can you turn on logging to show any issues?
Thanks.
Ramesh..
-
9. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
rareddy Oct 26, 2010 9:26 PM (in response to rareddy)S.Q,
I have tested the UPDATE with out the WHERE clause, not seeing the issue you reported. However, we see this issue
https://jira.jboss.org/browse/TEIID-1295
This has been fixed, it will be part of 7.2 - CR1 release. If you are using the 7.1 or any version before 7.2 - beta1 release, the reported update count was wrong. Teiid only counted the update from one of sources, not all of them.
Ramesh..
-
10. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
meltedmetal Oct 29, 2010 3:36 AM (in response to meltedmetal)Hi, Ramesh
Sorry for the slip of the pen.
For 1)
I think maybe you didn't understand my meanings. I can see the SOURCE_NAME column in tables of view model of finalVDB.
But the SQL statement with SOURCE_NAME is not executed correctly in the data structure mentioned in 1) .
I saw the TEIID-1308 is rejected. I think it is ok. As you mentioned we can add the SOURCE_COLUMN manually.
For 3)
Sorry for unclear description. Some more description.
There is a simple VDB without 'multi-source'. The view model is below:
The book table in view only includes part of the physical book table by selection transformation in above image.
The update transformation is below, the SOURCE_NAME column is physical column.
In the above case, the sql statement with where clause is ok.
update provisionedDFS.refbooks.book set name='444' where name ='333';
But, the sql statement without where clause is no affection.
update provisionedDFS.refbooks.book set name='444';
I have open the log with DEBUG level, but there is not any error or exception.
Teiid Command logs are below:
Log for with where clause :
2010-10-29 15:31:17,172 DEBUG [org.teiid.COMMAND_LOG] (New I/O server worker #2-2:) START USER COMMAND: startTime=2010-10-29 15:31:17.172 requestID=gvoBh/xMAvzn.0 txID=null sessionID=gvoBh/xMAvzn applicationName=JDBC principal=admin@teiid-security vdbName=provisionedDFS vdbVersion=1 sql=select * from provisionedDFS.refbooks.book; 2010-10-29 15:31:17,188 DEBUG [org.teiid.COMMAND_LOG] (Worker3_QueryProcessorQueue55:) START DATA SRC COMMAND: startTime=2010-10-29 15:31:17.188 requestID=gvoBh/xMAvzn.0 sourceCommandID=4 txID=null modelName=DFS connectorBindingName=mysql5 sessionID=gvoBh/xMAvzn principal=admin@teiid-security sql=SELECT g_0.id, g_0.name, g_0.authorId, g_0.publishId, g_0.SOURCE_NAME FROM DFS.refbooks.book AS g_0 2010-10-29 15:31:17,188 DEBUG [org.teiid.COMMAND_LOG] (Worker3_QueryProcessorQueue57:) END SRC COMMAND: endTime=2010-10-29 15:31:17.188 requestID=gvoBh/xMAvzn.0 sourceCommandID=4 txID=null modelName=DFS connectorBindingName=mysql5 sessionID=gvoBh/xMAvzn principal=admin@teiid-security finalRowCount=2 2010-10-29 15:31:17,188 DEBUG [org.teiid.COMMAND_LOG] (Worker4_QueryProcessorQueue58:) START DATA SRC COMMAND: startTime=2010-10-29 15:31:17.188 requestID=gvoBh/xMAvzn.0 sourceCommandID=1 txID=null modelName=provision connectorBindingName=mysql5 sessionID=gvoBh/xMAvzn principal=admin@teiid-security sql=SELECT g_0.refBookId FROM provision.books.provison_book AS g_0 2010-10-29 15:31:17,188 DEBUG [org.teiid.COMMAND_LOG] (Worker4_QueryProcessorQueue60:) END SRC COMMAND: endTime=2010-10-29 15:31:17.188 requestID=gvoBh/xMAvzn.0 sourceCommandID=1 txID=null modelName=provision connectorBindingName=mysql5 sessionID=gvoBh/xMAvzn principal=admin@teiid-security finalRowCount=1 2010-10-29 15:31:17,235 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue61:) END USER COMMAND: endTime=2010-10-29 15:31:17.235 requestID=gvoBh/xMAvzn.0 txID=null sessionID=gvoBh/xMAvzn principal=admin@teiid-security vdbName=provisionedDFS vdbVersion=1 finalRowCount=0 2010-10-29 15:31:31,594 DEBUG [org.teiid.COMMAND_LOG] (New I/O server worker #2-2:) START USER COMMAND: startTime=2010-10-29 15:31:31.594 requestID=8TYAIBle1hXT.0 txID=null sessionID=8TYAIBle1hXT applicationName=JDBC principal=admin@teiid-security vdbName=provisionedDFS vdbVersion=1 sql=update provisionedDFS.refbooks.book set name='666' where name ='555'; 2010-10-29 15:31:31,609 DEBUG [org.teiid.COMMAND_LOG] (Worker4_QueryProcessorQueue63:) START DATA SRC COMMAND: startTime=2010-10-29 15:31:31.609 requestID=8TYAIBle1hXT.0 sourceCommandID=1 txID=null modelName=DFS connectorBindingName=mysql5 sessionID=8TYAIBle1hXT principal=admin@teiid-security sql=UPDATE DFS.refbooks.book SET name = '666' WHERE DFS.refbooks.book.name = '555' 2010-10-29 15:31:31,641 DEBUG [org.teiid.COMMAND_LOG] (Worker4_QueryProcessorQueue65:) END SRC COMMAND: endTime=2010-10-29 15:31:31.641 requestID=8TYAIBle1hXT.0 sourceCommandID=1 txID=null modelName=DFS connectorBindingName=mysql5 sessionID=8TYAIBle1hXT principal=admin@teiid-security finalRowCount=1 2010-10-29 15:31:31,641 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue66:) END USER COMMAND: endTime=2010-10-29 15:31:31.641 requestID=8TYAIBle1hXT.0 txID=null sessionID=8TYAIBle1hXT principal=admin@teiid-security vdbName=provisionedDFS vdbVersion=1 finalRowCount=0
Log for without where clause is only two line:
2010-10-29 15:29:55,033 DEBUG [org.teiid.COMMAND_LOG] (New I/O server worker #2-2:) START USER COMMAND: startTime=2010-10-29 15:29:55.033 requestID=s75EBqy5fbh7.0 txID=null sessionID=s75EBqy5fbh7 applicationName=JDBC principal=admin@teiid-security vdbName=provisionedDFS vdbVersion=1 sql=update provisionedDFS.refbooks.book set name='555' 2010-10-29 15:29:55,049 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue53:) END USER COMMAND: endTime=2010-10-29 15:29:55.049 requestID=s75EBqy5fbh7.0 txID=null sessionID=s75EBqy5fbh7 principal=admin@teiid-security vdbName=provisionedDFS vdbVersion=1 finalRowCount=0
And I also found you mentioned the bug of ROWS_UPDATED.
Thanks for your support.
Best Regards,
S.Q.
-
11. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
rareddy Oct 29, 2010 11:10 AM (in response to meltedmetal)S.Q.
1) I assume this is multi-source scenario. With the SOURCE_NAME column there are couple restrictions, to support it.
In "SELECT", you can use SOURCE_NAME in selection as well as in WHERE
In "UPDATE" you can not use it in "SET" clause, but can use with WHERE. SET is not allowed because it is a pseudo column
In "DELETE" you can only use it "WHERE"
in "INSERT" you can use as one of the columns to be inserted.
2) You are saying this is not multi-source VDB. You added the "SOURCE_NAME" to physical source, and in general "UPDATE" is not behaving as you noted. Can you confirm. I am also running tests.
Thanks.
Ramesh..
-
12. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
meltedmetal Oct 31, 2010 7:59 AM (in response to rareddy)Hi, Ramesh
I should clarify the update scenario:
1) In the table of the view model, it is a part of physical table filtered by another table data. The select transformation is in the above screenshot.
2) The 'SOURCE_NAME' cloumn is in the physical and view table.
I think maybe that one of the two conditions is the root cause.
I will test other two scenarios, each scenario only has one of the above two conditions.
After my testing, I will tell you the results.
Thanks.
Best Regards,
S.Q.
-
13. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
rareddy Oct 31, 2010 11:41 AM (in response to meltedmetal)1 of 1 people found this helpfulS.Q,
It may help if you see the query plan for your execution. Just run
SET SHOWPLAN ON UPDATE ... SHOW PLAN
to see the query plan. More info about it here. http://docs.jboss.org/teiid/7.1.0.Final/reference/en-US/html_single/#query_plan
Ramesh..
-
14. Re: Support teiid feather 'multi-source' scenario in Teiid Designer
meltedmetal Nov 3, 2010 1:53 AM (in response to rareddy)Hi, Ramesh
Thanks for your help.
I have tried a simple scenario.
In a vdb,
First, import a source model named "mysql_books" from a JDBC database.
then, creating a view model named "vm_books" transformed from the existing model "mysql_books".
Execute the below update statement:
update vmbooks.book set name='555'
In the query plan I found that:
UPDATE mysql_books.book SET name = '555' WHERE 1 = 0
I ran the testing in the Teiid 7.2 Beta1.
The whole query plan is below:
ProjectNode
+ Output Columns:Count (integer)
+ Statistics:
0: Node Output Rows: 1
1: Node Process Time: 0
2: Node Cumulative Process Time: 0
3: Node Cumulative Next Batch Process Time: 0
4: Node Next Batch Calls: 1
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
PlanExecutionNode
+ Output Columns:Count (integer)
+ Statistics:
0: Node Output Rows: 1
1: Node Process Time: 0
2: Node Cumulative Process Time: 0
3: Node Cumulative Next Batch Process Time: 0
4: Node Next Batch Calls: 1
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Execution Plan:
Program
+ Instruction 0:
CREATE CURSOR
+ Result Set:EXECSQL_INSTRUCTION
+ Query:
AccessNode
+ Output Columns:Count (integer)
+ Statistics:
0: Node Output Rows: 1
1: Node Process Time: 0
2: Node Cumulative Process Time: 0
3: Node Cumulative Next Batch Process Time: 0
4: Node Next Batch Calls: 1
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:UPDATE mysql_books.book SET name = '555' WHERE 1 = 0
+ Model Name:mysql_books
+ Instruction 1:
ASSIGNMENT
+ Variable:ROWS_UPDATED
+ Expression:VARIABLES.ROWCOUNT
+ Output Columns:Count (integer)
+ Select Columns:CountThank you.
The query plan is very useful.
S.Q.