jtds issue

Description:	Opened: 2004-11-02 12:07

I have PAL Itn6, snapshot of ~12:00 yesterday installed in Tomcat 5.0.28
talking
to SQL server via a JNDI data-source and the JNDI driver
net.sourceforge.jtds.jdbc.Driver. When doing the PAL self-tests, it creates
vast
temporary files inside the Tomcat installation:

> pwd
/home/sybase/jakarta-tomcat-5.0.28/temp
> ls -l
total 13762224
-rw-r--r--   1 sybase   sybase   1465106432 Nov  1 18:16 jtds20228.tmp
-rw-r--r--   1 sybase   sybase   3934224384 Nov  1 18:58 jtds20230.tmp
-rw-r--r--   1 sybase   sybase   1643454464 Nov  1 18:58 jtds20231.tmp

Note that these files are left over from test runs yesterday evening; they seem
to have leaked. These files filled the space in the account running Tomcat and
stopped operations, both of PAL and of other services at CASU.

It's not clear whether the files are leaked when the tests are successful or
when they are aborted; some runs went each way.

We need to find some way of bounding the temporary space used and possibly of
redirecting it to a different directory.  We also need to document the amount
of
space needed as a function of the size of the DB.

This is a blocker. If we don't fix it, then it will not be possible to run PAL
on CASU machines. For now, during PAL tests, I'll delete the files manually as
they are leaked. However, it won't be possible to registe the service for
general use until the problem is solved.

------- Comment #1 From Guy Rixon 2004-11-02 14:31:02 -------

I've looked at the issues log of the jTDS project and it looks like this make
be
a mis-feature of their JDBC driver. They suggest that the temporary files are
deleted when the JVM exists (NBG for us) or when finalizers are called for
"certain classes" to do with the JDBC connection. I have may PAL set up with
connection pooling and I now suspect that the connections are never closed so
the files are never deleted. I've asked jTDS about this; no reply yet.

Anyway, even if this is not fixable in our PAL code we need to characterize it
and  to explain it in the installation documents. It may be that we have to
avoid JNDI data-sources and associated connection pooling.

------- Comment #2 From Martin (Cindy?!) 2004-11-02 14:59:09 -------

An alternative is to use the direct JDBC connections instead of data sources;
see the WEB-INF/classes/default.properties for which properties to set.  We use
the Microsoft JDBC drivers at ROE and don't seem to get this problem.

------- Comment #3 From Guy Rixon 2004-11-02 15:04:31 -------

Where does one get the MS JDBC driver? I couldn't find a download link on MS'
site. Is there a licence fee?

------- Comment #4 From Martin (Cindy?!) 2004-11-02 16:10:50 -------

Here's the link to the download :-) 

http://www.microsoft.com/downloads/details.aspx?FamilyID=ee91ad1a-1ee4-49e1-95ea-e3f0e39114a9&DisplayLang=en

I don't know if it includes all the patches and stuff.  I don't believe there's
any licence fee.

------- Comment #5 From Martin (Cindy?!) 2004-11-09 12:01:00 -------

I believe this has been resolved by using the m$ drivers

------- Comment #6 From Guy Rixon 2004-11-09 14:03:10 -------

Yes, the MS driver now works at IoA and avoids this problem. Thanks for
providing the link.

The MS driver is also faster, by about a factor of 20, in the self-test case of
a large ADQL output.