IBM Tivoli Netcool/OMNIbus, Version 7.4

Review and amend your SQL queries, and create a selection of well-designed, efficient indexes

It is useful to understand how SQL queries are optimized so that you can construct efficient SQL queries. Review your existing SQL queries and amend them to benefit from optimization and indexing.

Read through the SQL query and indexing guidelines provided.

When designing and creating indexes, it is also useful to understand the characteristics of the ObjectServer database tables and columns, to help you gauge which indexed columns might help to enhance performance. Integer comparisons are faster than string comparisons, so if your event data contains strings that are constants, consider using integers to represent the strings within your rules files, automations, and filters, and then use conversions to display the strings to users. For example, the Class column in the ObjectServer is an integer data type, but is displayed as a string in event lists. You can take the following actions to pass event data as integers, which are then displayed as strings:

Revise your probe rules files to set integer values that map to corresponding strings.
Revise your automations (and Netcool/Impact policies if applicable) to use the integer values.
Revise your filters to use the integer values in WHERE clauses.
Add conversions that map the integer values to the string values that will be displayed to users.

You can use the CREATE INDEX SQL command to create indexes, and the DROP INDEX command to delete redundant indexes. Details about the indexes that you create are stored in the catalog.indexes table.

After revising your SQL queries and creating indexes, temporarily set the MessageLevel property to debug so that the execution time of individual SQL queries, and indexing details, will be logged. Allow a suitable period of processing activity.

Examine the ObjectServer log file $NCHOME/omnibus/log/server_name.log to:

Determine which SQL queries negatively impacted performance; check to see which queries took too long, or were executed repeatedly.
Determine which indexes were used.
Analyze the response times for SQL queries that are frequently used, and assess whether the benefits are significant or marginal.

The following steps describe how to determine the length of time a specific SQL query takes to run:

Look for an ObjectServer log message that is similar to the following sample:

2010-03-19T14:39:27: Debug: D-OBJ-105-010: Client language command on 
connection ID N: [user1][isql][][hostname.ibm.com] [SQL statement].

where SQL statement is the SQL statement you want to analyze.

Make a note of the connection ID number N.
Look for an ObjectServer log message that is similar to the one shown below, which contains the same value for N:
```
2010-03-19T14:39:27: Information: I-OBJ-104-016: Profiler timing submitted from 
connection ID N: time in seconds
```
where the time in seconds is the length of time taken for the SQL query to run.

Using the following ObjectServer log output as an example:

2010-03-19T14:39:27: Debug: D-OBJ-105-010: Client language command on connection
 ID 1: [user1][isql][][hostname.ibm.com] [select * from catalog.indexes;]. 2010-03-19T14:39:27: Information: 
I-OBJ-104-016: Profiler timing submitted from connection ID 1: 0.000265

You can see that it has taken 0.000265 seconds for the select * from catalog.indexes command to complete.

Note: Setting ObjectServer logging to debug mode can be performed without taking the ObjectServer offline. This setting can adversely affect performance, so you must switch off debug logging after the data has been collected.