Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

UDF in MQT definition

Posted on 2008-10-06
3
Medium Priority
?
785 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 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
#Citrix #POC #XenDesktop #vCenter #VMware #ESX
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.

885 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