470,596 Members | 1,594 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

MQTs and UDFs

Friends,

Say I have the following UDF:

CREATE FUNCTION GET_MONTH(P_DATE DATE)
RETURNS INTEGER
INHERIT SPECIAL REGISTERS
SPECIFIC GET_MONTH
DETERMINISTIC
BEGIN ATOMIC
RETURN MONTH(P_DATE);--
END;

and that I'd like to use it in the following MQT:

CREATE TABLE
UDF_MQT
AS
(
SELECT
GET_MONTH()
FROM
SYSIBM.SYSDUMMY1
)
DATA INITIALLY DEFERRED REFRESH DEFERRED
NOT LOGGED INITIALLY;

When I try to create the MQT, I get an error SQL20058N, reason code 4:

(SQL20058N The fullselect specified for the materialized query table
<is not valid. Reason code = "4)

The fullselect must not contain references to functions that:

o depend on physical characteristics of the data, for example
DBPARTITIONNUM, HASHEDVALUE

o are defined as EXTERNAL ACTION

o are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA or
MODIFIES SQL DATA

Volume 2 of the SQL Reference has the following under CREATE TABLE:

When REFRESH DEFERRED or REFRESH IMMEDIATE is specified (as if,
according to the syntax diagram under refreshable-table-options, there
are any other kinds :-), the fullselect cannot include functions that
have any of the following attributes:

EXTERNAL ACTION
LANGUAGE SQL
CONTAINS SQL
READS SQL DATA
MODIFIES SQL DATA

Anyone know of a way around this, while still using a SQL scalar
function? This limitation seems very...limiting.

Thanks,

--Jeff

Nov 2 '06 #1
3 2134
jefftyzzer wrote:
Friends,

Say I have the following UDF:

CREATE FUNCTION GET_MONTH(P_DATE DATE)
RETURNS INTEGER
INHERIT SPECIAL REGISTERS
SPECIFIC GET_MONTH
DETERMINISTIC
BEGIN ATOMIC
RETURN MONTH(P_DATE);--
END;

and that I'd like to use it in the following MQT:

CREATE TABLE
UDF_MQT
AS
(
SELECT
GET_MONTH()
FROM
SYSIBM.SYSDUMMY1
)
DATA INITIALLY DEFERRED REFRESH DEFERRED
NOT LOGGED INITIALLY;

When I try to create the MQT, I get an error SQL20058N, reason code 4:

(SQL20058N The fullselect specified for the materialized query table
<is not valid. Reason code = "4)

The fullselect must not contain references to functions that:

o depend on physical characteristics of the data, for example
DBPARTITIONNUM, HASHEDVALUE

o are defined as EXTERNAL ACTION

o are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA or
MODIFIES SQL DATA

Volume 2 of the SQL Reference has the following under CREATE TABLE:

When REFRESH DEFERRED or REFRESH IMMEDIATE is specified (as if,
according to the syntax diagram under refreshable-table-options, there
are any other kinds :-), the fullselect cannot include functions that
have any of the following attributes:

EXTERNAL ACTION
LANGUAGE SQL
CONTAINS SQL
READS SQL DATA
MODIFIES SQL DATA

Anyone know of a way around this, while still using a SQL scalar
function? This limitation seems very...limiting.
There is no way around it. The problem lies in the routing. Gets pretty
pretty with SQL functions. Rather then allowing to create the beast and
never route to it we thought it better to block up front.
The "other kind" is: DEFINITION ONLY btw.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 2 '06 #2
Thanks, Serge. While not the answer I hoped for, at least it's
conclusive. As to my "other kind" comment, sorry--that was a cheap
shot.

BTW, my colleagues who went to your "SQL on Fire" sessions had great
things to say about it. I wish I could have attended the conference,
but going is a privilege we must rotate :-).

--Jeff

Serge Rielau wrote:
jefftyzzer wrote:
Friends,

Say I have the following UDF:

CREATE FUNCTION GET_MONTH(P_DATE DATE)
RETURNS INTEGER
INHERIT SPECIAL REGISTERS
SPECIFIC GET_MONTH
DETERMINISTIC
BEGIN ATOMIC
RETURN MONTH(P_DATE);--
END;

and that I'd like to use it in the following MQT:

CREATE TABLE
UDF_MQT
AS
(
SELECT
GET_MONTH()
FROM
SYSIBM.SYSDUMMY1
)
DATA INITIALLY DEFERRED REFRESH DEFERRED
NOT LOGGED INITIALLY;

When I try to create the MQT, I get an error SQL20058N, reason code 4:

(SQL20058N The fullselect specified for the materialized query table
<is not valid. Reason code = "4)

The fullselect must not contain references to functions that:

o depend on physical characteristics of the data, for example
DBPARTITIONNUM, HASHEDVALUE

o are defined as EXTERNAL ACTION

o are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA or
MODIFIES SQL DATA

Volume 2 of the SQL Reference has the following under CREATE TABLE:

When REFRESH DEFERRED or REFRESH IMMEDIATE is specified (as if,
according to the syntax diagram under refreshable-table-options, there
are any other kinds :-), the fullselect cannot include functions that
have any of the following attributes:

EXTERNAL ACTION
LANGUAGE SQL
CONTAINS SQL
READS SQL DATA
MODIFIES SQL DATA

Anyone know of a way around this, while still using a SQL scalar
function? This limitation seems very...limiting.
There is no way around it. The problem lies in the routing. Gets pretty
pretty with SQL functions. Rather then allowing to create the beast and
never route to it we thought it better to block up front.
The "other kind" is: DEFINITION ONLY btw.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 2 '06 #3
jefftyzzer wrote:
Thanks, Serge. While not the answer I hoped for, at least it's
conclusive. As to my "other kind" comment, sorry--that was a cheap
shot.

BTW, my colleagues who went to your "SQL on Fire" sessions had great
things to say about it. I wish I could have attended the conference,
but going is a privilege we must rotate :-).
The WAIUG Conference is dirt cheap. Final call for "SQL on Fire!".

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Eugene | last post: by
3 posts views Thread by David Carver | last post: by
5 posts views Thread by Suresh | last post: by
reply views Thread by Helmut Tessarek | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.