If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Moving data

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Apr 2006
Location: Belgium
Posts: 2,080
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Apr 2006
Location: Belgium
Posts: 2,080
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 ..
__________________
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

Last edited by przytula_guy; 03-10-11 at 08:55.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
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 01:23.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #9 (permalink)  
Old
Super Moderator
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On