Hi All,
Happy 2021!
I have a CLLE that sporadically is getting an error stating it cannot find
a UDTF function.
I can see that the *LIBL is correct.
I am able to run this program by itself without error if I call it
interactively or submit the job to batch.
The problem only appears when the CLLE is called from within a larger job
stream that is submitted from the job scheduler.
It seems like it must be environment related but other than the *LIBL I
don't know what else could cause this.
Google has pointed me in the direction of a parameter mismatch with SQL
thinking I have overloaded the function, but if that were the case wouldn't
it bomb all the time?
As always, thanks in advance and any help appreciated.
Anyway...here are the relevant pieces.
FUNCTION:
CREATE OR REPLACE FUNCTION FMALIB/IFSDIR(dirname VARCHAR(500))
RETURNS TABLE
(
filename varchar(640),
size decimal(20, 0),
type varchar(10),
create_time timestamp,
access_time timestamp,
modify_time timestamp,
change_time timestamp,
ccsid decimal(5, 0),
owner char(10),
group char(10),
checked_out char(10)
)
EXTERNAL NAME 'FMALIB/IFSDIR(IFSDIR)'
LANGUAGE RPGLE
PARAMETER STYLE DB2SQL
NO SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
NOT FENCED
NO SCRATCHPAD
NO FINAL CALL
DISALLOW PARALLEL
CARDINALITY 1
CLLE:
DCL VAR(&PROCPATH ) TYPE(*CHAR) LEN(1024)
DCL VAR(&RUNSQL ) TYPE(*CHAR) LEN(2048)
DCL VAR(&SQuote ) TYPE(*CHAR) Len(1) Value('''')
CHGVAR VAR(&RUNSQL) VALUE('CREATE TABLE +
QTEMP/IFSDIRO AS (SELECT * FROM +
TABLE(IFSDIR(' *TCAT &SQUOTE *TCAT +
%TRIM(&PROCPATH) *TCAT &SQUOTE *TCAT +
')) as T) WITH DATA')
RUNSQL SQL(&RUNSQL) COMMIT(*NONE) NAMING(*SYS)
JOBLOG:
Message . . . . : 43300 - RUNSQL
SQL('CREATE TABLE QTEMP/IFSDIRO AS (SELECT
* FROM
TABLE(IFSDIR(''/Import/BOP_NHL/PROD/PROCESS/Headers'')) as T) WITH
DATA') COMMIT(*NONE) NAMING(*SYS)
CPD439C Diagnostic 40 01/07/21 10:22:03.031941 QQQVFMT
QSYS *STMT QSQCRTT QSYS
From module . . . . . . . . :
QQQVFMT
From procedure . . . . . . :
QQQSNDPMSG
Statement . . . . . . . . . : 1530
To module . . . . . . . . . :
QSQCRTT
To procedure . . . . . . . :
ADD_TABLE_AS_COLS
Statement . . . . . . . . . : 33292
Thread . . . . : 000008DB
Message . . . . : Function IFSDIR
not found in library *N.
Cause . . . . . : Function IFSDIR
was not found during function resolution
for reason code 1. The reason codes
and their meanings follow: 1 -- No
function could be found with the
name specified. 2 -- A function was found
in library with the name
specified, but required a different
number of parameters than specified.
3 -- A function was found in library
with the name and number
of parameters specified, but parameter 0
had a data type that was not
promotable to the data type of the parameter
defined for the function. A library
name of *N indicates that the current
SQL path was used. Recovery . . . :
Refer to the DB2 for i SQL
programming topic collection in the
Database category in the IBM i
Information Center book,
http://www.ibm.com/systems/i/infocenter/ for more
information on functions and
function resolution. Correct the name,
arguments or path specified for the
function. Try the request again.
(MM) E AM-0960 J OB AMAPJG (AMAPJG) CONFLICTS WITH JOB-QZRCSRVS,
USER-HEATHERS, JOB NO.-80836
midrange.com
