SQL7008 error - workaround?

Question

I am using the JTOpen JDBC driver for the DB2 Universal database. I have very little SQL experience beyond simple instructions.

From this question , I see that the error I get (SQL7008) occurs when I try to "insert / update rows in a non-journal table during a transaction" (rephrased).

In accordance with the project management, our database is not logged and will not be soon (do not ask me why I am not a database administrator). However, I am working on a project in which it is possible to commit everything at once (and not AutoCommit-ing with each call to execute) is almost necessary (not required completely, but it solves a lot of problems the way).

Is there a way to work with erorr SQL7008 without enabling Journalling?

Answer 1

The only way to get around this without enabling logging is to disable transaction highlighting in the connection string as follows:

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

For a complete list of JDBC properties, see the IBM Toolbox for Java JDBC documentation.

Answer 2

An option to disable commit control can be added to the connection string.

Probably CommitMode=0 will work.

Answer 3

I found that using WITH NONE at the end of a DB2 statement solves the problem only if you use INSERT.

When I try to use SET OPTION COMMIT=*NONE in the "Delete" statement, it seems to skip where and removes everything, the same thing happens when I try to use WITH NC or WITH NONE

To resolve this issue, do one of the following:

Enable logging for the database table:. Windows: Add the CLI parameter "TxnIsolation" with a value of "32" within your ODBC settings in the "Administration" section. This parameter can be found under "Data Source" → "Advanced Settings" → "Add" → "TxnIsolation" as the "No Commit" switch.

AIX / Unix Run the following DB2 command in your database: 'db2 update cli cfg for the partition using TXNIsolation 32'. check these settings have the following command: 'db2 get cli cfg'

Alternative SQL workaround: (OS independent): Add 'WITH NONE' to the end of your SQL UPDATE command.

Additional Information...

Answer 4

The official list of SQL7008 is here (do CTRL-F for SQL7008). It looks like you can get more information from the reason code. If you get reason code 3, there seems to be no other option than logging.

If you get something other than reason code 3, then I think you have more options.

Hope this helps.

Answer 5

If you work with CL commands. The following command solves the problem:

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