Does the batch job user have permission to the UDTF function objects?
Chris Bipes
Director of Information Services
CrossCheck, Inc.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Stephen Coyle
Sent: Friday, January 8, 2021 11:20 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: UDTF SQL Function not Found
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
