It happens on job that creating/dropping tables on a database (even an instance for developing) could take a long time due to bureaucratic procedures: send email with required table to create, wait until approval, wait until creation. Populate the table do the test and if something is wrong send a mail for the table dropping and repeat the cycle.
In order to exit this hypnotic loop, I have a MySQL on my computer and I use it for every test. Then when I’m sure of the table structure send the email and wait.
But what when you have to create a table that you have to test in join with other tables? The main problem should be to get a script for creating the table and the data for populating on the MySQL.
Getting the create table script
In order to get the create table script from the DB2 I use the client I’m used to use for accessing database: eclipse + SQLExplorer. Select the table you want the create script to be created in the database structure window and with a right click use the create table script. This will open you a new SQL editor with the create table script for DB2. Now fix it in order to accomplish the MySQL syntax. Actually I had to remove the Db2 owner, the double quotes from the table name and replace the double single quotes with a single single quote (script adjustment). Now run the tuned script on the instance of MySQL (always from SQLExplorer).
Getting the data
Now it’s time to get the data from DB2 table in order to pass it to the MySQL. With a simple
select * from owner.table
ensuring to uncheck the limit rows option of SQLExplorer, you’ll get in the SQLResult window a table with all the data. Right click on a cell, and choose the export->csv. This will generate a text file with all the fields separated by a semicolon (;). Let’s fix the file in order to get it digestible to MySQL; from command line
$ sed 's/;/\t/g' exported.csv > exported.txt
This will replace all the semicolons to a tab character.
Import data into MySQL
Now with a mysql access via command line, with root user (or with enough privileges) use the following command
load data infile 'exported.txt' into table the_table_you_created;
That’s all folks.