I'm experiencing the following situation - I'd like to import data from a MySQL database to a DB2 database by using InfoSphere's control and data flows. I was able to create the connection to the MySQL DB in Data Source Explorer, I can query the DB and I created a physical data model to work with (by using reverse-engineering).
However, I can't seem to read any data out of the MySQL DB when using the Table Source or SQL Query Source operators. It seems that the that DB doesn't like the auto-generated code sent from those operators - JDBCExtractor: Query: SELECT Q5."id" AS "id" FROM (select id from easyrec.item) Q5
JDBCExtractor: SQL Exception: SQL state = 42000; error code = 1064; error Message = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"id" AS "id" FROM (select id from easyrec.item) Q5' at line 1
Indeed, if I query the MySQL DB with this SQL, I'm getting the same error. Is there a way how to influence the auto-generated SQL code?
In other words, is it possible to have a MySQL table as a source of data for further processing in data flows?
Thanks a lot.
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
2 replies Latest Post - 2012-04-05T08:44:49Z by Secloud
Pinned topic How to read data from a MySQL DB table?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-04-05T08:44:49Z at 2012-04-05T08:44:49Z by Secloud
SystemAdmin 110000D4XK203 Posts
Secloud 270004MJ6K5 PostsACCEPTED ANSWER
Re: How to read data from a MySQL DB table?2012-04-05T08:44:49Z in response to SystemAdminHi zhangqi,
Thanks so much for your prompt answer! I figured it out how to use the SQL Query Source operator (not sure about the Custom SQL, I'll play with it later) - I just needed to tell the engine not to generate so much code automatically - by unchecking the 'Enable optimization for code generation on distributed queries' and the 'Use declared temporary table as internal staging table when possible' options available for the whole data flow (see the attached screen shot).