• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

dynamic partitions for rolling week

Hi,
I want to create dynamic partitions , Currently my partitions are based on snapshot period which is year and week combination like (20091,20092,20093 and so on),

But we want to have keep just last 12 week and then delete previous week, so that means we really want only 12 partitions and that partitions should be dynamic,
As currently we have 2009,2009and 2010 partitions , so all we want is just quaterly partitions , so that we can keep 12 week data and then delete previous data,
Please guide me how i achieve it.


Right now we do select from history table and based on snapshot period we insert data in stg_history(As stg_history has 2008,2009,and 2010 data , Wwe want to delete
all previous data and just want to keep 12 week data and want to make in dynmaic going forward)

Basic idea is The tables are a rolling 12  week of data.

Thanks


Currently we have

SELECT
 aa1,
aa2,
,SNAPSHOT_PERIOD
FROM History


create table stg_history
IN aa_01 INDEX IN aa_01
DISTRIBUTE BY HASH (SNAPSHOT_PERIOD)
PARTITION BY RANGE(SNAPSHOT_PERIOD NULLS LAST)
      (PARTITION p200850 STARTING FROM ('200850') ENDING AT ('200850'),
       PARTITION p200851 STARTING FROM ('200851') ENDING AT ('200851'),
       PARTITION p200852 STARTING FROM ('200852') ENDING AT ('200852'),
       PARTITION p200853 STARTING FROM ('200853') ENDING AT ('200853')
                      and so onnnnnnnn---
version 9.5 on aix
0
sam2929
Asked:
sam2929
  • 4
  • 2
1 Solution
 
momi_sabagCommented:
so what exactly are you asking?
how can this be done?
0
 
momi_sabagCommented:
personaly, i like using attach and detach of partitions
but that is just me
you can read about rotating partitions here
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.partition.doc/doc/t0021567.html
0
 
sam2929Author Commented:
So for attach detach do we need pl/sql script ?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
momi_sabagCommented:
you can use a simple alter table statement in order to attach / detach a partition
if you want this done automatically i guess you will need some script that will also perform some validity checks before and after it runs the alter command
0
 
sam2929Author Commented:
yes i think we need some validty checks , Is it possible to get some script and i can modify as per my requirment
0
 
momi_sabagCommented:
for example you can just look up the alter table statement in the information center
what besides that staement you will wish to perform?
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now