Implementing table partitioning for core tables in WebSphere Product Center

Introduction:

Table partitioning is a database scheme that allows distribution of table data into small chunks for better performance, maintenance, and storage. There are different partitioning schemes available for distributing data into multiple partitions. The partitioning scheme that is most beneficial for WebSphere Product Center is range partitioning based on next_version_id splitting the data into 2 partitions: current version data and old version data. High use and high growth WebSphere Product Center tables that will benefit from partitioning are TCTG_ITM_ITEM, TCTG_ITA_ITEM_ATTRIBUTES, TCTG_ITD_ITEM_DETAIL and TCTG_ICM_ITEM_CATEGORY_MAP. More than 80% of transactions in WPC system involves these 4 tables and around 70% of WebSphere Product Center data is stored in these tables on a typical installation.

Advantages of partitioning WebSphere Product Center core tables

1. Improved performance: All queries in WebSphere Product Center look for the current version of data, so splitting the tables into current and old data partitions enhances the performance by eliminating processing old data partitions based on the version predicates of the query.

2. Better administration of large tables: By creating separate table spaces for old and current data partitions, it is easier to monitor and maintain WebSphere Product Center tables. High growth and high usage tables are easily identified, and dedicated storage can be provided if needed.

3. Better maintenance of old version data: If old version data in WebSphere Product Center is not required they can be quickly removed by using partition truncation or roll-out feature. If entirely removing old versions is not an option, old versions can be selectively removed by using the delete old version shell script. Partitioning improves the performance of the delete old version shell script since all old version data is in a separate partition.

4. Efficient use of database memory: By having all current version data in a separate partition, more current version data is available in database memory resulting in efficient use of database buffer pools.

Below diagram illustrates the partitioning scheme for WebSphere Product Center:

Prerequisite

Implementing table partitioning requires the following:
1. WebSphere Product Center, Version 5.3.2 Fix Pack 14 or later.

2. Either DB2 version 9 Enterprise Edition (9.0 Fix packs) or Oracle version 10g Release 2 Enterprise Edition with partitioning option (10.2.0.1 or later). Please note that there are additional licenses required for DB2 or Oracle for using the "Table Partitioning" feature.

3. Double the amount of existing database space is required while implementing the partitioning scheme.

General implementation steps for Oracle and DB2

Step 1: Create table spaces

Following new table spaces are required for partitioning the core tables. These table spaces will have high growth and high use WebSphere Product Center tables. It is recommended that these table spaces are stored on optimal disks with automatic storage enabled in the database.
1. ITM_OLD
2. ICM_OLD
3. ITA_OLD
4. ITD_OLD
5. ITM_CURRENT
6. ICM_CURRENT
7. ITA_CURRENT
8. ITD_CURRENT
9. ITM_IX
10. ICM_IX
11. ITA_IX
12. ITD_OTHER

Guidelines for creating table spaces

Table space name	Growth	Estimated initial size
ITM_OLD	Moderate	200MB or as appropriate
ICM_OLD	Moderate	200MB or as appropriate
ITA_OLD	HIGH	5GB or as appropriate
ITD_OLD	HIGH	5GB or as appropriate
ITM_CURRENT	Low	200MB or less
ICM_CURRENT	Low	200MB or less
ITA_CURRENT	Moderate	5GB or as appropriate
ITD_CURRENT	Moderate	5GB or as appropriate
ITM_IX	Moderate	200MB
ICM_IX	Moderate	200MB
ITA_IX	HIGHEST	5GB or as appropriate
ITD_OTHER	Low	200MB

For more information on estimating storage space required for above table spaces refer to DB2 and Oracle documentation on the links provided below:

Estimating Space requirements in DB2

Estimating Space requirements in Oracle using Data Pump export utility

Step 2: Shutdown your WebSphere Product Center instance and backup your database

Shutdown your WebSphere Product Center instance and backup the database to ensure data recovery.

Step 3: Create partitioned tables and load data

Run sql script create_load_current_part_tables.sql to create the partitioned tables and load data in to the tables. This script creates dummy tables with partitioning scheme and loads data from the original tables. These dummy tables will be renamed to original tables in later steps. If you find any error in the script, it is safe to rerun the script after correcting the error. There will not be any data loss by rerunning this script but existing indexes will be dropped. Ignore error messages regarding drop table and drop index statements while running the script.

Please note that the script loads only the current object versions to the core tables to speed up the implementation process.

To run the script, from your application server or database server, log in to the database using db_user and db_password. Then run the SQL script

Step 4: Rename original tables

Run the rename_tables.sql script to rename dummy to original tables and run db statistics. Original tables will not be dropped to enable quick fallback on any issues.

Step 5: Verification

You can bring up your WebSphere Product Center instance and make sure the implementation is successful. Once verified you can run drop_original_tables.sql to drop the original non-partitioned tables. This will release the space occupied by the original tables.

Script Notes:

Use the following scripts in the given order. Script names are same for Oracle and DB2 databases and are provided in a separate zip file available for download from this page.

1. create_load_current_part_tables.sql
2. rename_tables.sql
3. drop_original_tables.sql

DO NOT use these scripts for DB2 version 9.7. These scripts are not compatible with DB2 version 9.7

Additional Oracle Specific features for WebSphere Product Center

Oracle has a specific type of table called Index Organized tables that offer significant performance improvement in a high throughput WebSphere Product Center environment. Index Organized Tables have the following advantages
1. Reduced size: Since table data is stored along with the primary key index in IOT, extra space for the table is not required.
2. Improved performance when concurrent transactions are running (for example many users editing or multiple jobs running simultaneously).
3. Provides consistent performance and scalability. Performance of long running jobs will not deteriorate with Index Organized tables compared to default heap organized tables.
4. Efficient space reuse within IOT

All WebSphere Product Center tables that have partitioning enabled are also converted to Index Organized tables during this implementation.

Deleting old object versions in a partitioned environment

Deleting all the old version objects in your WebSphere Product Center system in a partitioned environment is significantly faster compared to non-partitioned environment. Partitioning provides partition removal feature to quickly roll out or truncate old version partition. Using this feature to delete old version objects takes only few minutes compared to several hours when using delete_old_versions.sh script.

There are 2 options for deleting old versions in a partitioned environment as mentioned below

Option	Description	Action	Duration
1	Selective delete of some old versions	Run delete_old_version.sh script available from 5.3.2 FP14.	This will take several hours based on number of old versions
2	Delete all old versions	Modified delete old version script with name delete_old_partitions.sh is available part of the attached zip file	Several orders of magnitude faster than option 1.

Use the shell script delete_old_partitions.sh in the attached zip file to quickly delete all old versions in a partitioned environment. It is recommended to delete all old versions in the system after the partitioning implementation using one of the above two options.