Refreshing a Text Index

Refresh a text index for a system store (IQ catalog store) table to bring it up to date when the underlying data has changed.

Prerequisites
Database Version Text Index Privileges
SAP Sybase IQ 15.3 and 15.4 Requires one of:
  • DBA authority.
  • REFERENCE permission on the underlying table being indexed.
  • You own the underlying table being indexed.

Also requires EXECUTE permission on the sa_text_index_stats system procedure to display a list of existing text indexes.
SAP Sybase IQ 16.0 Requires one of:
  • ALTER ANY INDEX system privilege.
  • ALTER ANY OBJECT system privilege.
  • REFERENCE permission on the underlying table being indexed.
  • You own the underlying table being indexed.
Also requires:
  • If the system procedure security model* of the selected database is set to Definer, you require EXECUTE permission on the sa_text_index_stats system procedure to display a list of existing text indexes.
  • If the system procedure security model* of the selected database is set to Invoker, you require one of the following system privileges to display a list of existing text indexes.
    • MANAGE ANY STATISTICS system privilege
    • CREATE ANY INDEX system privilege
    • ALTER ANY INDEX system privilege
    • DROP ANY INDEX system privilege
    • CREATE ANY OBJECT system privilege
    • ALTER ANY OBJECT system privilege
    • DROP ANY OBJECT system privilege
*The system procedure security model of the selected database appears on the General page of database properties.
Task
  1. In the Perspective Resources view, select the resource and select Resource > Administration Console.
  2. In the left pane, select IQ Servers > Schema Objects > Text Index.
  3. Select a text index from the right pane and either:
    • Click the arrow to the right of the name and select Refresh Data, or
    • From the Administration Console menu bar, select Resource > Refresh Data.
    Note: If the text is not available for refreshing, the Refresh Data button is grayed out.
    Tip: Use Shift-click or Control-click to select multiple text indexes.
  4. In the Text Index Refresh Data view, select the isolation level for the refresh:
    Level Description
    Read uncommitted (level 0)
    • Can read rows with or without write lock
    • Applies no read locks
    • Data may change during the refresh
    • Allows dirty reads, nonrepeatable reads, and phantom rows
    Read committed (level 1)
    • Can read only rows with no write lock
    • Read-locks only the current row and releases it immediately after reading
    • Data may change during the refresh
    • Prevents dirty reads
    • Allows nonrepeatable reads and phantom rows
    Repeatable read (level 2)
    • Can read only rows with no write lock
    • Read-locks each row as it is read; holds the lock until the refresh is done
    • Prevents dirty reads and nonrepeatable reads
    • Allows phantom rows
    Serializable (level 3)
    • Can read only rows with no write lock
    • Read-locks every row for the duration of the refresh operation
    • Prevents dirty reads, nonrepeatable reads, and phantom rows
    Snapshot
    • Applies no read locks
    • Can read any row
    • The database takes a snapshot of committed data when the refresh operation reads the first row
    Share mode (the default)
    • Allows other transactions to read the underlying table during the refresh operation
    • Uses shared table locks
    Exclusive mode
    • Does not change the isolation level
    • Locks the underlying table to ensure that the text index is updated to be consistent with committed data in the table
    • If an exclusive table lock cannot be obtained, the refresh fails
  5. Click OK to refresh the text index.
Parent topic: Text Indexes
Related tasks
Creating a Text Index
Deleting a Text Index
Truncating a Text Index
Generating Text Index DDL Commands
Viewing or Modifying Text Index Properties
Authenticating a Login Account for a Managed Resource
Related reference
Text Index Privilege Summary