?
Solved

UDF in MQT definition

Posted on 2008-10-06
3
Medium Priority
?
777 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
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.

800 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