SEO

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

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

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

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