Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    14

    Unanswered: db2 data copy table question

    Hi, does anyone know of a utility that will allow me to drag and drop tables from a local UNIX db2 9.7 database to a remote database that can be on AIX or iSeries? I have to periodically refresh certain static tables on a client’s database with data from my system. Tables can be as large as 500,000 rows. I do this by using EXPORT to .csv or ixf-> FTP to client site -> IMPORT REPLACE INTO…. I’m trying to find a safe way to allow team members with no UNIX experience to do this. So a GUI interface would be great. I haven’t found a way in Data Studio yet. Thanks

  2. #2
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Quote Originally Posted by john11788 View Post
    Hi, does anyone know of a utility that will allow me to drag and drop tables from a local UNIX db2 9.7 database to a remote database that can be on AIX or iSeries? I have to periodically refresh certain static tables on a client’s database with data from my system. Tables can be as large as 500,000 rows. I do this by using EXPORT to .csv or ixf-> FTP to client site -> IMPORT REPLACE INTO…. I’m trying to find a safe way to allow team members with no UNIX experience to do this. So a GUI interface would be great. I haven’t found a way in Data Studio yet. Thanks
    Even i was searching for the same soln. explored tools like toad, dbvisualiser, data studio etc but no luck. If the tables are fixed, you may try build scripts and make the end users run them.
    ssumit

  3. #3
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    14
    Thanks for the reply ssumit. One thing I tried was adding the client’s database to my catalog and using LOAD FROM CURSOR. This works great and is very fast but it leaves the table in a non-recoverable state or the tablespace in a backup-pending state. That’s not a workable solution for our clients that are active 24/7. If only there were a way to do IMPORT FROM CURSOR….

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by john11788 View Post
    it leaves the table in a non-recoverable state
    That would only happen if the LOAD failed. You could also try using the COPY YES option.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    14
    n_i, that’s a very interesting statement, I was under the impression that even if the LOAD FROM CURSOR…. NONRECOVERABLE completed successfully the table was left in a non-recoverable state, meaning it is usable but would have to be dropped and recreated if the database had to be restored. I have tried the COPY YES but that does leave the tablespace in backup pending state.

  6. #6
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Quote Originally Posted by john11788 View Post
    n_i, that’s a very interesting statement, I was under the impression that even if the LOAD FROM CURSOR…. NONRECOVERABLE completed successfully the table was left in a non-recoverable state, meaning it is usable but would have to be dropped and recreated if the database had to be restored. I have tried the COPY YES but that does leave the tablespace in backup pending state.
    Hi John,

    Can you post the full command you are using to load ? I think command can be reviewed as per your need.

    Also in your scenario "LOAD CLIENT FROM..." might be useful.
    ssumit

  7. #7
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Quote Originally Posted by ssumit View Post
    Hi John,

    Can you post the full command you are using to load ? I think command can be reviewed as per your need.

    Also in your scenario "LOAD CLIENT FROM..." might be useful.
    Hello John,

    You may customise the below command as per need :

    db2 "load client from "E:\sumit_data\data_load_final.txt" of del Modified by COLDEL, Messages "E:\sumit_data\data_load_final_msg.txt" insert into DWH.RAJARAM NONRECOVERABLE DISK_PARALLELISM 2"

    tables and table space state normal after load
    source file is on client system ( where you cataloged the DB).
    Remember to take backup of DB after the load as loaded table data cant be recovered from old backups
    ssumit

Tags for this Thread

Posting Permissions

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