This document describes the steps for implementing table partitioning for core tables in WebSphere Product Center. Table partitioning highly improves the application performance and scalability. It also reduces the database maintenance time.
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:
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.
Guidelines for creating table spaces
Table space name
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|
|ITA_IX||HIGHEST||5GB or as appropriate|
For more information on estimating storage space required for above table spaces refer to DB2 and Oracle documentation on the links provided below:
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.
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.
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
|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.
Original publication date