SQL0104N in Command Center - Interactive

Why do I get SQL0104N in UDB 8.1 for Windows Command Center -
Interactive, when I copy-paste-and-execute this function from the
docs?

FWIW I have % defined as the statement terminator, but appending a %
to the input just gives a different error: SQL0007N The character "%"
following "BEGIN-OF-STATEMENT" is not valid.

Here's the function from the docs:

CREATE FUNCTION TAN (X DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SIN(X)/COS(X)

Here's the full error message:

SQL0104N An unexpected token "RETURN SIN(X)/" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<create_view> ".

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.

re: SQL0104N in Command Center - Interactive

Makes no sense.
DB2 seems to swallow everything but the last line.
The % won't make a difference. Command Center will add it for you if
it's not there
What happend is that you added % to the next line and now % is begining
of statement.

Must be something glaringly obvious .. in hindsight.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

re: SQL0104N in Command Center - Interactive

Breck Carter wrote:
[color=blue]
> Why do I get SQL0104N in UDB 8.1 for Windows Command Center -
> Interactive, when I copy-paste-and-execute this function from the
> docs?
>
> FWIW I have % defined as the statement terminator, but appending a %
> to the input just gives a different error: SQL0007N The character "%"
> following "BEGIN-OF-STATEMENT" is not valid.
>
> Here's the function from the docs:
>
> CREATE FUNCTION TAN (X DOUBLE)
> RETURNS DOUBLE
> LANGUAGE SQL
> CONTAINS SQL
> NO EXTERNAL ACTION
> DETERMINISTIC
> RETURN SIN(X)/COS(X)
>
> Here's the full error message:
>
> SQL0104N An unexpected token "RETURN SIN(X)/" was found
> following "BEGIN-OF-STATEMENT". Expected tokens may include:
> "<create_view> ".
>
> Explanation:
>
> A syntax error in the SQL statement was detected at the specified
> token following the text "<text>". The "<text>" field indicates
> the 20 characters of the SQL statement that preceded the token
> that is not valid.[/color]

For what it is worth I tried this in Stinger - after defining statemetn
termination character as '@' I also got -104 in Command Editor.
After quitting and restarting Command Editor - function was created
successfully.
Perhaps you defined statement termination character in the same session
in which yoy tried to define function? I am thinking that this
'settings' or 'property' is acted upon only during tool initialization.
Another suggestion to try is to avoid characters '%' and '_' as
statement termination as those are valid SQL wild characters used in
LIKE predicates.

Jan M. Nelken

re: SQL0104N in Command Center - Interactive

I'm quite certain I have changed teh termination character in flight in
the past. And I use % myself.
One more thought though:
There is a ticky to switch it on as well as a field to enter the
character. If thi ticky is NOT on.
Then DB2 will interprete each line as a separate command.
So what you would see is just the last error.... ?

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

re: SQL0104N in Command Center - Interactive

Thanks for your help!

The Tools Settings - "Use statement termination character" was checked
initially and I left it alone. All I did was change the character from
";" to "%". I also stopped and started Command Center several times.

HOWEVER, this morning when I started Command Center, "Use statement
termination character" was UNchecked. When I checked it, everything
worked OK. I do not know why it was unchecked.

As far as I can tell, you can change the termination character in
flight, but you also have to uncheck and check "Use statement
termination character" or subsequent behavior is strange.

If someone suggests my workstation is under a voodoo spell, I won't
argue :)

Breck

Serge Rielau <srielau@ca.eye-be-em.com> wrote in message news:<cb150s$m9u$1@hanover.torolab.ibm.com>...[color=blue]
> I'm quite certain I have changed teh termination character in flight in
> the past. And I use % myself.
> One more thought though:
> There is a ticky to switch it on as well as a field to enter the
> character. If thi ticky is NOT on.
> Then DB2 will interprete each line as a separate command.
> So what you would see is just the last error.... ?
>
> Cheers
> Serge[/color]