dynamic partitions for rolling week

Posted on 2010-01-12
Medium Priority
Last Modified: 2012-05-08
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.


Currently we have

FROM History

create table stg_history
IN aa_01 INDEX IN aa_01
      (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
Question by:sam2929
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 37

Expert Comment

ID: 26293540
so what exactly are you asking?
how can this be done?
LVL 37

Accepted Solution

momi_sabag earned 2000 total points
ID: 26293569
personaly, i like using attach and detach of partitions
but that is just me
you can read about rotating partitions here

Author Comment

ID: 26293833
So for attach detach do we need pl/sql script ?
Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

LVL 37

Expert Comment

ID: 26293967
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

Author Comment

ID: 26294264
yes i think we need some validty checks , Is it possible to get some script and i can modify as per my requirment
LVL 37

Expert Comment

ID: 26297609
for example you can just look up the alter table statement in the information center
what besides that staement you will wish to perform?

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question