Although table partitioning is often presented as a performance option for Data Warehouse systems, it can also be used very successfully in OLTP systems such as PeopleSoft. Even official PeopleSoft documentation such as the Red Paper ‘PeopleSoft 8 Batch Performance on Oracle Database’ discusses the merits of partitioning. However, that paper completely fails to mention that PeopleSoft’s Application Designer will not build the DDL to build partitioned objects. A very limited partitioning utility was introduced in PeopleTools 8.54 (see PeopleSoft DBA Blog). However, it does not save the configuration in any metadata, though Application Designer will preserve existing partitioning. If you want to partition tables in your application, you are substantially on your own facing the prospect of ongoing manual maintenance of DDL scripts.
The GFCBUILD utility dynamically generates the DDL to create and manage partitioned and Global Temporary tables and indexes in PeopleSoft systems based on the meta-data in the PeopleTools tables and addition meta-data that describes the partitioning strategy. These generated scripts replace the use of the Application Designer to build DDL scripts.
Partitioning can also be a segue into archiving. Older historical data in separate partitions can be archived by dropping the partition or changing it with another table (and possibly exchanging it again into another partition. GFC_PSPART can suppress generation of partitions that have been archived and generate a partitioned copy of a partitioned table with only the partitions necessary to hold archived data.
If you are partitioning output tables to prevent read-consistency contention between concurrent processes, then temporary working storage tables must also be addressed. However, rather than partition them it is an option to make them Global Temporary tables. The GFC_PSPART package can also generate the DDL for this.
Essentially, GFC_PSPART can be considered to be a replacement for the build script builder in Application Designer for PeopleSoft records that are to be built as either Partitioned or Global Temporary tables.
- The GFC_PSPART package generates DDL, much as Application Designer does, based on the contents of the PeopleTools tables, but also uses additional meta-data that describes the partitioning.
- GFC_PARTDATA_PKG is an example package that sets up the metadata used by GFC_PSPART. Each customer creates their own version this package that creates their partition meta-data. Using a PL/SQL package has the advantage version-controlled code can be released that creates appropriate meta-data and therefore appropriate partitioning. The metadata can also be generated dynamically so that it evolves over time.
GFCBUILD is on Github at https://github.com/davidkurtz/gfcbuild