Warnings for Update empty table (db2)

[dohertys]

I have a job which tries to update rows on a table.

If these rows don't exist on the table, then under some conditions I get warnings and under other conditions I get no warnings.

Does anyone know why, or what the rules are for this behaviour?


It seems that if I use 'Generated SQL' (update existing rows only) I will always get the warning.

DB2_UDB_API_5: [IBM][CLI Driver][DB2/AIX64] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

If I use 'User-defined SQL' (with exactly the same SQL statement as the generated sql), then warnings are not shown if the number of records processed is an exact multiple of the array size on the db2 stage or if the number of records processed is 1.

e.g. If I process 40 rows, and the array size is 1,2,4,10,20 then it'll give no warnings. If I have any other array size then it will give a warning.


I've only tested this on files where all the rows are not found in the table, so I don't know what affect it would have if some rows where found and some not.


Is there a reliable rule for this behaviour? If there is a reliable rule, then I can see that it might be useful in certain situations.

Is this behaviour something that is likely to continue to work as we move to new versions of datastage?

Is this a potential problem if some jobs rely on getting the warning?

[venkat n]

Supress the warnings thru datastage manager. thats the best way i feel

I have a job which tries to update rows on a table.

If these rows don't exist on the table, then under some conditions I get warnings and under other conditions I get no warnings.

Does anyone know why, or what the rules are for this behaviour?


It seems that if I use 'Generated SQL' (update existing rows only) I will always get the warning.

DB2_UDB_API_5: [IBM][CLI Driver][DB2/AIX64] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

If I use 'User-defined SQL' (with exactly the same SQL statement as the generated sql), then warnings are not shown if the number of records processed is an exact multiple of the array size on the db2 stage or if the number of records processed is 1.

e.g. If I process 40 rows, and the array size is 1,2,4,10,20 then it'll give no warnings. If I have any other array size then it will give a warning.


I've only tested this on files where all the rows are not found in the table, so I don't know what affect it would have if some rows where found and some not.


Is there a reliable rule for this behaviour? If there is a reliable rule, then I can see that it might be useful in certain situations.

Is this behaviour something that is likely to continue to work as we move to new versions of datastage?

Is this a potential problem if some jobs rely on getting the warning?

[ray.wurlod]

<rant>That is really poor advice, particularly from someone from IBM. The poster seeks technical knowledge as to a cause; you are advocating suppressing a symptom. </rant>

Have you (dohertys) tested the same conditions with user-defined SQL that is identical to the generated SQL? Clearly the interaction with array size has something to do with it, but there's no documentation (in the public domain, at least) about how this works.

[dohertys]

Ray, thanks for the reply. Sorry for the delay, I've had to wait for a premium member to tell me what it said.

The Generated SQL seems to give me the warnings all the time, where as the User Defined SQL gives warnings depending on the array size and number of records processed.

I guess that if this isn't documented it's probably not safe for me to rely on it working consistently.

For the moment I'll either do an extra lookup to check if the row exists, or I'll set the array size to 1.

Any other ideas welcome.