problem with MQT

**Lennart** · Jan 7 '08, 02:45 PM

Re: problem with MQT

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:

Hi
>
I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.
>
I have a base table (base_table) as:
>
st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999
>
My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:
>
CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED
>
Then tried creating the staging table:
>
create table m_out_table for out_table propagate
immediate
>
This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7
>
Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?
>
Thanks.

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

db2 "? SQL20058N"

SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".

Explanation:

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

o the materialized query table must not contain duplicate rows

o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list

o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list

o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list

o correct the GROUP BY clause to ensure there are no duplicate
grouping sets

o remove the nullable column, C, or add GROUPING(C) in the
select list

o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list

Why do you need an MQT in the first place, can't you use a view
instead?

CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;

Another option is to add generated columns for the transformations ,
but I would aim for the view

/Lennart

**amitabh.mehra@gmail.com** · Jan 8 '08, 04:45 AM

Re: problem with MQT

On Jan 7, 7:43 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:
>
>
>

Hi

>

I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.

>

I have a base table (base_table) as:

>

st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999

>

My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:

>

CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED

>

Then tried creating the staging table:

>

create table m_out_table for out_table propagate
immediate

>

This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7

>

Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?

>

Thanks.

>
If you use a staging table you must obey the same rules as for an MQT
refresh immediate:
>
db2 "? SQL20058N"
>
SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".
>
Explanation:
>
The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.
>
This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.
>
The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:
>
o the materialized query table must not contain duplicate rows
>
o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list
>
o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list
>
o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or
>
o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list
>
o correct the GROUP BY clause to ensure there are no duplicate
grouping sets
>
o remove the nullable column, C, or add GROUPING(C) in the
select list
>
o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list
>
Why do you need an MQT in the first place, can't you use a view
instead?
>
CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;
>
Another option is to add generated columns for the transformations ,
but I would aim for the view
>
/Lennart

Thanks Lennart for the reply. But the problem with the view is that I
would not be able to refresh them incrementally if my base table gets
updated (as with mqts). or can i?

**amitabh.mehra@gmail.com** · Jan 8 '08, 04:55 AM

Re: problem with MQT

On Jan 8, 9:44 am, amitabh.me...@g mail.com wrote:

On Jan 7, 7:43 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
>
>
>

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:

>

Hi

>

I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.

>

I have a base table (base_table) as:

>

st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999

>

My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:

>

CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED

>

Then tried creating the staging table:

>

create table m_out_table for out_table propagate
immediate

>

This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7

>

Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?

>

Thanks.

>

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

>

db2 "? SQL20058N"

>

SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".

>

Explanation:

>

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

>

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

>

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

>

o the materialized query table must not contain duplicate rows

>

o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list

>

o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list

>

o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

>

o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list

>

o correct the GROUP BY clause to ensure there are no duplicate
grouping sets

>

o remove the nullable column, C, or add GROUPING(C) in the
select list

>

o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list

>

Why do you need an MQT in the first place, can't you use a view
instead?

>

CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;

>

Another option is to add generated columns for the transformations ,
but I would aim for the view

>

/Lennart

>
Thanks Lennart for the reply. But the problem with the view is that I
would not be able to refresh them incrementally if my base table gets
updated (as with mqts). or can i?

Also since I am not using REFRESH IMMEDIATE, I cant understand how
this error is coming.

**Lennart** · Jan 8 '08, 05:25 AM

Re: problem with MQT

On Jan 8, 5:44 am, amitabh.me...@g mail.com wrote:

On Jan 7, 7:43 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
>
>
>

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:

>

Hi

>

I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.

>

I have a base table (base_table) as:

>

st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999

>

My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:

>

CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED

>

Then tried creating the staging table:

>

create table m_out_table for out_table propagate
immediate

>

This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7

>

Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?

>

Thanks.

>

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

>

db2 "? SQL20058N"

>

SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".

>

Explanation:

>

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

>

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

>

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

>

o the materialized query table must not contain duplicate rows

>

o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list

>

o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list

>

o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

>

o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list

>

o correct the GROUP BY clause to ensure there are no duplicate
grouping sets

>

o remove the nullable column, C, or add GROUPING(C) in the
select list

>

o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list

>

Why do you need an MQT in the first place, can't you use a view
instead?

>

CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;

>

Another option is to add generated columns for the transformations ,
but I would aim for the view

>

/Lennart

>
Thanks Lennart for the reply. But the problem with the view is that I
would not be able to refresh them incrementally if my base table gets
updated (as with mqts). or can i?

I'm not sure I understand your question, but a view is sort of a query
stored in the database so you will definitely get updated results from
the view as you update the underlaying tables. In an MQT on the other
hand, the result is physically stored on disk.

HTH
/Lennart

**Lennart** · Jan 8 '08, 05:25 AM

Re: problem with MQT

On Jan 8, 5:45 am, amitabh.me...@g mail.com wrote:

On Jan 8, 9:44 am, amitabh.me...@g mail.com wrote:
>
>
>

On Jan 7, 7:43 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:

>

Hi

>

I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.

>

I have a base table (base_table) as:

>

st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999

>

My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:

>

CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED

>

Then tried creating the staging table:

>

create table m_out_table for out_table propagate
immediate

>

This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7

>

Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?

>

Thanks.

>

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

>

db2 "? SQL20058N"

>

SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".

>

Explanation:

>

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

>

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

>

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

>

o the materialized query table must not contain duplicate rows

>

o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list

>

o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list

>

o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

>

o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list

>

o correct the GROUP BY clause to ensure there are no duplicate
grouping sets

>

o remove the nullable column, C, or add GROUPING(C) in the
select list

>

o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list

>

Why do you need an MQT in the first place, can't you use a view
instead?

>

CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;

>

Another option is to add generated columns for the transformations ,
but I would aim for the view

>

/Lennart

>

Thanks Lennart for the reply. But the problem with the view is that I
would not be able to refresh them incrementally if my base table gets
updated (as with mqts). or can i?

>
Also since I am not using REFRESH IMMEDIATE, I cant understand how
this error is coming.

That's because of the staging table. If you add a staging table to a
"refresh deferred" MQT, youi must obey the same rules as for an
"refresh immediate" MQT

/Lennart

**Lennart** · Jan 8 '08, 05:35 AM

Re: problem with MQT

On Jan 8, 6:21 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

On Jan 8, 5:44 am, amitabh.me...@g mail.com wrote:
>
>
>

On Jan 7, 7:43 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:

>

Hi

>

I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.

>

I have a base table (base_table) as:

>

st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999

>

My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:

>

CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED

>

Then tried creating the staging table:

>

create table m_out_table for out_table propagate
immediate

>

This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7

>

Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?

>

Thanks.

>

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

>

db2 "? SQL20058N"

>

SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".

>

Explanation:

>

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

>

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

>

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

>

o the materialized query table must not contain duplicate rows

>

o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list

>

o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list

>

o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

>

o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list

>

o correct the GROUP BY clause to ensure there are no duplicate
grouping sets

>

o remove the nullable column, C, or add GROUPING(C) in the
select list

>

o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list

>

Why do you need an MQT in the first place, can't you use a view
instead?

>

CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;

>

Another option is to add generated columns for the transformations ,
but I would aim for the view

>

/Lennart

>

Thanks Lennart for the reply. But the problem with the view is that I
would not be able to refresh them incrementally if my base table gets
updated (as with mqts). or can i?

>
I'm not sure I understand your question, but a view is sort of a query
stored in the database so you will definitely get updated results from
the view as you update the underlaying tables. In an MQT on the other
hand, the result is physically stored on disk.
>

That is, with a view you don't need to do any refresh. It is refreshed
by definition

/Lennart

**amitabh.mehra@gmail.com** · Jan 8 '08, 05:55 AM

Re: problem with MQT

On Jan 8, 10:26 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

On Jan 8, 6:21 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
>
>
>

On Jan 8, 5:44 am, amitabh.me...@g mail.com wrote:

>

On Jan 7, 7:43 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:

>

Hi

>

I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.

>

I have a base table (base_table) as:

>

st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999

>

My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:

>

CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED

>

Then tried creating the staging table:

>

create table m_out_table for out_table propagate
immediate

>

This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7

>

Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?

>

Thanks.

>

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

>

db2 "? SQL20058N"

>

SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".

>

Explanation:

>

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

>

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

>

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

>

o the materialized query table must not contain duplicate rows

>

o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list

>

o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list

>

o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

>

o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list

>

o correct the GROUP BY clause to ensure there are no duplicate
grouping sets

>

o remove the nullable column, C, or add GROUPING(C) in the
select list

>

o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list

>

Why do you need an MQT in the first place, can't you use a view
instead?

>

CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;

>

Another option is to add generated columns for the transformations ,
but I would aim for the view

>

/Lennart

>

Thanks Lennart for the reply. But the problem with the view is that I
would not be able to refresh them incrementally if my base table gets
updated (as with mqts). or can i?

>

I'm not sure I understand your question, but a view is sort of a query
stored in the database so you will definitely get updated results from
the view as you update the underlaying tables. In an MQT on the other
hand, the result is physically stored on disk.

>
That is, with a view you don't need to do any refresh. It is refreshed
by definition
>
/Lennart

The problem with the view is that it will get updated as soon my base
table is updated. I dont want that. I want to refresh my"snapshot" as
n when required. So i found out that mqt was solving my purpose. I cud
update them as n when required.
Any other idea?

**Lennart** · Jan 8 '08, 07:05 AM

Re: problem with MQT

On Jan 8, 6:50 am, amitabh.me...@g mail.com wrote:

On Jan 8, 10:26 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
>
>
>

On Jan 8, 6:21 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 8, 5:44 am, amitabh.me...@g mail.com wrote:

>

On Jan 7, 7:43 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:

>

Hi

>

I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.

>

I have a base table (base_table) as:

>

st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999

>

My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:

>

CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED

>

Then tried creating the staging table:

>

create table m_out_table for out_table propagate
immediate

>

This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7

>

Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?

>

Thanks.

>

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

>

db2 "? SQL20058N"

>

SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".

>

Explanation:

>

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

>

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

>

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

>

o the materialized query table must not contain duplicate rows

>

o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list

>

o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list

>

o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

>

o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list

>

o correct the GROUP BY clause to ensure there are no duplicate
grouping sets

>

o remove the nullable column, C, or add GROUPING(C) in the
select list

>

o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list

>

Why do you need an MQT in the first place, can't you use a view
instead?

>

CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;

>

Another option is to add generated columns for the transformations ,
but I would aim for the view

>

/Lennart

>

Thanks Lennart for the reply. But the problem with the view is that I
would not be able to refresh them incrementally if my base table gets
updated (as with mqts). or can i?

>

I'm not sure I understand your question, but a view is sort of a query
stored in the database so you will definitely get updated results from
the view as you update the underlaying tables. In an MQT on the other
hand, the result is physically stored on disk.

>

That is, with a view you don't need to do any refresh. It is refreshed
by definition

>

/Lennart

>
The problem with the view is that it will get updated as soon my base
table is updated. I dont want that. I want to refresh my"snapshot" as
n when required. So i found out that mqt was solving my purpose. I cud
update them as n when required.
Any other idea?

Why do you need the staging table?

/Lennart

**amitabh.mehra@gmail.com** · Jan 8 '08, 07:45 AM

Re: problem with MQT

On Jan 8, 12:03 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

On Jan 8, 6:50 am, amitabh.me...@g mail.com wrote:
>
>
>

On Jan 8, 10:26 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 8, 6:21 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 8, 5:44 am, amitabh.me...@g mail.com wrote:

>

On Jan 7, 7:43 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:

>

Hi

>

I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.

>

I have a base table (base_table) as:

>

st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999

>

My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:

>

CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED

>

Then tried creating the staging table:

>

create table m_out_table for out_table propagate
immediate

>

This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7

>

Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?

>

Thanks.

>

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

>

db2 "? SQL20058N"

>

SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".

>

Explanation:

>

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

>

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

>

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

>

o the materialized query table must not contain duplicate rows

>

o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list

>

o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list

>

o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

>

o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list

>

o correct the GROUP BY clause to ensure there are no duplicate
grouping sets

>

o remove the nullable column, C, or add GROUPING(C) in the
select list

>

o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list

>

Why do you need an MQT in the first place, can't you use a view
instead?

>

CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;

>

Another option is to add generated columns for the transformations ,
but I would aim for the view

>

/Lennart

>

Thanks Lennart for the reply. But the problem with the view is that I
would not be able to refresh them incrementally if my base table gets
updated (as with mqts). or can i?

>

I'm not sure I understand your question, but a view is sort of a query
stored in the database so you will definitely get updated results from
the view as you update the underlaying tables. In an MQT on the other
hand, the result is physically stored on disk.

>

That is, with a view you don't need to do any refresh. It is refreshed
by definition

>

/Lennart

>

The problem with the view is that it will get updated as soon my base
table is updated. I dont want that. I want to refresh my"snapshot" as
n when required. So i found out that mqt was solving my purpose. I cud
update them as n when required.
Any other idea?

>
Why do you need the staging table?
>
/Lennart

So that I can incrementally refresh a REFRESH DEFERRED MQT. The idea
is to use the staging table to incrementally refresh the MQT, rather
than regenerate the MQT from scratch and so enhance the performance.

**Lennart** · Jan 8 '08, 08:15 AM

Re: problem with MQT

On Jan 8, 8:41 am, amitabh.me...@g mail.com wrote:

On Jan 8, 12:03 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
>
>
>

On Jan 8, 6:50 am, amitabh.me...@g mail.com wrote:

>

On Jan 8, 10:26 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 8, 6:21 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 8, 5:44 am, amitabh.me...@g mail.com wrote:

>

On Jan 7, 7:43 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:

>

On Jan 7, 12:51 pm, amitabh.me...@g mail.com wrote:

>

Hi

>

I havent used MQT before. Read the online tips and tutorials but none
seems to give any hint for my problem.

>

I have a base table (base_table) as:

>

st varchar(25) default 'my_null'
dt timestamp default
'1900-01-01-00.00.00.00000'
num integer default -999999

>

My requirement is that for any of these default value in base table,
null should be populated in the materialized view.
I tried the following to create a mqt:

>

CREATE TABLE OUT_table AS (SELECT
CASE DT WHEN
'1900-01-01-00.00.00.000000 ' THEN null ELSE DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST FROM
base_table)
DATA INITIALLY DEFERRED REFRESH DEFERRED

>

Then tried creating the staging table:

>

create table m_out_table for out_table propagate
immediate

>

This gave me error: SQL20058N The fullselect specified for the
materialized query table "test.OUT_table " is not valid. Reason code =
"7

>

Can, the thing that I am trying to do (put null values for 'my_null'
etc), be done? If yes, how?

>

Thanks.

>

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

>

db2 "? SQL20058N"

>

SQL20058N The fullselect specified for the materialized query
table "<table-name >" is not valid. Reason code =
"<reason-code>".

>

Explanation:

>

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

>

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

>

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

>

o the materialized query table must not contain duplicate rows

>

o when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list

>

o when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
and if C is a nullable GROUP BY item that appears within
GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
in the select list

>

o when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

>

o correct the CREATE TABLE statement to ensure all GROUP BY
items are in the select list

>

o correct the GROUP BY clause to ensure there are no duplicate
grouping sets

>

o remove the nullable column, C, or add GROUPING(C) in the
select list

>

o correct the CREATE TABLE statement to ensure at least one
unique key from each table referenced in the query appears in
the select list

>

Why do you need an MQT in the first place, can't you use a view
instead?

>

CREATE VIEW OUT_table AS
SELECT CASE DT WHEN '1900-01-01-00.00.00.000000 ' THEN null ELSE
DT END DT,
CASE NUM WHEN -999999 THEN null ELSE NUM END NUM,
CASE ST WHEN 'my_null' THEN null ELSE ST END ST
FROM base_table;

>

Another option is to add generated columns for the transformations ,
but I would aim for the view

>

/Lennart

>

Thanks Lennart for the reply. But the problem with the view is that I
would not be able to refresh them incrementally if my base table gets
updated (as with mqts). or can i?

>

I'm not sure I understand your question, but a view is sort of a query
stored in the database so you will definitely get updated results from
the view as you update the underlaying tables. In an MQT on the other
hand, the result is physically stored on disk.

>

That is, with a view you don't need to do any refresh. It is refreshed
by definition

>

/Lennart

>

The problem with the view is that it will get updated as soon my base
table is updated. I dont want that. I want to refresh my"snapshot" as
n when required. So i found out that mqt was solving my purpose. I cud
update them as n when required.
Any other idea?

>

Why do you need the staging table?

>

/Lennart

>
So that I can incrementally refresh a REFRESH DEFERRED MQT. The idea
is to use the staging table to incrementally refresh the MQT, rather
than regenerate the MQT from scratch and so enhance the performance.

I see. How long does it take to refresh the MQT without a staging
table, and what is acceptable? As mentioned before you can't use a
staging table with your query definition, since it doesnt obey the
rules for immediate refresh.

From what you have described I don't see any other way than to build
something on your own to manually maintain OUT_table.

/Lennart

problem with MQT

problem with MQT

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment