I don't know if it's just me, but DB2 seems to have the worst syntatic
parser ever created!

Even after triple checking the documentation online, for the syntax of
the statements the CLP seems to be unable to parse the simplest of
statemets!

For example, I would very much like to have this piece of work to
work:

IF EXISTS ( Select 'DELETE'
from sysibm.routines
where specific_schema = 'NHM'
and ROUTINE_TYPE = 'FUNCTION'
and ROUTINE_NAME = 'TESTCONSTRUCTS')
THEN DROP FUNCTION TESTCONSTRUCTS
END IF@

The parser messages are ambiguous as always:

Erros msg: An unexpected token "IF EXISTS ( Select 'DELETE'" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<space>


This wonderful parser is so user friendly, it also seem to be able to
parse better the previous code, if it's surrounded by a BEGIN
Atomic ... END@ statement;
in this last case, the parser only fails when it's faced with the END
IF statement.

I am farily used to both PL/SQL and TSQL and can say without a doubt,
that i've never seen any parser this bad! Not only does CLP force a
person to introduce ending statement characters, not even there own
offical SQL PL syntax is parsable.

Any help?

On 30 Set, 14:30, NHM <Nuno.GodinhoMa...@gmail.comwrote:

I don't know if it's just me, but DB2 seems to have the worst syntatic
parser ever created!
>
Even after triple checking the documentation online, for the syntax of
the statements the CLP seems to be unable to parse the simplest of
statemets!
>
For example, I would very much like to have this piece of work to
work:
>
IF EXISTS ( Select 'DELETE'
* * * * from sysibm.routines
* * * * where specific_schema = 'NHM'
* * * * and ROUTINE_TYPE = 'FUNCTION'
* * * * and ROUTINE_NAME = 'TESTCONSTRUCTS')
THEN DROP FUNCTION TESTCONSTRUCTS
END IF@
>
The parser messages are ambiguous as always:
>
Erros msg: An unexpected token "IF EXISTS ( Select 'DELETE'" was
found following "BEGIN-OF-STATEMENT". *Expected tokens may include:
"<space>
>
This wonderful parser is so user friendly, it also seem to be able to
parse better the previous code, if it's surrounded by a BEGIN
Atomic ... END@ statement;
in this last case, the parser only fails when it's faced with the END
IF statement.
>
I am farily used to both PL/SQL and TSQL and can say without a doubt,
that i've never seen any parser this bad! Not only does CLP force a
person to introduce ending statement characters, not even there own
offical SQL PL syntax is parsable.
>
Any help?

On further note I've spotted that after the THEN token, the SQL
statments have to be preceded by a semicolon; a little small detail
hard to notice on the syntax page. Still...
It's not working:

SQL0104N An unexpected token "DROP FUNCTION" was found following
"ESTCONSTRUCTS')
THEN". Expected tokens may include: "<signal_stmt_head>

Code executed:
BEGIN ATOMIC
IF EXISTS ( Select 'DELETE'
from sysibm.routines
where specific_schema = 'NHM'
and ROUTINE_TYPE = 'FUNCTION'
and ROUTINE_NAME = 'TESTCONSTRUCTS')
THEN DROP FUNCTION TESTCONSTRUCTS;
END IF;
END@

And why "the hell", can't a person put an IF token after a BEGIN
Statement token, as the CLP likes to point out?
Does it have to be inside, a function, procedure or compount statemnt?
Again the documentation tells nothing of the sort.
I can only say: i hate SQL PL, so alike every other procedure
language, and at the same time so much worse!

NHM wrote:

On 30 Set, 14:30, NHM <Nuno.GodinhoMa...@gmail.comwrote:

>
On further note I've spotted that after the THEN token, the SQL
statments have to be preceded by a semicolon; a little small detail
hard to notice on the syntax page. Still...
It's not working:
>
SQL0104N An unexpected token "DROP FUNCTION" was found following
"ESTCONSTRUCTS')
THEN". Expected tokens may include: "<signal_stmt_head>
>
Code executed:
BEGIN ATOMIC
IF EXISTS ( Select 'DELETE'
from sysibm.routines
where specific_schema = 'NHM'
and ROUTINE_TYPE = 'FUNCTION'
and ROUTINE_NAME = 'TESTCONSTRUCTS')
THEN DROP FUNCTION TESTCONSTRUCTS;
END IF;
END@
>
And why "the hell", can't a person put an IF token after a BEGIN
Statement token, as the CLP likes to point out?
Does it have to be inside, a function, procedure or compount statemnt?
Again the documentation tells nothing of the sort.
I can only say: i hate SQL PL, so alike every other procedure
language, and at the same time so much worse!

I will not discuss whether this is obvious, intuitive or not, but keep
in mind that DROP <objectis an administrative command - not SQL element.

This should work:

db2 -td@ -vf test.sql
BEGIN ATOMIC
IF EXISTS ( Select 'DELETE'
from sysibm.routines
where specific_schema = 'NHM'
and ROUTINE_TYPE = 'FUNCTION'
and ROUTINE_NAME = 'TESTCONSTRUCTS')
THEN CALL ADMIN_CMD('DROP FUNCTION TESTCONSTRUCTS');
END IF;
END
DB20000I The SQL command completed successfully.

Jan M. Nelken

SQL0104N An unexpected token "DROP FUNCTION" was found following

"ESTCONSTRUCTS')
THEN". Expected tokens may include: "<signal_stmt_head>

Well the parser is absolutely right here. DROP FUNCTION is not supported
in inline SQL PL (Dynamic compound statement)
dynamic compounds are meant for quick ETL kind of stuff. It's not the
full blown SQL PL.

W.r.t. the other comments send me a note, I may be able to help.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab