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

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

Probably CommitMode=0 would work.

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

The official listing of SQL7008 is here (do CTRL-F for SQL7008). It looks like you can get more information from the reason code. If you're getting reason code 3, it looks like there is no other option besides enabling journaling.

If you're getting something other than reason code 3, then I guess you have more options.

Hope that helps.

Answer 4

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 5

If working on CL commands. The follow command solves the issue:

RUNSQLSTM SRCFILE(LIBNAME/SRCFILE) SRCMBR(MBRFILE) COMMIT(*NONE) NAMING(*SQL)