1

In one of my fact table which has close to 25 million records in it and now when a cognos team try to run the report on top of that it takes lot of time, So i am thinking of partitioning the table we store the records in fact table based on daily basis and we do have have the id calaendarday dma in the fact table as data type int.So is there a way i can partition this fact table based on id calaendarday dma?

Please help me on the above query as it is impacting the cognos reports

Improve this question
2
  • 2
    Have you done any work to determine why you are getting slow response? Range partitioning might be a good solution, but it's probably worthwhile determining why you have a problem instead of guessing at a solution (especially one that's relatively "expensive" to implement).
    – Ian Bjorhovde
    Commented Mar 5, 2013 at 18:27
  • Please indicate which DB2 platform family you are using: z/OS, i, or LUW. Partitioning is likely to be implemented on these three platforms rather differently and therefore affect performance differently.
    – WarrenT
    Commented Mar 21, 2013 at 23:00

2 Answers 2

Reset to default
1

I have an answer in a previous question that talks about the different forms of partitioning.

Not knowing if you have Database Paritioning Feature (DPF) enabled (since it is a separate license for DB2), your biggest bang for the buck would be to have table partitioning and/or a multidimensional clustering table. MDC's you want to cluster on things with low cardinality, so don't use your date field for that. But you may be able to use other fields used in the query for it.

As for partitioning, you will first need to create your table with partitions defined. (You unfortunately can't partition a non-partitioned table). In your definition, you could define all the partitions you want. After that you can always attach new ones. Once they are defined you can move the data from the old table to the new table, or just reload your fact table (depending on your business case).

Now, here is another thought on defining partitioning. IBM does recommend putting each partition in its own tablespace (for performance and disaster recover reasons). To do this you would have to define the table multiple times each with its partition range and assign it to a tablespace. Then do ALTER TABLE ADD PARITION and attach the table to the "base table". If you are using one day at a time for data, this perhaps could get costly, so maybe you would rather wish to partition quarterly or monthly or something like that.

Improve this answer
0

As Ian suggests, you may be far better off investigating why Cognos is performing so poorly.

For example, do you have an ideal index to support the query(s) involved?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.