SQL7008 Error - Workaround?

Question

I'm using the JTOpen JDBC driver for a DB2 Universal database. I have very little experience with SQL beyond simple statements.

From this question, I see that the error I'm getting (SQL7008) is thrown when trying to "insert/update rows in a non-journaled table during a transaction" (paraphrased).

According to the project lead, our DB is not journaled and won't be any time soon (don't ask me why, I'm not the DBA). However, I'm working on a project where being able to commit everything in one go (rather than AutoCommit-ing each time an execute is called) is nearly necessary (not totally required, but it would solve a lot of issues down the road).

Is there any way to work around erorr SQL7008 without enabling Journalling?

Answer 1

The only way to work around it without enabling journaling is to disable transaction isolation in your connection string as follows:

jdbc:as400://systemname;naming=sql;errors=full;transaction isolation=none;date format=iso

The full list of JDBC properties can be found in the IBM Toolbox for Java JDBC properties documentation.

Answer 2

I have found that using WITH NONE at the end of the DB2 statement solve the problem, only if you use INSERT.

When I try using SET OPTION COMMIT=*NONE on a Delete statement, it seems to skip the where, and it deletes everything, the same happens when i try to use WITH NC or WITH NONE

To resolve this issue, do one of the following:

Enable journaling for the database table: Windows: Add a CLI Parameter 'TxnIsolation' with the value '32' within your ODBC settings under "Administrative Tools". This option can be found under: "Data Source" -> "Advanced Settings" -> "Add" -> "TxnIsolation" as a radio button "No Commit".

AIX / Unix: Run the following DB2 command on your database: ' db2 update cli cfg for section using TXNIsolation 32'. Verify these settings with the following command: ' db2 get cli cfg'

Alternate SQL workaround: (not OS-specific): Add 'WITH NONE' to the end of your SQL UPDATE command.

More info...

Answer 3

There is an option that can be added to your connection string that disables commitment control.

Probably CommitMode=0 would work.