Text Index Management

About Text Index Management

This option allows you, as the cisdba user or the aspadm user, to create and maintain text indexes on columns of tables of a CES database. We currently support Text Index creation for properties only with data type VARCHAR.

This option deals with these functions:

Options under 1 and 2 can be performed only by the aspadm user and option 3 can be performed only by the cisdba user.

Before you can search your database using the Net Search Extender for the first time, run the following EDM options in the specified sequence:

  • Instance Setup > Start. This option starts the Net Search Extender services for an instance.

  • Database Setup > Enable. This option is used for enabling the database for text and needs to be run only once.

  • Database setup > Create tablespace. This option allows you (aspadm) to create a tablespace (CISTEXT_SPACE) to use for text indexes.

  • Text Indexes. This option creates a full text index on a text column and updates it for use in full text queries.

Instance Setup

This option allows you (aspadm) to perform text index management tasks at the instance level. Using this option, you can Start or Stop the Net Search Extender services for an instance.

This task explains the procedure to start or stop the Net Search Extender services for the current instance:
  1. Log on as aspadm and run edm.sh from $EDS_HOME/bin directory. For additional information, see Using CES Database Manager.

  2. From the EDM main window, select a database to be used for text index management.

  3. Select Tools > Text Index Management > Instance Setup option.

    The Instance Setup window appears
    .

  4. The name of the instance that has been set up for the Net Search Extender appears. Click Start or Stop to start or stop the Net Search Extender services.

  •    You must Start the Net Search Extender services for an instance, before you do these tasks:

    • Disable the Net Search Extender for a database

    • Create and update a Text index

    • Update a Text index

    • Drop a Text index

    • Modify update frequency for a Text index

    • Perform a text search

Note: Update and locking services are started when you Start NSE services.

  • Locking services controls the concurrent access to the Text Index and ensures that no two processes attempt to change the Text Index simultaneously. When an index is locked for the first time, memory is reserved for the database and the Text Index, in the locking services. If further, Text Indexes are locked, the memory is reallocated to these indexes.

  • This memory is freed only when the Text Index is dropped or the database is disabled for NSE or when the NSE services are restarted. It is recommended to Stop and then Start the services to release the memory held by locking services.

Database Setup

This option allows you (aspadm) to perform text index management tasks at the database level. Using this option, you can perform these tasks:

  • Enable/Disable the Net Search Extender for a database

  • Creating tablespace for text indexes

  • Creating the thesaurus

Enabling or Disabling Net Search Extender for Database

The Enable option prepares the connected database for use by the DB2 Net Search Extender. This is a mandatory step before a text index can be created on class properties in the database. When the database is enabled for Net Search Extender, the db2ext schema with different views such as db2ext.dbdefaults, db2ext.textindexformats, and db2ext.textindexs is created, and the text configurations are set.

The Disable option resets preparation work done by DB2 Net Search Extender for a database. It removes the Net Search Extender database objects for the connected database, so that it can no longer be used by other DB2 Net Search Extender commands.

This task explains the procedure to enable/disable the Net Search Extender:
  1. Log on as aspadm and run edm.sh from $EDS_HOME/bin directory. For additional information, see Using CES Database Manager.

  2. From the EDM main window, select a database for which the Net Search Extender is to be enabled / disabled.

  3. Select Tools > Text Index Management > Database Setup > Enable/Disable option.

    The Enable/Disable window appears
    .

     

    Name Description
    Instance Name The name of the instance that has been setup for the Net Search Extender appears.
    Database Name The name of the database for which the Net Search Extender is to be enabled/disabled appears.
  4. Click Enable to enable the Net Search Extender for a database.

  5. Click Disable to disable the Text Information for a database. If text indexes already exist for a database, a prompt appears asking you to confirm whether the Net Search Extender should be disabled for this database. If you click OK, the existing text indexes are dropped and the database is disabled.
     

Creating Tablespace for Text Indexes

This option allows you (aspadm) to create a tablespace (CISTEXT_SPACE) for use for text indexes.

This task explains the procedure to create tablespace for text indexes:
  1. Log on as aspadm and run edm.sh from $EDS_HOME/bin directory. For additional information, see Using CES Database Manager.

  2. From the EDM main window, select a database for which the text index tablespace is to be created.

  3. Select Tools > Text Index Management > Database Setup > Create Tablespace option.

    The Create Tablespace window appears
    .

     

    Name Description
    Name This is the text index tablespace name - CISTEXT_SPACE.
    Total Size For DMS tablespaces only, the total size of the tablespace will be displayed here.
    Type The tablespace type is Regular.
    Space Management This can be:
    • SMS (System Managed Tablespace)
    • DMS (Database Managed Tablespace)

      For SMS tablespaces, you should specify the path names of all its containers.For
      DMS tablespaces, you should specify the names of the files/devices that implement
      the containers and their sizes.
    Containers that make up this tablespace
    Container Name This is the name and the fully qualified path of the directory in which the index files are stored. For:
    • SMS - you need to specify the full path to a directory.
    • DMS - you need to specify the full path to a file or device.
    Size This is the storage size of the container. This is applicable only for DMS
    Unit This is the storage units of the container. This is applicable only for DMS and can be:
    • P (Page)
    • K (KB)
    • M (MB)
    • G (GB)

    You can perform these tasks:

    • Add a new Container Name. To do so, click Add. A window appears. You can specify the Container Name, Size and Storage Units if applicable and click OK to add it to the list.

    • Modify the details for a Container. To do so, select the Container Name from the list and click Edit. A window appears. You can modify the destination directory path and size/storage units if applicable. After you have made the changes, click OK to modify the Container details.

    • Delete newly defined Containers. To do so, select the Container to be deleted and click Delete to delete it. Predefined
      containers cannot be deleted.

      Name Description
      Page Size This can be 4K, 8K, 16K or 32K.
      Extent Size The space allocated to a database object within the tablespace.
      Prefetch Size The number of pages to be fetched from the tablespace in advance of being referenced
      in an attempt to anticipate page references and reduce writing for I/O.
      Buffer Pool Select a Buffer Pool from the list to associate to the tablespace.
  4. Click Create to create the tablespace for text indexes.

  5. Click Cancel to return to the EDM main menu.

Creating Thesaurus

A thesaurus is a controlled vocabulary of semantically related items that usually covers a specific subject area. The thesaurus can enhance the search capabilities of your application. Before creating the thesaurus, create a thesaurus definition file and compile into a thesaurus dictionary.
This option allows you to create the default thesaurus. This default thesaurus can be used while searching the text properties for which text indexes have been defined.

Using the Default Thesaurus

  • All the thesaurus entries should be defined in the default.def file and aspadm should have read permission on this file.

  • File can be located in the default directory (/<instance home>/sqllib/db2ext/thes) or in any directory where the generated thesaurus files have to be stored.

    Note: If you have stored the generated thesaurus files in a location other than the default one, you should create links from the default directory (/<instance home>/sqllib/db2ext/thes) to the actual location of the thesaurus generated files.

This task explains the procedure to create the thesaurus:

  1. Log on as aspadm and run edm.sh from $EDS_HOME/bin directory. For additional information, see Using CES Database Manager.

  2. From the EDM main window, select a database for which the default thesaurus is to be created.

  3. Select Tools > Index Management > Database Setup > Create Default Thesaurus option.

    The Create Default Thesaurus window appears
    .

     

    Name Description

    Instance Name

    The name of the instance enabled for the Net Search Extender.

    Database Name

    The name of the database enabled for the Net Search Extender.

    Thesaurus Directory

    The directory where you have saved the thesaurus definition file (default.def). The generated thesaurus files are stored in this directory. The default thesaurus directory is /<instance home>/sqllib/db2ext/thes. You can specify any directory, in which aspadm has write permission.

    The default.def file should follow the correct format as given below:

    :WORDS
    <TERM>
    .SYNONYM_OF <associated_term>
    ......
    .......
    ......
    .SYNONYM_OF <associated_term>
    :WORDS
    <TERM>
    .SYNONYM_OF <associated_term>
    ......
    .......
    ......
    .SYNONYM_OF <associated_term>
    For example
    :WORDS
    advisor
    .SYNONYM_OF consultant
    .SYNONYM_OF diagnosis
    .SYNONYM_OF expertise
    :WORDS
    education
    .SYNONYM_OF didactics
    .SYNONYM_OF insructorship
    .SYNONYM_OF instruction
    .SYNONYM_OF school
    .SYNONYM_OF teacher
    .SYNONYM_OF teaching

  4. Click Create to create the default thesaurus.

  5. Click Cancel to return to the EDM main window.

Text Indexes

This option allows you (cisdba) to perform text index management tasks at the database object level. Using this option, you can perform these tasks:

  • Create and update text indexes for system and user classes

  • Update a text index

  • Drop a text index

  • Modify update frequency for a text index

This task explains the procedure to perform text index management tasks:

  1. Log on as cisdba and run edm.sh from $EDS_HOME/bin directory. For additional information, see Using CES Database Manager.

  2. From the EDM main window, select a database to be used for text index management.

  3. Select Tools > Text Index Management > Text Indexes option.

  4. You are prompted for the cisdba1 password.

    After you enter the password, the Text Indexes window appears
    .

Name Description

Class Label

The name of the class for which you are creating, dropping or updating a text index.

Class DBName

The DB name of the class for which you are creating, dropping or updating a text index.

Property DB Name

The DB name of the property associated with the selected class. You can create, update or drop a text index for this property.

Property Label

The name of the property associated with the selected class. You can create, update or drop a text index for this property.

Data Type

The data type of the property. Currently only VARCHAR data type is supported.

Column Length

The length of the column.

Is Indexed

Indicates whether the property is already indexed.

Text Index Name

If it is indexed, the name of the text index appears.

Update Frequency

If the text index exists for this property, the frequency with which the text index should be updated appears.

You can retrieve eligible class properties for which you want to create, drop or update the text index as follows:

  • Select a Class Label or the Class DB Name. Depending on your selection, the list of Class Labels or the Class DB Names are displayed. Select one from the list or enter a Class Label or a Class DB Name in the Search field to select a Class Label or a Class DB Name. Click Get Properties to see a list of eligible properties for the selected class.

Based on your selection, the eligible properties are displayed on the window. You can perform these tasks:
 

  • Creating a text index for a property

    Select the property from the list and click Create. The Text Index Directory and Update Frequency/Auto Reorganize window appears. You are prompted for the directory in which the text index files have to be stored and the Update Frequency. The instance owner must own the index directory and the permissions must be set to 2715. In the Auto Reorganize window, specify AUTO REORGANIZE for the text index.

    For additional information about the Update Frequency, see Updating Frequency. < doc note : create link here> Specify these and click OK to create the text index. After the text index has been successfully created, the Is Column Indexed attribute for the property is set to YES.

Note:

When a text index is dropped and re-created, DB2 may not recognize the recreated index until you issue the following SQL statement as a user with DBADM/SYSADM authority.

db2=> FLUSH PACKAGE CACHE DYNAMIC

  • Updating an existing text index

    Select the property, which has a text index and click Update. This will refresh the text index and brings it up-to-date to reflect the current contents of the text column with which the index is associated.

  • Dropping a Text Index

    This option deletes all index files stored in the index directory and drops the text index from the database. Select the property for which the index is to be dropped and click Drop. The text index for this property will be dropped.

  • Modifying the Update Frequency for a Text Index

    This option allows you to change the frequency of an index update. Select a property, which has a text index and click Update Frequency. The Update Frequency window appears and you can modify the frequency details. For additional information about the frequency, see Updating Frequency. < doc note : create link here>

Updating Frequency

The frequency at which an index can be updated. You will be prompted for the D (day), H (hour) and M (minutes).

  • D. The day(s) of the week when the index is updated. * (everyday) or 0 to 6 (0=Sunday).

  • H. The hour(s) of the specified day(s) when the index is updated. * (every hour) or 0 to 23.

  • M. The minute(s) of the specified hour(s) when the index is updated. 0 to 59.