I am converting an application to use DB2/CLI (v9.1) on solaris. I am experiencing a very few, random SQL0504N errors when running through a regression pack.

I have stepped through the application and observed this behaviour first hand. This is roughly what I observe: -

- the application uses "UPDATE ... WHERE CURRENT OF ... " techniques
- the application may create and free lots of statement handles, with around 100 being in existence at any one time
- SQLPrepare has been executed for a statement (albeit maybe a while ago)
- an appropriate SQLExecute/SQLFetch has occurred for the prepared statement, both succeeding and returning data
- SQLGetCursorName on the "SELECT .. FOR UPDATE" statement succeeds and a cursor name is returned
- SQLExecDirect ("WHERE CURRENT OF ..") fails with SQL0504N
- there have been no calls to close/free statement on the cursor in question

Has anyone else experienced this? I'm trying to determine whether the problem lies in application code, or DB2.

Does anyone have any good approaches with which to investigate more fully? I'm currently using a debugger, but would be interested if theres any way to check the statement cache in DB2 to find out cursor names in existence, current state etc.

Any help would be appreciated.

Has a commit been issued and the cursor is not defined as with hold?

Dave

The connection is set to be autocommit on. It is also set to be SQL_CURSOR_HOLD_ON as per SQLSetConnectAttr().

There is no other activity on the connection between the call on SQLGetCursorName and the SQLExecDirect, so the cursor name should be valid. Alternatively, if the select cursor were unpositioned or closed, I would expect a different error rather than 0504.

Does any of this sound suspect?

An interesting update. The rough pseudocode of current tests that I am executing: -

- Alloc Cursor 1
- SQLSetCursorName("xxx_CUR_1")
- Prepare cursor xxx_CUR_1 (SELECT .. FOR UPDATE)
- Alloc Cursor 2
- SQLExecDirect("UPDATE .. WHERE CURRENT OF xxx_CUR_1")
error message = SQL0504N The cursor "SQL_CURSH201C26" is not defined.
- SQLGetCursorName() on cursor 1, result is SQL_SUCCESS and name = "xxx_CUR_1"

It looks very strange that DB2 is referring to a cursor name that shouldnt exist. Especially as there is no reference to it in the UPDATE clause. Also, SQLGetCursorName() returns the correct cursor name.

I cannot see why this is failing. It suggests to me that the problem lies in DB2.

It looks like the problem is that DB2 defines packages behind the scenes when using the CLI (small/medium/large). What was happening was that the cursors were being moved from one package to another (this happens automatically) due to space/memory restrictions in the packages. When this happens, any attempt to perform a positioned update/delete can result in a SQL0504 error if it has been moved.

At this point, I'm not sure whether IBM consider this a defect or works as intended. In my mind, I would say this is a defect as the error returned is not indicative of the problem, and the CLI usage is correct. However, there is a workaround...

To avoid this problem, you need to set the following in your db2cli.ini

[COMMON]
ONLYUSEBIGPACKAGES=1