Dynamic versus Static SQL from a Java program

Brenda Hunsicker

Dynamic versus Static SQL from a Java program

Is anyone using SQLJ instead of JDBC for access to DB2 z/OS from a Java
program run on an intel box?

How about the use of stored procedures or Java Beans executing a CICS
program for access to DB2 z/OS?

We are looking at ways to improve the performance of our Java programs
and we are considering a standard that states that DB2 z/OS access is
via static SQL.

I am looking for pros/cons or any other advice that you can share for
access to DB2 from Java via one of these methods - JDBC, SQLJ, Java
Beans executing CICS program, or stored procedures (the call statement
for the stored procedure can be via JDBC and the stored procedure would
be required to be written in Cobol)

Thanks in advance for your replies and Happy Holidays.

Thanks,
Brenda

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Jan tje

Re: Dynamic versus Static SQL from a Java program
(in response to Brenda Hunsicker)
On Tue, 22 Dec 2009 07:36:42 -0600, Hunsicker, Brenda
<[login to unmask email]> wrote:

>
>Is anyone using SQLJ instead of JDBC for access to DB2 z/OS from a Java
>program run on an intel box?
Yes. And frankly, for all cases where you have to actually code the SQL, this
is my preferred way. Comes actually rather close to embedded SQL like we
used to do in the COBOL days (that are far from over, B.T.W.) There is a little
extra burden in preparing the programs, but tools like RAD7 are doing that
for you.

>
>How about the use of stored procedures or Java Beans executing a CICS
>program for access to DB2 z/OS?
Can be done. No sweat. Only, be aware of the overhead required to actually
call the stored procedure. If you call a stored procedure that then runs for half
an hour and returns just one row; that's OK. But (and I have seen this happen
and we are still working to get rid of the debris it caused...) do not call a
stored procedure just to do a select of one column and then another stored
procedure to actually fetch one row...
The point is that calling a stored procedure is rather costly, just to actually
invoke the procedure. If that is for doing a fair amount of work within the
database, then that is no issue, but if your stored procedure indeed is doing
just a simple select, you will find the overhead of calling that stored procedure
can be up to 500% of the cost of that select.
If that stored procedure then invokes a CICS transaction just to do that single,
simple select, the picture becomes even more grotesk...

>We are looking at ways to improve the performance of our Java programs
Aren't we all?

>and we are considering a standard that states that DB2 z/OS access is
>via static SQL.
Static SQL in and of itself is not a guarantee of good performance. Neither is
dynamic SQL synonym of bad performance. Just make sure you have the
tools that enable you to measure and tune.
It has to be said that, traditionally, in your average z/OS shop, more tools are
available for dealing with static SQL than there are for dealing with dynamic.

>I am looking for pros/cons or any other advice that you can share for
>access to DB2 from Java via one of these methods - JDBC, SQLJ, Java
Have you considered using a Java persistency framework (things like iBatis
or Hibernate)? Correctly configured (and provided you let them generate that
dynamic SQL), this can take a whole lot of coding burden off of your
developers and the resulting SQL is not bad at all.

Make sure you obtain some tool for looking at and tuning your dynamic SQL.

Use the dynamic statement cache and hit your developers over the head
when they forget to use parameter markers instead of actual values.

Use stored procedures for what they are meant to be used for and not to
encapsulate small, simple SQL.


Cheers,

Jantje.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L