Write Mode : Delete and Insert fails

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
jeisma
Participant
Posts: 18
Joined: Mon Mar 20, 2017 12:19 pm

Write Mode : Delete and Insert fails

Post by jeisma »

Hi,

I have a stage where write mode is Delete and Insert. If I run the job, I would get a warning that says the Delete "failed to run", followed by fatal error "QLSTATE = 02000: Native Error Code = 100: Msg = [IBM][CLI Driver][DB2/NT64] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table", eventually the job aborts. Indeed there are no records that match the delete condition. However, if I change the stage to just "Delete", the job would run just fine without the above warning and error.

If there are records that match the Delete condition, the job runs.

I want to conclude that Delete fails because there are no records to delete.(ignoring the fact that, write mode Delete works with no records).

I'm thinking of creating an SQL statement that tests if records exist before but I can't for the life of me figure out the DB2 SQL statement to do that. Any ideas how to construct the statement? or perhaps you have solution how to solve the job problem given the above scenario.


TIA!
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post by rkashyap »

Could there be multiple records with same key values in incoming data?

What are values of 'Auto Commit mode', 'Array size', 'Transaction record count'?

Test after changing array size to 1.
jeisma
Participant
Posts: 18
Joined: Mon Mar 20, 2017 12:19 pm

Post by jeisma »

Hi,

Incoming data have unique key values.

Auto Commit : off
Array Size: 2000
Transaction Record Count : 2000

to test, changed to:
On
1
2000

it worked! thank you. Tried different numbers for Array Size and Record Count, all tests failed except only when Array Size is 1. It's running painfully slow however. :(
Post Reply