Hi there,

I've run into an issue, and wondering whether anyone out there has tackled this before/any ideas?

- I am writing an app in C++ using the ODBC/CLI on solaris.
- I have a database connection that is using autocommit.
- I have a number of statements/cursors on the connection.
- Updates/Deletes occur on the cursors as per the positioned method (WHERE CURRENT OF)
- There is no natural commit point/synchronization (hence the autocommit)
- The app is dynamic in nature and can query a lot of tables in a lot of different ways (using select/where clauses etc)
- A number of cursors will be active at any given time
- There is no order to cursor usage (either which tables, or selects or updates)
- For performance reasons, I use SQLPrepare and cache the cursor, reusing it where possible.
- For scalability, I retire old cursors that aren't in use and not used very much every so often.

I have run into a problem. When closing a cursor (SQLCloseCursor) in autocommit mode, it causes all cursors to become unpositioned. If I then want to perform an "UPDATE ... WHERE CURRENT OF ..." on one of the cursors, I get the error "SQL0508N The cursor specificed in the UPDATE or DELETE statement is not positioned on a row. SQLSTATE=24504"

The SQLCloseCursor caused the commit, which caused all cursors to become unpositioned.

Is it possible to reposition/refresh the cursor so a positioned UPDATE can occur? I am using solaris and use the following statement settings (below). I have to use a forward only cursor, as the true dynamic cursor is only available on AIX (unfortunate that), which means I cant use SQLSetPos.

[cursor settings]
SQL_ATTR_CONCURRENCY=SQL_CONCUR_LOCK
SQL_ATTR_CURSOR_SCROLLABLE=SQL_SCROLLABLE
SQL_ATTR_CURSOR_TYPE=SQL_CURSOR_FORWARD_ONLY

Documentation refers to the use of SQLFetch(), but this reads in the next row, and it is no longer possible to perform the UPDATE on the previous row.

I could detect a SQL0508N error and reissue the SQLExecute(), followed by n SQLFetch() operations, but...
- this is costly (especially as it could happen many times)
- reissuing SQLExecute means that the result set may have changed (read consistency)
- this is manual, and error prone (ie previous result set was 5, now its 6 because a record had been inserted by another app)

PATCH1=4096 (DB2 Database for Linux, UNIX, and Windows) allows the CLI to not send the COMMIT during the SQLFreeStmt() call, but I dont think this buys me anything as I have no idea when I'm safe to issue a commit otherwise.

Any help or thoughts would be much appreciated?

rgds,
Jason

------------
[OS = Solaris 10 Update 5]
> db2level
DB21085I Instance "dbdevar1" uses "64" bits and DB2 code release "SQL09015"
with level identifier "01060107".
Informational tokens are "DB2 v9.1.0.5", "s080512", "U815923", and Fix Pack
"5".
Product is installed at "/opt/IBM/db2/V9.1".

> db2licm -l
Product name: "DB2 Workgroup Server Edition"
License type: "CPU"
Expiry date: "Permanent"
Product identifier: "db2wse"
Version information: "9.1"
Max number of CPUs: "4"
Max amount of memory (GB): "16"
Annotation: "3;(_c)"
Features:
DB2 Performance Optimization WSE: "Not licensed"
DB2 High Availability WSE: "Not licensed"
DB2 Workload Management WSE: "Not licensed"
DB2 pureXML WSE: "Not licensed"
IBM Homogeneous Federation WSE: "Not licensed"

Whenever a commit occurs (whether auto-commit or not doesn't matter), all cursors of the current transaction are closed, unless you use holdable cursors. Holdable cursors are indeed unpositioned after the commit because all row-level locks are released. And while a cursor is positioned on a row, it must at least be S-locked to prevent concurrent updates on the row, even with isolation level CS. So the behavior you observe is correct and expected. Also, unpositioning does not only happen on cursor close but on every SQL statement that you execute.

My suggestion would be that you revise your design, and
(1) turn auto-commit off and commit explicitly
(2) turn holdable cursors off because those require more resources on the DBMS
(this are the two most-problematic default configurations for JDBC if you ask me).

Thanks for the info.

Unfortunately, I am restricted as I am attempting to port a very large application to DB2. (We have a mixture of begin/commit for a number of tables that are part of transaction protection, and a number of different tables are autocommited).

I believe I have a workaround, which is to create a separate connection for each table access that requires autocommit (per thread). This may result in more connections than I would prefer, but it should be feasible and hopefully will provide the compliancy without changing the application layer (expensive.

rgds,
J

Sorry, I don't follow you. Transaction management has nothing whatsoever to do with tables. Transactions are related to connections (aka SQL sessions). (And this is not specific for DB2 but rather a general concept in relational database systems - at least for those that have transaction management build in.

Ah yes, ok let me explain.

We have configuration that is part of our application code that identifies each table, and whether it takes part in transaction protection. Transaction protection indicates whether the use of the table takes part in commit/rolled back behaviour based upon some criteria.

Eg.,

Table X (TP = yes)
Table Y (TP = yes)
Table Z (TP = no)

[pseudocode]
process a message
- begin TP
- insert into X
- update Z
- insert into Y
- error, TP rollback

Following this, I would expect no rows to have been inserted into either X or Y, but I would expect the update on Z to still take place.

Establishing a connection (non-autocommit) for access to tables X & Y, and a separate connection (autocommit) for access to table Z seems to solve this. Of course, if we introduce Table A (TP = no), then this would have a separate autocommit connection to Table Z. As the list of tables that fall into category autocommit is rather small, this approach could work (although not ideal).

Sorry for the confusion, but it is application specific logic.

"There are more things in heaven and earth, Horatio,
Than are dreamt of in your philosophy."

Sorry, I still don't get it. You may want to read up on what "transactions" mean in the context of relational databases. There is a very good book from Gray/Reuter, "Transaction Processing". You may want to read it because it explains the fundamentals of transactions in general - which seems to be something completely different than what you try to do.

I think the only thing you could do is to use something like autonomous transactions:
- begin transaction 1
- insert into X
- begin autonomous transaction 2
- update Z
- end autonomous transaction 2
- insert into Y
- rollback transaction 1

For that, you can use separate connections in DB2, which means that transaction 1 and 2 are completely unrelated things. So throwing them together is just confusing.