JDBC updates to DB2 (Dave is no longer with FHSC)

Dave Nance

JDBC updates to DB2 (Dave is no longer with FHSC)
Please remove this address from your list.

"MMS <firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use of the named recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution of this communication(s) is expressly prohibited. If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
=====

---------------------------------------------------------------------------------
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

Patrick Steurs

Re: JDBC updates to DB2
(in response to Dave Nance)
We are also using DB2 V7 on Z/OS & using Websphere/JDBC-driv
Hi,

We are also using DB2 V7 on Z/OS & using Websphere/JDBC-driver Sequelink
for our Web applications.
But we also use a lot of stored procedures for a long time. Why ?
1) To reduce network-traffic
2) A Stored procedure can be called from any environment --> reusability

3) A stored procedure can be static ! --> less cpu and faster responses.
We never had problems with these SP since the beginning. (db2v5)

Patrick Steurs
DBA at Central bank of Belgium


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: vrijdag 16 december 2005 9:27
To: [login to unmask email]
Subject: Re: [DB2-L] JDBC updates to DB2

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

-----------------------------------------
Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message should not be construed
as binding on the part of the National Bank of Belgium (NBB) unless
otherwise and previously stated. The opinions expressed in this message
are solely those of the author and do not necessarily reflect NBB
viewpoints, particularly when the content of this message, or part
thereof, is private by nature or does not fall within the professional
scope of its author."


---------------------------------------------------------------------------------
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 Patrick Steurs)
Myron, 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?


On Thu, 15 Dec 2005 08:47:36 -0800, Myron Miller <[login to unmask email]>
wrote:

>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 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 [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bart Mertens

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

If you are going to use dynamic sql then the userID associated with the
WebSphere-datasource needs full CRUD.

You might want to consider setting DataSource and Connection properties
in jdbc. These properties can be used for auditing and
troubleshooting/monitoring purposes. Also WLM can pickup these values
and assign service classes.

Some info:
redbook <Distributed Function of DB2
for z/OS and OS390> SG24-6952-00. Chapter 3.4 "Defining
classification rules" .
http://publib-b.boulder.ibm.com/abstracts/sg246952.html?Open


DB2 for zOS and WebSphere The Perfect Couple.pdf Publication nr:
SG24-6319-00

http://www-128.ibm.com/developerworks/db2/library/techarticle/0212shayer
/0212shayer.html


http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.
ibm.db2.udb.doc/ad/rjvdsprp.htm


Bart

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Edward Kosek
Verzonden: dinsdag 20 december 2005 15:34
Aan: [login to unmask email]
Onderwerp: Re: [DB2-L] JDBC updates to DB2

Myron, 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?


On Thu, 15 Dec 2005 08:47:36 -0800, Myron Miller
<[login to unmask email]>
wrote:

>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 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
[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

Philip Sevetson

Re: JDBC updates to DB2 (Dave is no longer with FHSC)
(in response to Bart Mertens)
Yikes! Did this get mentioned and I missed it? .... Dave?

On 12/19/05, David Nance <[login to unmask email]> wrote:
>
> Please remove this address from your list.
>
> "MMS <firsthealth.com>" made the following annotations.
>
> ------------------------------------------------------------------------------
> This message, including any attachments, is intended solely for the use of
> the named recipient(s) and may contain confidential and/or privileged
> information. Any unauthorized review, use, disclosure or distribution of
> this communication(s) is expressly prohibited. If you are not the intended
> recipient, please contact the sender by
> reply e-mail and destroy any and all copies of the original message.
>
> ======
>
>
> ---------------------------------------------------------------------------------
> 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
>



--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
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

Kevin Arnold

Re: JDBC updates to DB2
(in response to Philip Sevetson)
> It is ingrained in us that static SQL is better than dynamic. Static SQL performs better and security is better controlled.
I can't disagree with this. We are at DB2 v8 running WAS 5.x and 6.x. As the security guy, I am highly concerned about the dynamic SQL. It started out several years ago as "just a few tables" but now the ID can read just about anything. In our shop, just letting the information out is a major problem. Plus the developers now want to do dynamic updates from external web pages - that's just not going to happen if I can help it. But it's security's job to find a way to enable the business to work securely, not to stop it.

To help alleviate our security concerns we considered SQLJ and stored procedures. Obviously both would require re-engineering in our case which would be significant. If you are just starting out, these may be viable. With where we are, we are looking at utilizing controls that include an application-level firewall that will ensure things like SQL insertion and runaway dynamic tasks cannot be executed. We are also moving DB2 security control to RACF so its usage can actually be audited as well. We can make the ID RESTRICTED and put other RACF controls on it this way as well. It's not perfect but it works. But if I had the chance do it over again, I would prohibit dynamic SQL from WAS strictly from a security perspective. And FWIW, the developer arguments against SQLJ and static SQL are pretty weak with today's tools IMHO.

Regards,
Kevin
614-224-8204



CONFIDENTIALITY NOTICE: The Ohio Public Employees Retirement System intends this e-mail message, and any attachments, to be used only by the person(s) or entity to which it is addressed. This message may contain confidential and/or legally privileged information. If the reader is not the intended recipient of this message or an employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that you are prohibited from printing, copying, storing, disseminating or distributing this communication. If you received this communication in error, please delete it from your computer and notify the sender by reply e-mail.


---------------------------------------------------------------------------------
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

Erick Wodarz

Re: JDBC updates to DB2
(in response to Kevin Arnold)
The shop I work in is different than most in that we don't have separate
people/group's working as DBA's/Security/Applications. This definitely has
some drawbacks, but there are some positive aspects to this as well. We
have a more collective responsibility for our database(s), and in general we
don't have situations where priorities diverge.
We started out with embedded SQL, but this simply wasn't flexible enough for
us. We now use about about 90% dynamic SQL, and while we've had troubles
that have been mentioned previously, we were able to solve these in the app
itself. We are now tyring to improve the security of the database and are
moving towards Stored procedures to improve this.
IMO, an application w/ dynamic SQL is very flexible and should be at least
able to handle all the non-security issues with dynamic SQL. The main
problem would be programmers proving to the DBA that the app is incapable of
causing a major headache for said DBA, and for the DBA to be open to
solutions that are out of his box.

My $.02

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Arnold, Kevin
Sent: Wednesday, December 21, 2005 9:55
To: [login to unmask email]
Subject: Re: [DB2-L] JDBC updates to DB2


> It is ingrained in us that static SQL is better than dynamic. Static SQL
performs better and security is better controlled.
I can't disagree with this. We are at DB2 v8 running WAS 5.x and 6.x. As
the security guy, I am highly concerned about the dynamic SQL. It started
out several years ago as "just a few tables" but now the ID can read just
about anything. In our shop, just letting the information out is a major
problem. Plus the developers now want to do dynamic updates from external
web pages - that's just not going to happen if I can help it. But it's
security's job to find a way to enable the business to work securely, not to
stop it.

To help alleviate our security concerns we considered SQLJ and stored
procedures. Obviously both would require re-engineering in our case which
would be significant. If you are just starting out, these may be viable.
With where we are, we are looking at utilizing controls that include an
application-level firewall that will ensure things like SQL insertion and
runaway dynamic tasks cannot be executed. We are also moving DB2 security
control to RACF so its usage can actually be audited as well. We can make
the ID RESTRICTED and put other RACF controls on it this way as well. It's
not perfect but it works. But if I had the chance do it over again, I would
prohibit dynamic SQL from WAS strictly from a security perspective. And
FWIW, the developer arguments against SQLJ and static SQL are pretty weak
with today's tools IMHO.

Regards,
Kevin
614-224-8204



CONFIDENTIALITY NOTICE: The Ohio Public Employees Retirement System intends
this e-mail message, and any attachments, to be used only by the person(s)
or entity to which it is addressed. This message may contain confidential
and/or legally privileged information. If the reader is not the intended
recipient of this message or an employee or agent responsible for delivering
the message to the intended recipient, you are hereby notified that you are
prohibited from printing, copying, storing, disseminating or distributing
this communication. If you received this communication in error, please
delete it from your computer and notify the sender by reply e-mail.


----------------------------------------------------------------------------
-----
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

Lennart Henang

Re: JDBC updates to DB2
(in response to Erick Wodarz)
We did it this way...

First, we have a rule that says that you should have very strong arguments
if you should be allowed to execute dynamic SQL against our production system.

The result of the above has been that JDBC applications have been developed
to be run against our DW system using tables copied from our production
system. Not a good solution, but we don't get any dynamic SQL into our
production system...

Second, for our WebSphere environment, we wanted to reuse existing business
logic running in our CICS systems. So, we introduced Stored Procedures as a
middleware solution. Almost needless to say, the Stored Procedures are
written in COBOL.

Third, in order for our WebSphere developers to access the Stored Procedures
within our production system, we only allow calling them within an SQLJ
context. I.e., from day one we do not allow JDBC calls!

Fourth, now when WebSphere developers wants to access tables directly we
have a rule that says that they are allowed to access the tables only if the
tables contain parameter data. AND, of course, they are not allowed to use
JDBC, but have to stick with SQLJ (which is already a working part of our
Java framework developed for the Stored Procedure access).

When it comes to security, we have the same view on security on WebSphere as
we have on CICS. I.e., the real end users have to authenticate themselves to
the application server and then we authenticate the application against DB2
(and RACF) through the application user used in the WebSphere Datasource.

For the end user authentication we planned on using J2EE Security so we
built a custom user registry by developing 12 security stored procedures
that interacts with RACF. These security procedures does things like checkin
passwords (authenticate) and checking authorization against RACF classes.

Due to restrictions in WebSphere (you cannot have more than one custom user
registry) we are now calling the security procedures from our Java
framework. In this way, the real end user has to login to the application
and the application will check if she is authorized to run the different
parts of the application (we can allow a user to just read information or to
update information related to the application).

We are also calling the authority procedure from within CICS today.

For more information, see a presentation that was held on the European IDUG
2004 in Prague and at the Nordic GSE conference 2005 in Riga. It can be
found here
http://gse-nordic.org/forum/wg/nrtc/2005/S08_Whereinthemiddlewerewe.pdf

Regards, Len...

---------------------------------------------------------------------------------
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