Cannot delete from the database...?

Question

So, I have 2 database instances, one is for development in general, another was copied from development for unit tests.

Something changed in the development database that I can't figure out, and I don't know how to see what is different.

When I try to delete from a particular table, with for example:

delete from myschema.mytable where id = 555

I get the following normal response from the unit test DB indicating no row was deleted:

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

However, the development database fails to delete at all with the following error:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0440N No authorized routine named "=" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

My best guess is there is some trigger or view that was added or changed that is causing the problem, but I have no idea how to go about finding the problem... has anyone had this problem or know how to figure out what the root of the problem is?

(note that this is a DB2 database)

Answer 1

You might have an open transaction on the dev db...that gets me sometimes on SQL Server

Answer 2

Is the type of id compatible with 555? Or has it been changed to a non-integer type?

Alternatively, does the 555 argument somehow go missing (e.g. if you are using JDBC and the prepared statement did not get its arguments set before executing the query)?

Answer 3

@Leigh Caldwell

I am doing this directly in the db2 shell, exactly as I have above, so the arguments can't be going missing.

When I do a describe on the table in the 2 instances, they are exactly the same.

Answer 4

Can you add more to your question? That error sounds like the sql statement parser is very confused about your statement. Can you do a select on that table for the row where id = 555 ?

You could try running a RUNSTATS and REORG TABLE on that table, those are supposed to sort out wonky tables.

Answer 5

@castaway

A select with the same "where" condition works just fine, just not delete. Neither runstats nor reorg table have any affect on the problem.

Answer 6

Hmm, applying the great oracle to this question, I came up with:

http://bytes.com/forum/thread830774.html

It seems to suggest that another table has a foreign key pointing at the problematic one, when that FK on the other table is dropped, the delete should work again. (Presumably you can re-create the foreign key as well)

Does that help any?

Answer 7

@castaway

We actually just solved the problem, and indeed it is just what you said (a coworker found that exact same page too).

The solution was to drop foreign key constraints and re-add them.

Another post on the subject:

http://www.ibm.com/developerworks/forums/thread.jspa?threadID=208277&tstart=-1

Which indicates that the problem is a referential constraint corruption, and is actually, or supposedly anyways, fixed in a later version of db2 V9 (which we are not yet using).

Thanks for the help!

Answer 8

Please check 1. your arguments of triggers, procedure, functions and etc. 2. datatype of arguments.