MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Different indexes for different partitions

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
misi01
Advanced


Joined: 02 Dec 2002
Posts: 614
Topics: 171
Location: Stockholm, Sweden

PostPosted: Wed Oct 27, 2021 8:18 am    Post subject: Different indexes for different partitions Reply with quote

My impression (after googling) is that the answer is no, but .....

Can different partitions in a table have different indexes. For example, partition 1 might have an index on part_no, but partition 2 on sales_date.

I'm been tasked with looking at the idea of the most efficient way of logging transactions in the bank where I'm working. Note that I am not a trained DBA (so why have I been tasked with the job? Next question).
We're talking 4-5 million records per day, with , at a guess, 7-8 million around Xmas.
If it's at all relevant, the idea would be to load the partition(s) based on a 90 day schedule and then drop the oldest dates on a daily basis.
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Wed Oct 27, 2021 10:33 am    Post subject: Re: Different indexes for different partitions Reply with quote

misi01 wrote:
My impression (after googling) is that the answer is no, but .....

Can different partitions in a table have different indexes. For example, partition 1 might have an index on part_no, but partition 2 on sales_date.


Misi01,

It is quite unclear as to what you want to do. A partition is simply putting the keys in a different segment based on the range.

So assuming you have defined the table on partition on PART_NO, then each and every partition will have the SALES_DATE on it. You can have an index on it, but it applies to all partitions. Not sure as to how you can avoid sales_date in particular partition.

Here is a sample table definition.
Code:

CREATE TABLE some_tbl (Part_no     interger    NOT NULL,
                       sales_date  date        NOT NULL,
                       some_info   varchar)
        in your.tablespc
        partition by (Part_no)
( part 01  value (02000000),
  part 02  value (04000000),
  part 03  value (06000000),
  part 04  value (08000000),
  part 05  value (10000000));


CREATE INDEX A  ON some_tbl(part_no)
...

CREATE INDEX B  ON some_tbl(sales_date)
...
   

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Spolacek
Beginner


Joined: 17 Dec 2002
Posts: 17
Topics: 2
Location: NJ, USA

PostPosted: Sun Oct 31, 2021 5:51 pm    Post subject: Reply with quote

Hello Michael

I think what you want to accomplish can be done by defining the table using range partitioning. For example, each partition could house rows for a particular sales_date week. At the end of a week, you would remove the partition containing the rows for the oldest week and create a partition for the upcoming week.

Here's a link to the IBM DB2 documentation on range partitioning.

https://www.ibm.com/docs/en/db2/10.5?topic=tables-defining-ranges

You remove partitions from the table using the Detach Partition command. Here's a link to the documentation on detaching.

https://www.ibm.com/docs/en/db2/11.1?topic=ranges-detaching-data-partitions

Here's a link to a site that has articles on range partitioning and partition detaching in the context of something similar to what you may want to consider.

https://philipcarrington.wordpress.com/db2-maintenance/db2-table-range-partition-maintenance/

I hope this information helps. I'm no longer working and I don't have access to a mainframe, so I doing this from memory.
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 314
Topics: 48
Location: germany

PostPosted: Mon Nov 15, 2021 11:16 am    Post subject: Reply with quote

Spolacek,

your links, remarks according "detach partition" seems to be for DB2 LUW, right?

This does not work for DB2 zOS, my impression.

For deleting all records in one partition I know only the DB2 zOS utility function REORG DATA with the DISCARD option and an meaningful WHERE clause for this purpose.

Are there any other options to delete the content of one partition (without rotation and redefining the limiting keys of the last partition)????

I'm talking about partition by range table spaces.

kind regards,
bauer
Back to top
View user's profile Send private message
Spolacek
Beginner


Joined: 17 Dec 2002
Posts: 17
Topics: 2
Location: NJ, USA

PostPosted: Tue Nov 16, 2021 8:03 pm    Post subject: Reply with quote

Hello Bauer

I'm no longer working so I can't confirm whether the detach partition command pertains DB2 on zOS. I recall DBAs talking about partition ranging and detaching partitions, but I'm not certain now whether they were referencing zOS.

If your information shows that it does not pertain to zOS, then I withdraw my suggestion to Mis01 and I stand corrected.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group