By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

MQT/Staging Table/Syntax

P: n/a
I have searched far and wide (not wide enough some might chip in) but
cannot find the syntax for creating a staging table for an MQT.

IBM manuals talk long and longer in serveral sections of this "marvel"
but there is no indication of what it takes to create one.

I have seen indications that appear to suggest that the CREATE table
statement is all I need.

They also seem to suggest that the Staging table and MQT are created in
the same statement .
From all this I assume this is not related to the DATA CAPTURE element

of tables, which would have to be resident on every table I intend to
join in my MQT.

Help Please.

Zri

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Zri Man wrote:
I have searched far and wide (not wide enough some might chip in) but
cannot find the syntax for creating a staging table for an MQT.

IBM manuals talk long and longer in serveral sections of this "marvel"
but there is no indication of what it takes to create one.

I have seen indications that appear to suggest that the CREATE table
statement is all I need.

They also seem to suggest that the Staging table and MQT are created in
the same statement .
From all this I assume this is not related to the DATA CAPTURE element

of tables, which would have to be resident on every table I intend to
join in my MQT.

Help Please.

Zri

Here is an example (courtesy of Miro, thanks!):

-- Create deferred MQT

CREATE TABLE SALES_BY_TRANSID AS
( SELECT TRANSID, COUNT(*) AS ITEM_COUNT, SUM(AMOUNT) AS TOTAL
FROM TRANSITEM
GROUP BY(TRANSID) )
DATA INITALLY DEFERRED
REFRESH DEFERRED;

-- Create staging table for MQT above

CREATE TABLE STG_SALES_BY_TRANSID FOR SALES_BY_TRANSID PROPAGATE IMMEDIATE;

-- Bring the staging table online

SET INTEGRITY FOR STG_SALES_BY_TRANSID IMMEDIATE CHECKED;

-- Bring the MQT online

REFRESH TABLE SALES_BY_TRANSID;

-- Optional steps for improving performance

CREATE INDEX IX_SALES_BY_TRANSID ON SALES_BY_TRANSID(TRANSID);

RUNSTATS ON TABLE STARS.SALES_BY_TRANSID WITH DISTRIBUTION AND DETAILED
INDEXES
ALL;

-- populate the transaction table with last weeks data

INSERT INTO TRANSITEM SELECT ...;

-- Incrementally refresh the MQT from staging table

REFRESH TABLE SALES_BY_TRANSID INCREMENTAL;

-- alternatively, an example with LOAD (Note the extra refresh step)

LOAD FROM ... INSERT INTO TRANSITEM;

REFRESH TABLE STG_SALES_BY_TRANSID INCREMENTAL;
REFRESH TABLE SALES_BY_TRANSID INCREMENTAL;

It is rumoured there is soem discussion in:
http://www.redbooks.ibm.com/redbooks/pdfs/sg246546.pdf
Here is the syntax diagram in CREATE TABLE:

staging-table-definition:
7
7 |--+-------------------------------+--FOR--table-name2---------->
7 | .-,-------------------. |
7 | V | |
7 '-(----staging-column-name-+--)-'
7
7 >--PROPAGATE IMMEDIATE------------------------------------------|

Any suggestions for improvement?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Thanks.

I wish the manuals that talk at length about Staging tables and MQT's
hand an example.

How the relationship is forged between MQT and Staging tables and the
Source tables to MQT is obvious, however to know that by doing a CREATE
TABLE FOR <TABLE_NAME> blahblah blah would establish this relationship
is a leap of logic IBM should not be expecting the User to make.
Nevertheles thank you.

Nov 12 '05 #3

P: n/a
I echo this sentiment. I remember scouring every Redbook I could find
in addition to the standard docs and not being able to come up with a
simple "AHA!" example like the one Serge provided. It does seem like a
legitimate gap in the manuals unless someone can point it out to us.
Just my 2 cents...

Pete H

Nov 12 '05 #4

P: n/a
In article <11*********************@g47g2000cwa.googlegroups. com>,
peteh (ph******@intellicare.com) says...
I echo this sentiment. I remember scouring every Redbook I could find
in addition to the standard docs and not being able to come up with a
simple "AHA!" example like the one Serge provided. It does seem like a
legitimate gap in the manuals unless someone can point it out to us.
Just my 2 cents...

Pete H

There's a chapter in the SQL Cookbook about MQT's at
http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM
It contains a lot of other usefull examples also.
Nov 12 '05 #5

P: n/a
peteh wrote:
I echo this sentiment. I remember scouring every Redbook I could find
in addition to the standard docs and not being able to come up with a
simple "AHA!" example like the one Serge provided. It does seem like a
legitimate gap in the manuals unless someone can point it out to us.
Just my 2 cents...

Pete H

I took the liberty of raising this with our ID folks.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

P: n/a
Serge,

Appreciate your input and your suggestion within to your own company
on this.

Zridhar

Nov 12 '05 #7

P: n/a
Zri Man wrote:
Serge,

Appreciate your input and your suggestion within to your own company
on this.

Zridhar

*mumblemumble*...customer driven...*mumblemumble*

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.