Compile problems

[serge]

I have a stored procedure that is over 1000 + lines of code. When i try to
compile
I get the following error:

ABC.TEST123: 1285: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected
token "END" was found following " END IF; END IF; ". Expected tokens may
include: "END". LINE NUMBER=1285. SQLSTATE=42601
I tried creating a new tablespace + buffer pool on this database but still
compile
errors. I don't necessarily understand a lot about the tablespace and the
buffer pool,
but I did my best to create it. Maybe i didn't give the correct size? I
chose 32k.

Would you know if the compile problem i am getting is caused by a low buffer
pool
size or something? If i start commenting blocks of code, the compile error
goes to a
different line. But all the lines the compile complains about seem OK to me.

Any help? What would you suggest i try?
Thank you

[Serge Rielau]

serge wrote:

I have a stored procedure that is over 1000 + lines of code. When i try to
compile
I get the following error:

ABC.TEST123: 1285: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected
token "END" was found following " END IF; END IF; ". Expected tokens may
include: "END". LINE NUMBER=1285. SQLSTATE=42601
I tried creating a new tablespace + buffer pool on this database but still
compile
errors. I don't necessarily understand a lot about the tablespace and the
buffer pool,
but I did my best to create it. Maybe i didn't give the correct size? I
chose 32k.

Would you know if the compile problem i am getting is caused by a low buffer
pool
size or something? If i start commenting blocks of code, the compile error
goes to a
different line. But all the lines the compile complains about seem OK to me.

Any help? What would you suggest i try?
Thank you

That's indeed an amusing error message :-)
It has absolutely nothing to do with any buffer bool or so.
The problem must lie in the syntax.

I consider a procedure of this size a bit over teh top.
No so much because DB2 can't hanelde it (it can), but because it will be
both hard to read, and if you just find out, to debug.

Note that CLP in Db2 V8.2 still imposes a limit of 64K on the stored
proc size. You can check whether you're close to that by checking teh
file length.
If you use another client, such as command center or development center
the limit is 2MB which should be plenty.

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

[serge]

I get the compile error in the Development Center.
I will save it as a file and try in the Command Line Processor.

Thanks

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:3a******** *****@individua l.net...

serge wrote:

I have a stored procedure that is over 1000 + lines of code. When i try
to compile
I get the following error:

ABC.TEST123: 1285: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected
token "END" was found following " END IF; END IF; ". Expected tokens
may
include: "END". LINE NUMBER=1285. SQLSTATE=42601
I tried creating a new tablespace + buffer pool on this database but
still compile
errors. I don't necessarily understand a lot about the tablespace and the
buffer pool,
but I did my best to create it. Maybe i didn't give the correct size? I
chose 32k.

Would you know if the compile problem i am getting is caused by a low
buffer pool
size or something? If i start commenting blocks of code, the compile
error goes to a
different line. But all the lines the compile complains about seem OK to
me.

Any help? What would you suggest i try?
Thank you

That's indeed an amusing error message :-)
It has absolutely nothing to do with any buffer bool or so.
The problem must lie in the syntax.

I consider a procedure of this size a bit over teh top.
No so much because DB2 can't hanelde it (it can), but because it will be
both hard to read, and if you just find out, to debug.

Note that CLP in Db2 V8.2 still imposes a limit of 64K on the stored proc
size. You can check whether you're close to that by checking teh file
length.
If you use another client, such as command center or development center
the limit is 2MB which should be plenty.

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

[dotyet]

put a @ character after the last line of your procedure code, and
compile with the following command:

db2 -td@ -vf <your_procedure _file.sql>

post back if this does'nt help ;-)

regards,
dotyet

[serge]

I open the Command Window and I type:

E:\Program Files\IBM\SQLLI B\BIN>db2 -td@ -vf e:\temp\testall .sql
DB21006E The input command is too long. The maximum length is 131070.

E:\Program Files\IBM\SQLLI B\BIN>

I get the input command is too long. What is this number? The size of the
file
must not exceed 131KB? Because Windows explorer shows the size as
174KB. This 131070 is not number of characters right?
Thank you

put a @ character after the last line of your procedure code, and
compile with the following command:

db2 -td@ -vf <your_procedure _file.sql>

post back if this does'nt help ;-)

[Serge Rielau]

serge wrote:

I open the Command Window and I type:

E:\Program Files\IBM\SQLLI B\BIN>db2 -td@ -vf e:\temp\testall .sql
DB21006E The input command is too long. The maximum length is 131070.

E:\Program Files\IBM\SQLLI B\BIN>

I get the input command is too long. What is this number? The size of the
file
must not exceed 131KB? Because Windows explorer shows the size as
174KB. This 131070 is not number of characters right?

Either way 174K is too big for CLP for an SQL Statement. CLP may have a
different, higher limit for commands (128K?), but 64K is it for SQL.
That's not really the issue here anyway, since you use development
center. So let's focus on the issue at hand.
If you want you can send me the file by email and I'll eyeball it.
Can't promise anything..best effort

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[serge]

I've been working on this issue (among other issues) for a few days now,
obviously I lack the experience and understanding of what I am doing.

Just a moment ago I managed to BUILD the procedure using the
DB2 Development Center. I wasn't able to do so before. I commented one
CALL line but I wasn't getting any compile errors on this line before.

I will repeat my steps and see if I can build the same procedure on another
DB2 server.

By the way, this single SP with 2570+ lines has 3 different cursors being
used plus
a lot of UNIONs, a lot of CASE statements and a lot of WHERE conditions.

I didn't write this procedure but when you read the above fact, how would
you
categorize this SP? Bad? Very bad? Terrible?

Thank you

Either way 174K is too big for CLP for an SQL Statement. CLP may have a
different, higher limit for commands (128K?), but 64K is it for SQL.
That's not really the issue here anyway, since you use development center.
So let's focus on the issue at hand.
If you want you can send me the file by email and I'll eyeball it.
Can't promise anything..best effort

[Serge Rielau]

serge wrote:

I've been working on this issue (among other issues) for a few days now,
obviously I lack the experience and understanding of what I am doing.

Just a moment ago I managed to BUILD the procedure using the
DB2 Development Center. I wasn't able to do so before. I commented one
CALL line but I wasn't getting any compile errors on this line before.

I will repeat my steps and see if I can build the same procedure on another
DB2 server.

By the way, this single SP with 2570+ lines has 3 different cursors being
used plus
a lot of UNIONs, a lot of CASE statements and a lot of WHERE conditions.

I didn't write this procedure but when you read the above fact, how would
you
categorize this SP? Bad? Very bad? Terrible?

Well, it truly deends on what the procedure does.
Complex SQL statements aren't neccessarily bad. Infact I prefer complex
SQL over spaghetty IF THEN ELSE anytime.

There are a few question sthough that come to mind:
What do the cursors do? Are they nested? Three nested cursors would be
horrible. Three cursors following each other would perhaps invite
spliting eth procedure into three parts...
A procedure is bad when _you_ (or those taking over from you) can't read
it anymore. :-)

The interesting question now becemes what was special about the CALL
statement....

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Will Honea]

On Sun, 27 Mar 2005 19:55:01 UTC Serge Rielau <sr*****@ca.ibm .com>
wrote:

A procedure is bad when _you_ (or those taking over from you) can't read
it anymore. :-)

Those immortal words should be enshrined as the first line of every
prorgamming text published - regardless of language!

--
Will Honea

[N. Shamsundar]

serge wrote:

I open the Command Window and I type:

E:\Program Files\IBM\SQLLI B\BIN>db2 -td@ -vf e:\temp\testall .sql
DB21006E The input command is too long. The maximum length is 131070.

E:\Program Files\IBM\SQLLI B\BIN>

I get the input command is too long. What is this number? The size of the
file
must not exceed 131KB? Because Windows explorer shows the size as
174KB. This 131070 is not number of characters right?
Thank you

put a @ character after the last line of your procedure code, and
compile with the following command:

db2 -td@ -vf <your_procedure _file.sql>

post back if this does'nt help ;-)

131070 = 128K - 2 bytes.

N. Shamsundar
University of Houston