Hello,
We recently upgraded our Progress OpenEdge software to version 11.6. We are interested in using Table Partitioning feature in Progress 11.6.
Has anybody implemented Table Partitioning feature on their database ?
Do you received any benefits like performance , easier maintenance etc.
What about application changes ? Do you have to change your code to include partition key in your create statements or do you have to convert all RECID's to ROWIDS? Also what happens if you use ROWID and your partition data gets migrated from one partition to another partition ? Does your application gives wrong data ?
Please share your thoughts to help us what to consider in this project.
Thanks,
Sunil
This thread should be moved to OpenEdge General; it doesn't belong in a PUG forum.
Disclaimer: I haven't used TP yet.
I suggest you start your research with the Progress documentation. There is a manual in the 11.6 documentation set called Getting Started: Table Partitioning, and more info in the DB Admin manual. There are also web papers and Exchange/PUG Challenge conference materials available for download. Search on this site for the Exchange material and on pugchallenge.org for PUG Challenge material that gives best-practices guidance and answers several of your questions. Look at Exchange 2013 and onward, and PUG Challenge 2014 and onward.
Without trying to reproduce all of that material here, you need to educate yourself about the application constraints with TP so you can look for possible issues in your code base. For example, the ROWID function has been extended to include partition information, so it will continue to work with partitioned tables. RECID (which has been deprecated for years) has not been extended to work with TP and should not be used with your application tables. There are a couple of legitimate use-cases for RECID, i.e. working with meta-schema tables and VSTs, but other uses should be changed to use ROWID (whether or not you choose to implement TP).
You also have to examine your record-creation logic for partitioned tables. Your code must fully populate the fields in the partition policy definition during the create transaction.
E.g.: you choose to partition TableA on field1 and field2. If your current code creates a TableA record and assigns a value to field1, and then a later update transaction sets the value of field2, the AVM will throw a run-time error because the partition key hasn't been populated so the storage engine can't choose a partition (storage object) in which to store the record.
Also, you can't have unknown values in partition columns. And you shouldn't partition on columns whose values are likely to change as an update could cause the record to be deleted from one partition and created in another, affecting application performance.
Data being migrated from one partition to another will be transparent to your application (except for the caveat about performance), as long as your application doesn't store rowids long-term and reference them later. But in general, you shouldn't do that anyway. Even apart from TP, a rowid isn't guaranteed to be static for the lifetime of a given record. E.g. a proutil tablemove in a non-TP database will almost certainly change all the rowids in that table.
The bottom line is that you need to know your data, you need to know your application's data-access patterns, and you need to know what and why you intend to partition and which potential benefit(s) you hope to optimize for, and design your partitioning scheme and structure accordingly. Then test, test, test. Good luck.