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:
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.