473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Manually initializing MQT und use Staging table

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
6 5433
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Mike Davison | last post by:
I've made a program that fetches data from an ODBC data source. Everything works fine when the table contains only a few rows. The problem is that when there are many rows (6000, in this case),...
7
by: Zri Man | last post by:
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...
2
by: ms | last post by:
Access 2000: I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3...
0
by: Joe | last post by:
Does anyone know of a tool that can automate code migration from a staging server to a production server. As a developer I only have rights to migrate to staging and I would like a tool that could...
2
by: rom | last post by:
what do i have to more from staging to production every time i make changes? all the files only? what about the dll in the bin directory? if i copy the dll file do i also have to copy the aspx and...
3
by: darrel | last post by:
I have three tables making a many-to-many relationship: Companies CoID | CoName Link CoID | InsID Insurers InsID | InsName
0
by: tshad | last post by:
I am trying to find the best way to handle my development process. I am using Dreamweaver to handle the check-in/check-out process which works pretty well. We are trying to work out the best...
8
by: metalheadstorm | last post by:
ok ive set up a connection between my access db ( 97) and my vb6 interface and this is what i got form_load Data6.DatabaseName = App.Path & "\cjmillers.mdb" Data6.RecordSource = "select * from...
13
by: WaterWalk | last post by:
Hello. When I consult the ISO C++ standard, I notice that in paragraph 3.6.2.1, the standard states: "Objects with static storage duration shall be zero-initialized before any other...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.