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
 

 

data copy functionality

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

New User


Joined: 10 Mar 2006
Posts: 26

PostPosted: Wed Nov 18, 2009 4:04 pm    Post subject: data copy functionality
Reply with quote

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

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 18, 2009 4:36 pm    Post subject:
Reply with quote

I would give crossloading a try : http://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.
Back to top
View user's profile Send private message
kanisha_prabha

New User


Joined: 10 Mar 2006
Posts: 26

PostPosted: Wed Nov 18, 2009 5:31 pm    Post subject: Re: data copy functionality
Reply with quote

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

Thanks for the cross-loading info.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 18, 2009 6:01 pm    Post subject:
Reply with quote

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
Code:
SELECT ...
FROM EMPLOYEE E
WHERE exists ( select 1 FROM PREV_EMPLOYER P WHERE e.EMPNO = p.EMPNO and ...)


Depending on what other fields of prev_employer are in the where clause,
you could add fields to the index making sure you have an index-only access on prev_employer
or have prev_employer clustered on EMPNO

If max 1 prev_employer per employee is possible (considering the extra conditions in the where), then you might just write a join.
Code:
SELECT E.*
FROM EMPLOYEE E
JOIN PREV_EMPLOYER P ON e.EMPNO = p.EMPNO
where P. ...

or
Code:
SELECT E.*
FROM EMPLOYEE E
JOIN PREV_EMPLOYER P ON e.EMPNO = p.EMPNO
                    and P. ...
Back to top
View user's profile Send private message
kanisha_prabha

New User


Joined: 10 Mar 2006
Posts: 26

PostPosted: Wed Nov 18, 2009 6:08 pm    Post subject: Re: data copy functionality
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 18, 2009 6:58 pm    Post subject:
Reply with quote

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

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Nov 18, 2009 7:08 pm    Post subject:
Reply with quote

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

New User


Joined: 30 Sep 2006
Posts: 78
Location: pune

PostPosted: Thu Nov 19, 2009 12:32 am    Post subject: Reply to: data copy functionality
Reply with quote

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

New User


Joined: 10 Mar 2006
Posts: 26

PostPosted: Thu Nov 19, 2009 10:47 am    Post subject: Reply to: data copy functionality
Reply with quote

Hello All,

Thank you for your replies. This helps a lot!

Rgds.
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 copying data without knowing location arunsoods DFSORT/ICETOOL 6 Thu Jul 20, 2017 1:03 pm
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts NDM syntax checking without actually ... GAPX1 All Other Mainframe Topics 0 Wed Jun 07, 2017 2:36 am


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