IC4NOTICE: 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.
2 replies Latest Post - ‏2012-04-05T08:44:49Z by Secloud
5 Posts

Pinned topic How to read data from a MySQL DB table?

‏2012-04-04T13:56:25Z |
Hi folks,

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.
Updated on 2012-04-05T08:44:49Z at 2012-04-05T08:44:49Z by Secloud
  • SystemAdmin
    203 Posts

    Re: How to read data from a MySQL DB table?

    ‏2012-04-05T01:49:43Z  in response to Secloud
    You can use Custom SQL operator instead. This is a known problem and will be fix in the upcoming release.
    • Secloud
      5 Posts

      Re: How to read data from a MySQL DB table?

      ‏2012-04-05T08:44:49Z  in response to SystemAdmin
      Hi 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).