DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
dnat
Participant



Joined: 06 Sep 2007
Posts: 200

Points: 1865

Post Posted: Wed Jan 24, 2018 11:19 am Reply with quote    Back to top    

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

I am using "Delete then insert" option to load data in target Oracle table. Source is a .csv file. Every file is identified with an id and data needs to be replaced if we get the same file again even with different data.

I have defined the key as the file id and load option as "Delete then insert". So, ideally it should delete all the records in the table with the id and then insert. B

I had a file with around 3000 records and when i ran the job, the log says all 3000 has been inserted, but there were only 400 records in the table. I changed the option to insert only and it inserted 3000 records without any problem.

What could be causing the problem.

With 100 records there are no issues, but the problem started with loading more than 2000 records.
rschirm

Premium Poster



Group memberships:
Premium Members, Inner Circle

Joined: 13 Dec 2002
Posts: 27

Points: 203

Post Posted: Mon Jan 29, 2018 4:43 pm Reply with quote    Back to top    

The Delete then Insert logic works like this.

Row of data comes in it goes to database and deletes records based on the keys specified. Then inserts the current row. Next row of data comes in and goes to database to delete records based on the keys specified and then inserts the row.

If I were to guess I would say that you have approx. 400 rows with unique key values.
Rate this response:  
Not yet rated
dnat
Participant



Joined: 06 Sep 2007
Posts: 200

Points: 1865

Post Posted: Mon Jan 29, 2018 6:10 pm Reply with quote    Back to top    

My data is like this

ID value
1 abcd
1 defg
1 rtrt
1 6767
1 7887

As such there is no key in the table. I am specifiying the ID column as the key column in datastage for delete then insert option.

My assumption was that When I give Delete then insert based on key(ID), it deletes all the records with the ID=key(in this case 1) and insert all the records.

So if it works row by row then the first record would get deleted when it tries to insert the 2nd record. When the data is small around 1000 it works, but with just 3000 records it is having this problem.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42753
Location: Denver, CO
Points: 220316

Post Posted: Mon Jan 29, 2018 10:30 pm Reply with quote    Back to top    

It doesn't work, it just appears to with small volumes and your assumption as to how it would work is incorrect, as noted by Rick. I would suggest two passes through the data or perhaps two targets, controlling which is executed first. First pass or target would get all of the distinct "key" values from the data and delete all existing records in the target with those keys. Then a second pass to insert all of the source records.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
dnat
Participant



Joined: 06 Sep 2007
Posts: 200

Points: 1865

Post Posted: Tue Jan 30, 2018 12:32 pm Reply with quote    Back to top    

Thanks Rick and Craig!!..And looks like the array size is the one responsible for the job working good when record count is low. Since array count was set to 2000, it didnt cause any problem when it processed 1000 records or so. I tested it by increasing it to 5000 and it worked fine if the record count was below 5000.

But anyway, I changed the design to have a before sql statement to delete based on the key and changed the Write mode to "insert new rows"
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42753
Location: Denver, CO
Points: 220316

Post Posted: Tue Jan 30, 2018 1:03 pm Reply with quote    Back to top    

Again, that depends on your definition of "works fine". Wink

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
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