We are upgrading CPM from 6.2 sp3 to 713. Our Database is DB2ZOS. We just performed an upgrade from Pega 6.2 sp1 to Pega 7.1.8 . As part of the CPM upgrade guide, we executed the generateddl.sh script with the correct parameters as instructed.
When generated ddl was executed, the following error appeared:
ALTER TABLE XXXXXX.pca_rating_knowcont,
ADD CONSTRAINT pca_rating_knowcont_PK PRIMARY KEY
DSNT408I SQLCODE = -542, ERROR: RATINGENTITY CANNOT BE A COLUMN OF A HASH
KEY, PRIMARY KEY, A UNIQUE CONSTRAINT, OR A PARENT KEY BECAUSE IT CAN
CONTAIN NULL VALUES
Should RATINGENTITY column be defined as NOT NULL to allow it to be part of the primary key, as a column cannot be NULL and be part of a primary key? If a column can be NULL then it could allow duplicate entries and a primary key has to be unique.
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
I took a look at CPM 6.2 SP3 build files and checked the DB2 Z/oS Install file. I reviewed the DDL of the table “pca_rating_knowcont” and its index “pca_rating_knowcont_PK”. Below are my observations
1. In Install DDL files of DB2 UDB, Oracle & SQL Server, the index – “pca_rating_knowcont_PK” on the table “pca_rating_knowcont” is already defined on 2 columns namely, EntityKey, RatingEntity.
2. Only in DDL file of DB2 Z/OS file, the index is defined on ONE column – EntityKey. Moreover, the column RatingEntity is defined as NULL.
3. In the Install DDL files of Oracle & SQL Server, though the column RatingEntity is NOT explicitly defined as “NOT NULL”, the primary key constraint pca_rating_knowcont_PK on the definition will MAKE the column RatingEntity as “NOT NULL”.
4. The discrepancy in the column definition of the index/constraint pca_rating_knowcont_PK between DB2 Z/oS and rest of the DB in CPM 6.2 SP3 build looks to me as an inconsistency problem.
5. The problem of “RatingEntity being NULL” has caused the reported problem of -542 SQL error while upgrading the CPM 6.2 SP3 in Z/oS DB2 to CPM7.1.3. This problem would NOT occur to upgrade of CPM 6.2 SP3 in DB2 UDB/ORACLE/SQLSERVER to CPM7.1.3 as the column RatingEntity is already part of Primary constraint/index.
If the table is empty or if the column "RatingEntity" does not have any NULL values currently, my suggested solution will be to let the DBA alter the table “pca_rating_knowcont” to define RatingEntity as NOT NULL and then run the upgrade script.