Re: Puzzling V7R1 UDTF not found error

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Birgitta is on the right track...

IIRC....7.2 enhanced the systems ability to implicitly cast values when
needed.

Which would explain why it's working on 7.2 but not on 7.1 without the
explicit cast.

Charles

On Thu, Aug 2, 2018 at 1:36 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> 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;
>
> --
>
> Regards
> Evan Harris
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> unsubscribe,
> or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> moment to review the archives at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> http://amzn.to/2dEadiD
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD