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"].
midrange.com
