DB2 Insert Problem - Invalid Table State

[Shane Parker]

Hi All,

I am trying to insert some records into a table using the Universal JDBC Driver. When I go to execute the prepared statement I get the following error:

com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -7008, SQLSTATE: 55019, SQLERRMC: TABLE1 ;LIBRARY ;3

SQL State 55019 is 'The table is in an invalid state for the operation.' From the info I've found, this problem seems to relate to journaling. The physical file I want to insert records into is not journaled (and I don't want it to be). A proposed solution has been to turn off commitment control (ie. use Connection.setAutoCommit(false)) but this doesn't work either.


Example code that gives the error:

//...set up url, username, password.

Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
Connection con = DriverManager.getConnection(url,username,password);

con.setAutoCommit(false);

String SQL = "insert into LIBRARY.TABLE1 values(?,?)";
PreparedStatement ps = con.prepareStatement(SQL);

ps.setString(1,"A");
ps.setString(2,"B");
ps.executeUpdate(); // Fails on this line.
con.commit();


I have used similar code for select statements and they work fine. I only get the problem for insert/update/delete.

Can someone please help?

[Dharamvir (DV) Punia]

Hi,
You might want to check the datatype of your columns in DB2, make sute its "varchar", you will get this error if there is conflict with data types meaning your setting in your string in your prepared statemnent as shown below:
ps.setString(1,"A");
ps.setString(2,"B");
But one of them might not be a proper data type in db2, that's why it will go in an invalid state, if you still have problems, post you Table ddl for you Libaray.table1, i will take a look at it. What version of db2 you are using?

You might also want to specify in your insert statements:
INSERT INTO LIBRARY.TABLE1(DB2_FIELD1, DB2_FIELD2) VALUES(?,?)

If still doesn't work then try the following statement , just to see if you can insert it:
INSERT INTO LIBRARY.TABLE1 (DB2_FIELD1, DB2_FIELD2) VALUES('A','B')
At least this will give you an idea that there is nothing wrong with the datatypes in db2.

Let me know if it helps.
-DV

[Shane Parker]

Thanks for your reply Dharamvir.

I have checked that the datatypes are all the correct type (ie. CHARACTER). I am actually trying to update a table in DB2 running on an iSeries (V5R2). When I try the insert from the interactive SQL prompt I have no problems.

ie. INSERT INTO LIBRARY.TABLE1 (FIELDA,FIELDB) VALUES('A',B')

But when I try exactly the same statement from my Java program it fails (with the error mentioned previously).

I'm almost certain this is do with Journaling on the iSeries. If I associate the physical file I'm trying to insert into with a journal, it all works fine. But I need to somehow do inserts into tables (ie. physical files) that are not journaled.

[Bear Bibeault]

"Shane P",

We're pleased to have you here with us on the Ranch, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender