Recommended Lock Mode for heavy JDBC usage?
Sorry for the cross-post, but it applies in both places :-( I'm writing several database conversion utilities for a client, under Informix 7.31.UD2, which run fairly well. However, during the conversion of some 56,000 rows, I've got about twelve errors for "Could not position within a table (anthony.milestonedef). -243". Error -243, but it recommends looking at OS errors, if not there, then ensure that the table's aren't corrupted. My tables are not corrupted, nor is anything wrong with the hardware. However, if I run the program by limiting the number of fetches to the new database (SELECT statements), or by limiting the number of rows converted at a time, it runs without errors. I'm accessing everything through JDBC, and my conversion routines are all done in Java. My lock mode is set to row, and I've placed within my routines the statements: "SET LOCK MODE TO WAIT", and I'm still getting these errors. As a test, I ran a Java program which queries the database and yanks some 65,000 rows from it. About half-way through, I get the same error, and it dies. However, while watching the memory, it never uses everything it has at its disposal, so I assume the problem is LIKELY with the db, or JDBC driver. What can be done, if anything, about this? Is there a recommended "lock mode"? How can I force the Informix JDBC driver to WAIT (if that's the problem)? Thanks. --Anthony
"Anthony Presley" <anthony@zoraptera.com> wrote > I'm accessing everything through JDBC, and my conversion routines are > all done in Java. My lock mode is set to row, and I've placed within > my routines the statements: "SET LOCK MODE TO WAIT", and I'm still > getting these errors. If you have SET LOCK MODE TO WAIT, then the process will wait indefinitely to lock the rows. that would mean that you should never get error -243. Additionally it is not a good practice to set it to wait indefinitely. start with a figure of 60 (1 min). Also what is the lock mode of the table. page/row.
I hope to not have to run the application with SET LOCK MODE TO WAIT, but was at the end of my rope. I had started around 3min, and worked my way up. I'm probably doing something wrong in my code .... Currently, in JDBC, I'm doing something similar to: Statement lockMode = cAzure.createStatement (); lockMode.execute ("SET LOCK MODE TO WAIT 150"); lockMode.close (); And then go onto processing statements, using Connection cAzure. Will that keep the lockmode set to 150? Do I have to set the lock mode each / every time I access the db, or can I set it for the life of the Connection? The table is set to lock mode row, or was when I created it. Thanks. "rkusenet" <rkusenet@sympatico.ca> wrote in message news:<bb0ids$4knn1$1@ID-75254.news.dfncis.de>... > "Anthony Presley" <anthony@zoraptera.com> wrote > > > I'm accessing everything through JDBC, and my conversion routines are > > all done in Java. My lock mode is set to row, and I've placed within > > my routines the statements: "SET LOCK MODE TO WAIT", and I'm still > > getting these errors. > > If you have SET LOCK MODE TO WAIT, then the process will wait indefinitely > to lock the rows. that would mean that you should never get error -243. > > Additionally it is not a good practice to set it to wait indefinitely. start > with a figure of 60 (1 min). > > Also what is the lock mode of the table. page/row.
rkusenet wrote: > > "Anthony Presley" <anthony@zoraptera.com> wrote > > > I'm accessing everything through JDBC, and my conversion routines are > > all done in Java. My lock mode is set to row, and I've placed within > > my routines the statements: "SET LOCK MODE TO WAIT", and I'm still > > getting these errors. > > If you have SET LOCK MODE TO WAIT, then the process will wait indefinitely is supposed to wait indefinitely - I've never trusted it after I got bit once. > to lock the rows. that would mean that you should never get error -243. > > Additionally it is not a good practice to set it to wait indefinitely. start > with a figure of 60 (1 min). > > Also what is the lock mode of the table. page/row. -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #