volking
asked on
UDF in MQT definition
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 ;
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 ;
ASKER
@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 ...........
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 ...........
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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