RE: Puzzling V7R1 UDTF not found error

What happens if you explicitly cast 'Q2130000003' to CHAR(10).

SELECT S1.* FROM TABLE(EJHLIB.EXTPFRCOLCPU(Cast('Q213000003' as Char(10))
S1;

UDFs and UDTFs can be overloaded, i.e. multiple UD(T)F with the same name,
but different parameter definitions can coexist in the same schema.
CHAR and VARCHAR are different data types for SQL, an a literal is
interpreted as VARCHAR, so the UD(T)F with an VARCHAR parameter is not found
in the library.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of Evan Harris
Sent: Donnerstag, 2. August 2018 01:37
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Puzzling V7R1 UDTF not found error

Hi all

I have been experimenting with UDTFs for a little while now and created a
UDTF to extract performance data on a system running V7R2, which works fine.
As far as all the documentation I have read goes the same UDTF should work
on V7R1 but when I attempt to run the UDTF on a V7R1 system it returns the
following error(s):

SELECT S1.* FROM TABLE(EJHLIB.EXTPFRCOLCPU('Q213000003')) S1;

SQL State: 42704
Vendor Code: -204
Message: [SQL0204] EXTPFRCOLCPU in EJHLIB type *N not found ....

The Q213000003 value is the performance member name I want to extract and
summarize

The UDTF creation runs perfectly fine and no errors are returned.

Using the Schema tool I can see the EXTPFRCOLCPU function in the EJHLIB
library; I can even retrieve the SQL from the function and it matches the
SQL i used to create the function so I am at a loss as to why it is not
found when running from SQL script window. Checking the QZDASOINIT job for
kicks also shows the same message (as expected)

I realise it might not work at V7R1 (planning to upgrade this particular box
in the next month or so) but none of the documentation I have read indicates
there was any problem doing this at V7R1 and some simpler efforts I tried
work fine, so I would just like to check that:
- V7R1 is indeed the issue
- What exactly the problem is for future reference
- if it's not V7R1 and is indeed my SQL (more than likely) what am I doing
wrong

The code is below in case someone wants to critique or reproduce it.


CREATE OR REPLACE FUNCTION EJHLIB.EXTPFRCOLCPU(MbrName Char(10)) RETURNS
TABLE (SYSNAME VARCHAR(10), INTNUM INT, CSDTETIM VARCHAR(16), DDMMYY
VARCHAR(8), HHMM VARCHAR(6), LPAR_CPU_Utilization FLOAT,
Dispatched_CPU_Time FLOAT, CPU_Queuing_Time FLOAT, Disk_Time FLOAT,
Journal_Time FLOAT, OS_Contention_Time FLOAT, Lock_Contention_Time FLOAT,
Ineligible_Waits_Time FLOAT
)
LANGUAGE SQL
NO EXTERNAL ACTION
MODIFIES SQL DATA
DISALLOW PARALLEL
CARDINALITY 150
SET OPTION COMMIT=*NONE

BEGIN

DECLARE STMT CHAR(1000);

SET STMT = 'CREATE OR REPLACE ALIAS EJHLIB.APMISUM_ALIAS FOR
QPFRDATA.QAPMISUM(' CONCAT MbrName CONCAT ')'; PREPARE S1 FROM STMT; EXECUTE
S1;

SET STMT = 'CREATE OR REPLACE ALIAS EJHLIB.APMSYSTEM_ALIAS FOR
QPFRDATA.QAPMSYSTEM(' CONCAT MbrName CONCAT ')'; PREPARE S2 FROM STMT;
EXECUTE S2;

RETURN

SELECT
CURRENT_SERVER as SYSNAME,
QSY.INTNUM,
QSY.CSDTETIM
AS CSDTETIM,
SUBSTR(dtetim,5,2) || '/' || SUBSTR(dtetim,3,2) || '/' ||
SUBSTR(dtetim,1,2) AS DDMMYY,
SUBSTR(dtetim,7,2) || ':' || SUBSTR(dtetim,9,2)
AS HHMM,
MAX(PCTSYSCPU)
AS LPAR_CPU_Utilization,
ROUND(SUM(TIME01) * .000001, 2)
AS Dispatched_CPU_Time,
ROUND(SUM(TIME02) * .000001, 2)
AS CPU_Queuing_Time,
ROUND(SUM(TIME05 + TIME06 + TIME07 + TIME08 + TIME09 + TIME10) *
.000001, 2) AS Disk_Time,
ROUND(SUM(TIME11) * .000001, 2)
AS Journal_Time,
ROUND(SUM(TIME14 + TIME15 + TIME19 + TIME32) * .000001, 2)
AS OS_Contention_Time,
ROUND(SUM(TIME16 + TIME17) * .000001, 2)
AS Lock_Contention_Time,
ROUND(SUM(TIME18) * .000001, 2)
AS Ineligible_Waits_Time
FROM
(
SELECT
DTECEN || DTETIM AS CSDTETIM, DOUBLE(JWTM01) AS
TIME01, DOUBLE(JWTM02) AS TIME02, DOUBLE(JWTM05) AS TIME05, DOUBLE(JWTM06)
AS TIME06, DOUBLE(JWTM07) AS TIME07,
DOUBLE(JWTM08) AS TIME08, DOUBLE(JWTM09) AS TIME09,
DOUBLE(JWTM10) AS TIME10, DOUBLE(JWTM11) AS TIME11, DOUBLE(JWTM14) AS
TIME14, DOUBLE(JWTM15) AS TIME15,
DOUBLE(JWTM16) AS TIME16, DOUBLE(JWTM17) AS TIME17,
DOUBLE(JWTM18) AS TIME18, DOUBLE(JWTM19) AS TIME19, DOUBLE(JWTM32) AS TIME32
FROM
EJHLIB.APMISUM_ALIAS
) WAITS
INNER JOIN
(
SELECT
INTNUM, DTECEN || DTETIM AS CSDTETIM, INTSEC,
DEC(SYSPTU/DOUBLE(SYSCTA) * 100, 28, 2) AS PCTSYSCPU, DTETIM AS DTETIM,
DTECEN AS DTECEN
FROM
EJHLIB.APMSYSTEM_ALIAS
) QSY
ON
QSY.CSDTETIM = WAITS.CSDTETIM
GROUP BY
QSY.INTNUM,
QSY.CSDTETIM,
DTETIM,
DTECEN
ORDER BY
CSDTETIM;

END;