materialized views using identity columns

[amitab...@gmail.com]

hi

can i create a materialized view on some table that is not having any
pk and instead use identity columns (very much like rowid in case of
oracle)? while searching online, i stumbled upon this:
http://www.ibm.com/developerworks/data/library/techarticle/dm-0708khatri/index.html

but trying it is giving me error.

eg:

describe table test_table:
Data type Column
Column name schema Data type name
Length Scale Nulls
------------------------------- --------- -------------------
---------- ----- ------
COL1 SYSIBM VARCHAR
1000 0 Yes
COL2 SYSIBM VARCHAR
10000 0 Yes

now create materialized query:

create table out_test_table as (select a.col1, a.col2, rowid_col not
null generated always as identity (start with 1, increment by 1) from
test_table a) data initially deferred refresh deferred

this gives me following error:
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "null generated always as identity
(start " was
found following ".col2, rowid_col not". Expected tokens may include:
"<space>". SQLSTATE=42601

am i missing something here? i m using db2 v9.5

thanks in advance
amitabh

[Serge Rielau]

amitab...@gmail.com wrote:
> am i missing something here? i m using db2 v9.5

You are missing that you have a RID_BIT() function in DB2 9.5.
(which does the same as ROWID).
So no need to use identity columns.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[amitab...@gmail.com]

On Mar 9, 5:57 pm, Serge Rielau <srie...@ca.ibm.com> wrote:

well, i tried:
create table out_test_table as (select a.col1 as col1, a.col2 as col2,
rid(a) as rowid from test_table a) data initially deferred refresh
deferred

this gave following error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:

SQL20058N The fullselect specified for the materialized query table
"DEV02USR.OUT_TEST_TABLE" is not valid. Reason code = "4".
SQLSTATE=428EC

reason 4 states (source:http://publib.boulder.ibm.com/infocenter/
db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.msg.doc/doc/sql20058.htm):
The fullselect must not contain references to functions that:

* depend on physical characteristics of the data, for example
DBPARTITIONNUM, HASHEDVALUE
* are defined as EXTERNAL ACTION
* are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA or
MODIFIES SQL DATA

[Lennart]

On Mar 9, 12:57 pm, amitabh.me...@gmail.com wrote:
> hi
>
> can i create a materialized view on some table that is not having any
> pk and instead use identity columns (very much like rowid in case of

> oracle)? while searching online, i stumbled upon this:http://www.ibm.com/developerworks/data/library/techarticle/dm-0708kha...
>

I was a bit surprised when I looked into the article, I didn't think
that it was possible to add a column in an mqt like that (I didn't get
it to work either, but I didn't put to much effort in there). Anyhow,
I don't fully understand why you would wanna do something like that.
Can't you just add the generated column in the base table, and use it
in the mqt?

/Lennart

[...]

[Tonkuma]

> Can't you just add the generated column in the base table, and use it
> in the mqt?

Or, use ROW_NUMBER() OVER() in the MQT.

[Arun Srinivasan]

Someone please remove that article or atleast change it. The identity
just doesn't work. It is wrong. Also the author didn't use it in his
scripts.

[amitab...@gmail.com]

No, I cant touch that table. This table has no PK/UK etc. And I need
to have a MQT for it that too using staging table for incremental
refresh (you might have guessed it seeing the refresh deferred
clause). Can I do this in any way? Thinking on lines of oracle, I
thought of using rowid i.e rid_bit() but got the error which I
mentioned in the earlier post.

As suggested by Tonkuma, I tried creating MQT with row_number() over
(). That was successful but threw following error while I was creating
the staging table:

------ create MQT
create table out_test_table as (select col1 as col1, col2 as col2,
row_number() over() as rownum from test_table) data initially deferred
refresh deferred
DB20000I The SQL command completed successfully.

------ create staging table
create table m_test_table for out_test_table propagate immediate

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:

SQL20058N The fullselect specified for the materialized query table

"DEV02USR.OUT_TEST_TABLE" is not valid. Reason code = "6".
SQLSTATE=428EC

which I am not able to understand since my query uses referesh
deferred.

Any pointers?

Thanks
Amitabh

[Serge Rielau]

Arun Srinivasan wrote:
> Someone please remove that article or atleast change it. The identity
> just doesn't work. It is wrong. Also the author didn't use it in his
> scripts.

I have asked the authors to engage.

[Lennart]

If you want to use a staging table for your deferred MQT you have to
obey the same set of rules as for a refresh immediate MQT.

I'm a bit puzzled on what problem you are trying to solve. What does
the out_test_table give you that you can't get from the underlaying
table? Replacing a primary key in the base table with some surrogate
key in the mqt doesn't make sense to me. Can you elaborate on what the
purpose of this is?

Meanwhile, you can define your mqt as:

create table out_test_table as (
select a.col1, a.col2, count(*) cnt
from test_table a
group by a.col1, a.col2
) data initially deferred refresh immediate

i.e. you can also define it deferred with a staging table. Not sure if
this is of any comfort though.

/Lennart

[jefftyzzer]

Amitabh,

If you have some flexibility on the datatype of this surrogate PK for
your MQT, perhaps the generate_unique() function could be used, e.g.,

CREATE TABLE YOUR_MQT
(
YOUR_MQT_ID,
COL1,
COL2
) AS
(
SELECT
CHAR(HEX(GENERATE_UNIQUE())),
COL1,
COL2
FROM
...

--Jeff