Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2012
    Posts
    32

    Unanswered: DB2 copy table from a remote server ?

    I want to copy a table named HC_IW from a db2 database which is located remotely on a different server and store it on my db2 locally. I have all the necessary information and have connected to the server from the command line but for some reason I am not able to copy the table and data from the server and paste the table and data on my system locally. Can some one guide me on this table is large and has lot of data will i even have to increase the buffer space of the table locally ?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which DB2 version and OS for each server?

  3. #3
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Always put your DB2 version+fixpack and Operating-System name on each new topic, otherwise we cannot help you.

    Are both source and target databases DB2 ?

    Give the command-lines that you are using, and give the error code/error message.

    You can use different techniques:
    (a) use Export from source, load/import to target
    (b) on the target, use LOAD ...FROM CURSOR...
    (c) federate and use INSERT INTO... SELECT....FROM...

  4. #4
    Join Date
    Jun 2012
    Posts
    32
    Sorry guys.

    The DB2 Version is 9.7.3 and it is on a Linux Operating system on the server . I have the DB2 locally on a windows operating system.DB2 version on my local system is 10.1.0. I hope this information is useful.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use db2look on the remote server to extract the DDL for the table and create the table as needed on the local server.

    Then use LOAD to load the data straight from the remote to the local table. See listing 17 on how to do this:

    Fast and easy data movement using DB2's LOAD FROM CURSOR feature

    Andy

  6. #6
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    Or an export to an ixf file and an import. More than one way to get the result

  7. #7
    Join Date
    Jun 2012
    Posts
    32
    I tried using the following command to export and import rows of data from one table to the other, but can someone explain me how do i copy the structure of a table into another table as the below command does not copy the structure but just the data. And is it ok to use this commands if i have tables which has thousand and thousand of rows it a huge table and i cant lose any data or stop the server from responding as it is located remotely.

    export to temp.ixf of ixf select * from table

    import from C:\temp.ixf of ixf insert into tablename

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by marshell08 View Post
    I tried using the following command to export and import rows of data from one table to the other, but can someone explain me how do i copy the structure of a table into another table as the below command does not copy the structure but just the data. And is it ok to use this commands if i have tables which has thousand and thousand of rows it a huge table and i cant lose any data or stop the server from responding as it is located remotely.

    export to temp.ixf of ixf select * from table

    import from C:\temp.ixf of ixf insert into tablename
    Read the documentation on IMPORT. (Use CREATE INTO).

    Andy

  9. #9
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    You can use a load from cursor. Use the target computer and make sure that the source database is cataloged.Execute:
    Code:
    declare abc cursor database sourcedb user sourceid using sourcepasswd for select * from schema.table1;
    load from abc of cursor insert into schema.table2;
    This was one of the greatest features I learned at the IDUG@prague last year. Have not tested it myself yet so please let us know.
    The credits go to Michael Tiefenbacher.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  10. #10
    Join Date
    Jun 2012
    Posts
    32
    Quote Originally Posted by dr_te_z View Post
    You can use a load from cursor. Use the target computer and make sure that the source database is cataloged.Execute:
    Code:
    declare abc cursor database sourcedb user sourceid using sourcepasswd for select * from schema.table1;
    load from abc of cursor insert into schema.table2;
    This was one of the greatest features I learned at the IDUG@prague last year. Have not tested it myself yet so please let us know.
    The credits go to Michael Tiefenbacher.

    But i want to copy the table into another database I tried your step the first command worked successfully but then i can only copy it into another table of the same database, whereas I want to copy it into a table in a different database.

  11. #11
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    Well, if you have difficulty with that approach try another such as the ixf file I suggested. As numerous people have said, there are various ways of doing this.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by marshell08 View Post
    But i want to copy the table into another database I tried your step the first command worked successfully but then i can only copy it into another table of the same database, whereas I want to copy it into a table in a different database.
    removing my duplicate and incorrect info
    Last edited by db2girl; 06-21-12 at 00:39.

Posting Permissions

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