Upgrading 5.5sp1 to 7.1 pxAssemblyCacheKey column size issue
Upgrading from 5.5sp1 to 7.1.5 on db2zos
In 5.5sp1 the pr4_log_rule_usage_details table has a column pxAssemblyCacheKey which is varchar2(2048). In a new installation of 7.1.5 this column length has been shortened to 255 characters and there is an index pr_log_detail_cacheusage that should be added on this column and pxparentruleusagekey,
The update DDL does not alter this column length to 255 but it does try to create the index. On db2zos the combined length of the columns in the index is too long (with it still at 2048) so it cannot be created without altering the column length.
The table has data in it. Can the table be truncated, alter the column length, and then add the index? Are there other tables that need to be truncated as well?
Steps to Reproduce
Generate ddl for upgrade from a 5.5sp1 to 7.1.5 system on db2zos
The root cause of this problem is an error/omission in product documentation. The pxAssembleyCacheKey column needs to be shortened to 255 characters but because this table contains data that may be needed for licensing purposes it cannot be handled programatically. Upgrade guide should contain steps to check this column length and alter it appropriately before starting the upgrade.
This issue is resolved through the following local change:
If data is not needed, drop the pr_log_detail_cacheusage table and recreate with table structure with pxAssemblyCacheKey column as varchar2(255) and then proceed with the upgrade. If the data is needed then you need to export the data before dropping and recreating it and then import the data (truncating the data being inserted into the pxAssemblyCacheKey column to fit into 255 characters).
Documentation bug opened to address how to handle this in the PRPC 7.1.6 db2zos Upgrade Guide.