Re: SQL0444 work around?

[CRPence <CRPbottle@xxxxxxxxx>]

On 18 Jul 2013 09:50, fbocch2595@xxxxxxx wrote:
> when running stored procedures I'm getting the SQL0444 msg and I
> was wondering if anyone had a way to avoid having to qualify the
> library?
>
> SQL0444 - External program &A in &B not found (DB2 UDB for AS/400
> SQL): The SQL0444 is generated on an execute or execute direct when
> the database server is unable to locate the program. Remember that
> SQL does not perform a library list search.

  When using *SYS [vs *SQL] naming, the default PATH is *LIBL.  The 
default non-executable search list [e.g. to find a TABLE] is also *LIBL.

> The stored procedure name (the procedure name parameter that is used
> on the CREATE PROCEDURE statement) must be in the default collection.

  I infer that is a statement of the requirement, not an [misleading] 
implication of an impression how the SQL functions.

  The procedure can be created with a qualified name [explicitly naming 
the collection in which the SP is considered to reside], without having 
to qualify the EXTERNAL NAME.  As well the /default collection/ can be 
left unspecified [no SET SCHEMA, nor DFTRDBCOL, nor any synonymous 
specification], and along with the use of system-naming for which the 
CURRENT SCHEMA will be *LIBL, thus the procedure will be created into 
the *CURLIB.

  For example, given I have established NAMING=*SYS and a program named 
CRP2/DSPOVR exists [with no parameters] and my user library list 
consists of only the *CURLIB of CRP1, consider the following SQL script 
and the described effects:

   create procedure dovr () specific dovr
   external name        dspovr /* no lib specified */
   parameter style general
   ; -- Routine DOVR was created, but cannot be saved and restored.
   ; -- the SP /created in/ library CRP1; see later SELECT output
   call dovr
   ; -- External program DSPOVR in *LIBL not found. {SQL0444}
   call qcmdexc ('ADDLIBLE CRP2 *LAST', 0000000019.00000)
   ; -- CALL statement complete.
   call dovr
   ; -- CALL statement complete.
   SELECT char(external_name, 10) as extname
        , char(sql_path, 15) as path
        , char(current path, 15)
        , char(current schema, 10)
   FROM sysprocs
   WHERE SPECIFIC_SCHEMA = 'CRP1' and SPECIFIC_NAME like '%DOVR%'
   ; -- report follows
   EXTNAME          PATH             CHAR             CHAR
   *LIBL/DSPOVR     -                *LIBL            *LIBL
   ********  End of data  ********
   ;
   set path qsys2,crp1
   ; -- SET PATH statement complete.
   call dovr
   ; -- CALL statement complete.
   SELECT char(external_name, 10) as extname
        , char(sql_path, 15) as path
        , char(current path, 15)
        , char(current schema, 10)
   FROM sysprocs
   WHERE SPECIFIC_SCHEMA = 'CRP1' and SPECIFIC_NAME like '%DOVR%'
   ; -- report follows
   EXTNAME          PATH             CHAR             CHAR
   *LIBL/DSPOVR     -                "QSYS2","CRP1"   *LIBL
   ********  End of data  ********
   ;

  The second CALL is included only to emphasize how the PATH 
established by the caller does not hinder the effects of the EXTNAME 
defined as *LIBL/DSPOVR in the SP recorded in the catalog.  Note that 
because the PATH is no longer the default of *LIBL, the library CRP1 had 
to be included in the explicit list, so the unqualified call of the DOVR 
procedure could be located [using "function resolution"].