insert/update with no key

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
dnat
Participant
Posts: 200
Joined: Thu Sep 06, 2007 2:06 am

insert/update with no key

Post by dnat »

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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

What kind of transactional volume are we talking about here?
-craig

"You can never have too many knives" -- Logan Nine Fingers
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

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: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Can you perform a DIFFERENCE against your log table as the source for the load?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Still a wee bit curious how much data we're talking about here, both already in the target and what shows up in your average load...
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply