Discussion
TCS
TCS
GB
TCS
Posted: Aug 24, 2015
Last activity: Apr 6, 2018
Last activity: 6 Apr 2018 19:26 EDT
Closed
-542 error on CPM upgrade from 6.2 sp3 to 713
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
(
EntityKey,
RatingEntity
)
@
---------+---------+---------+---------+---------+---------+---------+--------
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.
To see attachments, please log in.
**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.
Posted: 9 years ago
TCS
TCS
GB
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.
To see attachments, please log in.