Troubleshooting
Problem
RDM deployment results in index creation failure with DB2 SQL errors. This is seen on v11.6.0.2 on DB2. This index creation failure does not affect RDM functionality, however, may result in some slowness when working with RDM hierarchies.
Symptom
While installing RDM, user may see following errors on madconfig logs after deployment.
DB2 SQL Error: SQLCODE=-614, SQLSTATE=54008, SQLERRMC=VALUEPROPERTY_VAL, DRIVER=3.71.22
SQLCODE=-614, SQLSTATE=54008, SQLERRMC=UPPRPROVALIDX, DRIVER=3.71.22
Error Code: -614 SQLState: 54008
Listed below are the index on table RDVALUEPROPERTY that fail on creation:
- VALUEPROPERTY_VAL ON "RDVALUEPROPERTY" ("RD_PROPERTY_VAL")
- UPPRPROVALIDX ON "RDVALUEPROPERTY" ("UPPERPROPVAL")
Cause
In RDM v11.6.0.2 , OOTB database creation script uses CODEUNIT32 encoding as default string_units on database level. There is DB2 limit on index key length which is based on the page size of the tablespace used by the index . With CODEUNIT32 encoding, some RDM indexes length exceed the limit on index key length and cause index creation fail.
Environment
Deploy RDM v116.0.2 on DB2 LUW using CODEUNIT32 string_units.
Diagnosing The Problem
After deploying RDM, user will see error messages in the madconfig logs related to index creation failure with following error codes “SQLCODE=-614, SQLSTATE=54008”
Resolving The Problem
1. If RDM data is not loaded yet, user can redeploy RDM with DEFAULT (SYSTEM) string_units instead of CODEUNIT32.
From OOTB creatDB.sql, by commenting out following line, DB will be created with DEFAULT string_units
UPDATE DB CONFIG FOR <DBNAME> USING STRING_UNITS CODEUNITS32;
2. If RDM data is loaded, user can use admin_move_table store procedure to move RDVALUEPROPERTY table and index to larger page size tablespace and recreate indexes which failed during deployment.
Recommended tablespace size:
- Move RDVALUEPROPERTY table to 8K page size tablespace
- Move indexes to 16K page size tablespace
Please refer below for admin_move_table and index recreation examples:
Call SYSPROC.ADMIN_MOVE_TABLE('DBSCHEMA','RDVALUEPROPERTY','TBS8K','TBS16K','TBS16K','','','','','','MOVE');
CREATE INDEX "INST11DB"."VALUEPROPERTY_VAL" ON "RDVALUEPROPERTY"
("RD_PROPERTY_VAL" ASC)
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
CREATE INDEX "INST11DB"."UPPRPROVALIDX" ON "RDVALUEPROPERTY"
("UPPERPROPVAL" ASC)
PCTFREE 10 MINPCTUSED 10
COLLECT SAMPLED DETAILED STATISTICS
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
For detailed usage of admin_move_table store procedure, user can refer to DB2 knowledge center
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0055069.html
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg22010757