Creating indexes in the Information Store database

In the Information Store, you can add indexes to the item type tables, and to the property type columns of those tables.

Indexes can improve the performance of Visual Query searches. Indexes can also improve the performance of the merged property values definition views if you are using them.

To create an index, you must use the informationStoreModifications.sql file. This script is run every time the Information Store database is created. You can also use the modifyInformationStoreDatabase toolkit task to run the script at any time.

The file must be in the configuration\environment\opal-server\databases\infostore directory. infostore is the value of the id attribute of the database element for your Information Store database in the topology.xml file.

In the Information Store database, the IS_DATA schema contains the tables for each item type. Entity item type tables are prefixed with E_ and link item type tables with L_. In these tables, property columns are prefixed with P_.

For example, your i2 Analyze schema might contain a Person entity with a First Given Name property. If you know that this property is used a lot by analysts in Visual Query searches, you might want to create an index on that table and column. To create a simple index for the First Given Name property, add the following statement to the informationStoreModifications.sql file:

CREATE INDEX IS_DATA.E_PERSON_FN_IX ON IS_DATA.E_PERSON (P_FIRST_GIVEN_NAME);

IS_DATA.E_PERSON_FN_IX is the name of the index to create, IS_DATA.E_PERSON is the table for the Person entity type, and P_FIRST_GIVEN_NAME is the column for the first given name property type.

To determine the syntax of the SQL statement that you must use to create the index, use the documentation for your database management system. For more information about creating indexes in a Db2 database, see CREATE INDEX statement and for SQL Server, see CREATE INDEX (Transact-SQL).

Identify the item types, and any of their property types, that you want to add indexes for.
Create the directory for the informationStoreModifications.sql file.
1. In the configuration\environment\opal-server directory, create the databases directory.
2. In the databases directory that you created, create the infostore directory.
  You can find the value to use for infostore in your topology.xml file. The value to use in your deployment is the value of the id attribute of the <database> element for your Information Store database.
  For example, configuration\environment\opal-server\databases\infostore.
Using a text editor, create a file that is named informationStoreModifications.sql in the configuration\environment\opal-server\databases\infostore directory.
Develop a script to create the indexes on the tables and columns that you identified in step 1 in the informationStoreModifications.sql file.
Save and close the file.
If the Information Store database exists, run the modifyInformationStoreDatabase toolkit task to run the script that you saved in step 4.
1. Open a command prompt and navigate to the toolkit\scripts directory.
2. Run the following command to run the informationStoreModifications.sql file:
```
setup -t modifyInformationStoreDatabase
```

Ensure that the indexes you expect are in the Information Store database. You can use IBM Data Studio or SQL Server Management Studio to see the indexes in the Information Store database.