Error due to incorrect character encoding of database during database transfer

I was getting the following error when executing ./ConfigEngine database-transfer configuration command to transfer database from derby to DB2. I was facing this error because my RELDB61 was not created with UTF-8 as character encoding. So when portal tried creating a table with var char it failed

In my case i did create the RELDB61 database using the DB2 Control center by right clicking on the Database -> Create database.

[sqlproc] CREATE TABLE release.WSRP_PROD_LOD (
[sqlproc] WSRP_PROD_OID CHAR(18) FOR BIT DATA NOT NULL,
[sqlproc] LOCALE VARCHAR(64) NOT NULL,
[sqlproc] TITLE VARGRAPHIC(255) NOT NULL,
[sqlproc] DESCRIPTION VARGRAPHIC(1024),
[sqlproc] CONSTRAINT PK560 PRIMARY KEY (WSRP_PROD_OID, LOCALE),
[sqlproc] CONSTRAINT FK560 FOREIGN KEY (WSRP_PROD_OID) REFERENCES release.WSRP_PROD (OID) ON DELETE CASCADE
[sqlproc] )

[sqlproc] Error during statement execution!
[sqlproc] com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -1216, SQLSTATE: 56031, SQLERRMC: null
[sqlproc] at com.ibm.db2.jcc.b.sf.e(sf.java:1680)
[sqlproc] at com.ibm.db2.jcc.b.sf.b(sf.java:1207)
[sqlproc] at com.ibm.db2.jcc.c.jb.i(jb.java:224)
[sqlproc] at com.ibm.db2.jcc.c.jb.b(jb.java:50)
[sqlproc] at com.ibm.db2.jcc.c.w.b(w.java:36)
[sqlproc] at com.ibm.db2.jcc.c.cc.f(cc.java:150)
[sqlproc] at com.ibm.db2.jcc.b.sf.m(sf.java:1204)
[sqlproc] at com.ibm.db2.jcc.b.sf.a(sf.java:1987)
[sqlproc] at com.ibm.db2.jcc.b.sf.e(sf.java:809)
[sqlproc] at com.ibm.db2.jcc.b.sf.execute(sf.java:793)
[sqlproc] at com.ibm.wps.config.SqlProcessor.process(SqlProcessor.java:609)
[sqlproc] at com.ibm.wps.config.SqlProcessor.process(SqlProcessor.java:148)
[sqlproc] at com.ibm.wps.config.SqlProcessorTask.executeSqlScript(SqlProcessorTask.java:774)
[sqlproc] at com.ibm.wps.config.SqlProcessorTask.execute(SqlProcessorTask.java:327)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.taskdefs.Sequential.execute(Sequential.java:64)
[sqlproc] at com.ibm.wps.config.If.execute(If.java:75)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.taskdefs.Sequential.execute(Sequential.java:64)
[sqlproc] at com.ibm.wps.config.If.execute(If.java:75)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.Target.execute(Target.java:341)
[sqlproc] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[sqlproc] at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
[sqlproc] at org.apache.tools.ant.helper.SingleCheckExecutor.executeTargets(SingleCheckExecutor.java:37)
[sqlproc] at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
[sqlproc] at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:382)
[sqlproc] at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:107)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at com.ibm.wps.config.ForTask.execute(ForTask.java:56)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.Target.execute(Target.java:341)
[sqlproc] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[sqlproc] at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
[sqlproc] at org.apache.tools.ant.helper.SingleCheckExecutor.executeTargets(SingleCheckExecutor.java:37)
[sqlproc] at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
[sqlproc] at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:382)
[sqlproc] at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:107)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at com.ibm.wps.config.ForTask.execute(ForTask.java:56)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.Target.execute(Target.java:341)
[sqlproc] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[sqlproc] at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
[sqlproc] at org.apache.tools.ant.helper.SingleCheckExecutor.executeTargets(SingleCheckExecutor.java:37)
[sqlproc] at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
[sqlproc] at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:382)
[sqlproc] at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:107)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.Target.execute(Target.java:341)
[sqlproc] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[sqlproc] at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
[sqlproc] at org.apache.tools.ant.Project.executeTarget(Project.java:1185)
[sqlproc] at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:40)
[sqlproc] at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
[sqlproc] at org.apache.tools.ant.Main.runBuild(Main.java:668)
[sqlproc] at org.apache.tools.ant.Main.startAnt(Main.java:187)
[sqlproc] at org.apache.tools.ant.Main.start(Main.java:150)
[sqlproc] at com.ibm.wps.config.ConfigEngine.process(ConfigEngine.java:861)
[sqlproc] at com.ibm.wps.config.ConfigEngine.main(ConfigEngine.java:236)
[sqlproc] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[sqlproc] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
[sqlproc] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[sqlproc] at java.lang.reflect.Method.invoke(Method.java:618)
[sqlproc] at com.ibm.ws.bootstrap.WSLauncher.main(WSLauncher.java:263)
[sqlproc] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[sqlproc] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
[sqlproc] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[sqlproc] at java.lang.reflect.Method.invoke(Method.java:618)
[sqlproc] at com.ibm.wps.config.launch.WpsConfigLauncher.process(WpsConfigLauncher.java:243)
[sqlproc] at com.ibm.wps.config.launch.WpsConfigLauncher.main(WpsConfigLauncher.java:459)
[sqlproc] (ErrCode=-1216, SqlState=56031)
[sqlproc] _________________________________________________________
[sqlproc] Ant Database Properties:

[sqlproc] DbDomain specific settings (release):
[sqlproc] release.DbType = db2
[sqlproc] release.DbUrl = jdbc:db2://dbserver.wpcertification.com:50000/RELDB61
[sqlproc] release.DbName = RELDB61
[sqlproc] release.DbSchema = release
[sqlproc] release.DbUser = db2admin
[sqlproc] release.DbPassword = PASSWORD_REMOVED

[sqlproc] DbType specific settings (db2):
[sqlproc] db2.DbDriver = com.ibm.db2.jcc.DB2Driver
[sqlproc] db2.DbLibrary = /software/IBM/db2jcc.jar | /software/IBM/db2jcc_license_cu.jar
[sqlproc] _________________________________________________________
[sqlproc] _________________________________________________________

BUILD FAILED
/software/WebSphere/wp_profile/ConfigEngine/base_dynamic.xml:821: The following error occurred while executing this line:
/software/WebSphere/wp_profile/ConfigEngine/config/actions/db_cfg_mgr.xml:290: The following error occurred while executing this line:
/software/WebSphere/PortalServer/base/wp.db.impl/config/includes/wp.db.impl_cfg.xml:95: The following error occurred while executing this line:
/software/WebSphere/PortalServer/base/wp.db.impl/config/includes/wp.db.impl_cfg.xml:337: ERROR: Error during the execution of the sql files.

Total time: 3 minutes 1 second
isIseries currently set to: null
uploading registry
CELL: spinfotech
Websphere:_Websphere_Config_Data_Type=Registry,_Websphere_Config_Data_Id=cells/spinfotech|registry.xml#Registry_1242441339497,_WEBSPHERE_CONFIG_SESSION=anonymous1242503359324


In order to solve this problem, i had to drop all the databases that i created using DB2 Control center and re-create them using the following DB2 script

db2 "CREATE DB RELDB61 using codeset UTF-8 territory us PAGESIZE 8192"
db2 "UPDATE DB CFG FOR RELDB61 USING applheapsz 4096"
db2 "UPDATE DB CFG FOR RELDB61 USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR RELDB61 USING stmtheap 32768"
db2 "UPDATE DB CFG FOR RELDB61 USING dbheap 2400"
db2 "UPDATE DB CFG FOR RELDB61 USING locklist 1000"
db2 "UPDATE DB CFG FOR RELDB61 USING logfilsiz 4000"
db2 "UPDATE DB CFG FOR RELDB61 USING logprimary 12"
db2 "UPDATE DB CFG FOR RELDB61 USING logsecond 20"
db2 "UPDATE DB CFG FOR RELDB61 USING logbufsz 32"
db2 "UPDATE DB CFG FOR RELDB61 USING avg_appls 5"
db2 "UPDATE DB CFG FOR RELDB61 USING locktimeout 30"
db2 "UPDATE DB CFG FOR RELDB61 using AUTO_MAINT off"


This is only for Release database so i had to recreate COMDB61, CUSDB61, JCRDB61, FDBKDB61, LMDB61 database again using the similar code.

After executing the code i retried the database-transfer configuration task and it seems to be working now

No comments: