Importing Data from remote database into a local database.

Discussions

General J2EE: Importing Data from remote database into a local database.

  1. I have a requirement of importing data from a remote DB2 database (on AS400) into a local MySQL database.

    I am thinking of establishing different connection to DB2 and MySQL database. And execute select statement on DB2. Then loop through the resultset, inserting the selected rows into MySQL one by one.

    Is there any better/efficient way of doing this?

    Thanks,
    Manas.
  2. I would suggest:

    (a) 1 select from DB2, and then caching the data, either in memory, in an XML file, or in a comma-delimited file.

    (b) Perform each insert into MySQL.

    This is a teensy bit more effecient, because you only communicate with one database at a time, and should work well unless you have a huge number of rows (in which case you can chunk the select up).

    If you need to sync regularly between the two databases, see if you can get timestamps for each row in DB2 (maybe with an extra field and an insert/update trigger), so that your synchronizations can be limited to new data only.

    If you need something sophisticated, you might look for a synchronization product. A Google search brought up the following:

    http://www.relexus.com/products/data_sync/index.shtml

    http://www.hitsw.com/products_services/dbmoto/dbmoto.html
  3. Hi,
    If you must do this DB export/import once I would also suggest export in CSV, import from CSV. I know mySql has an CSV import tool. I am not sure if DB2 has one. However, but I am sure you cand find a third party tool that exports in CSV the results of a query.
    Although the CSV export/import might sound easy, you have to be carefull about the order of records you are exporting so that you don't break constraints.
    For example you must import first the master tables that have foreign keys in other tables. For foreign keys refferencing the same table you must provide an apropriate ORDER BY clause to the SQL query. You also must be carefull not to import the autoincrement columns.
    A more radical solution would be to drop all your constriants, import the data (without any ordering strategy) and then put back the constraints.

    Best regards, Mircea