SQLState 02000 No row was found for FETCH, UPDATE, or DELETE
I'm running jobs through Datastage with the DELETE then INSERT connector. I'm having several jobs failing for this error:
DB2_Connector: DB2 reported: SQLSTATE = 02000 Native Error Code = 100, Msg = IBM[CLIDriver][DB2/NT64] SQL01000W No row was found for FETCH, UPDATE, or DELETE
When I run the delete statement in Data Studio directly in DB2, it gives this same error so I know it's a DB2 error, not a Datastage error.
Is there anyway to supress the message in Datastage or when I run the statement in DB2 is there anyway I can keep that message from coming up? It's stopping my DS jobs now with a Fatal error and not continuing to load.
There has got to be a way to turn off the message. I know in SQL Server if no rows are found it does not give this error, it just says zero or doesn't return records but in DB2 this error is coming up and I'm not sure if there is a way to turn it off.
Answer
First of all, you seem to be confused about precisely what an error is, and what a message is.
An error is when something goes wrong.
A message is when some piece of software is kind enough to let you know that something went wrong.
From this it follows that suppressing a message has no bearing whatsoever on the actual error. Your software is not failing because of the message, your software is failing because something is going wrong. Receiving a message about it is actually a good thing: the alternative would be your software failing without you being given any clue whatsoever as to what is going wrong.
Suppressing or otherwise ignoring errors is like hiding your head in the sand: you are still going to end up as meal.
So, what you need to make go away is the error, not the message.
Which means that you have to figure out what you did wrong.
Luckily, you have the message giving you a hint as to what you did wrong, though you have to keep in mind that messages are sometimes misleading.
SQLState 02000 is not an error, it is a warning. (And note that DB2_Connector is not saying ERROR!!!1!:, it is saying DB2 reported:.) Luckily JDBC issues warnings when it detects situations that might be indicative of errors; there is a lot of software out there that ignores JDBC warnings, (essentially hiding your head in the sand for you, how nice,) but luckily DB2_Connector reports them.
What this means is that one of two things is going wrong:
Either your assumption that it is okay if no rows are found is wrong, and the fact that no rows were found is the cause of your problem, which means that you have to somehow make sure that some rows are found, or
Your assumption that it is okay if no rows are found is correct, in which case the warning reported has absolutely nothing to do with the problem at hand, so it can safely be ignored, and you have to look at the problem elsewhere.