*LIBL not allowed in SQL CREATE FUNCTION - IBM AS400

This is a discussion on *LIBL not allowed in SQL CREATE FUNCTION - IBM AS400 ; 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 ) ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: *LIBL not allowed in SQL CREATE FUNCTION

  1. *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


  2. Re: *LIBL not allowed in SQL CREATE FUNCTION

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

    Birgitta


  3. Re: *LIBL not allowed in SQL CREATE FUNCTION

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


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

    Ewout


  4. Re: *LIBL not allowed in SQL CREATE FUNCTION

    On Jun 12, 2:11 am, Ewout wrote:
    > On Jun 11, 4:29 pm, "Hau...@sss-software.de"
    > wrote:
    >
    > > Why to specify *LIBL/ at all?
    > > Just remove it

    >
    > > Birgitta

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


    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.


  5. Re: *LIBL not allowed in SQL CREATE FUNCTION

    On Jun 12, 4:55 pm, Elvis wrote:

    > 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.


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

    Ewout


  6. Re: *LIBL not allowed in SQL CREATE FUNCTION

    On Jun 11, 7:18 am, Ewout wrote:
    > 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?


    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.


+ Reply to Thread