JDBC hangs Informix.....

[Sam]

Hi All,

I know nothing of Informix internals. Sybase yes, informix no. Anyway,
here's the scoop:

I'm using a HP-UX box running Informix 7.2 and using JDBC type 4 from
Informix to connect to the db. My program is a system that takes a
kazillion SQLs into java memory and process them across different
types of databases (no standards...some groups use oracle, some use
informix, etc.) to synch up data (yes, yes...not the best way of doing
things, but this process was already here when i got here).

All the sqls are either an update/insert/delete or a stored proc that
returns a value which I don't need (they may return "Success" or
something else which i care nothing about).

When I establish a connection to informix and start processing these
SQLs, nobody else can do anything. It locks the database. I do a
statement.close() on all stmts allocated. eventually when the
"worker" thread is done processing whatever, it allows other people to
use the db. Note this happens on the instant the connection is closed.

Now...i just recently discovered the environment variable OPTOFC. When
I set this bad boy to 1, nobody hangs, people are happy, everything
works. I really didn't think this was going to work, but it did.

What I don't get is why?

Can somebody explain to me what is going on? I'm using generic JDBC
APIs like execute(), and then if it's true, i flush out the result set
and close that also without caring what data i actually get back.

Any explanation/theory/similar problems are appreciated.

thanks

Sam

[hype]

What version of the jdbc driver are you using ? is it 2.20 ?

the OPTOFC "optimize fetch close" , causes the database to free up
cursor resources when all rows are retrieved, without waiting for an
explicit close()

can u post some code ?
Are u locking or causing database exclusive mode ?

HTH,
Hype.

jungle...@excite.com (Sam) wrote in message news:<b2c36af2.02032...@posting.google.com>...

[Sam]

Thanks for responding...

> What version of the jdbc driver are you using ? is it 2.20 ?
>

According to my program...i am using INFORMIX-OnLine 7.31.UC2 with
a Informix Dynamic Server 2.20.JC1 driver

> the OPTOFC "optimize fetch close" , causes the database to free up
> cursor resources when all rows are retrieved, without waiting for an
> explicit close()
>

hmmm....maybe the closes arent't working...interesting.

> can u post some code ?

nothing exact...lot of code out there. the main code is basically like

Statement i_stmt=icon.createStatement();

try{
for (int i=0; i < sql.getSize(); ++i)
{

if (i_stmt.execute(sql[i]))
{

ResultSet tt=i_stmt.getResultSet()
while(tt.next())
{
//do whatever
}
tt.close()
}

else
{
int rowsEffected=i_stmt.getUpdateCount();
}
}
}

catch (Exception e){}

finally{ i_stmt.close();}

> Are u locking or causing database exclusive mode ?
>

I don't seem to be locking anything...but i'd saying i am causing
database exclusive mode considering my connection somehow gets a high
priority #

I ran the same code without the OPTOFC set to 1 on our production
environment, and it doesn't cause problems (or maybe because
production has 20 times the horsepower of DEV, the problem doesn't
show up). Can the set up of the Informix server have something to do
with it?

Any ideas are appreciated.

Sam