Solved

UDF in MQT definition

Posted on 2008-10-06
3
756 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

As the title indicates, I have done this before. It chills me everytime I update the OS on my phone, (http://www.experts-exchange.com/articles/18084/Upgrading-to-Android-5-0-Lollipop.html) because one time I did this and I essentially had a bricked …
Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
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.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now