DB2 table partitioning: Would you recommend this key as a partitioning key?
Posted on 2015-02-19
Hi DB2 Gurus,
First my business is in the NorthEast and in the retail business and a lot of the things we do is "store" driven - sales, promos, delivery, purchasing, ec, etc. I am about to embark on partitioning some of our large tables and it was recommended that I partition the tables by Store_key (INTEGER). The store_Key is nothing special, just numbers begining fro 1 through 1200. That's it!
Question 1: Would you recommend the store_key as a partitiong key? If not, why not?
Question 2: If you accept store_key as a partitioning key, how would you code the parttioning details?. For example, I have in mind to create the partitions as follows:
create table products
(xxx int, store_key int, storeno int....)
partition by (store_key)
(partition 1 begin at (1),
partition 2 begin at (2),
partition 2 begin at (3),
partition 2 begin at (4),
partition 2 begin at (5),
partition 2 begin at (6),
SELECT STORE_KEY,STORENO, ZONENO, REGIONNO FROM RAPM.STORES
Question 3: What recommendation can anyone give on how to distribute the table parts on tablespace partitions?
Does it make sense to have 1200 partitions now or 600 partitions now, etc, etc.?
Is there such thing as auto-add partition? I don't mean partition-by-growth?
All any assistance, recommendations or links to useable partitioning information will be highly appreciated.