Any SQLJ performance experts out there?

Scott Goodell

Any SQLJ performance experts out there?
We have been trying to use SQLJ to access DB2 OS390 and have determined some
"problems" with host variable generation by db2profc.

db2profc creates a ".bnd" file with String host variables for CHAR columns with
a length of 254 instead of the actual column length. This causes DB2 OS390 to
NOT use matching index scans because of the length miss-match. I have verified
this by editing the ".bnd" file and changing the host var lengths to the correct
value and then binding. After the manual "fix" access paths are correct.

I just wondered if anyone has any experience with this and figured out a work
around?

I will take this issue to IBM as well but wondered if anyone had a quick answer.

Thanks
Scott



Venkat (PCA) Pillay

Re: Any SQLJ performance experts out there?
(in response to Scott Goodell)
That is definitely a problem, I haven't found a way yet. Apart from it the
number of inner classes the SQLJ precompiler puts also creates performance
degradation as compared to JDBC straight.

My JDBC servlet runs 5 times faster that SQLJ servlet (exact same code
except for being JDBC or SQLJ). This seems ridiculous that dynamic SQL
program runs faster that static SQL program !!!

Do you or any one on the list-serv know a way to reuse the context from one
class to another class (the class which instantiated the first) ????

For example: DB2Con.class creates the connection and context.

ApplServlet.class uses DB2Con.class to create connection as well as sets
packageset=:collid, now how can ApplServlet reuse the context created by
DB2Con ??? Any idea ?

Please let us know what IBM says regarding the mismatch of variables causing
tablespace scan for SQLJ.

Regards
Venkat Pillay


> -----Original Message-----
> From: Scott Goodell [SMTP:[login to unmask email]
> Sent: Thursday, January 04, 2001 5:43 PM
> To: [login to unmask email]
> Subject: Any SQLJ performance experts out there?
>
> We have been trying to use SQLJ to access DB2 OS390 and have determined
> some
> "problems" with host variable generation by db2profc.
>
> db2profc creates a ".bnd" file with String host variables for CHAR columns
> with
> a length of 254 instead of the actual column length. This causes DB2
> OS390 to
> NOT use matching index scans because of the length miss-match. I have
> verified
> this by editing the ".bnd" file and changing the host var lengths to the
> correct
> value and then binding. After the manual "fix" access paths are correct.
>
> I just wondered if anyone has any experience with this and figured out a
> work
> around?
>
> I will take this issue to IBM as well but wondered if anyone had a quick
> answer.
>
> Thanks
> Scott
>
>
>
>
>