Convert DB2 -> mysql with sql::translator

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to convert a db2 (db2look) look dump to mysql schema. I get the error below:

Invalid statement: Was expecting comment, or create
Error: translate: Error with parser 'SQL::Translator::Parser::DB2':  n
+o results at /usr/bin/sqlt line 290.
[download]

The command that I ran was

sqlt -f DB2 -t MySQl dumpdb2.sql
[download]

I have also tried


  my $translator          = SQL::Translator->new(
      debug               => 1,
      trace               => 0,
      no_comments         => 1,
      show_warnings       => 0,
      add_drop_table      => 0,
      validate            => 1,
  );

my $output     = $translator->translate(
      from       => 'DB2',
      to         => 'MySQL',
      # Or an arrayref of filenames, i.e. [ $file1, $file2, $file3 ]
      filename   => 'test.sql',
  ) or die $translator->error;

  print $output;
[download]

No matter how I do it, I get that same error message, can anyone send me some monk's inner light?

Comment on Convert DB2 -> mysql with sql::translator Select or Download Code

Replies are listed 'Best First'.
Re: Convert DB2 -> mysql with sql::translator by clwolfe (Scribe) on Jul 08, 2006 at 07:00 UTC
I had a similar problem when trying to use SQL::Translator to parse SQL intended for PostgreSQL. As the previous poster said, the problem is definitely in the input SQL you're feeding sqlt. Unfortunately, the error messages generated by the parser is fairly unhelpful. While posting all the SQL might help, I bet the problem is in the table name. My problem was the same - it's the period. I dunno what DB2 calls it, but Postgres calls it a schema - I had fully qualified table names like financial.tbl_employees, meaning the table tbl_employees in the schema financial. Some RDMS call them namespaces, and some use more periods - Sybase uses namespace.owner.tablename, for example. The big problem here is that SQL::Translator just doesn't have any concept of a schema/namespace. So when it parses the input SQL, it first chokes on the period, then chokes on the fact that it really doesn't have namespaces in the model. It doesn't have namespaces in the model, because RDMSs do them very differently, if at all - MySQL, your target, doesn't support them at all. If you're feeling crazy, and like dealing with parsers, you could edit SQL::Translator::Parser::DB2 and change its parser configuration to allow periods in table names. Then you could munge the old table names and replace the periods with underscores or something, and then have SQL::Translator::Producer::MySQL barf out the data definition SQL for you. But changing the parser config is not for the faint-hearted - I am no Parse::RecDescent expert, but maybe you are. Or, pre-process your input SQL file and replace the periods in table names with underscores. That might be simpler, in fact. Or, if you have a running DB2 installation, you could create the old database, then slurp it out using SQL::Translator::Parser::DBI::DB2, which uses a live DBI connection as its source, rather than a SQl file. You may encounter the same difficulty, though. Best of luck!	[reply]
Re: Convert DB2 -> mysql with sql::translator by stonecolddevin (Parson) on Jul 08, 2006 at 04:12 UTC
are you absolutely positive your SQL is valid? post that, maybe something is going on in your SQL code. meh.	[reply]
Re^2: Convert DB2 -> mysql with sql::translator by Anonymous Monk on Jul 08, 2006 at 04:37 UTC
it dies on this line CREATE TABLE "DB2INST1"."TABLE1" (	[reply]
Re^3: Convert DB2 -> mysql with sql::translator by stonecolddevin (Parson) on Jul 08, 2006 at 06:15 UTC
that really doesn't help your cause, as we can't see what's before or after that, so once again, please post the entire SQL script, or the section that the line is in. meh.	[reply]