Results 1 to 9 of 9

Thread: Moving data

  1. #1
    Join Date
    Oct 2007
    Posts
    224

    Moving data

    hi

    Moving large data (number of tables) from one server to another server. Different database, db2 move or declare cursor would be the best option.

    Catalog the source db in target server and we can use this option ?

    declare c1 cursor database dbname user username password pwd
    select * from tabname
    load from c1 of cursor insert into tabname nonrecoverable.

    regds
    Paul

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,295

    cursor

    you can not connect to server-a and read data from server-b
    you need to setup federated (free for db2 source)
    and work on nicknames..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Oct 2007
    Posts
    224
    hello sir,

    if for example server a and server b,
    server a is cataloged in server b in that case can we use this
    declare c1 cursor database dbname user username password pwd
    select * from tabname
    load from c1 of cursor insert into tabname nonrecoverable.

    Coz doc says this.

    Ease of use
    There is no need to enable federation, define a remote datasource, or declare a nickname. Specifying the DATABASE option (and the USER and USING options if necessary) is all that is required. While this method can be used with cataloged databases, the use of nicknames provides a robust facility for fetching from various data sources which cannot simply be cataloged. To support this remotefetch functionality, the load utility makes use of infrastructure which supports the SOURCEUSEREXIT facility. The load utility spawns a process which executes as an application to manage the connection to the source database and perform the fetch. This application is associated with its own transaction and is not associated with the transaction under which the load utility is running.

    regds
    Paul

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,295
    if the doc indicates this = ok
    I can see it is documented in detail in the datamovement guide.....
    so what was your question about ...or just an indication that you have read the book and found something we did not know about ? to check us out ..
    Last edited by przytula_guy; 03-10-11 at 09:55.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Oct 2007
    Posts
    224
    no sir,

    actually there is upcoming work related to migration of windows to aix the db2, so was checking out all possibility of data movement, Currently i don't have any dev server to check this, just went thru the doc and wanted know any one has used this way too.

    So just posted what the doc was saying.

    regds
    Paul
    Last edited by Mathew_paul; 03-11-11 at 02:23.

  6. #6
    Join Date
    Jan 2010
    Posts
    335
    Hi Paul,

    yes, both ways are possible. I used both methods to replicate data from prod to test-environment. For the "declare cursor" you need to configure federation first, but it's a lower consumption of resources.

  7. #7
    Join Date
    Oct 2007
    Posts
    224
    hi nvk,

    thks for your reply, actually the discussion was for the later option in which if ur db is cataloged in another db2 server, there is no need to configure federation i believe you can use.
    declare c1 cursor database dbname user username password pwd
    select * from tabname
    load from c1 of cursor insert into tabname nonrecoverable.
    not sure coz i have not chked this.
    regds
    Paul

  8. #8
    Join Date
    Jan 2010
    Posts
    335
    Hi Paul,

    it seems to be there, but it's not documented. There's an option in the LOAD using ADMIN_CMD.
    LOAD using ADMIN_CMD

    I've never worked with it.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    Tried the same from command line, and it works
    Code:
    db2 connect to sample
    db2 create table db2inst1.tables like syscat.tables
    db2 declare c1 cursor database samp2 user db2inst1 using db2inst1 for select * from syscat.tables
    db2 load from c1 of cursor insert into db2inst1.tables nonrecoverable 
    db2 terminate
    db2 connect to sample 
    db2 "select count(*) as rowsct from db2inst1.tables"
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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