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

Manually initializing MQT und use Staging table

P: n/a
I want to create an MQT, intialize it manually and then use staging table to
refresh it with new data, but i don' t see a way for not doing full refresh
after creating the staging table.
I tried set integrity immediate unchecked, but refresh table after that
seems to do a full refresh.
Should this work? If so, what would be the right statements for doing this.
Thanks
Klemens

Jun 20 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Here are the steps to USE STAGING TABLES WITH SUMMARY TABLES

1. create table SUMMARY_TABLE as (select .......) data initially
deferred refresh deferred.

2. create table STAGING_TABLE for SUMMARY_TABLE Propagate immediate.

3. Set integrity for SUMMARY_TABLE QUERY IMMEDIATE UNCHECKED

4. Set integrity for STAGING_TABLE STAGING IMMEDIATE CHECKED

5. Then YOU HAVE TO DO A FULL REFRESH FOR THE SUMMARY TABLE

db2 refresh table SUMMARY_TABLE.

cheers...
Shashi Mannepalli
Klemens wrote:
I want to create an MQT, intialize it manually and then use staging table to
refresh it with new data, but i don' t see a way for not doing full refresh
after creating the staging table.
I tried set integrity immediate unchecked, but refresh table after that
seems to do a full refresh.
Should this work? If so, what would be the right statements for doing this.
Thanks
Klemens


Jun 20 '06 #2

P: n/a
AND .. once the FULL refresh is done, you can then refresh with the
INCREMENTAL option to use the new staging table.

Jun 21 '06 #3

P: n/a
refresh table SUMMARY_TABLE
ist that what I want to get arround.
I want to do this work manually because there is not enough transaction log
space to do this in one transaction and refresh table holds locks that makes
the whole database unusable for us.

Thanks
Klemens

"Shashi Mannepalli" <sh********@yahoo.com> schrieb im Newsbeitrag
news:11**********************@c74g2000cwc.googlegr oups.com...
Here are the steps to USE STAGING TABLES WITH SUMMARY TABLES

1. create table SUMMARY_TABLE as (select .......) data initially
deferred refresh deferred.

2. create table STAGING_TABLE for SUMMARY_TABLE Propagate immediate.

3. Set integrity for SUMMARY_TABLE QUERY IMMEDIATE UNCHECKED

4. Set integrity for STAGING_TABLE STAGING IMMEDIATE CHECKED

5. Then YOU HAVE TO DO A FULL REFRESH FOR THE SUMMARY TABLE

db2 refresh table SUMMARY_TABLE.

cheers...
Shashi Mannepalli
Klemens wrote:
I want to create an MQT, intialize it manually and then use staging table to refresh it with new data, but i don' t see a way for not doing full refresh after creating the staging table.
I tried set integrity immediate unchecked, but refresh table after that
seems to do a full refresh.
Should this work? If so, what would be the right statements for doing this.

Thanks
Klemens

Jun 21 '06 #4

P: n/a
If logging is your concern you can turn OFF logging while doing the
refresh statement.
Use NOT LOGGED INITIALLY option.

cheers...
Shashi Mannepalli
Klemens wrote:
refresh table SUMMARY_TABLE
ist that what I want to get arround.
I want to do this work manually because there is not enough transaction log
space to do this in one transaction and refresh table holds locks that makes
the whole database unusable for us.

Thanks
Klemens

"Shashi Mannepalli" <sh********@yahoo.com> schrieb im Newsbeitrag
news:11**********************@c74g2000cwc.googlegr oups.com...
Here are the steps to USE STAGING TABLES WITH SUMMARY TABLES

1. create table SUMMARY_TABLE as (select .......) data initially
deferred refresh deferred.

2. create table STAGING_TABLE for SUMMARY_TABLE Propagate immediate.

3. Set integrity for SUMMARY_TABLE QUERY IMMEDIATE UNCHECKED

4. Set integrity for STAGING_TABLE STAGING IMMEDIATE CHECKED

5. Then YOU HAVE TO DO A FULL REFRESH FOR THE SUMMARY TABLE

db2 refresh table SUMMARY_TABLE.

cheers...
Shashi Mannepalli
Klemens wrote:
I want to create an MQT, intialize it manually and then use staging table to refresh it with new data, but i don' t see a way for not doing full refresh after creating the staging table.
I tried set integrity immediate unchecked, but refresh table after that
seems to do a full refresh.
Should this work? If so, what would be the right statements for doing this.

Thanks
Klemens


Jun 21 '06 #5

P: n/a
It's the biggest problem.
But another problem is the time it runs.
It may get down if I refresh in blocks where the sorts and other actions on
temporary tables could be done in memory.

I found out that set integritiy on the staging table will not set staging on
that table to successfull state even the statement was successful.
So I tried to set staging on the staging table unchecked. Next refresh table
was done in one second. So that seemed to work.

Thanks
Klemens

"Shashi Mannepalli" <sh********@yahoo.com> schrieb im Newsbeitrag
news:11**********************@r2g2000cwb.googlegro ups.com...
If logging is your concern you can turn OFF logging while doing the
refresh statement.
Use NOT LOGGED INITIALLY option.

cheers...
Shashi Mannepalli
Klemens wrote:
refresh table SUMMARY_TABLE
ist that what I want to get arround.
I want to do this work manually because there is not enough transaction log space to do this in one transaction and refresh table holds locks that makes the whole database unusable for us.

Thanks
Klemens

"Shashi Mannepalli" <sh********@yahoo.com> schrieb im Newsbeitrag
news:11**********************@c74g2000cwc.googlegr oups.com...
Here are the steps to USE STAGING TABLES WITH SUMMARY TABLES

1. create table SUMMARY_TABLE as (select .......) data initially
deferred refresh deferred.

2. create table STAGING_TABLE for SUMMARY_TABLE Propagate immediate.

3. Set integrity for SUMMARY_TABLE QUERY IMMEDIATE UNCHECKED

4. Set integrity for STAGING_TABLE STAGING IMMEDIATE CHECKED

5. Then YOU HAVE TO DO A FULL REFRESH FOR THE SUMMARY TABLE

db2 refresh table SUMMARY_TABLE.

cheers...
Shashi Mannepalli
Klemens wrote:
> I want to create an MQT, intialize it manually and then use staging

table to
> refresh it with new data, but i don' t see a way for not doing full

refresh
> after creating the staging table.
> I tried set integrity immediate unchecked, but refresh table after that > seems to do a full refresh.
> Should this work? If so, what would be the right statements for
doing this.
>
>
> Thanks
> Klemens

Jun 21 '06 #6

P: n/a
So u ran

Set integrity for STAGING_TABLE STAGING IMMEDIATE UNCHECKED ?

Good to know that it worked.

As far as your REFRESH taking long time i would suggest you to tune the
SQL.
(adding indexes...etc)

cheers...
Shashi Mannepalli

Klemens wrote:
It's the biggest problem.
But another problem is the time it runs.
It may get down if I refresh in blocks where the sorts and other actions on
temporary tables could be done in memory.

I found out that set integritiy on the staging table will not set staging on
that table to successfull state even the statement was successful.
So I tried to set staging on the staging table unchecked. Next refresh table
was done in one second. So that seemed to work.

Thanks
Klemens

"Shashi Mannepalli" <sh********@yahoo.com> schrieb im Newsbeitrag
news:11**********************@r2g2000cwb.googlegro ups.com...
If logging is your concern you can turn OFF logging while doing the
refresh statement.
Use NOT LOGGED INITIALLY option.

cheers...
Shashi Mannepalli
Klemens wrote:
refresh table SUMMARY_TABLE
ist that what I want to get arround.
I want to do this work manually because there is not enough transaction log space to do this in one transaction and refresh table holds locks that makes the whole database unusable for us.

Thanks
Klemens

"Shashi Mannepalli" <sh********@yahoo.com> schrieb im Newsbeitrag
news:11**********************@c74g2000cwc.googlegr oups.com...
> Here are the steps to USE STAGING TABLES WITH SUMMARY TABLES
>
> 1. create table SUMMARY_TABLE as (select .......) data initially
> deferred refresh deferred.
>
> 2. create table STAGING_TABLE for SUMMARY_TABLE Propagate immediate.
>
> 3. Set integrity for SUMMARY_TABLE QUERY IMMEDIATE UNCHECKED
>
> 4. Set integrity for STAGING_TABLE STAGING IMMEDIATE CHECKED
>
> 5. Then YOU HAVE TO DO A FULL REFRESH FOR THE SUMMARY TABLE
>
> db2 refresh table SUMMARY_TABLE.
>
> cheers...
> Shashi Mannepalli
> Klemens wrote:
> > I want to create an MQT, intialize it manually and then use staging
table to
> > refresh it with new data, but i don' t see a way for not doing full
refresh
> > after creating the staging table.
> > I tried set integrity immediate unchecked, but refresh table after that > > seems to do a full refresh.
> > Should this work? If so, what would be the right statements for doing this.
> >
> >
> > Thanks
> > Klemens
>


Jun 21 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.