DB2 migration

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

Hi,

We got a DATA REFRESH requirement. What does that mean is, we have to copy the data from production environment to development environment.

I know that we can achieve this using UNLOAD and LOAD utilities. But the problem here is, we have around 600 tables to copy. So to complete this activity it will take huge time.

Could you please let me know is there any way to copy entire database from one environment to other instead of doing UNLOAD/LOAD for each table.

Thanks in advance :)

Mallik

sushanth bobby · Posted: Fri Nov 06, 2009 2:20 pm

Mallik,

For the unloading part you can do in a utility combination like LISTDEF, TEMPLATE & UNLOAD.
ex:- this will unload all the tablespace in BOBT1 database

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

Thanks for the quick reply Sushanth.
UNLOAD is working for me.

Can we automate LOAD also in similar way?

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

Sushanth, Can we get control information also for the UNLOADing tables inthe above process.

Control information means,Table structure and Load control statement.

sushanth bobby · Posted: Fri Nov 06, 2009 4:57 pm

Mallik,

Yes. You can.

For that you can refer
DB2 V9 Utility Reference Manual --> UNLOAD --> Example 8

Sushanth

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

Thanks Sushanth,
I got the control card to get the SYSPUNCH with UNLOAD

FYI:
LISTDEF EXAMPLE1
INCLUDE TABLESPACES DATABASE MYDATABASE
TEMPLATE UNLDDS DSN &USERID..SMPLUNLD.&TS.
UNIT SYSDA DISP (OLD,CATLG,CATLG) SPACE (2,1) CYL
TEMPLATE PCHDDS DSN &USERID..SMPLPNCH.&TS.
UNIT SYSDA DISP (NEW,CATLG,CATLG) SPACE (2,1) CYL
UNLOAD LIST EXAMPLE1
UNLDDN UNLDDS
PUNCHDDN PCHDDS

GuyC · Posted: Tue Nov 10, 2009 4:18 pm

IF your tables have the same structure, same number of partitions,..

You can have a look at DSN1COPY which is just a smart idcams.
No need to go thru the unloading,reloading.

sushanth bobby · Posted: Tue Nov 10, 2009 4:48 pm

Hi GuyC,

Neat. But, i have not tried it.

I just have one question.
Does it by any chance reduce time ? If you have tried let us know.

Thank You Very Much,
Sushanth

GuyC · Posted: Tue Nov 10, 2009 5:18 pm

It saves you the unload into a temp file and the implicit reorg that a load would do.

I don't have exact statistics, but I know we have several DSN1COPY jobs running daily to copy tables from operational to Information warehouse. But not under my responsability, so I don't know the details.

Another thing is crossloading, but i doubt this will be faster

sushanth bobby · Posted: Tue Nov 10, 2009 5:36 pm

GuyC,

In our shop, CROSSLOADER is faster if there are lesser number of rows or if we restrict specific rows for testing.

One more thing, just thought of it. I think to use DSN1COPY you must have access to DB2 datasets.

Thank You,
Sushanth

GuyC · Posted: Tue Nov 10, 2009 5:42 pm

Access to the DB2 VSAMs is necessary, but I assumed (never thought about it) that you need access to the VSAM also when you access the table through normal SQL or LOAD/UNLOAD.