Update Problem

shiva459 · Post by **shiva459** » Fri Jun 06, 2003 8:48 am

Hi All
I have two jobs to load a table.The first job loads the data from master to the target.The second job updates the data from child to the target.But when the second job finds no data to be updated it gets aborted with the error .
[DataStage][SQL Client][ODBC][IBM][CLI Driver][DB2/6000] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

Does the datastage job gets aborted if it does not find anything to update?If this is the case, then how should I implement the update strategy?

Regards
Shiva

Shivakumar

dtsipe · Post by **dtsipe** » Fri Jun 06, 2003 9:55 am

Hi,

I anderstood that you work with DB2.
What plug do you use for update ?

ray.wurlod · Post by **ray.wurlod** » Fri Jun 06, 2003 7:22 pm

I'd be reporting that as a bug. DataStage should be able to cope with processing zero rows. It certainly shouldn't abort the job. As a workaround, perhaps you could try performing the SELECT using another stage type - ODBC perhaps?

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

shiva459 · Post by **shiva459** » Sat Jun 07, 2003 1:07 am

Hi
I am using ODBC stage to perform update
Regards
Shiva

Shivakumar

alex · Post by **alex** » Sat Jun 07, 2003 8:49 am

Shiva,
When you design second job, before you update, you should lookup to with key column. If you found the key column you can update, othewise you can't. Try that.

All the best,
Alex

ray.wurlod · Post by **ray.wurlod** » Sat Jun 07, 2003 5:39 pm

Good plan. It's more efficient to identify updateable rows before trying to update them. DB2 does not allow you to update non-existent rows (this is true of most databases).
What is your "update rule" - update existing rows only? A workaround would be to change this to "update existing rows or insert new rows" but this won't run as fast, and may not be what you want.