Problem with DB2 UDB SQL Script

[Shaun]

I am writing a SQL script and am having problems with the continuation
of columns named in the query. When the columns are listed on the same
line, the query works but if the columns are listed on sequential
lines, I get an error.
Example:

Select col1, col2, col3 from TabA where ..... works O.K.

Select col1,
col2,
col3
from TabA..... doesn't work and gives an error "col2 not expected"

I suspect an option needs to be set somewhere but have not been able
to find anything in the manuals so far.

I am using DB2 7.2 and AIX 4.3 on an RS/6000 platform.

Any help is appreciated.

Shaun

[Jerry Stuckle]

Shaun,

You need to specify the statement termination character to the DB2
command line environment, i.e.

db2 -td script.ddl

sets the default termination character as a semicolon. If you have
nested SQL statements, you will need to use a different termination
character, i.e.

db2 -td@ script.ddl

sets the statement terminator as '@'. You can then use a ';' as
required within the statements, and a '@' to terminate the statement.

--
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
====================================

[Serge Rielau]

Hi,

If an SQL statement crosses lines you need to use a termination
character.

"db2 -t" will enable ';' as termination character.
"db2 -td<somecharacter>" will enable <somecharacter> as termination
character.
The first setting is teh default for the GUI tools. The later is used
when writing SQL PL (sich as CREATE TRIGGER, ...) because those contain
';' in their bodies.

See the Command Reference Manual for details.
Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada

[paul....@ukk.ibmm.comm]

Serge, are you sure that ';' is the default in the GUI tools?

Last time I installed DB2 I had to change it to ';' myself as the default
was 'carrage-return'.
Makes life confusing for newbies .

Paul Vernon

[Serge Rielau]

Uhm.... no I'm not sure ....

[Fan Ruo Xin]

Serge Rielau wrote:

> Hi,
>
> If an SQL statement crosses lines you need to use a termination
> character.
>
> "db2 -t" will enable ';' as termination character.
> "db2 -td<somecharacter>" will enable <somecharacter> as termination
> character.
> The first setting is teh default for the GUI tools. The later is used
> when writing SQL PL (sich as CREATE TRIGGER, ...) because those contain
> ';' in their bodies.
>

I run SQL PL (the sample from manual P250) use this way
db2 -td@ -vf script.db2
DB21007E End of file reached while reading the command.
"db2 -td@" didn't enable "@" as termination.

[Jerry Stuckle]

Fan,

It's always worked for me...

Did you have your SQL statement(s) terminated with an '@'? Can you post
your SQL?

--

[Fan Ruo Xin]

Jerry Stuckle wrote:

> Fan,
>
> It's always worked for me...
>
> Did you have your SQL statement(s) terminated with an '@'? Can you post
> your SQL?

Thank you, Jerry. It is a sample from the "Application Development Guide"
CREATE PROCEDURE UPDATE_SALARY_IF
(IN employee_number CHAR(6), IN rating SMALLINT)
LANGUAGE SQL
BEGIN
DECLARE not_found CONDITION FOR SQLSTATE ‘02000’;
DECLARE EXIT HANDLER FOR not_found
SIGNAL SQLSTATE ‘20000’ SET MESSAGE_TEXT =’Employee not found’;
IF (rating =1)
THEN UPDATE employee
SET salary =salary *1.10, bonus =1000
WHERE empno =employee_number;
ELSEIF (rating =2)
THEN UPDATE employee
SET salary =salary *1.05, bonus =500
WHERE empno =employee_number;
ELSE UPDATE employee
SET salary =salary *1.03, bonus =0
WHERE empno =employee_number;
END IF;
END
@

[Serge Rielau]

Hi Fan,

I don't know what P250 is. Can you post the stored procedure?
Does it indeed end with a '@'?

[Fan Ruo Xin]

Thank you Serge!
I am sorry I didn't write it clearly. P250 here I mean "Application
Developer Guide" Page 250. I already post it under Jerry's reply.
I believe it should work. But why it doesn't for me.
Regards,

[Serge Rielau]

Very odd.
Lets figure this out incrementally (maybe your shell plays a trick?):
1) db2 -td@
values 1@
2) db2 -td!
values 1!

3) db2 -td! -vf test.clp
with
test.clp: values 1!

...

[Jerry Stuckle]

Fan,

The sql works fine for me with the command

db2 -td@ -vf testfile.ddl

The only problem was I couldn't compile it because I don't have the
system I tested on set up with a compiler.

I did have to change a couple of things - probably because of language
differences, a couple of characters came out differently:

The two statements I changed were:

DECLARE not_found CONDITION FOR SQLSTATE ‘02000’;

SIGNAL SQLSTATE ‘20000’ SET MESSAGE_TEXT =’Employee not found’;

Changed to ' (single quote) around the 02000; on my system it shows up
as a graphic. Same with the quotes around '20000' and 'Employee not
found' in the second statement.

Which brings up another question - are you using an ascii text editor to
edit this, or a word processor and saving as a text file? If the
latter, it would explain the differences - and might be causing your
problem.

[Fan Ruo Xin]

Thanks a lot, Jerry and Serge.
Now I figured it out. You see I made a stupid action. I copy and paste that script
to a word file. I just realized this, and paste it to a text file. Everything is
fine!
Best Regards,

Rosaline Fan