Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    5

    Unanswered: Copy queried data from production data base to a different data base

    I am running DB2 and am working on some testing where I have to copy some production data (a completely different machine) to a developement/qa machine. The table structure is identical. Currently I have to run the queries and "export" using DBVisualizer into the receiving database. Is there a way to run the query and on the host database and insert into the receiving database?

    Here are the steps I have to follow.

    First I have to clear out the receiving database of all "old" data:

    DELETE FROM EIFA.CURRENCY_INFO;
    DELETE FROM EIFA.CURRENCY_HIST;
    DELETE FROM CLHDB.CLH_INFO

    Then I have to select all of the data from the host database:

    SELECT * FROM EIFA.CURRENCY_INFO;
    SELECT * FROM EIFA.CURRENCY_HIST WHERE DATE_UPDATED > '20080101';
    SELECT * FROM CLHDB.CLH_INFO WHERE GROUP <> ''

    The result (using DBVisualizer) is three tabs worth of data that I have to export into sql and then paste into the sql editor and run.

    The host url is as follows: chq.chq.ei:50000/corp
    The recipient url is as follows: qa4.chq.ei:50000/corp

    Is there a way to tie all of this together to have it run in one seamless execution?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    On Production machine:

    db2 "export to EIFA.CURRENCY_INFO.IXF OF IXF SELECT * FROM EIFA.CURRENCY_INFO"
    db2 "export to EIFA.CURRENCY_HIST.IXF OF IXF SELECT * FROM EIFA.CURRENCY_HIST WHERE DATE_UPDATED > '20080101'"
    db2 "export to CLHDB.CLH_INFO .IXF OF IXF SELECT * FROM CLHDB.CLH_INFO WHERE GROUP <> ''"

    scp EIFA.CURRENCY_INFO.IXF userid@hostname:/directory_path
    scp EIFA.CURRENCY_HIST.IXF userid@hostname:/directory_path
    scp CLHDB.CLH_INFO.IXF userid@hostname:/directory_path

    On Development machine:
    # do these in correct order to satisfy referential integrity

    db2 "import from EIFA.CURRENCY_INFO.IXF OF IXF COMMITCOUNT 1000 REPLACE INTO EIFA.CURRENCY_INFO"
    db2 "import from EIFA.CURRENCY_HIST.IXF OF IXF COMMITCOUNT 1000 REPLACE INTO EIFA.CURRENCY_HIST"
    db2 "import from CLHDB.CLH_INFO.IXF OF IXF COMMITCOUNT 1000 REPLACE INTO CLHDB.CLH_INFO"
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2008
    Posts
    5
    Can you explain what each of the three blocks of commands is doing?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The first block exports the data to a file.
    The second block sends the files to another server (I used scp, but you might be able to use ftp, etc).
    The third block imports the data to a table, replacing all the existing data.

    If you still cannot understand it, you need to find a local DBA to help you on this.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2008
    Posts
    5
    I understand now. I guess my original question what is there a way to do all of this using sql and not in a shell?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In theory you could do it with SQL if you set up federated support which allows one SQL statement to access two different databases. But the way that I showed you is less trouble, safer from a security standpoint, performs much better, and is relatively straightforward.

    If you cannot understand the DB2 and shell commands used in the script I gave you, chances are you will not be able to understand DB2 federated support. Note that you will need a DBA to configure DB2 ferderated support.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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