Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: UDB\DB2 V9 export a table to another DB

    Can this be done??????
    I know how to do it in SQL Server (DTS) but I don't see any kind of import/export function like this in DB2. I tried to right click on the table and choose export.. but I keep getting an empty file?????

    I have 2 DBs and the table exists in both.. just want to move data from one to another..

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    there is an export utility in db2. export adn then import/load.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Dec 2007
    Posts
    288
    Thanks. I see import/export/load when I right click on the table, I just thought I would be able to import from one table to another (different databases) but I guess I can't do that?

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    you can if you set up federated links. if you do that you can load from cursor which might be faster and you wont be touching data which is always a good thing
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Dec 2007
    Posts
    288
    excuise my ignorance but " load from cursor" ?

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Get the manual SQL Reference, Vol 2

    Syntax:  DECLARE cursor-name CURSOR WITH HOLD  TO CALLER WITH RETURN TO CLIENT FOR select-statement statement-name 

    Then you can load from Cursor. Difference if you do not need export data. instead you are reading it directly from the table. Minimizing the chance of having problems with special charachters and screwing up data.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by itsonlyme44
    Thanks. I see import/export/load when I right click on the table, I just thought I would be able to import from one table to another (different databases) but I guess I can't do that?
    It is a lot easier to export to a file, and then import the file to another database. If you use IXF format and REPLACE_CREATE option, then the new table will be automatically created for you and the data will be loaded.

    If you moving a lot of tables, then the db2move utility can be used.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    easier is debatable Mine main concern with export is you are actually touching the data and taking a chance that something is going to get corrupted or truncated.

    Don't get me wrong, I have used export/import millions of times and still find a time to use, but for one table operation, as in this case, I think CURSOR is the safest option.

    Both will work. There is no right or wrong here.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Cougar8000
    easier is debatable Mine main concern with export is you are actually touching the data and taking a chance that something is going to get corrupted or truncated.
    If you export to a file using something other than IXF format, then I can understand the concern. But with IXF format I don't understand how the data could be corrupted without the load/import blowing up.

    The only exception is with a LOB column, which must be exported to a separate LOB file if any of the LOB column lengths are greater than 32K. If you use dbmove, the LOB will be exported to a separate LOB file by default.

    One big advantage in using an IMPORT is that you can use the COMMITCOUNT parm to do intermediate COMMITs without worrying about filling up the active log. Using the LOAD only uses minimal logging.

    Another way to ensure the integrity of the export file is to compress the file on the source server, and then uncompress it on the target server. When the uncompress is done that will verify that the file was transferred correctly without corruption.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    IXF is a good file format for exports, unless you are using partitioned tablespaces, then it cannot be used - at least not in V8.2 FP15.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •