The Index Utility Ixutil

The ixutil utility supports reorganizing an SPD Server hybrid index to improve query performance and minimize disk space. The utility also prints the disk usage statistics or the contents of indexes.

Ixutil Usage

ixutil -crejidx <data set1,column1> < data set2,column2> ... <data set_n,column_n>-libpath <physical path> -joinparts< number of parallel join work units > ;
Create a join index for a pair of data sets in the same domain that can be used by the SPD Server Parallel Range Join optimization. The columns must already be indexed. The recommended number of parallel join work units is two times the number of processors.
ixutil -deljidx <data set1,column1> < data set2,column2> ... <data set_n,column_n>-libpath <physical path> ;
Delete a join index.
ixutil -lstjidx -libpath <physical path> [-verbose] ;
List the join indexes in a domain.
ixutil -statjidx <data set1,column1> < data set2,column2> ... <data set_n,column_n>-libpath <physical path> ;
Gather statistics about the join index parts. Pay attention to the average join row percentage, which indicates the average number of rows that are read by a parallel join work unit. For example, a percentage of 75 indicates the parallel join work unit will use 75 percent of the rows it must read. The closer the percentage is to 100, the better the performance. The percentage will increase as the distribution of the data for the join column becomes more sorted.
ixutil -stats< indx1,indx2,...>-dsn<data set> -libpath<physical path> [-dist] ;
For a specified set of indexes that belong to a given table, print the disk usage statistics and segment list fragmentation statistics. Each value in the index has a segment list. A value's segment list can become fragmented when the index is updated. An index that is highly fragmented can degrade query performance and waste disk space. To improve performance and reclaim the wasted disk space, the index should be reorganized using the -reorg option of ixutil.
ixutil -runstats<indx1,indx2,...>-dsn< data set name> -libpath<physical path> [-maxruns<number>] ;
For a specified set of indexes that belong to a given table, print the run statistics for each index. Run statistics give an indication of how the values of a particular index are sorted in relation to their observation numbers. By default, ixutil run stats display the ten longest runs in the data set. A run is defined as the number of successive observations that contain the same index value. The optional [-maxruns<number>] argument can be used to change from the default setting of 10 runs to any integer between 1 and 100. Ixutil run stats can be useful in constructing more efficient BY and WHERE clause constructs for the data set.
ixutil -reorg <index1,index2,...>-dsn< data set name> -libpath<physical path> ;
Reorganize the specified set of indexes in a table to reclaim wasted disk space and to aggregate the per-value segment lists. Reorganizing an index results in optimal disk usage and query performance.
ixutil -fixclustmem -dsn <data set name> -libpath < physical path>
Make cluster member tables accessible, if the CDF metadata is corrupted or deleted. Run this command for each member table in the cluster, and then remove the .cdf file from the directory path.
ixutil -help
Print the help menu.

Ixutil Options

-crejidx <data set1,column1> < data set2,column2> ... <data set_n,column_n>
Create a join index for SPD Parallel Join use.
-deljidx <data set1,column1> < data set2,column2> ... <data set_n,column_n>
Delete a Join index.
-dist
Include the distribution statistics with the index statistics.
-lstjidx-libpath<library path>
List the join indexes for a domain.
-statjidx < data set1,column1> <data set2,column2> ... < data set_n,column_n>
Get statistics about a join index.
-stats <index,index...>
For each specified index, prints the index disk usage statistics and value segment list statistics.
-runstats < index,index...> [
For each specified index, print the "run" statistics. "Runs" are defined as successive observations in a table that contain the same index value
-reorg <index,index...>
For each index, reorganize the index to reclaim any unused disk space and coalesce any fragmented value segment lists.
-joinparts <number of parallel join work units>
Specifies the number of parallel join work units for a join index. Parallel join threads will join the work units concurrently and then merge their partial results into the final result.
-dsn<data set name>
The SPD Server table that contains the index.
-libpath<physical path>
The physical path of the domain containing the table.
-help
Prints the ixutil help menu.
Note: Index names are case sensitive and MUST be specified exactly as listed in the PROC CONTENTS output.