DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
dnat
Participant



Joined: 06 Sep 2007
Posts: 193

Points: 1788

Post Posted: Tue Nov 21, 2017 12:20 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi,

I have to load a oracle table from a file. There are no keys in the table. In this situation what would be the best approach to load the table with restartability in place..If the job aborts in the middle with some rows inserted, how do i make sure that the same records dont insert into the table again..

I have a log table which has a ID associated with every file. So, I have this ID inserted into the table to identify which file these records are from

These are the options i could think of.
1.Have all the fields has keys and do upsert..But there are so many fields with null values..
2. Use the option of delete then insert(delete all records with the ID field from log table)
3. Have a high commit count..

Anyone faced this issue and what could be the ideal method.
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42217
Location: Denver, CO
Points: 216760

Post Posted: Tue Nov 21, 2017 12:56 pm Reply with quote    Back to top    

What kind of transactional volume are we talking about here?

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
FranklinE



Group memberships:
Premium Members

Joined: 25 Nov 2008
Posts: 623
Location: Malvern, PA
Points: 5893

Post Posted: Tue Nov 21, 2017 1:44 pm Reply with quote    Back to top    

For secure and reliable restartability, full (bulk) load is the first choice. Doesn't matter what DBMS you are using, or the structure of the table.

For example:

Job 1 -- bulk unload for backup/restore if "new" data cannot be fully loaded.

Job 2 -- merge unloaded data with "new" data, doing any updates as delete/inserts on file instead of to table.

Job 3 -- bulk load of merged data.

We use this method for VSAM file updates. For your requirements, it looks like a good choice.

_________________
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: http://www.dsxchange.com/viewtopic.php?t=143596
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54038
Location: Sydney, Australia
Points: 293144

Post Posted: Tue Nov 21, 2017 4:24 pm Reply with quote    Back to top    

Can you perform a DIFFERENCE against your log table as the source for the load?

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours