Creating a Text Index

Set up a text index for a table.

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 along with CREATE permission on the dbspace where the index is being created. You own the underlying table being indexed along with CREATE permission on the dbspace where the index is being created. 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: CREATE ANY INDEX system privilege along with CREATE permission on the dbspace where the index is being created. CREATE ANY OBJECT system privilege. 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.

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 along with CREATE permission on the dbspace where the index is being created.
You own the underlying table being indexed along with CREATE permission on the dbspace where the index is being created.

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:

CREATE ANY INDEX system privilege along with CREATE permission on the dbspace where the index is being created.
CREATE ANY OBJECT system privilege.

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

In the Perspective Resources view, select the resource and select Resource > Administration Console.
In the left pane, select IQ Servers > Schema Objects > Text Indexes.
Click the arrow next to Text Indexes and select New.
The Create Text Index Wizard appears.

On the Text Index Name page, specify

Option	Description
Select a resource for which the view will be created	From the list, select the resource for which the text index will be created.
For which table do you want to create the text index?	From the list, select the table.
What do you want to name the text index?	Enter a unique name for the new text index; maximum of 128 characters.

Click Next.
On the Columns page, select the columns to be included in the index.
Click Next.
On the Text Configuration page, select the text configuration object to be included in the index.
If the index is on a main store table, skip to step 11. If the index is on a table in the system store (catalog store), you see a page that lets you select a refresh type.

(System store tables only) On the Refresh Type page, specify:

Option	Description
Immediate	The text index is automatically refreshed when data in the underlying table changes.
Manual	The text index is refreshed only when explicitly requested.
Automatic	The text index is automatically refreshed using a time interval that you specify. If you select Automatic, set the interval at which the text index refreshes

Note: If the index is on a main store table, it refreshes automatically whenever the data in the underlying table changes.

Click Next.
On the Dbspace page, select the dbspace in which you want to store the text index.
Click Next.
(Optional) On the Comment page, enter a comment for the text index.
Click Finish to create the text index.