Create function error DB2 UDB v8.1.5 Linux

[Bob Stearns]

I don't understand the following error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "CREATE FUNCTION NALF_PREFIX(tattoo
varchar" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". LINE NUMBER=8. SQLSTATE=42601

which I received when doing

db2 -td@ -vf $1.db2
on the file:

-- vim: set ai ts=4 sw=4 :
connect to animals@

set schema is3@

-- drop function nalf_prefix@

CREATE FUNCTION nalf_prefix(tattoo varchar(50))
returns varchar(50)
language sql
deterministic
no external action

begin
declare result varchar(50);
result = replace(upcase(tattoo,' ',''));
result = substr(result,1,length(result)-1);
result = replace(translate(tattoo,'','0123456789'),' ','');
return result;
end@

Any help would be appreciated. I thought I followed the example from the
'book' (IBM documentation PDF) pretty faithfully.

[Jan M. Nelken]

Bob Stearns wrote:

I don't understand the following error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "CREATE FUNCTION NALF_PREFIX(tattoo
varchar" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". LINE NUMBER=8. SQLSTATE=42601

Correcting syntax errors renders function usable:

CREATE FUNCTION nalf_prefix(tattoo varchar(50))
returns varchar(50)
language sql
deterministic
no external action

begin atomic
declare result varchar(50);
set result = replace(upper(tattoo),' ','');
set result = substr(result,1,length(result)-1);
set result = replace(translate(tattoo,'','0123456789'),' ','');
return result;
end @
E:\>db2 values nalf_prefix('a1b2c3 d4 e5')

1
--------------------------------------------------
abcde

1 record(s) selected.
Jan M. Nelken

[Bob Stearns]

Jan M. Nelken wrote:

Bob Stearns wrote:

I don't understand the following error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "CREATE FUNCTION NALF_PREFIX(tattoo
varchar" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". LINE NUMBER=8. SQLSTATE=42601

Correcting syntax errors renders function usable:

CREATE FUNCTION nalf_prefix(tattoo varchar(50))
returns varchar(50)
language sql
deterministic
no external action

begin atomic
declare result varchar(50);
set result = replace(upper(tattoo),' ','');
set result = substr(result,1,length(result)-1);
set result = replace(translate(tattoo,'','0123456789'),' ','');
return result;
end @
E:\>db2 values nalf_prefix('a1b2c3 d4 e5')

1
--------------------------------------------------
abcde

1 record(s) selected.
Jan M. Nelken

Thank you. The syntax diagram of the BEGIN statement (Page 767 of SQL
Reference Vol. 1) indicates that the ATOMIC clause is optional, at least
that is how I read it.

[Serge Rielau]

Bob Stearns wrote:

I don't understand the following error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "CREATE FUNCTION NALF_PREFIX(tattoo
varchar" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". LINE NUMBER=8. SQLSTATE=42601

which I received when doing

db2 -td@ -vf $1.db2
on the file:

-- vim: set ai ts=4 sw=4 :
connect to animals@

set schema is3@

-- drop function nalf_prefix@

CREATE FUNCTION nalf_prefix(tattoo varchar(50))
returns varchar(50)
language sql
deterministic
no external action

begin
declare result varchar(50);
result = replace(upcase(tattoo,' ',''));
result = substr(result,1,length(result)-1);
result = replace(translate(tattoo,'','0123456789'),' ','');
return result;
end@

Any help would be appreciated. I thought I followed the example from the
'book' (IBM documentation PDF) pretty faithfully.

http://publib.boulder.ibm.com/infoce...n/r0004240.htm
One word: ATOMIC

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

[Serge Rielau]

Bob Stearns wrote:

Jan M. Nelken wrote:

Bob Stearns wrote:

I don't understand the following error:

DB21034E The command was processed as an SQL statement because it
was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "CREATE FUNCTION NALF_PREFIX(tattoo
varchar" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". LINE NUMBER=8. SQLSTATE=42601

Correcting syntax errors renders function usable:

CREATE FUNCTION nalf_prefix(tattoo varchar(50))
returns varchar(50)
language sql
deterministic
no external action

begin atomic
declare result varchar(50);
set result = replace(upper(tattoo),' ','');
set result = substr(result,1,length(result)-1);
set result = replace(translate(tattoo,'','0123456789'),' ','');
return result;
end @
E:\>db2 values nalf_prefix('a1b2c3 d4 e5')

1
--------------------------------------------------
abcde

1 record(s) selected.
Jan M. Nelken

Thank you. The syntax diagram of the BEGIN statement (Page 767 of SQL
Reference Vol. 1) indicates that the ATOMIC clause is optional, at least
that is how I read it.

That's the wrong statement.
Only SQL Procedures use the regular compound statement.
Triggers and functions use the dynamic compound statement (see my
earlier URL).

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

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

I don't understand the following error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "CREATE FUNCTION NALF_PREFIX(tattoo
varchar" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". LINE NUMBER=8. SQLSTATE=42601

which I received when doing

db2 -td@ -vf $1.db2
on the file:

-- vim: set ai ts=4 sw=4 :
connect to animals@

set schema is3@

-- drop function nalf_prefix@

CREATE FUNCTION nalf_prefix(tattoo varchar(50))
returns varchar(50)
language sql
deterministic
no external action

begin
declare result varchar(50);
result = replace(upcase(tattoo,' ',''));
result = substr(result,1,length(result)-1);
result = replace(translate(tattoo,'','0123456789'),' ','');
return result;
end@

Any help would be appreciated. I thought I followed the example from
the 'book' (IBM documentation PDF) pretty faithfully.

http://publib.boulder.ibm.com/infoce...n/r0004240.htm

One word: ATOMIC

Cheers
Serge

The update documentation differs from the base; I should have looked
there before complaining (I do have it bookmarked). Sorry.