The Index Utility ixutil

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

ixutil Usage

Invoking Ixutil as a Stand-alone Utility

Ixutil Options

ixutil Usage

Note: Index names are case sensitive. You must specify them exactly as they are listed in the PROC CONTENTS output.

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 that are in the same domain. The join index can be used by the SPD Server to optimize parallel range joins. 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 that are 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. The average join row percentage indicates the average number of rows that a parallel join work unit reads. For example, a row percentage of 75 indicates that the parallel join work unit uses 75% of the rows that it must read. The closer the percentage is to 100, the better performance will be. The percentage increases as the distribution of the data for the join column becomes more sorted.

ixutil -stats <indx1,indx2,...>
                              -dsn<data set> -libpath
                              <physical path> [-dist]
                              ;

Print the disk usage statistics and segment list fragmentation statistics for the specified set of indexes that belong to a given table. 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 ixutil option -reorg.

ixutil -runstats <indx1,indx2,...>
                              -dsn <data set name>
                              -libpath <physical path>
                              [-maxruns <number>] ;

Print the run statistics for each index for the specified set of indexes that belong to a given table. Run statistics provide an indication of how the values of a particular index are sorted in relation to their observation numbers. By default, ixutil run statistics 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. Use the optional [-maxruns<number>] argument to change the default setting of 10 runs to any integer in the range 1–100. You can use ixutil run statistics to construct 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 -help

Print the Help menu.

Invoking Ixutil as a Stand-alone Utility

The following environment variables must be established in order to invoke this utility as a stand-alone utility. These environment variables are not needed if the utility is invoked in PROC SPDO via the spdscmd interface.

For UNIX:

INSTDIR=<!replace with SPD Server 5.2 Installation Directory!>
 SASVER=9.4 
 SASHOME=<!replace with SPD Server 5.2 Installation Directory!>
 LD_LIBRARY_PATH=$INSTDIR/bin:$SASHOME/SASFoundation/$SASVER/sasexe 
 export LD_LIBRARY_PATH
 LIBPATH=$INSTDIR/bin:$SASHOME/SASFoundation/$SASVER/sasexe
 export LIBPATH
 TKPATH=$INSTDIR/bin:$SASHOME/SASFoundation/$SASVER/sasexe:$SASHOME/SASFoundation
 /$SASVER/utilities/bin
 export TKPATH
 MSGPATH=$SASHOME/SASFoundation/$SASVER/misc/tkspddrv 
 export MSGPATH

For Windows:

SET SASROOT=<!replace with SPD Server 5.2 Installation Directory!>
SET INSTALLDIR=<!replace with SPD Server 5.2 Installation Directory!>
SET MSGPATH=@SASROOT@\tkspddrv\sasmisc 
SET TKPATH=@INSTALLDIR@\bin;@SASROOT@\core\sasext

Ixutil Options

-crejidx <data set1,column1>
                              < data set2,column2>
                              ... <data set_n,column_n>

Create a join index for the SPD Server parallel join utility to 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, print 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>

The number of parallel join work units for a join index. Parallel join threads 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 that contains the table.

-help

Print the ixutil help menu.