Bookshelf v7.5: Converting Nonpartitioned Tables to Partitioned Tables

Implementing Siebel eBusiness Applications on DB2 UDB for OS/390 and z/OS > Customizing a Development Environment >

Converting Nonpartitioned Tables to Partitioned Tables

Siebel eBusiness Applications support two scenarios for converting nonpartitioned tables to partitioned tables.

NOTE: In the procedures below, source table refers to your current nonpartitioned table, and target table refers to the destination partitioned table.

Source table and target table are defined in different table spaces. If the source table and target table are defined in different table spaces, and the target table space is a new table space that does not yet exist in your current database, no special action is required. Run the two utilities available in the Siebel Software Configuration Utility (Migrate Development to Production, Synchronize Database Schema Definition) to automatically rebuild the table in the new partitioned table space and drop the original table.

Source table and target table are defined in the same table space. If the source table and target table are defined in the same table space (for example, if you used the dbconfig.xls spreadsheet to convert an existing table space from nonpartitioned to partitioned), then you must perform one of the following procedures (as appropriate for your configuration) to convert a nonpartitioned table to a partitioned table on the same table space.

NOTE: You must perform one of the following procedures, because the database operations do not support rebuilding a table space from nonpartitioned to partitioned. (This results in a validation failure and a halted operation.)

Perform the following steps if your database layout has a multiple-tables-per-table space structure, and you want to convert a nonpartitioned table to a partitioned table defined in the same table space.

To convert to a partitioned table for a multiple-tables-per-table space configuration

Review the Siebel objects naming convention that is used in the one-table-per-table space partitioned storage control file (storage_p_1to1.ctl) to determine the name of the table space that you want to move your nonpartitioned table to.

NOTE: Verify that the new table space name is unused.

Modify the storage file with the new table space name by using the dbconfig process (define it as a partitioned table space and define the table in this new table space).

Run the Siebel Software Configuration Utility: Migrate Development to Production. This process automatically rebuilds the source tables in the new partitioned table space and drops the original table.

Perform the following steps if your database layout has a single-table-per-table space structure, and you want to convert a nonpartitioned table to a partitioned table defined in the same table space.

To convert to a partitioned table for a one-table-per-table space configuration

Use your preferred database tool to manually move every nonpartitioned table (every table that you intend to convert) to a temporary table space, and then drop their original table spaces.

Use the dbconfig.xls utility to modify the storage control file, changing the mode of the table spaces to partitioned.

Manually drop the temporary table space that you created in Step 1.

To convert nonpartitioned tables to partitioned tables using alternate tools

Your DBA can use alternate tools (for example, BMC Change Manager or IBM Compare Utility) to convert nonpartitioned tables to partitioned table spaces. In this case, after changes have been applied, the last step is to change the schema version by executing the following SQL:

update S_APP_VER

set CUSTOM_SCHEMA_VER = char(integer(CUSTOM_SCHEMA_VER) + 1)

This SQL is stored in bumpver.sql and can be executed outside of the Siebel application.