Hello all,
I'm writing this as a warning to others. We installed DB2 8.1.2 with
all fixpaks onto our Linux server with the intention of moving our systems
to it. The plan was to use SQL stored procedures in the database to provide
speed of access and some basic business logic. In the middle layer we have
a class library written in Java which calls these stored procs.

The library was originally written to run against user defined functions
installed into a Postgres database. Moving to DB2 allowed us to bring our
custom data together with our Accpac accounting data. We thought it would
take us around 3 to 4 days to convert our Postgres functions to DB2 procs
and get them running with our Java library. We have around about 150 procs
in all with 90% of them containing a single query. In others words very
simple stuff.

We have two developers (including myself) with experience across a number of
databases and languages including Oracle, Sybase, MS SQL, etc.

It's now 3 weeks later and we have just made the decision to completely
abandon calling SQL procedures from Java. We are now starting to completely
re-write all access within our java code to use SQL directly.

Why ? Simply put, we have run into too many bugs, poorly doented or
undoented behaviour with the IBM supplied JDBC driver when using
procedures. Some of these "brick walls" we have come across are:

1. Scrollable result sets cannot be returned from a proc. Even if you
explicitly set a statement to return a scrollable, the driver will return
you a forward only result set.

2. If method (A) gets a result set from a proc, loops thorugh it and calls
method (B) which in turn accesses more data, and B explicitly closes the
result set, the result set in A is automatically closed by the driver, even
though the code is still trying to loop through it.

This only happens if method A calls a proc to get the data. If it uses a
piece of sql then the result set is not closed by B. Note that in our test
code to debug this, we had complete separate statements and result sets.
The only common element to them was the connection object.

3. Calling a proc that does not exists causes the driver to issue a null
pointer exception rather than a more correct SQL0440N error.

4. Some SQLs issue a SQL0100W warning when they do not find any data to
process. For example, a delete statement which fails to find a record to
delete. If called directly from Java the warning is returned in the
SQLWarning object. If the same SQL is placed inside a stored proc and
called, the warning is not returned. We do not know if there are any other
warnings which also get "lost".

You can code around this sort of thing by explicity coding Get Diagnostic
checks or not found handlers and using signal to generate custom errors.

5. Several our our load SQLs for setting up data within the database call
procedures to perform tasks. We found that the only way we could call a
procedure (and return the value of an OUT parameter) from within SQL was to
turn the SQL into a procedure as well. When we attempted this we were
unable to get it to work. DB2 kept issuing messages about nested save
points even though we had not coded any commit/rollback or savepoint
commands into our SQL code. We tried to do this to see if that was the
problem, but even though the doentation said we could put commit and
rollback commands into a proc, DB2 refused to compile them. We eventually
gave up and recoded the load routines as Java programs.

6. When attempting to debug a lot of these things we spent a large amount of
time trolling the doco and Internet. Of all the example pieces of code we
looked at, almost none of them used procs. This made it difficult to
establish if these things would work with procs at all.

There were a number of other issues I can't remember at the moment, mostly
seeming to stem from the way the driver handled procs as opposed to SQL.
The ones I have mentioned above are just the ones that spring to mind from
the last week or so. Individually they are not that bad, but in total they
make using procedures just not practial.

I hope our experiences help someone else from wasting a large amount of time

Derek Clarkson