Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 migration

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Fri Nov 06, 2009 10:12 am    Post subject: DB2 migration
Reply with quote

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
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Nov 06, 2009 2:20 pm    Post subject:
Reply with quote

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

Code:
 LISTDEF BOBB                                             
              INCLUDE TABLESPACES DATABASE BOBT1         
                                                         
TEMPLATE UNLDDS DSN &USERID..SMPLUNLD.&TS.               
        UNIT SYSDA DISP (NEW,CATLG,CATLG) SPACE (2,1) CYL
                                                         
UNLOAD LIST BOBB                                         
        UNLDDN UNLDDS                                     


For the load you can create JCL PROC and call it. Bit of manual work is required. I don't know if there is any other method.

Thank You,
Sushanth
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Fri Nov 06, 2009 3:23 pm    Post subject:
Reply with quote

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

Can we automate LOAD also in similar way?
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Fri Nov 06, 2009 4:01 pm    Post subject:
Reply with quote

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

Control information means,Table structure and Load control statement.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Nov 06, 2009 4:57 pm    Post subject:
Reply with quote

Mallik,

Yes. You can.

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

Sushanth
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Fri Nov 06, 2009 5:05 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Nov 10, 2009 4:18 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Nov 10, 2009 4:48 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Nov 10, 2009 5:18 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Nov 10, 2009 5:36 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Nov 10, 2009 5:42 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Db2 migration from V4/6 to V8/10 Venkatesh G DB2 1 Thu Jul 14, 2016 8:54 am
No new posts Data Migration from Fujitsu AIM/DB to... umakalikiri DB2 2 Fri Apr 15, 2016 12:26 pm
No new posts Fujitsu Cobol 85 to IBM Cobol (Any ve... umakalikiri COBOL Programming 4 Fri Apr 15, 2016 7:40 am
No new posts Natural/Adabas - Migration/Replatform Virendra Shambharkar Java & MQSeries 5 Tue Apr 05, 2016 2:37 pm
No new posts z/OS V2R2 Migration Impact on Existin... dexter.mozer All Other Mainframe Topics 4 Mon Feb 29, 2016 6:22 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us