A DB2 SQL Error SQLCODE=-478,SQLSTATE=42893 occurs when migrating from WebSphere Lombardi Edition (WLE) Version 7.2 to IBM Business Process Manager (BPM) Version 8.0

Technote (troubleshooting)


Problem(Abstract)

You are migrating WebSphere Lombardi Edition Version 7.2 to IBM Business Process Manager Version 8.0. When you use the upgrade_7x command with IBM DB2 V9.7.5, you encounter a DB2 SQL Error: SQLCODE=-478, SQLSTATE=42893.

Symptom

The following text lists the entire error message:

com.lombardisoftware.tools. SQLFileExec - DB2 SQL Error: SQLCODE=-478, SQLSTATE=42893, SQLERRMC=COLUMN;DB2ADMIN.LSW_DYNAMIC_GROUP.NAME;CHECK CONSTRAINT, DRIVER=4.11.69 com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-478, SQLSTATE=42893, SQLERRMC=COLUMN;DB2ADMIN. LSW_DYNAMIC_GROUP.NAME;CHECK CONSTRAINT, DRIVER=4.11.69



Resolving the problem

Before you run the upgrade_7X command for IBM Business Process Manager V8.0 with IBM DB2 V9.7.5, complete the following steps:

  1. Open the BPM_Install_Root/AppServer/profiles/profile_name/dbscripts/ProcessServer/DB2/database_name/wle_upgradeSchema_ProcessServer.sql file.
  2. Locate the following commands in wle_upgradeSchema_ProcessServer.sql file:
    ALTER TABLE LSW_DYNAMIC_GROUP ALTER "NAME" SET DATA TYPE VARCHAR(512)
    GO  
    ALTER TABLE LSW_DYNAMIC_GROUP DROP CHECK NAME ADD CONSTRAINT "NAME" CHECK (CHARACTER_LENGTH( "NAME",CODEUNITS16) <= 128) ENFORCED ENABLE QUERY OPTIMIZATION
    GO
    Change these commands to the following commands:
    ALTER TABLE LSW_DYNAMIC_GROUP DROP CHECK "NAME"
    GO
    ALTER TABLE LSW_DYNAMIC_GROUP ALTER "NAME" SET DATA TYPE VARCHAR(512)
    GO
    ALTER TABLE LSW_DYNAMIC_GROUP ADD CONSTRAINT "NAME" CHECK ( CHARACTER_LENGTH("NAME",CODEUNITS16) <= 128) ENFORCED ENABLE QUERY OPTIMIZATION
    GO
  3. Locate the following commands in wle_upgradeSchema_ProcessServer.sql file:
   ALTER TABLE LSW_BRANCH ALTER "NAME" SET DATA TYPE VARCHAR(256)
   GO
   ALTER TABLE LSW_BRANCH DROP CHECK "NAME" ADD CONSTRAINT "NAME" CHECK    
   (CHARACTER_LENGTH("NAME",CODEUNITS16) <= 64)  ENFORCED ENABLE QUERY  
   OPTIMIZATION
   GO
Change these commands to the following commands:
ALTER TABLE LSW_BRANCH DROP CHECK "NAME"
   GO
ALTER TABLE LSW_BRANCH ALTER "NAME" SET DATA TYPE VARCHAR(256)
   GO
   ALTER TABLE LSW_BRANCH ADD CONSTRAINT "NAME" CHECK    
   (CHARACTER_LENGTH("NAME",CODEUNITS16) <= 64)  ENFORCED ENABLE QUERY  
   OPTIMIZATION
   GO
4. Locate the following commands in wle_upgradeSchema_ProcessServer.sql file:
   ALTER TABLE LSW_BRANCH ALTER "ACRONYM" SET DATA TYPE VARCHAR(256)
   GO
   ALTER TABLE LSW_BRANCH DROP CHECK "ACRONYM" ADD CONSTRAINT "ACRONYM"
   CHECK (CHARACTER_LENGTH("ACRONYM",CODEUNITS16) <= 64)  ENFORCED ENABLE  
   QUERY OPTIMIZATION
   GO
Change these commands to the following commands:
ALTER TABLE LSW_BRANCH DROP CHECK "ACRONYM"
GO
   ALTER TABLE LSW_BRANCH ALTER "ACRONYM" SET DATA TYPE VARCHAR(256)
   GO
   ALTER TABLE LSW_BRANCH ADD CONSTRAINT "ACRONYM"
   CHECK (CHARACTER_LENGTH("ACRONYM",CODEUNITS16) <= 64)  ENFORCED ENABLE  
   QUERY OPTIMIZATION
   GO
5. Locate the following commands in wle_upgradeSchema_ProcessServer.sql file:
   ALTER TABLE LSW_SNAPSHOT ALTER "ACRONYM" SET DATA TYPE VARCHAR(256)
   GO
   ALTER TABLE LSW_SNAPSHOT DROP CHECK "ACRONYM" ADD CONSTRAINT "ACRONYM"
   CHECK (CHARACTER_LENGTH("ACRONYM",CODEUNITS16) <= 64)  ENFORCED ENABLE  
   QUERY OPTIMIZATION
   GO
Change these commands to the following commands:
ALTER TABLE LSW_SNAPSHOT DROP CHECK "ACRONYM"
GO
ALTER TABLE LSW_SNAPSHOT ALTER "ACRONYM" SET DATA TYPE VARCHAR(256)
   GO
   ALTER TABLE LSW_SNAPSHOT ADD CONSTRAINT "ACRONYM"
   CHECK (CHARACTER_LENGTH("ACRONYM",CODEUNITS16) <= 64)  ENFORCED ENABLE  
   QUERY OPTIMIZATION
   GO
6. Locate the following commands in wle_upgradeSchema_ProcessServer.sql file:
   ALTER TABLE LSW_SERVER ALTER "PASSWORD" SET DATA TYPE VARCHAR(1000)
   GO
   ALTER TABLE LSW_SERVER DROP CHECK "PASSWORD" ADD CONSTRAINT "PASSWORD"
   CHECK (CHARACTER_LENGTH("PASSWORD",CODEUNITS16) <= 250)  ENFORCED ENABLE
   QUERY OPTIMIZATION
   GO
   Change these commands to the following commands:
ALTER TABLE LSW_SERVER DROP CHECK "PASSWORD"
   GO
   ALTER TABLE LSW_SERVER ALTER "PASSWORD" SET DATA TYPE VARCHAR(1000)
   GO
   ALTER TABLE LSW_SERVER ADD CONSTRAINT "PASSWORD"
   CHECK (CHARACTER_LENGTH("PASSWORD",CODEUNITS16) <= 250)  ENFORCED ENABLE
   QUERY OPTIMIZATION
   GO
7. Locate the following commands in wle_upgradeSchema_ProcessServer.sql file:
   ALTER TABLE LSW_USR_GRP_XREF ALTER "GROUP_NAME" SET DATA TYPE  
   VARCHAR(1024)
   GO
   ALTER TABLE LSW_USR_GRP_XREF DROP CHECK "GROUP_NAME" ADD CONSTRAINT
   "GROUP_NAME" CHECK (CHARACTER_LENGTH("GROUP_NAME",CODEUNITS16) <= 256)  
   ENFORCED ENABLE QUERY OPTIMIZATION
   GO
   Change these commands to the following commands:
ALTER TABLE LSW_USR_GRP_XREF DROP CHECK "GROUP_NAME"
   GO
   ALTER TABLE LSW_USR_GRP_XREF ALTER "GROUP_NAME" SET DATA TYPE  
   VARCHAR(1024)
   GO
   ALTER TABLE LSW_USR_GRP_XREF ADD CONSTRAINT
   "GROUP_NAME" CHECK (CHARACTER_LENGTH("GROUP_NAME",CODEUNITS16) <= 256)  
   ENFORCED ENABLE QUERY OPTIMIZATION
   GO
8. Locate the following commands in wle_upgradeSchema_ProcessServer.sql file:
   ALTER TABLE LSW_USR_GRP_XREF ALTER "DISPLAY_NAME" SET DATA TYPE  
   VARCHAR(1024)
   GO
   ALTER TABLE LSW_USR_GRP_XREF DROP CHECK "DISPLAY_NAME" ADD CONSTRAINT
   "DISPLAY_NAME" CHECK (CHARACTER_LENGTH("DISPLAY_NAME",CODEUNITS16) <=  
   256)  ENFORCED ENABLE QUERY OPTIMIZATION
   GO
   Change these commands to the following commands:
ALTER TABLE LSW_USR_GRP_XREF DROP CHECK "DISPLAY_NAME"
GO
   ALTER TABLE LSW_USR_GRP_XREF ALTER "DISPLAY_NAME" SET DATA TYPE  
   VARCHAR(1024)
   GO
   ALTER TABLE LSW_USR_GRP_XREF DROP CHECK "DISPLAY_NAME" ADD CONSTRAINT
   "DISPLAY_NAME" CHECK (CHARACTER_LENGTH("DISPLAY_NAME",CODEUNITS16) <=  
   256)  ENFORCED ENABLE QUERY OPTIMIZATION
   GO

After making these changes, you can run the upgrade_7x command successfully with IBM DB2 V9.7.5.

Note:
This issue does not occur when you are migrating from WebSphere Lombardi Edition Version 7.2 to IBM Business Process Manager Version 8.0.1 with IBM DB2 V9.7.5. Alternately, you can migrate to IBM Business Process Manager V8.0.1 to avoid this error.

Cross reference information
Segment Product Component Platform Version Edition
Business Integration IBM Business Process Manager Express Migration Linux, Windows 8.0
Business Integration IBM Business Process Manager Standard Migration AIX, Linux, Windows 8.0

Historical Number

43990
999
616

Rate this page:

(0 users)Average rating

Document information


More support for:

IBM Business Process Manager Advanced
Migration

Software version:

8.0

Operating system(s):

AIX, Linux, Windows

Reference #:

1622266

Modified date:

2013-01-15

Translate my page

Machine Translation

Content navigation