MQTs and UDFs

[jefftyzzer]

Friends,

Say I have the following UDF:

CREATE FUNCTION GET_MONTH(P_DAT E 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.SYSDUMMY 1
)
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

[Serge Rielau]

jefftyzzer wrote:

Friends,

Say I have the following UDF:

CREATE FUNCTION GET_MONTH(P_DAT E 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.SYSDUMMY 1
)
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

[jefftyzzer]

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_DAT E 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.SYSDUMMY 1
)
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

[Serge Rielau]

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