[DB2-L] Dynamic versus Static SQL from a Java program

Georg Peter

[DB2-L] Dynamic versus Static SQL from a Java program
>> If you call a stored procedure that then runs for half an hour and
returns just one row; that's OK.<<

Jan, you are just kidding ?

With kind regards - mit freundlichen Gruessen,
Georg H. Peter
c/o Datenzentrale Baden-Wuerttemberg
Software Development & Technology Center
Knowledge Center Database Systems
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------


Abonnieren Sie unseren Infobrief und erfahren Sie regelmäßig die neuesten Nachrichten über unsere Lösungen, aktuellen Projekte und Entwicklungen. Melden sie sich an mit diesem Link http://www.datenzentrale.de/Info-Brief
_______________________________________________________________________________

Datenzentrale Baden-Württemberg, Anstalt des öffentlichen Rechts
Krailenshaldenstr. 44, 70469 Stuttgart
Telefon (0711) 8108-0, Telefax (0711) 8108-350
E-Mail [login to unmask email], Internet www.datenzentrale.de
Vorstand: Karl Tramer (Vors.) und Harald Schätzle, Vorsitzender des Verwaltungsrats: Gunter Czisch
USt-Id-Nr. DE147794223
_______________________________________________________________________________

_____________________________________________________________________

* 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

Ted MacNEIL

Re: AW: [DB2-L] Dynamic versus Static SQL from a Java program
(in response to Georg Peter)
>> If you call a stored procedure that then runs for half an hour and
returns just one row; that's OK.<<

>Jan, you are just kidding ?

What if that one row contains data that is mission critical?

Technicians should NOT be making business decisions.
What you should be doing is determining the best method to support the business.
If this one row per half-hour is the best way, it is NOT up to you to stop it.
-
Too busy driving to stop for gas!

_____________________________________________________________________

* 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

Jan tje

Re: AW: [DB2-L] Dynamic versus Static SQL from a Java program
(in response to Ted MacNEIL)
On Wed, 23 Dec 2009 13:14:35 +0100, Peter, Georg
<[login to unmask email]> wrote:

>>> If you call a stored procedure that then runs for half an hour and
>returns just one row; that's OK.<<
>
>Jan, you are just kidding ?

Only half...

The point is that the overhead of invoking a stored procedure is
considerable. When the stored procedure does a fair amount of work, that is
no problem, because, percentage-wise, the overhead will be small. But if the
stored procedure is only doing a simple select and nothing else, then indeed
the overhead will be bigger than the cost of the actual select.

Cheers,

Jantje.

_____________________________________________________________________

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

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

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: AW: [DB2-L] Dynamic versus Static SQL from a Java program
(in response to Jan tje)
On Wed, 23 Dec 2009 12:50:09 +0000, Ted MacNEIL
<[login to unmask email]> wrote:

>What if that one row contains data that is mission critical?
Well, probably it will. But that is not the point. If you have to get to the data in
order to accomplish your mission, then you must get to it. One way or another.

>
>Technicians should NOT be making business decisions.
I cannot agree more with this statement.

>What you should be doing is determining the best method to support the
business.
And that is exactly the point I was trying to make. There are different ways of
accessing data in DB2. Which method to use will, as always "depend"...

I just wanted to warn against using stored procedures for too simple queries,
because of the performance penalty of invoking a stored procedure.

>If this one row per half-hour is the best way, it is NOT up to you to stop it.
It never was my intention to stop anything. Again: I just wanted to warn for the
cost.

Cheers,

Jantje.

_____________________________________________________________________

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

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

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

Wayne Campbell

Re: Dynamic versus Static SQL from a Java program
(in response to Jan tje)
You may also want to look into IBM's PureQuery. It has some benefits, in that you don't have to rewrite you Java code to run static packages, ease of development, ant the ability to refer back to the java code where the SQL is being called.



Wayne Campbell
DB2 DBA
Administrative Office of the Courts
(360) 705-5268
Email: [login to unmask email]
-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hunsicker, Brenda
Sent: Tuesday, December 22, 2009 5:37 AM
To: [login to unmask email]
Subject: [DB2-L] 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

_____________________________________________________________________

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

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

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