Solved

UDF in MQT definition

Posted on 2008-10-06
3
770 Views
Last Modified: 2013-12-06
Can an MQT definition include a UDF value.
Like this ...
-----------------------------------------
CREATE TABLE MyLIB.MyMQT
(fldA, fldB, fldC)
AS
(
    SELECT
        T1.X as fldA,
        MyLib.MyUDF(T1.Y) as fldB,
        T1.Z as fldC
    FROM MyLib.Table01 T1
)
DATA INITIALLY IMMEDIATE
REFRESH DEFERRED
MAINTAINED BY USER
ENABLE QUERY OPTIMIZATION;
-----------------
    I get error ...
    SQL State: 428EC
    Vendor Code: -20058
    Message: [SQ20058] Subselect for materialized query table SALINDMQ0 not valid for reason code 6. Cause . . . . . :
        6 -- Refers to a function that has an external action or that is not deterministic
-----------------
Here's the function definition ....
-----------------
CREATE FUNCTION MyLIB.MyUDF (
     PARAM1 VARCHAR(12) )
     RETURNS NUMERIC(10, 2)  
     LANGUAGE SQL
     SPECIFIC MyLIB.MyUDF
     DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
     DISALLOW PARALLEL
     BEGIN
     DECLARE RET NUMERIC ( 10 , 2 ) ;
     SELECT SUM ( TQ . MyVal )
          INTO RET
          FROM MyLib.TablQQQQ TQ
          WHERE TQ.LookUp = PARAM1 ;
     RETURN RET ;
END  ;
0
Comment
Question by:volking
3 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22656949
the udf has to be deterministic
when you create the udf use the determenistic keywork
deterministic means that every time you pass the same parameters the function will return the same answer
for example - substring is deterministic
function such as getdate() (which is not in db2 but you can guess what it does) is not deterministic

you can only use deterministic udf in your mqt definition
0
 
LVL 5

Author Comment

by:volking
ID: 22658953
@Momi
But I do declare UDF as Deterministic ... don't I?

CREATE FUNCTION MyLIB.MyUDF (
     PARAM1 VARCHAR(12) )
     RETURNS NUMERIC(10, 2)  
     LANGUAGE SQL
     SPECIFIC MyLIB.MyUDF
->     DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
     DISALLOW PARALLEL
     BEGIN ...........
0
 
LVL 27

Accepted Solution

by:
tliotta earned 500 total points
ID: 22664603
volking:

You do "declare" it as deterministic, but it isn't deterministic. It's perfectly possible for the result value to change every time the function is called with the same PARAM1 value. I.e., the PARAM1 value does NOT result in a deterministic value because the internal SELECT can return a different SUM ( TQ . MyVal ) as the rows in MyLib.TablQQQQ are changed.

Your declaration contradicts the coding in the UDF. Actually, I'm surprised you didn't get at least a warning about it when you created the UDF.

Tom
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally Windows/Microsoft Updates will fail to update. We have found a code that will delete all temporary files and re-register all dll's related to Windows/Microsoft Updates! This works 99% of the time to get the updates working again! The…
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question