JDBC updates to DB2

Edward Kosek

JDBC updates to DB2
We are a DB2 V7 on Z/OS shop using Websphere/JDBC for our Web
applications.
JAVA programs that update the database do so by calling a CICS program
which does the actual update.
Calling a CICS program gives us the 2-phase commit and addresses
recoverability issues.
We are now looking at the probability of having JAVA programs directly
update the database(eliminate CICS program calls).
Stored procedures and SQLJ are being considered but it appears JDBC is the
preferred method of our developers.
It is ingrained in us that static SQL is better than dynamic. Static SQL
performs better and security is better controlled.
We are concerned about moving in the direction of JDBC updates and dynamic
SQL. Am I living in the past?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Myron Miller

Re: JDBC updates to DB2
(in response to Edward Kosek)
My client has been doing direct JDBC updates to DB2 databases for several years
now. The biggest issue was understanding and tracking the dynamic SQL from a
performance perspective. It's hard to capture and harder still to tell which
SQL is causing performance problems. What we found is that it wasn't
necessarily the biggest and longest but quite often was the small short million
times executed that were causing us the most problems.

One real key is to ensure that your JAVA programmers use prepare statement and
host variables. This ensures that the DB2 Dynamic cache will be effective.
I'm assuming that they will be coming in from the same generic AUTHID from
websphere. Having a lot of SQL with where x = '12345' etc will hurt.

As long as you can accomplish this and get the threads to release, you really
shouldn't see much difference in performance over the static. Slightly worse
yes, but not significantly. And probably considering the overhead of calling
CICS maybe overall a slight decrease in total system CPU used.

Myron

--- Edward Kosek <[login to unmask email]> wrote:

> We are a DB2 V7 on Z/OS shop using Websphere/JDBC for our Web
> applications.
> JAVA programs that update the database do so by calling a CICS program
> which does the actual update.
> Calling a CICS program gives us the 2-phase commit and addresses
> recoverability issues.
> We are now looking at the probability of having JAVA programs directly
> update the database(eliminate CICS program calls).
> Stored procedures and SQLJ are being considered but it appears JDBC is the
> preferred method of our developers.
> It is ingrained in us that static SQL is better than dynamic. Static SQL
> performs better and security is better controlled.
> We are concerned about moving in the direction of JDBC updates and dynamic
> SQL. Am I living in the past?
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at [login to unmask email] Find
> out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Abe Kornelis

Re: JDBC updates to DB2
(in response to Myron Miller)
Edward,

one of the things to be aware of is the 'binding' process that
DB2 performs for every dynamic SQL statement. Even when a cache
hit occurs, the equivalent of an incremental bind will be needed.

To ensure good performance, these binds need to find all required
catalog/directory pages in storage. So you should really make sure
that catalog and directory have a private bufferpool. Monitor the
bufferpool for it's I/O rate. The number of reads should be
virtually zero.

In our shop we try to keep an eye on EDM pool usage, too.
I 'm not sure it's needed, but I try to keep around 10% of EDM free.
Not because I'm expecting an increase in load; not because
we cannot tolerate unused skeletons to be cannibalized,
but mainly to reduce the risk of fragmentation in our
subsystem, which is processing a huge amount of badly
tuned dynamic SQL. I.e. the application generates SQL
with lots of literals :-((

Abe Kornelis
Technisch Specialist
Data Centers & Hosting Services
Getronics PinkRoccade

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Edward Kosek
Sent: donderdag 15 december 2005 17:24
To: [login to unmask email]
Subject: JDBC updates to DB2


We are a DB2 V7 on Z/OS shop using Websphere/JDBC for our Web
applications.
JAVA programs that update the database do so by calling a CICS program
which does the actual update.
Calling a CICS program gives us the 2-phase commit and addresses
recoverability issues.
We are now looking at the probability of having JAVA programs directly
update the database(eliminate CICS program calls).
Stored procedures and SQLJ are being considered but it appears JDBC is the
preferred method of our developers.
It is ingrained in us that static SQL is better than dynamic. Static SQL
performs better and security is better controlled.
We are concerned about moving in the direction of JDBC updates and dynamic
SQL. Am I living in the past?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Simon George

Re: JDBC updates to DB2
(in response to Abe Kornelis)
Hi,

We have gone the other way. Out client application talk to WAS (on z/OS)
and that in turn talks to CICS (z/OS) via the CICS Transaction Gateway.
This runs (in 99.9% of the cases) static SQLJ transactions. That gives
us the full transactional integrity of CICS and far better performance
(a smaller memory footprint as well if you can get CICS Shared Classes
to work!). We do run some transactions directly in WAS but they are all
read only and about 50/50 static/dynamic. All your reasons for SQLJ are
correct, plus you should tell your developers its far easier syntax to
code! The only downside (which developers hate) is the additional build
(customise) and bind steps required for the static packages. We get
round this by letting the developers develop in WASD using purely
dynamic sql (its still sqlj syntax but unprofiled so it runs
dynamically). That keeps them happy. Then at application assembly/build
time we do a one off (hopefully) build/customise/bind etc.

Cheers Simon
[login to unmask email]
(1931) 74408 (internal)
(020) 7567 4408 (external)
Team Website: http://ode/ODE/EqITTS/home.htm
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Edward Kosek
Sent: 15 December 2005 16:24
To: [login to unmask email]
Subject: [DB2-L] JDBC updates to DB2

We are a DB2 V7 on Z/OS shop using Websphere/JDBC for our Web
applications.
JAVA programs that update the database do so by calling a CICS program
which does the actual update.
Calling a CICS program gives us the 2-phase commit and addresses
recoverability issues.
We are now looking at the probability of having JAVA programs directly
update the database(eliminate CICS program calls).
Stored procedures and SQLJ are being considered but it appears JDBC is
the preferred method of our developers.
It is ingrained in us that static SQL is better than dynamic. Static
SQL performs better and security is better controlled.
We are concerned about moving in the direction of JDBC updates and
dynamic SQL. Am I living in the past?

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore
does not accept liability for any errors or omissions in the contents
of this message which arise as a result of e-mail transmission. If
verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Edward Kosek

Re: JDBC updates to DB2
(in response to Simon George)
On Fri, 16 Dec 2005 08:19:34 +0100, Kornelis, Abe
<[login to unmask email]> wrote:

>Edward,
>
>one of the things to be aware of is the 'binding' process that
>DB2 performs for every dynamic SQL statement. Even when a cache
>hit occurs, the equivalent of an incremental bind will be needed.
>
>To ensure good performance, these binds need to find all required
>catalog/directory pages in storage. So you should really make sure
>that catalog and directory have a private bufferpool. Monitor the
>bufferpool for it's I/O rate. The number of reads should be
>virtually zero.
>
>In our shop we try to keep an eye on EDM pool usage, too.
>I 'm not sure it's needed, but I try to keep around 10% of EDM free.
>Not because I'm expecting an increase in load; not because
>we cannot tolerate unused skeletons to be cannibalized,
>but mainly to reduce the risk of fragmentation in our
>subsystem, which is processing a huge amount of badly
>tuned dynamic SQL. I.e. the application generates SQL
>with lots of literals :-((
>
>Abe Kornelis
>Technisch Specialist
>Data Centers & Hosting Services
>Getronics PinkRoccade
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
>Behalf Of Edward Kosek
>Sent: donderdag 15 december 2005 17:24
>To: [login to unmask email]
>Subject: JDBC updates to DB2
>
>
>We are a DB2 V7 on Z/OS shop using Websphere/JDBC for our Web
>applications.
>JAVA programs that update the database do so by calling a CICS program
>which does the actual update.
>Calling a CICS program gives us the 2-phase commit and addresses
>recoverability issues.
>We are now looking at the probability of having JAVA programs directly
>update the database(eliminate CICS program calls).
>Stored procedures and SQLJ are being considered but it appears JDBC is the
>preferred method of our developers.
>It is ingrained in us that static SQL is better than dynamic. Static SQL
>performs better and security is better controlled.
>We are concerned about moving in the direction of JDBC updates and dynamic
>SQL. Am I living in the past?
>
>--------------------------------------------------------------------------
-------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>
>--------------------------------------------------------------------------
-------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

Abe, thanks for your input on my question regarding JDBC updates to DB2.
We will definitely keep it in mind as we move forward.
I was wondering if you have any comments on how to handle DB2 security.
We are looking at granting full CRUD to the ID associated
with the Websphere Server. Are there any other options we should
consider?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm