Enabling interim fixes JR45152, JR45153, and JR46765

Interim fixes JR45152, JR45153 (included in JR47678 and JR47300) , and JR46765 enhance the performance of generating or importing many unique promotion codes in approved content and in workspaces. The fixes also handle PX_CDPOOL table duplicate key exceptions that you might encounter when you publish a task group that contains promotions with promotion codes.

Before you begin

Ensure that you complete the following tasks:

Install the cumulative interim fix for Feature Pack 5, JR53048.fep. This cumulative fix includes these interim fixes.
Install the interim fix for APAR JR48519 and complete the associated manual steps. For more information, see Enabling interim fix JR48519. By applying this fix, you can view public promotion codes for promotions within .

Procedure

Run the following SQL statements in your development, staging, and production databases to add new columns to the PX_CDPOOL table:

DB2/Derby:

ALTER TABLE PX_CDPOOL ADD COLUMN REFERENCE_ID BIGINT DEFAULT NULL;
ALTER TABLE PX_CDPOOL ADD COLUMN INTERNAL_ID BIGINT DEFAULT 0;
ALTER TABLE PX_CDPOOL ADD COLUMN WORKSPACE VARCHAR(25);
ALTER TABLE PX_CDPOOL ADD COLUMN TASKGROUP VARCHAR(25);
DROP INDEX I0001313;
CREATE UNIQUE INDEX I0001313 ON PX_CDPOOL (CODE ASC, STORE_ID ASC, INTERNAL_ID ASC);

Oracle:

ALTER TABLE PX_CDPOOL ADD REFERENCE_ID NUMBER NULL;
ALTER TABLE PX_CDPOOL ADD INTERNAL_ID NUMBER DEFAULT 0 NOT NULL;
ALTER TABLE PX_CDPOOL ADD WORKSPACE VARCHAR2(25) NULL;
ALTER TABLE PX_CDPOOL ADD TASKGROUP VARCHAR2(25) NULL;
DROP INDEX I0001313;
CREATE UNIQUE INDEX I0001313 ON PX_CDPOOL
(
	CODE		ASC,
       STORE_ID	   ASC,
       INTERNAL_ID	ASC
);

Complete the following steps if you enabled workspaces.

Run the updateWorkspacesSchemaTable Ant script to recalibrate the change.

For example:


./config_ant.sh 
-buildfile WC_installdir/components/Workspaces/xml/updateWorkspacesSchemaTable.xml  
-DinstanceName=instance_name 
-DtableName=px_cdpool 
-DdbaName=DbaName
-DdbaPassword=DbaPassword  
CM_updateWorkspacesTable
-DresourceXMLLocation=location_of_resource_XML_Files

For more information, see

Drop and re-create the triggers for your database system:
- DB2: Connect to your database and run the following SQL file,
  - WC_installdir/schema/db2/apar/wcs.staging.trigger.pxcdpool.update.sql
  - WCDE_installdir/schema/db2/apar/wcs.staging.trigger.pxcdpool.update.sql.
  Note: If the file does not exist, run the following SQL file instead,
  - WC_installdir/components/foundation/schema/fep5/apar/wcs.staging.trigger.pxcdpool.updateForDb2.sql
  - WCDE_installdir/components/foundation/schema/fep5/apar/wcs.staging.trigger.pxcdpool.updateForDb2.sql
- Oracle: Connect to your database and run the following SQL file,
  - WC_installdir/schema/oracle/apar/wcs.staging.trigger.pxcdpool.update.sql
  - WCDE_installdir/schema/oracle/apar/wcs.staging.trigger.pxcdpool.update.sql
  Note: If the file does not exist, run the following SQL file instead,
  - WC_installdir/components/foundation/schema/fep5/apar/wcs.staging.trigger.pxcdpool.updateForOracle.sql
  - WCDE_installdir/components/foundation/schema/fep5/apar/wcs.staging.trigger.pxcdpool.updateForOracle.sql
For more information about how to run an SQL file for your database type, see the product documentation for your specific database.
Note: If you encounter errors that the size of the SCHBRDCST.CLONEID column is too small after you apply these fixes, increase the column size for this table column. For instance, run the following SQL command: alter table schbrdcst modify (cloneid varchar2(64));
Optional: If you need to migrate public promotion codes that existed before you apply these fixes to a new instance, you must reactivate the associated promotions within your new instance. Reactivate the promotions that have asssociated public promotion code by deactivating the promotions. Then, activate the promotions again.

After you apply these fixes, promotions that have existing public promotion codes can have NULL values within the PX_CDPOOL database table for newly added columns. For instance, the columns WORKSPACE, REFERENCE, TASKGROUP, and INTERNAL_ID can include NULL values for the existing public promotion codes. Since the values for these columns are NULL, the accociated Staglog trigger for the table does not pick up changes that are related to these codes during a stage propagation operation. By reactivating the promotions that use the migrated codes, a new record is created for each promotion code. These records can now be picked up by the Staglog trigger during a stage propagation process. The previous database records are now obsolete and can be deleted.