REPEAT --> Simple Question on Statement Size

[Anurag]

I feel really embarassed asking this.
However, ask I will.

When we say that:
(1) "CLP in Db2 V8.2 still imposes a limit of 64K on the stored proc
size;
(2) "....If you use another client, such as command center or
development center the SP size limit is 2MB";
(3) "Statement size has been increased to allow 2M in Stinger"

etc etc, what size are we referring to? I am baffled:
(1) Is it that my text file that holds the SP code should be less than
64k?
OR
(2) How to ascertain SP size when using Development Center?
OR
(3) How do you measure the statement size while executing from CLP /
Command Editor?

As simple as this may be, someone pl throw light (and enlighten me).

TiA...........a nurag

[Serge Rielau]

Anurag,

teh size in question is the "SQL Statement Size".
CLP cannot handle an SQL Statement size bigger than 64K.
CREATE PROCEDURE is one(!) SQL Statenent including all the whitepace and
comments which are preserved in the catalog.
So you can have megabyte sized scripts and feed them into CLP via
db2 -td% -f <bigfile>

Does CLP provide a way to measure teh size of teh individual SQL
Statements? No. And it shoudln't. I for one, much rather have CLP fixed
then waste resources on the symptom?
If you need to knwo the size of a CREATE PROCEDURE statement package it
into an individual file... voila.
Note that to the best of my knowledge CLP is the only client interface
with that limit. If you access DB2 via JDBC/ODBC, embedded SQL or the
GUIs this limit does not exist in V8.2.
If, by any means 2MB is not big enough speak up: It's just a random
number that was thought to be reasonable.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Anurag]

Hi Serge,
Thanks for the info.

What now worries me is the method that you advise to measure size of
the SQL statement (or Procedure). Different software editors (like
TEXTPAD / NOTEPAD / EDITPLUS etc on WINDOWS) would display different
file sizes!!!
And the natural followup question is - How does my DB2 measure the
statement size?

Thanks......... anurag
P.S. - Questions open to other readers also :-)

[Serge Rielau]

Anurag wrote:

Hi Serge,
Thanks for the info.

What now worries me is the method that you advise to measure size of
the SQL statement (or Procedure). Different software editors (like
TEXTPAD / NOTEPAD / EDITPLUS etc on WINDOWS) would display different
file sizes!!! I am rather surprised by the fact that this issue is so important to
you. Are you sure you are concentrating on the right issue here?
Whether a routine is 60k or 64k long, in my book, as a 20 year
C-developer that is too long. So whether the thumbrule of checking the
filesize is accurate or not is besides the point.
64k are a booklet of 30 pages!
And the natural followup question is - How does my DB2 measure the
statement size?

DB2 measures the byte length of the string. Presumably PERL would be
able to do this measurement if you need a tool.

If this limitation would be there to stay I might see your point and may
be work towards an error message token, but given that it's a leftover
to be removed it's hardly worth the effort, unless you intend to write
2MP large SQL statements.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jurgen Haan]

Anurag wrote:

Hi Serge,
Thanks for the info.

What now worries me is the method that you advise to measure size of
the SQL statement (or Procedure). Different software editors (like
TEXTPAD / NOTEPAD / EDITPLUS etc on WINDOWS) would display different
file sizes!!!
And the natural followup question is - How does my DB2 measure the
statement size?

Thanks......... anurag
P.S. - Questions open to other readers also :-)

Keep in mind not every editor uses the same newline discipline.
where one would place a \n another may place \r
That's 1 Byte per line.

But still, if you issue a 'dir' in CMD and you see the filesize in
bytes, then that is you size.

-R-