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.
Secloud 270004MJ6K5 Posts
Re: How to read data from a MySQL DB table?2012-04-05T08:44:49ZThis is the accepted answer. This is the accepted answer.
- SystemAdmin 110000D4XK
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).