Update only job fails if no rows found to update

[DWH-M]

Hi,
We have an Update job, which uses DB2 connector stage to update the DB2 table. The job finishes successfully if there are rows to update. But it fails, if there are no rows found to update with the below error message.

DB2_Tgt_Opty_Brand_Updt,0: SQLExecute reported: SQLSTATE = 02000: Native Error Code = 100: Msg = [IBM][CLI Driver][DB2/LINUXZ64] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 (CC_DB2DBStatement::processRowStatusArray, file CC_DB2DBStatement.cpp, line 2,453)

Before throwing this fatal error, there is a warning in the log file as below,

DB2_Tgt_Opty_Brand_Updt,0: Statement update RMTETL.opty_brand
set rep_pv = quoted_value
where rep_pv is null
and opty_key in
(select distinct op.opty_key
from RMTETL.v_opty_slim op inner join RMTETL.v_opty_brand br on br.opty_key = op.opty_key
where op.rep_key = 'C-DV0F897'
and coalesce(br.rep_pv ,0) =0
and br.quoted_value > 0
and fiscal_month_key in (select distinct EXPIRNG_FISCAL_MTH_DIMNSN_ID from RMTODS.RMT_OPPRTNTY_MSTR mstr)
) failed to run.

The update command runs successfully if there are rows to update. So there is no issue with the update command.
Is there a way to suppress this error message or make the job to complete even if there are no rows to update ? Should we make any project level change in settings? Please let me know.

[jerome_rajan]

Surprised that you get this in DS9. Remember bumping into a similar issue with either the SP stage or the Oracle connector a year back on DS8. A fix pack solved the problem

[DWH-M]

Oh. Do you have more details on the fix pack ? Can you please share it ?
Because, we also see that the Update Commands are failing randomly in many jobs. Example of error message shown below,

DB2_Tgt_Opty_Brand_Updt,0: Statement UPDATE RMT.OPTY_BRAND SET SYS_MODIFIED_BY=?,ACV_EXPIRED=?,ACV_RENEWED=?,QUOTED_VALUE=?,SRP_EXPIRED=?,SRP_RENEWED=?,ASP_EXPIRED=?,ASP_RENEWED=?,SYS_MODIFIED_DT=? WHERE BRAND_CD=? AND OPTY_KEY=? failed to run.

This job completes successfully in 1 environment. But fails in another environment/DB.

[chulett]

If you search, there are 22 other posts with that same 'no rows found' DB2 message in them. Perhaps this one helps? viewtopic.php?t=124265