• 1 reply
  • Latest Post - ‏2013-02-22T21:33:42Z by SystemAdmin
17917 Posts

Pinned topic Import the dat files into Db2 with both table structure and data

‏2013-02-18T03:55:22Z |

I have a default database derby in my application, which consists of the files with the extension .dat. I would like to dump all the files with extension .dat files into Db2 database. How can i pull all the dat files into Db2 keeping the structure and also the data. Will the below command of import brings the whole structure and data into the tables into Db2??

E.g. db2 import from X:\test.dat of del insert into edw.test

Thanks in advance
  • SystemAdmin
    17917 Posts

    Re: Import the dat files into Db2 with both table structure and data

    I don't know anything about Apache Derby, but I will try to answer a part of this question maybe... :

    1) db2 import (at the v9.7 level anyway) supports the following file formats (
    "ASC (non-delimited ASCII format)
    DEL (delimited ASCII format), which is used by a variety of database manager and file manager programs
    WSF (work sheet format), which is used by programs such as:Lotus® 1-2-3®, Lotus Symphony™
    IXF (Integration Exchange Format, PC version) is a binary format that is used exclusively by DB2."

    Unless Derby stores data in, or can export into, one of them, import probably will not work directly.

    2)db2 import needs you to tell it into what table you want it to import data, and that table has to exists BEFORE you import data into it:
    "The database table or hierarchy must exist before data in the ASC, DEL, or WSF file formats can be imported; however, if the table does not already exist, IMPORT CREATE or IMPORT REPLACE_CREATE creates the table when it imports data from a PC/IXF file. For typed tables, IMPORT CREATE can create the type hierarchy and the table hierarchy as well."

    It does not seem, therefore, that you could import the table structure from a Derby .dat file. This, at least, seems to be easily fixed:

    3)Have a look at Derby dblook utility ( It should allow you to extract DDL from your Derby database. Since Derby seems to follow IBM DB2 syntax, you should be able to use the DDL generated by the utility to create a database with the same structure as your Derby DB in DB2. Minor modifications could be required, but probably nothing major.

    That will get you as far as having a DB2 database with tables of the same structure as your Derby database but containing no data. From there you could import the data using the DB2 import command, provided you can get that data out of Derby in one of the supported formats...