DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501

Question

When I used JDBC to execute a prepared statement as following:

select count(1) from TableName where col1 = 9 and col2 = ?

it ocurred a DB2 SQL Error:

SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURSH200C1, DRIVER=3.64.104.

what's more, this problem doesn't come out everytime,sometimes it can query successfully.

Could anyone tell me the reason for the problem, thanks very much!

PS:DB2 version is 9.5

Answer 1

Here's a link to the DB2 error codes, so you can look up your next error yourself.

DB2 thinks that your select is using a cursor. Try

select count(*) from TableName where col1 = 9 and col2 = ?

and see if the error goes away.

Here's the full explanation of the error from the IBM DB2 Error code manual.

-514

THE CURSOR cursor-name IS NOT IN A PREPARED STATE

Explanation

The application program has tried to use a cursor, 'cursor-name,' that is not in a prepared state. The cursor is associated with a statement that:

1. Was never prepared.
2. Was invalidated by a commit or rollback operations

System action

The statement cannot be processed. Programmer response

For case 1, ensure that you prepare the statement that is named in the DECLARE CURSOR statement for 'cursor-name' before you try to open the cursor.

For case 2, take one of the following actions:

• Use the WITH HOLD option of DECLARE CURSOR.
• Do not execute a commit or rollback operation until you are finished using the cursor.
• Prepare the statement again after the commit or rollback.