data copy functionality

kanisha_prabha · New User Joined: 10 Mar 2006 Posts: 26

Hi,

I have around 40 DB2 tables which need to be copied to similar tables in another DB2 database. The following are some details -
1. Some of the tables are quite large with no. of rows around 75 million.
2. Average no. of rows will be 10 million.
3. Some of the tables are quite small with no. of rows around 10K
4. I need a consistent data copy approach.
5. The data copy will be dependent on a specific condition which involves a where clause. For some of the tables, the where clause would include 2 (or 3 max.) tables ... such as -
SELECT ...
FROM EMPLOYEE
WHERE EMPNO IN
(SELECT EMPNO FROM PREV_EMPLOYER WHERE ...)

Given the above scenario, could you let me know which is the best possible approach to take for data copy? Should I stick to UNLOAD-LOAD DB2 utility or are there other tools? Should I also consider writing COBOL programs?

Thanks,
- Kanisha

GuyC · Posted: Wed Nov 18, 2009 4:36 pm

I would give crossloading a try : publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.ugref/db2z_loadusecrossloaderfunction.htm

But make sure your SQL is optimal. The example you gave doesn't seem to be optimal when you have like x millions prev_employee in the in-list.

kanisha_prabha · New User Joined: 10 Mar 2006 Posts: 26

What would be the optimal query if there are say 20M that the subquery can return?

Thanks for the cross-loading info.

GuyC · Posted: Wed Nov 18, 2009 6:01 pm

most likely there is an index on prev_employer(EMPNO)
and/or an index on employee(EMPNO)

It depends on #rows,pages, %rows qualifying of employee ,%rows qualifying of prev_employer, indexes and clustering
which of the following is optimal.

Some access paths will start reading from EMPLOYEE (all rows) and then do the test on prev_employee
others will search on prev_employer and then get all matching employees
other might sort one or two tables and do a merge scan

My guess : this one is the best

kanisha_prabha · New User Joined: 10 Mar 2006 Posts: 26

Thanks! this helps a lot.
Are there any other utilities available - such as Fileaid related copy, etc.?
Just trying to run through all options before I settle with one.

- Kanisha

GuyC · Posted: Wed Nov 18, 2009 6:58 pm

I don't know fileaid and have no experience with BMC utilities.

I'm pretty sure that crossloading is faster then writing cobol programs.

It might be cheaper to copy the tables entirely with DSN1COPY and then do deletes on the other database. This could relieve the CPU on your operational data base. (depends on the number of deletes)

sushanth bobby · Posted: Wed Nov 18, 2009 7:08 pm

Kanisha,

You could also use FASTUNLOAD & FASTLOAD but those utilities come with Platinum, check with your DBA for the available utilities in your shop.

Sushanth

sachin_star3 · New User Joined: 30 Sep 2006 Posts: 78 Location: pune

use they BMC unload and load utility which are very faster.

1.Unload using the BMC:
format like:
UNLOAD LOG NO
SELECT * FROM TABLE1 WHEERE EMP_NAME EXIST (
SELECT 1 FROM TABLE2 WHERE EMP_NO=1233
AND EMP_SALARY='50000')

And create unload file say file1

2.Load using BMC
format like
LOAD ORDER NO
CHECKPENDING NO
SYSREC
TABLE3
(EMP_NAME CHAR (4) (1,4),
EMp_SALARY CHAR(4) (5,4))

if you want exact syntax then let me know i will provide you.

kanisha_prabha · New User Joined: 10 Mar 2006 Posts: 26

Hello All,

Thank you for your replies. This helps a lot!

Rgds.