Problem with Modifing MQT

[lpmsk]

Hi,
we are using DB2 v8.1 on Linux64 for a DW application. We have a
table with around 800 million records in it. For performance purpose we
created about 2 or 3 MQT's(summary tables) on the table with refresh
immediate option. But when developers come up with some changes on the
MQT(which happens all most once every month). We drop the MQT and
recreate it. When the table was small we never had problem. But later
on we started having the transaction log full error.

As a work around, we are dropping all the MQTs. Renaming the table to
someother name. And then create the table again, create the MQT and
then insert the 800 million records again. Which requires around 3 -4
hrs time to reinsert all the records back into the table. Is there
anyother simple way to solve this problem.

Regards,
Koushik S

[Phil Sherman]

Assuming that you're using circular logging; did you try increasing the
log space?

Phil Sherman
lpmsk wrote:

Hi,
we are using DB2 v8.1 on Linux64 for a DW application. We have a
table with around 800 million records in it. For performance purpose we
created about 2 or 3 MQT's(summary tables) on the table with refresh
immediate option. But when developers come up with some changes on the
MQT(which happens all most once every month). We drop the MQT and
recreate it. When the table was small we never had problem. But later
on we started having the transaction log full error.

As a work around, we are dropping all the MQTs. Renaming the table to
someother name. And then create the table again, create the MQT and
then insert the 800 million records again. Which requires around 3 -4
hrs time to reinsert all the records back into the table. Is there
anyother simple way to solve this problem.

Regards,
Koushik S

[lpmsk]

Hi Phil,
Yes, We are using circular log.
We already increased the log file size. Our log file size is 30 000
pages. Increasing more than this resulted in slower performance of the
ETL process the load the DW.
Log Retain No
Log primary 20
Log secondary 30
This combination worked good for us. So we retained it. Is there
anything I need to change in this.

Regards,
Koushik S
Phil Sherman wrote:

Assuming that you're using circular logging; did you try increasing the
log space?

Phil Sherman
lpmsk wrote:

Hi,
we are using DB2 v8.1 on Linux64 for a DW application. We have a
table with around 800 million records in it. For performance purpose we
created about 2 or 3 MQT's(summary tables) on the table with refresh
immediate option. But when developers come up with some changes on the
MQT(which happens all most once every month). We drop the MQT and
recreate it. When the table was small we never had problem. But later
on we started having the transaction log full error.

As a work around, we are dropping all the MQTs. Renaming the table to
someother name. And then create the table again, create the MQT and
then insert the 800 million records again. Which requires around 3 -4
hrs time to reinsert all the records back into the table. Is there
anyother simple way to solve this problem.

Regards,
Koushik S

[Phil Sherman]

It didn't quite work - your update job failed!

I'd try upping the "log secondary" parameter to at least 80 (doubling
the total log space) and verify that you have the disk space to support
it. After the job completes, do a "GET SNAPSHOT FOR DATABASE ON db_name"
and look at the "Maximum secondary log space used" line to determine how
to properly set the parameter.

Phil Sherman
lpmsk wrote:

Hi Phil,
Yes, We are using circular log.
We already increased the log file size. Our log file size is 30 000
pages. Increasing more than this resulted in slower performance of the
ETL process the load the DW.
Log Retain No
Log primary 20
Log secondary 30
This combination worked good for us. So we retained it. Is there
anything I need to change in this.

Regards,
Koushik S
Phil Sherman wrote:

>>Assuming that you're using circular logging; did you try increasing the
log space?

Phil Sherman
lpmsk wrote:

>>>Hi,
we are using DB2 v8.1 on Linux64 for a DW application. We have a
table with around 800 million records in it. For performance purpose we
created about 2 or 3 MQT's(summary tables) on the table with refresh
immediate option. But when developers come up with some changes on the
MQT(which happens all most once every month). We drop the MQT and
recreate it. When the table was small we never had problem. But later
on we started having the transaction log full error.

As a work around, we are dropping all the MQTs. Renaming the table to
someother name. And then create the table again, create the MQT and
then insert the 800 million records again. Which requires around 3 -4
hrs time to reinsert all the records back into the table. Is there
anyother simple way to solve this problem.

Regards,
Koushik S

[Ian]

lpmsk wrote:

Hi,
we are using DB2 v8.1 on Linux64 for a DW application. We have a
table with around 800 million records in it. For performance purpose we
created about 2 or 3 MQT's(summary tables) on the table with refresh
immediate option. But when developers come up with some changes on the
MQT(which happens all most once every month). We drop the MQT and
recreate it. When the table was small we never had problem. But later
on we started having the transaction log full error.

You can also do:

db2 +c alter table mqt activate not logged initially
db2 refresh table mqt;
db2 commit