hi all,

I have a issue with purging.

The tables in my database are partitioned based by month, so i want to
purge the partition based on monthly basis, in total 8 months
partitions each where each months size is approx 140GB

My questions are :

Is it possible to purge 8 months partition at a time ? meaning by
issuing detach command in one script and executing it - if yes - then
i would be having locking issues - which means the Async Index clean
up might take very long to complete and my maintenance window time is
just 4 hours.

As there are 20 tables with an average of 10 million pages in each one
of them.

if tried to detach one partition at a time then it takes approx 3 hrs
for Async Index cleanup to complete.

I would appreciate - if anyone of you can suggest a smart solution in
order to speed up the process.

Nevertheless :

I thought of other alternatives too but not sure about the performance
and how it would impact the system.

1) Emptying the tables on monthly basis by deleting with 10000 rows
per execution, which would too tedious

In order to speed up the process is if try deleting 1 million rows
then i might be filling up the logs.

In order to avoid log, i should try to alter the table with NOT LOGGED
statement. (This change i cannot implement as per the company norms).

Then thought of doing online reorg.

I am running out of ideas, please suggest me a novel way to speed up
this process.

Regards
Raoul

On Aug 12, 3:56*am, shekhar udb <shekhar... (AT) gmail (DOT) com> wrote:
the good design for data purging is to maintain Data partition for
each day as apposed to one single month. As the data grows it become
managable to purge data on daily basis as apposed to a complete month
purge. Also each partition goes in individual tablespace.

We performed the same exercise using daily partition purge but in one
execution we purged one month worth of data at one go and likewise for
7 months.In our case we have one table of 500GB span across 40
database partitions and 3 other tables with 200GB each spanning across
40 partitions.

On each of the table we had SYSTEM MQT so the refresh was the
operation that was taking time, but the ASYNC did its jobs without
intefering other transaction. Yes you can see some Contention on table
during ASYNC index cleanup but it didnt do much harm to us. You might
see db2 process "db2taskd" incurring some LOCKs. But simply ignore
because it is the process of scheduling background task. It schedule
the ASYNC task and you see lock on SYSIBM.SYSTASKS table.

I would recommend to test the operation on your test environment
before pushing the solution to your PROD machine.

wrt to DELETE, it would result into more contention on the table as
apposed to DETACH operation. Also NOT LOGGED initially is critical,
because if the transaction fails, the table goes in dropped peding
state where in you may need to drop and recreate the tables. Where in
the table would be unaavailable for long time.

On Aug 11, 7:56*pm, shekhar udb <shekhar... (AT) gmail (DOT) com> wrote:
Have you considered using partitioned indexes (also known as local
indexes)? There is no asynchronous index cleanup for local indexes
since the local indexes become indexes on the target table of detach.

Naresh

I don't know what the OP's database looks like, but there are some
restrictions to partitioned indexes that make it difficult to use in all
situations.

The main restriction is that the table partitioning key must be included in
all unique indexes (which means all PK's and Unique Constraints). This is
usually not a big problem with a star schema, but can be difficult in an
OLTP traditional 3rd Normal Form design.

On Aug 14, 10:35*pm, "Martin" <n... (AT) none (DOT) com> wrote:
Without this restriction, the cost of inserting into a partitioned
table would become prohibitive. The database manager would need to
look at all the index partitions in order to ensure uniqueness,
imagine the performance when there are hundreds of partitions. I am
actually surprised that this restriction comes up as often as it does.
To my knowledge, other database vendors (Oracle, Informix) have a
similar restriction.

Your point is valid though ... including the partitioning key in
unique indexes may violate the business constraint and not be an
option. In these cases, using nonpartitioned indexes to enforce
uniqueness is a good choice. However, converting the remaining indexes
to be local indexes would still help bring down the time it takes for
AIC to complete the index cleanup. Recall that there is one AIC task
per detach, per global index, per database partition.

Naresh