*LIBL not allowed in SQL CREATE FUNCTION

Hi group,

I try to create an SQL user-defined (table) function with the
following statement:

Create Function ILI_RtvInvDet
( Item VarChar ( 15 )
...
, OwnerShip VarChar ( 5 ) )
Returns TABLE
( Item VarChar ( 15 )
.....
, Available dec ( 11,2 ) )
Language RPGLE
Reads SQL Data
External Name '*LIBL/ILI_PRC01(ILI_RTVINVDET)'
Parameter Style DB2SQL
ScratchPad 10
No Final Call
Disallow Parallel
No External Action
Deterministic
Not Fenced
Cardinality 10;

The problem is in the External Name. (Note that this element refers to
a procedure within a service program.)

When I execute a RUNSQLSTM on the source member containing this
statement, it results in the following error:

SQL0113 30 18 Position 17 Name *LIBL not allowed.

I thought it should be possible to use *LIBL instead of hard-coding a
library name (or actually a schema name). The problem also occurs when
I remove the procedure name, i.e. when I specify *LIBL/ILI_PRC01.

Any suggestions?

Ewout

Why to specify *LIBL/ at all?
Just remove it

Birgitta

On Jun 11, 4:29 pm, "Hau...@sss-software.de" <Hau...@sss-software.de>
wrote:[color=blue]
> Why to specify *LIBL/ at all?
> Just remove it
>
> Birgitta[/color]

Doesn't work. It seems the (service) program name should always be
qualified.

Ewout

On Jun 12, 2:11 am, Ewout <ewout.bo...@home.nl> wrote:[color=blue]
> On Jun 11, 4:29 pm, "Hau...@sss-software.de" <Hau...@sss-software.de>
> wrote:
>[color=green]
> > Why to specify *LIBL/ at all?
> > Just remove it[/color]
>[color=green]
> > Birgitta[/color]
>
> Doesn't work. It seems the (service) program name should always be
> qualified.
>
> Ewout[/color]

There is a NAMING keyword on RUNSQLSTM. Make sure it's set to *SYS
before you run it. SQL naming convention does not honor library list
searching, but *SYS does.

On Jun 12, 4:55 pm, Elvis <budim...@hotmail.com> wrote:
[color=blue]
> There is a NAMING keyword on RUNSQLSTM. Make sure it's set to *SYS
> before you run it. SQL naming convention does not honor library list
> searching, but *SYS does.[/color]

Alas, this keyword did already have the value *SYS...

Ewout

On Jun 11, 7:18 am, Ewout <ewout.bo...@home.nl> wrote:[color=blue]
> Hi group,
>
> I try to create an SQL user-defined (table) function with the
> following statement:
>
> Create Function ILI_RtvInvDet
> ( Item VarChar ( 15 )
> ...
> , OwnerShip VarChar ( 5 ) )
> Returns TABLE
> ( Item VarChar ( 15 )
> .....
> , Available dec ( 11,2 ) )
> Language RPGLE
> Reads SQL Data
> External Name '*LIBL/ILI_PRC01(ILI_RTVINVDET)'
> Parameter Style DB2SQL
> ScratchPad 10
> No Final Call
> Disallow Parallel
> No External Action
> Deterministic
> Not Fenced
> Cardinality 10;
>
> The problem is in the External Name. (Note that this element refers to
> a procedure within a service program.)
>
> When I execute a RUNSQLSTM on the source member containing this
> statement, it results in the following error:
>
> SQL0113 30 18 Position 17 Name *LIBL not allowed.
>
> I thought it should be possible to use *LIBL instead of hard-coding a
> library name (or actually a schema name). The problem also occurs when
> I remove the procedure name, i.e. when I specify *LIBL/ILI_PRC01.
>
> Any suggestions?[/color]

I tried creating the procedure without any qualification for the
external name, and got this error message:

The external program name specified on a DECLARE
PROCEDURE, CREATE PROCEDURE, or CREATE
FUNCTION statement is not valid for the routine or the
language specified.
-- The external program name for a procedure or function
must be of the form 'library-name/program-name' or
'library-name/program-name(entry-point-name)'.

There's nothing in the SQL Reference (V5R3) that explicitly suggests
*LIBL is not permitted. However, in the one example they provide, the
service program name is qualified with an actual library name.

The manual does seem to suggest something a little contradictory.

If the naming option is *SYS and the name is not qualified:

* The current path will be used to search for the program
or service program at the time the function is invoked.
* *LIBL will be used to search for the program or service
program at the time grants or revokes are performed
on the function.

The validity of the name is checked at the application server.
If the format of the name is not correct, an error is returned.

If external-program-name is not specified, the external
program name is assumed to be the same as the function
name.

The program, service program, or java class need not exist
at the time the function is created, but it must exist at the
time the function is invoked.

In other words, regardless of where you say the program resides at the
time you create the function, the manual says that if you use *SYS
naming convention, the external program name will be resolved by the
current path.

I verified that not only does the program or service program not need
to exist at the time the function is created, but the library used to
qualify it also doesn't need to exist.