473,556 Members | 2,558 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Compile problems

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
Nov 12 '05 #1
9 3740
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
Nov 12 '05 #2
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

Nov 12 '05 #3
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

Nov 12 '05 #4
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 ;-)

Nov 12 '05 #5
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
Nov 12 '05 #6
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

Nov 12 '05 #7
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
Nov 12 '05 #8
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
Nov 12 '05 #9
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
Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1955
by: lmckaha | last post by:
Hi, Mysql version: 3.23.49 Solaris version: 2.7 gcc compiler version: 2.95.2 Python : 2.2.2 I'm evaluating the C and C++ API to decide which one to bye but I have many troubles.
0
2620
by: Jordan Willms | last post by:
My xsl stylesheet is as simple as follows: <?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet xmlns:ims="http://www.imsglobal.org/xsd/imsmd_v1p2" xmlns="http://ltsc.ieee.org/xsd/LOMv1p0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output method="xml" version="1.0" encoding="ISO-8859-1" indent="yes"/> <!--...
5
3798
by: Brice Prunier | last post by:
Here under 4 schemas i'm working with ( it may be long: sorry...) The context is the following : Resident.xsd imports Person.xsd and includes Common.xsd ( anonimous schema: no TargetNamespace ) Person.xsd includes Common-Naming.xsd ( anonimous schemas ) Common-Naming.xsd includes common.xsd ( both are anonimous schemas ) Compilation of...
10
19698
by: Chris LaJoie | last post by:
Our company has been developing a program in C# for some time now, and we haven't had any problems with it, but just last night something cropped up that has me, and everyone else, stumped. I have a struct that contains several different types of data. This struct is used throuout the program. Now, when I compile, I get 6 errors, all of...
2
3298
by: Gustavo | last post by:
After updating Windows 2000 I began to get a weird compile error message: Deleting intermediate files and output files for project 'pp - Win32 Debug'. --------------------Configuration: pp - Win32 Debug-------- ------------ Compiling... pp.cpp c:\program files\microsoft visual studio\vc98
2
1265
by: Tim | last post by:
hi, I'm wondering if anyone else has seen this. I've just built and published a web site on my developer machine, then copied the precompiled web to a staging / test machine. The code runs fine on my machine. On the staging machine I get a compile error: Compiler Error Message: CS0012: The type 'ASP....is defined in an assembly that is not...
7
1676
by: Arne | last post by:
I am porting a website to ASP.net 2.0. Temporarily I compile with Visual Studio 2003 and deploying to ASP.net 2.0. How do I compile my website under ASP.Net 2.0? I know it can compile each page as I touch it. During the beta there used to be something like mywebsite.com/compile.??? that you could run. Does it still exist?
17
4643
by: rdemyan via AccessMonster.com | last post by:
I have a launcher program that creates the shortcut to open my application using the Shell command. On the form I have a decompile checkbox that I can conveniently use to decompile my program. I follow the protocol described by Allen Browne. Opening my program in decompile mode has always functioned flawlessly. But I recently converted...
15
4784
by: steve yee | last post by:
i want to detect if the compile is 32 bits or 64 bits in the source code itself. so different code are compiled respectively. how to do this?
27
5579
by: CodeMonk3y | last post by:
gotta question on sizeof keyword does the sizeof keyword calcuates the size at compile time or run time ?? -- Posted on news://freenews.netfront.net - Complaints to news@netfront.net --
0
7624
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7547
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7589
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7907
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5452
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5171
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3576
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2040
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1161
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.