Re: Recommended Lock Mode for heavy JDBC usage?

Posted in 2003

John_Thomas@ao.uscourts.gov — Thu, 29 May 2003 09:50:21 -0400 — source: Usenet: comp.databases.informix

This is a multipart message in MIME format. --=_alternative 004C277D85256D35_= Content-Type: text/plain; charset="us-ascii" I've experienced this from time to time also, in straight DBACCESS scripts as well as in ESQL-C. In both cases, I restructured my transactions so that a maximum of 25-50 or so records are committed before each new BEGIN WORK. You would think that exclusive access combined with "set isolation to dirty read" would take care of the problem, but it did not for me. The only robust solution in our case was to break up large-scale inserts and/or updates into many (relatively) small transactions. --John 202-502-2599 (or x 2599) anthony@zoraptera.com (Anthony Presley) Sent by: owner-informix-list@iiug.org 05/29/2003 12:59 AM Please respond to anthony To: informix-list@iiug.org cc: Subject: Re: Recommended Lock Mode for heavy JDBC usage? The other error is 101 (ISAM Error: file is not open), as I just stated in a follow-up (just a minute ago). These tables are rather generic, and the indexes are correct, as per what's being accessed the most. I COULD do the query as a single statement, but it's actually about 400 small queries being run against the database. We built some support classes to do regular access of the db (when the fetches are closer to 10 rows), so we're using the same support classes to do the conversion programs. No other users are accessing the table at the same time, I've got exclusive access. Any ideas? "Murray Wood" <murray@quanta.co.nz> wrote in message news:<bb17jh$rvc$1@terabinaries.xmission.com>... > With eror 243 there is another code that tells you specifically what the > problem is. > Take the sql that was running and run it with E4GLEXPLAIN set. Do you have > the correct indexes or have you run the correct update stats? > How many other users are accessing this table at the same time? > > MW > > > -----Original Message----- > > From: owner-informix-list@iiug.org > > [mailto:owner-informix-list@iiug.org]On Behalf Of Anthony Presley > > Sent: Wednesday, 28 May 2003 8:32 a.m. > > To: informix-list@iiug.org > > Subject: 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 --=_alternative 004C277D85256D35_= Content-Type: text/html; charset="us-ascii" I've experienced this from time to time also, in straight DBACCESS scripts as well as in ESQL-C.  In both cases, I restructured my transactions so that a maximum of 25-50 or so records are committed before each new BEGIN WORK. You would think that exclusive access combined with "set isolation to dirty read" would take care of the problem, but it did not for me.   The only robust solution in our case was to break up large-scale inserts and/or updates into many  (relatively) small transactions. --John 202-502-2599 (or x 2599) <table width=100%> <tr valign=top> <td> <td>anthony@zoraptera.com (Anthony Presley) Sent by: owner-informix-list@iiug.org 05/29/2003 12:59 AM Please respond to anthony <td>                To:        informix-list@iiug.org         cc:                 Subject:        Re: Recommended Lock Mode for heavy JDBC usage?</table> The other error is 101 (ISAM Error: file is not open), as I just stated in a follow-up (just a minute ago). These tables are rather generic, and the indexes are correct, as per what's being accessed the most. I COULD do the query as a single statement, but it's actually about 400 small queries being run against the database.  We built some support classes to do regular access of the db (when the fetches are closer to 10 rows), so we're using the same support classes to do the conversion programs. No other users are accessing the table at the same time, I've got exclusive access. Any ideas? "Murray Wood" <murray@quanta.co.nz> wrote in message news:<bb17jh$rvc$1@terabinaries.xmission.com>... > With eror 243 there is another code that tells you specifically what the > problem is. > Take the sql that was running and run it with E4GLEXPLAIN set. Do you have > the correct indexes or have you run the correct update stats? > How many other users are accessing this table at the same time? > > MW > > > -----Original Message----- > > From: owner-informix-list@iiug.org > > [mailto:owner-informix-list@iiug.org]On Behalf Of Anthony Presley > > Sent: Wednesday, 28 May 2003 8:32 a.m. > > To: informix-list@iiug.org > > Subject: Recommended Lock Mode for heavy JDBC usage? > >