Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Rotate partitions based on month and year

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
suresh1624

New User


Joined: 21 Nov 2007
Posts: 28
Location: chennai

PostPosted: Mon May 11, 2009 2:31 pm    Post subject: Rotate partitions based on month and year
Reply with quote

Hi All,
my requirement is as follows.
I need to partition the table based on month and yr (which are the columns of the table) as the table contains huge data for each month and it currently holds 15 months of data.
The oldest month data should be dropped by rotating the partition. Currently the data for the new month is added and the old month data is deleted. Since the volume of data is huge, deleting the data with a query escaltes the lock and getting resource unavbl.

Any solutions other than partioning are also welcome. I need know how to define the partioning so as to rotate the partiotions.

Thanks in advance for your help and suggestions.
Back to top
View user's profile Send private message

Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Tue May 12, 2009 12:31 am    Post subject: Reply to: Rotate partitions based on month and year
Reply with quote

Hi suresh1624

Quote:
Any solutions other than partioning are also welcome. I need know how to define the partioning so as to rotate the partiotions


Do you really don't want any suggestion regarding partition?

Raghu
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 12, 2009 4:51 am    Post subject:
Reply with quote

Hello,

I believe Suresh is looking for suggestions of how to do (manage) the partitioning or if someone might recommend a method without using partitioning.

How many rows are in each month? How many queries use data from multiple months?
Back to top
View user's profile Send private message
suresh1624

New User


Joined: 21 Nov 2007
Posts: 28
Location: chennai

PostPosted: Tue May 12, 2009 9:58 am    Post subject:
Reply with quote

There are approximately 4M rows and in future it might increase to 8M. Currently i have suggested to unload the whole data excluding the oldest data and load it back with RESUME NO REPLACE instead of deleting the data with delete. I want to know the pros and cons of going for partitioning the table and rotating it.
Back to top
View user's profile Send private message
suresh1624

New User


Joined: 21 Nov 2007
Posts: 28
Location: chennai

PostPosted: Tue May 12, 2009 10:32 am    Post subject: Reply to: Rotate partitions based on month and year
Reply with quote

4 million row for each month and 15 months of data will be avbl in the table
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 12, 2009 10:35 am    Post subject:
Reply with quote

Hello,

I believe part of a successful implementation will take into account when and how the data is used.

How many keys/indexes are defined for these rows? What is the data volume for a month of data.

One of my smaller loads loads about 2.4 million rows in just a few minutes.

What i'd try if your requirement was mine is to unload the complete table, copy (using your sort product) the unloaded data deleting the oldest month. Then merge the new month data with the 12 months kept and reload everything into the table.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue May 12, 2009 11:04 pm    Post subject:
Reply with quote

There is a REORG option to DISCARD. You can clean the table during your normal reorg window. Sample syntax from Utility guide is,

Code:
REORG TABLESPACE DSN8D81A.DSN8S81E DISCARD FROM TABLE DSN8810.EMP WHEN (WORKDEPT = ’D11’) SHRLEVEL NONE COPYDDN SYSCOPY


More details in the utility guide.

I think the best option if you don't want PARTITIONING.

Else if you have segmented tablespace, alter to locksize tablespace nad then delete. It should be fast.

Or, partition the table, syntax in SQL Reference.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Execute JCL step based on the content... sprikitik JCL & VSAM 2 Tue Oct 03, 2017 10:03 am
No new posts How to insert a value on specific pos... Poha Eater DFSORT/ICETOOL 9 Sun Oct 01, 2017 3:04 am
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us