RE: DB2 Version 8.2, Fixpack 7, for Windows (Size of SQL Stored Procedure and SQL Stored Function)

Remember, you may be talking about Application Development Programmers -
there is no telling what they are doing. Assume the worst!

Douglas Kostelnik
Senior Database Administrator/Architect

-----Original Message-----
From: John Kruger
[mailto:db2udbdba-ezmlmshield-x19946142.[Email address protected]
Sent: Tuesday, December 12, 2006 8:17 AM
To: LazyDBA Discussion
Subject: RE: DB2 Version 8.2, Fixpack 7, for Windows (Size of SQL Stored
Procedure and SQL Stored Function)

All I know is the last thing to go is your hearing. Huh?

What are programmers storing in these new robust procedures...high
resolution pictures of their favorite pet?

John Kruger Meloncamp
DB2 Performance on AIX
IBM Certified DB2 DBA Universal Database V8 V9 V10 V11 V12 LUW

-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x57027002.[Email address protected]
Sent: Tuesday, December 12, 2006 3:02 AM
To: LazyDBA Discussion
Subject: RE: DB2 Version 8.2, Fixpack 7, for Windows (Size of SQL Stored
Procedure and SQL Stored Function)

Hey Alex,
Thanks for the info! Your statement "Or why a competent programmer
would ever think of approaching such a limit?" is why I asked this
question. Just maybe, since they did pass this limit, they just might
not be competent? Hehe.

However, after asking the question (being a LazyDBA), I said to myself
"Ed, what's the matter with you! Just because your birthday was Sunday
(10 Dec 2006) and you are now 57 years old, that's no excuse not to
search the Internet to answer your own question. In fact, Ed, isn't
that something you should do all of the time"! Oh my! Phew! Ouch! I
am talking to my self! Is that the first sign? Hehe.

Anyway, I found the following info on the Internet.

Languages for stored procedures
Using the Development Center, you can create stored procedures in Java
or SQL. Both languages create stored procedures that are highly portable
from platform to platform. The language that you choose depends on your
development environment.

SQL stored procedures

SQL provides procedural constructs for writing stored procedures.
Writing stored procedures with the SQL procedure language has the
following advantages:

You can support database modules that are written completely in an SQL
language.
You can quickly learn to write stored procedures with the SQL procedure
language. This ease of learning is especially true if you have
experience with other database languages.
SQL stored procedures are fast because the SQL routines run as compiled
routines.
You can debug SQL stored procedures on Windows, AIX, Sun, and Linux on
DB2 Universal Database servers and on DB2 for z/OS Version 8 server.
With SQL stored procedures, you can call other SQL procedures, nesting
calls up to 16 layers.
SQL stored procedures have size and parameter limitations, depending on
the version of DB2 that you are running:

For DB2 for OS/390 and z/OS versions 6 and 7, the maximum size for SQL
stored procedures is 32 KB.
For DB2 for z/OS version 8, the maximum size for SQL stored procedures
is 2MB For DB2 for Windows and UNIX versions 7 and 8.1, the maximum size
for SQL stored procedures in is 64 KB.
For DB2 version 8.2, the maximum size for SQL stored procedures is 2 MB
Java stored procedures

Writing stored procedures with Java has the following advantages:

You can establish a common development environment and common language.
This commonality can be shared by the stored procedures on the database
server and the client application that runs on a client workstation or a
middleware server (such as a Web server).
You can reuse code from Java methods that you have already written.
The input/output of files is possible with Java stored procedures,
within the security limits of Java. SQL stored procedures do not support
the input/output of files.
Java stored procedures have size and parameter limitations, depending on
the version of DB2 that you are running:

For DB2 for z/OS version 7, the maximum size for Java stored procedures
is 32 KB.
For DB2 for z/OS version 8, the maximum size for Java stored procedures
is 10MB
Restriction: For iSeries version 5 release 3, the following are not
supported for Java stored procedures:

PARAMETER STYLE DB2GENERAL
BINARY and VARBINARY built-in data types Parent topic: Stored procedures

Related concepts
Benefits of using stored procedures
SQL stored procedures
Java stored procedures

Related reference
Name lengths and restrictions for identifiers and parameters

-----Original Message-----
From: Alex Levy
[mailto:db2udbdba-ezmlmshield-x15174344.[Email address protected]
Sent: Tuesday, December 12, 2006 3:06 AM
To: LazyDBA Discussion
Subject: RE: DB2 Version 8.2, Fixpack 7, for Windows (Size of SQL Stored
Procedure and SQL Stored Function)

Hi Ed
There is no listed entry for SPs and UDFs in the SQL Limits in the V8
Information Center.
However the maximum text length is 2Mb ( SYSCAT.ROUTINES, column TEXT).
What is much more interesting is why you ask the question? Or why a
competent programmer would ever think of approaching such a limit?
My regards to Commissioner John L. Winn

Alex Levy
Sustainable Software Ltd
http://www.sustainablesoftware.net

-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x36227333.[Email address protected]
Sent: 11 December 2006 21:49
To: LazyDBA Discussion
Subject: DB2 Version 8.2, Fixpack 7, for Windows (Size of SQL Stored
Procedure and SQL Stored Function)

To All,
Does anyone know the maximum size of a SQL stored procedure and the
maximum size of a SQL stored function for DB2 version 8.2, Fixpack 7,
for Windows? Thanks.

Please take a few minutes to provide feedback on the quality of service
you received. The Department of Education values your feedback as a
customer.
Commissioner John L. Winn is committed to continuously assessing and
improving the level and quality of services provided to you by
Department staff. Simply use the link below. Thank you in advance for
completing the survey.

http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]

---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

-----------------------------------------------
Scanned by 186k-NetCleanse, spam and virus protection

---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

<html>
 <align=justify> 
_____________________________________________________________ 
The information transmitted is intended only for the person(s) or entity to which it is addressed and may contain confidential and or privileged material and should be treated as a confidential AAA Auto Club South communication. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.
</html>

DB2 & UDB email list listserv db2-l LazyDBA home page