Manually initializing MQT und use Staging table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Klemens - 20 Jun 2006 14:23 GMT

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

Reply to this Message

Shashi Mannepalli - 20 Jun 2006 22:20 GMT

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

> 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
[quoted text clipped - 5 lines]
> Thanks
> Klemens

Reply to this Message

p175 - 21 Jun 2006 03:44 GMT

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

Reply to this Message

Klemens - 21 Jun 2006 08:34 GMT

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

> Here are the steps to USE STAGING TABLES WITH SUMMARY TABLES
>
[quoted text clipped - 22 lines]
> > Thanks
> > Klemens

Reply to this Message

Shashi Mannepalli - 21 Jun 2006 18:14 GMT

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

cheers...
Shashi Mannepalli

> refresh table SUMMARY_TABLE
> ist that what I want to get arround.
[quoted text clipped - 34 lines]
> > > Thanks
> > > Klemens

Reply to this Message

Klemens - 21 Jun 2006 18:35 GMT

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

> If logging is your concern you can turn OFF logging while doing the
> refresh statement.
[quoted text clipped - 41 lines]
> > > > Thanks
> > > > Klemens

Reply to this Message

Shashi Mannepalli - 21 Jun 2006 18:55 GMT

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

> It's the biggest problem.
> But another problem is the time it runs.
[quoted text clipped - 58 lines]
> > > > > Thanks
> > > > > Klemens

Reply to this Message