Creating database indexes for class properties (DB2 for Linux, UNIX and Windows)

To optimize the performance of those queries that search a class property, create an index for the property in the object store database. The index that you create is a specific type of generated-column index in the following circumstances: case-insensitive search behavior is in effect, and the object store database is a DB2® for Linux, UNIX and Windows database.

To create a generated-column index for a class property:

1. Connect to the object store database by using DB2 for Linux, UNIX and Windows command line tools or GUI database tools.
2. Create the index with the following commands:
   1. Set integrity off.

      Command syntax	DB2 SET INTEGRITY FOR table_name OFF
      Multi-valued property example	DB2 SET INTEGRITY FOR schemaName.LISTOFSTRING OFF
      Single-valued property example	DB2 SET INTEGRITY FOR DocVersion OFF

      table_name

      The table that contains the column. For a single-valued property, the table names are DocVersion for documents, Generic for custom objects, or Container for folders. For a multi-valued property, the table name begins with ListOf.

   2. Add the column.

      Command syntax	DB2 ALTER TABLE table_name ADD COLUMN gen_col_name VARCHAR(byte_length) GENERATED ALWAYS AS (LOWER(orig_column_name))
      Multi-valued property example	DB2 ALTER TABLE schemaName.LISTOFSTRING ADD COLUMN EV_LOWER VARCHAR(byte_length) GENERATED ALWAYS AS (LOWER(ELEMENT_VALUE)) Important: Specify byte_length to be no larger than 8151 for the following reason: the index row length limit is 8 KB (8192 bytes) and some allowance must be made for system overhead and two more columns.
      Single-valued property example	DB2 ALTER TABLE DocVersion ADD COLUMN genDocTitle VARCHAR(byte_length) GENERATED ALWAYS AS (LOWER(u2e_documenttitle)) Important: Specify byte_length to be no larger than 8189.

      table_name

      The table that contains the column.

      gen_col_name

      A new name to be given for the generated column that is created by running the SQL commands.

      byte_length

      The byte length for the column. Specify the length as indicated by the multi-valued and single-valued property examples. The indicated column-length limits, however, are the result of the following assumption: a Varchar character requires 3 bytes of storage. In practice, this storage requirement can be relaxed for many languages. You must decide to what extent you can safely relax this requirement (if at all). Content Platform Engine generates errors if the generated column is not large enough to store a string value. For more information about column-length limitations, see Column-length limitations for generated-column database indexes for class properties (DB2 for Linux, UNIX and Windows)

      orig_column_name

      The original case-sensitive column in the table.

   3. Set integrity on.

      Command syntax	DB2 SET INTEGRITY FOR table_name IMMEDIATE CHECKED FORCE GENERATED
      Multi-valued property example	DB2 SET INTEGRITY FOR schemaName.LISTOFSTRING IMMEDIATE CHECKED FORCE GENERATED
      Single-valued property example	DB2 SET INTEGRITY FOR DocVersion IMMEDIATE CHECKED FORCE GENERATED

      table_name

      The table that contains the column.

   4. Create the index.

      Command syntax	DB2 CREATE INDEX idx_gen_name ON table_name (gen_col_name)
      Multi-valued property example	DB2 CREATE INDEX I_EV_LOWER ON schemaName.LISTOFSTRING (EV_LOWER ASC, PARENT_PROP_ID ASC, PARENT_ID ASC)
      Single-valued property example	DB2 CREATE INDEX I_genDocTitle ON DocVersion (genDocTitle)

      idx_gen_name

      The index name for the index that is created on the generated column.

      table_name

      The table that contains the column.

      gen_col_name

      A new name to be given for the generated column that is created by running the SQL commands.

3. In the administration console, set the maximum length of the property:
   1. Opening the property definition for a dependent class property. Open the property definition.
   2. In the property definition, set the value of the Maximum Length String property:

      Multi-valued property	Set the maximum length to be no larger than 2717. This limit allows the byte length for the generated column to be no larger than the allowed maximum for the LISTOFSTRING table: 8151 (2717 * 3).
      Single-valued property	Set the maximum length to be no larger than 2729. This limit must be correspondingly smaller if you add more columns to the database index.

   3. Save your changes.