Troubleshooting
Problem
After you start the db2mon performance monitoring tool in DB2, some i2 Analyze operations might stop working correctly.
For example, performing an Expand operation in Analyst's Notebook connected to the Information Store may get an error "A web response error occurred"
An Expand with conditions operation may get an error "Failed to retrieve connected records"
Other operations might also start to fail, with other error messages.
Cause
A temporary table cannot be indexed because the database page size is too small.
When i2 Analyze processes these user operations, it often creates temporary tables in DB2. At installation, i2 Analyze creates a Tablespace named IS_TEMP_IS in DB2 to hold these temporary tables. Tablespace IS_TEMP_IS has a 16K page size.
Db2mon creates its own Tablespace called DB2MONTMPTBSP for temporary tables. This has a page size of 4K.
At runtime, the DB2 Optimizer can choose either IS_TEMP_IS or DB2MONTMPTBSP to create Temporary tables. The Optimizer chooses whichever tablespace which can hold the most rows, at runtime.
Hence, an i2 Analyze temporary table might be created in the db2mon tablespace DB2MONTMPTBSP, instead of the i2 Analyze tablespace IS_TEMP_IS. For some operations, this is harmless. However for other operations, especially creating an Index on a temporary table, the 4K page size is too small. So the SQL operation fails with SQLCODE=-614, SQLSTATE=54008. In turn, an error message is displayed in the user interface.
Environment
This problem occurs if DB2's db2mon.sh script has been run.
Even after the db2mon script has been stopped, a tablespace DB2MONTMPTBSP can remain defined in DB2. This may cause errors in i2 Analyze.
Diagnosing The Problem
Configure Liberty tracing in server.xml with "trace.specification = *=audit:com.ibm.websphere.wim.*=all:com.ibm.ws.wim.*=all"
In the resulting trace.log, observe SQL errors similar to the following.
[12/23/20 0:26:01:498 AEDT] 0000005c SystemOut O 2020-12-23 00:26:01,498 - [com.i2group.apollo.common.toolkit.internal.ConsoleLogger] - (opal-services-is) - Errors occurred accessing system resources from InformationStoreResourceDelegate. The likely cause is logged at FATAL. (The full exception is logged at WARNING if enabled):
[12/23/20 0:26:01:499 AEDT] 0000005c SystemOut O 2020-12-23 00:26:01,499 - [com.i2group.apollo.common.toolkit.internal.ConsoleLogger] - (opal-services-is) - Failed Resource Type: [DATABASE]: Unable to perform an operation against one of the data sources for the InformationStoreResourceDelegate. Check stack trace for further details. - com.ibm.db2.jcc.am.SqlException: The index or index extension "RECORDIDS0_IX1" cannot be created or altered because the combined length of the specified columns is too long.. SQLCODE=-614, SQLSTATE=54008, DRIVER=4.21.29
[12/23/20 0:26:01:499 AEDT] 0000005c SystemOut O 2020-12-23 00:26:01,499 - [com.i2group.apollo.common.toolkit.internal.ConsoleLogger] - (opal-services-is) - Failed Resource Type: [DATABASE]: Unable to perform an operation against one of the data sources for the InformationStoreResourceDelegate. Check stack trace for further details. - com.ibm.db2.jcc.am.SqlException: The index or index extension "RECORDIDS0_IX1" cannot be created or altered because the combined length of the specified columns is too long.. SQLCODE=-614, SQLSTATE=54008, DRIVER=4.21.29
SQLCODE=-614 means "The Index cannot be created or altered, or the length of a column cannot be changed because the sum of the internal lengths of the columns for the Index is greater than the allowable maximum".
Resolving The Problem
Drop the existing DB2MONTMPTBSP Tablespace.
Modify the db2mon.sh shell script so it will create the DB2MONTMPTBSP Tablespace with a 16K pagesize, by using these lines:
create bufferpool DB2MONBP pagesize 16K ;
create user temporary tablespace DB2MONTMPTBSP pagesize 16K bufferpool DB2MONBP ;
The run db2mon.sh normally.
After this change, the DB2MONTMPTBSP tablespace will be able to hold and process temporary tables created by i2 Analyze.
Document Location
Worldwide
[{"Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSXVXZ","label":"IBM i2 Enterprise Insight Analysis"},"ARM Category":[{"code":"a8m500000008Z98AAE","label":"i2 Enterprise Insight Analysis"}],"ARM Case Number":"TS004716554","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]
Historical Number
TS004716554
Was this topic helpful?
Document Information
Modified date:
19 January 2021
UID
ibm16404280