Improving Visual Query performance for multi-line string property types

If your deployment of i2 Analyze uses an IBM Db2 database, and you originally deployed a version earlier than 4.3.5, then you can improve the performance of Visual Query searches for multi-line string properties by updating the Information Store database. After you update the Information Store, the datatype that's used to store multi-line string property values is changed from a CLOB to a VARCHAR.

Before you begin

  • The allowed size of a CLOB column is slightly larger than the size of a VARCHAR column. If a CLOB column contains more than 32,672 bytes (or octets), then it cannot be converted and no other CLOB columns in that table will be converted. Before you attempt to convert the CLOB columns, ensure that all data is less than 32,672 bytes in size.

  • You can use a toolkit task to update the multi-line string columns automatically, or you can generate the SQL scripts that update the Information Store database and run those scripts manually.

  • If i2 Analyze is deployed with high availability or disaster recovery, the process for updating the Information Store is different. For more information, see HADR.

If you want to update the Information Store using the SQL scripts, see Updating the Information Store manually.

Procedure

  1. Open a command prompt on the server, and navigate to the toolkit\scripts directory of the i2 Analyze toolkit.

  2. Run the following command:

    setup -t convertClobsToVarchars

  3. Restart i2 Analyze:

    setup -t restartLiberty

When Liberty starts, the process of updating the multi-line string columns is started. This process works in the same way as the processing that occurs to the Information Store after an upgrade. For more information about this processing, and how to monitor its progress, see: Information Store processing.

While the process is running, data cannot be ingested or uploaded into the Information Store.

Updating the Information Store manually

To generate the database scripts that update the columns so that you can run them manually, run the convertClobsToVarchars task with the --scripts argument. For example:

setup -t convertClobsToVarchars --scripts

Note: As part of the script generation process, each table that contains a CLOB column is checked to ensure the data in that CLOB column will fit inside a VARCHAR(32672) column. This process alone might take several hours depending on the number of rows in each table and the number of CLOB columns. If you know that there are no CLOB columns that contain data that is too large, you can use the -p skipSizeCheck parameter to skip this process.

When you run the command, the SQL scripts are generated in the toolkit\scripts\database\db2\InfoStore\generated\convert-clobs-to-varchars directory. There is a script for each item type, for example 0200-0300-clob-to-varchar_ET1.sql. The item type scripts can be run in parallel.

Use the Db2 command line processor to run the generated SQL scripts.

Data size

When running the convertClobsToVarchars toolkit task, the following message is displayed if the data in a CLOB column is more than 32,672 bytes (or octets):

The <table name> table contains a CLOB column that is too long to fit into a VARCHAR column. Conversion of this table has been abandoned.

To remedy the issue, truncate the data in the specified CLOB columns, re-run the convertClobsToVarchars toolkit task, and restart the server.

When running the SQL scripts, the following messages are displayed in the toolkit log file if the data in a CLOB column is more than 32,672 bytes (or octets):

Column <column name> in table <table name> has a defined length of <n> which is greater than the maximum permitted size of <n>. (Skipping table conversion).
Found: <n> values with length greater than 32,672 in table: <table name>, column: <column name>. (Skipping table conversion).

To remedy the issue, truncate the data in the specified CLOB columns, re-run the convertClobsToVarchars --scripts toolkit task, and run the scripts again.

Error handling

The process to convert columns from CLOB to VARCHAR is designed to be re-run in the event of a failure.

When you start the Liberty server, monitor the console.log to ensure that the process completes successfully. If an error does occur, the process will continue when you restart the server after you resolve the cause of the error.

If you are running the scripts manually, errors are reported in the Db2 command line processor. You can rerun the same script after you resolve the cause of the error.

The most common error is that the Db2 server runs out of disk space while populating the replica tables that are used to retain data while the data type is updated.

HADR

If the i2 Analyze deployment is in HADR mode, you must update the database by using the following procedure:

  1. Backup the primary database

  2. Generate the update scripts and run the scripts on the primary database

  3. Backup the primary database

  4. Disable HADR

  5. Restore the primary database onto the secondary server

  6. Re-enable HADR