Loading db2 tables from another database

Data movement is a common task that app dev and dbas perform often. DB2 has multiple options to mass add data to tables : IMPORT, LOAD and DB2MOVE.  Each of them have its own pros and cons.  If you are loading lots of data and you need it done fast, but can compromise on availability then LOAD is your best bet. The load command in DB2 LUW is loaded with different options. I am sure you have loaded ixf and del files to db2 tables.  What if you want to make a copy of a table on the same database ?  The traditional method would be to create a new table like the source table, then export the data in del or ixf format and then load the data using the LOAD command. A newer approach would be to use the cursor option in the load command . Create table, declare a cursor for the source table and load the target table using the cursor. Eg :

declare c1 cursor select * from ds1.sales;
load from c1 of cursor messages load_sales.msg insert into ds1.sales_stage;

So what if you have to load data from another database (same server or different server) ?  First you need to catalog the remote database on the server and then you can declare a cursor for a remote database and then load using that cursor. Eg:
Assuming you have cataloged the source database on the target server as srcdb

connect to targetdb;
declare c1 cursor database srcdb user user1 using secretpass for select * from ps1.sales;
load from c1 of cursor messages load_sales.msg insert into ds1.sales_stage nonrecoverable;

This will load the data on table ps1.sales from the srcdb to ds1.sales_stage on the targetdb eliminating a file extract and then a load from the file.

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in db2, how to and tagged , , , , , , , , , , . Bookmark the permalink.