Text indexes on views

You can create text indexes on views using the stored procedure or table-valued function, however, you are not allowed to include any scalar functions, for example, CONTAINS.

Another major drawback is that you cannot create triggers on views, so any changes in the underlying base tables are not recognized.

So with incremental index updates, the user has to know which document has been added, updated, or deleted in order to synchronize the text index with the database. To do this, you must add all the changes to the log table. This process is shown in the following sample:

  1. To create the base table, use the following command:
    db2 "create table sample (key INTEGER not null  PRIMARY KEY, name 
                VARCHAR(50) not null, comment VARCHAR(90))" 
  2. To add some entries, use the following commands:
    db2 "insert into sample values(1,'Claus','works in room 301')"
    db2 "insert into sample values(2,'Manja','is in the same office
                                   as Juergen')"
    db2 "insert into sample values(2,'Juergen','has the longest way to 
                                   Raiko')"
    db2 "insert into sample values(3,'Raiko','is sitting in the office 
                                   besides Claus ')"
  3. To create the view, use the following command:
    db2 "create view sampleview as select key, comment from sample"
  4. Use the following commands to create, update, and activate the text index:
    db2text "create index indexview for text on hde.sampleview(comment) 
                cache table (comment) maximum cache size 1 key columns 
                for index on view (key)" 
    db2text "update index indexview for text"
    db2text "activate cache for index indexview for text"
    Note

    You need to specify the cache table to be able to create a text index on a view. To create the correct log table, you must specify the key columns for the index on view. If you create an index in this way, you can also use the index with the table-valued function.

    When you use the stored procedure search in a distributed DB2 environment, you must explicitly specify a tablespace for administration tables on a single node and explicitly call on this node. To ensure that you connect to the correct node, use the DB2NODE environment variable.

  5. To update the table, use the following commands:
    db2 "insert into sample values(4,'Bernhard','is working in the same floor 
                as Manja, but not as Claus')"
    db2 "insert into sample values(5,'Guenter','shares the office with Raiko')"
  6. Then update the log table. To get the name of the log table, use the following command:
    db2 "select INDSCHEMA,INDNAME,LOGVIEWSCHEMA,LOGVIEWNAME 
                from db2ext.textindexes"
    This is the layout of the log table:
    sqltype               sqllen  sqlname.data             sqlname.length
    --------------------  ------  -----------------------  --------------
    496   INTEGER              4  OPERATION                             9
    392   TIMESTAMP           26  TIME                                  4
    497   INTEGER              4  PK01                                  4   
    To add the entries into the log table, use the following commands:
    db2 "insert into sample values(0,CURRENT TIMESTAMP,4)"
    db2 "insert into sample values(0,CURRENT TIMESTAMP,5)"
    The first value describes the operation (0 = insert, 1 = update, 2 = delete). The second should always be the CURRENT TIMESTAMP and the last value, the key which has been inserted.
  7. Use the following command to update the index again:
    db2text "update index indexview for text"

You can now search with the stored procedure on the new values.