Creating a long string index in a Db2 database

Create a long string text index to support filtering based on the contents of fields with long string data types. Scripts are provided for Microsoft Windows and Linux®.

Important: In Linux operating systems, when using asterisks (*) as parameter values in long string search scripts, the asterisks must be properly escaped with a double quotation mark, single quotation mark combination: '*'.

Before you begin

Complete the following tasks:

Procedure

  1. Log on to a system as a user with Administrator privileges. You can use any system with access to CLPPlus that can connect to the IBM OpenPages® database server.
    Note: For SQL tool information, see the Database tool information topic at Administrator's Guide.
  2. Open a command or shell window, and then navigate to the text indexing directory.
    The following table identifies the installation location of the application on the Microsoft Windows and Linux operating systems.
    Table 1. Installation location of the full-text-index directory
    Operating system Installation location
    Windows <OP_HOME>\aurora\bin\full-text-index
    Linux <OP_HOME>/aurora/bin/full-text-index
  3. Run the following script: 
    clpplus -nw @sql-wrapper CustomIndexing_Step2_IndexCreate.sql 
   <LOG_FILE_NAME> <DB2_SERVER_NAME>:<DB2_PORT_NUMBER>/<DATABASE_NAME> 
   <OP_DB_USER> <OP_DB_PASSWORD> <UPDATE_FREQUENCE_WEEKDAY> 
   <UPDATE_FREQUENCE_HOUR> <UPDATE_FREQUENCE_MINUTE> <MINIMUM_UPDATES>
    Table 2. Create Db2 long string index required script parameters
    Required Parameter Description
    <LOG_FILE_NAME>

    Name of the log file.
    <DB2_SERVER_NAME>

    Name of the Db2 server.
    <DB2_PORT_NUMBER>

    Port number of the Db2 database server.
    <DATABASE_NAME>

    OpenPages database name.
    <OP_DB_USER>

    OpenPages username for accessing the OpenPages database.
    <OP_DB_PASSWORD>

    Password of the <OP_DB_USER> user.

    If the password contains special characters, surround the password in quotation marks:
    • Windows: "password"
    • Linux: 'password'
    <UPDATE_FREQUENCE_WEEKDAY>

    Weekday update frequency.

    Accepted values are between 0 and 6. To enter multiple values, separate them with commas. For all weekdays, use * (asterisk).
    <UPDATE_FREQUENCE_HOUR>

    Hourly update frequency.

    Accepted values are between 0 and 23. To enter multiple values, separate them with commas. For all hours, use * (asterisk).
    <UPDATE_FREQUENCE_MINUTE>

    Minute update frequency.

    Accepted values are between 0 and 59. To enter multiple values, separate them with commas.

    Typically, values are specified as the top of the hour (0), or in multiples of 5-minute increments after the hour, for example, 0, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, or 55.
    <MINIMUM_UPDATES>

    The minimum number of updates in the base table before a scheduled index update runs.

    The following example shows a Windows script:

    clpplus -nw @sql-wrapper CustomIndexing_Step2_IndexCreate.sql 
   CustomIndexing_Step2_IndexCreate.log server1:50000/opx opuser 
   "password" "*" "*" "0,5,10,15,20,25,30,35,40,45,50,55" 1

    The following example shows the same script on Linux:

    clpplus -nw @sql-wrapper CustomIndexing_Step2_IndexCreate.sql 
   CustomIndexing_Step2_IndexCreate.log server1:50000/opx opuser 
   'password' "'*'" "'*'" "0,5,10,15,20,25,30,35,40,45,50,55" 1

    These examples create an index with updates that start every 5 minutes of every hour of every weekday if there is a minimum of one update to the PROPERTYVALS_CLOB table.

Results

An index is created for long string fields.