Use these procedures to monitor the level of fragmentation for each table.
sp_iqrowdensity
sp_iqindexfragmentation
It is best to relocate table when fragmentation is excessive, or when new dbspaces are added to the system.
In IQ 12.X sp_iqrelocate had been used to move tables .. but in IQ 15.X this is replaced with alter table.
-- IQ 12.7, relocate table to other dbspaces which are writable
commit;
alter dbspace IQ_SYSTEM_MAIN relocate ;
commit;
sp_iqrelocate 'table StoreInvoices';
commit;
-- IQ 15.X, relocate the table directly
alter table StoreInvoices move to Main005 ;
Alternate method: Using iq_rebuildindex, you can relocate tables a column at a time by rebuilding the FP index.
-- rebuild / relocate dept_id column
sp_iqrebuildindex 'employee', 'column dept_id';
-- rebuild / relocate all columns on employee table
sp_iqrebuildindex 'employee', 'column dept_id column first_name column last_name column salary column resume column hire_date';
Here's a handy Perl program, which generates the rebuildindex commands for a given table (DDL file is required) ..
#!/usr/bin/perl
#==================================================================
# rebuildindex.pl
#
# Generate sp_iqrebuildindex commands, for table rebuilds
#
# Batches the columns in groups of 24
#
# Parms: ddlfile tablename
#
# ./rebuildindex.pl employee.sql employee
#==================================================================
$fname1 = $ARGV[0];
$tname = $ARGV[1];
$fname2 = $tname . '.rebuildindex.sql' ;
print "\n\n Processing DDL file ... \n\n" ;
open(FILE1,"<$fname1") or die "ERROR - file1 \n\n\n" ;
open(FILE2,">$fname2") or die "ERROR - file2 \n\n\n" ;
$i = 0;
$j = 0;
$print_ind = 0 ;
$line2 = ' ' ;
print FILE2 "commit; \n\n" ;
while ()
{
$line1 = $_ ;
chomp $line1 ;
$line1 =~ s/\t/ /g ;
$line1 =~ s/^\(/ / ;
$line1 =~ s/^ +// ;
if ( ($line1 =~ /^go/) || ($line1 =~ /^\)/) )
{ $print_ind = 0 ; }
if ( ($line1 =~ /constraint/i) || ($line1 =~ /primary/i) || ($line1 =~ / ZZZ /) )
{ $line1 = '' ; }
# print $line1 . "\n" ;
if ( ($print_ind eq 1) && ($line1 =~ /[a-z]/i) )
{
$line1 =~ s/,/ /g ;
@line1 = split ' ', $line1 ;
$col = shift @line1 ;
$i++ ;
$j++ ;
$col = 'column ' . $col . ' ' ; ;
$line2 = $line2 . $col ;
if ($i > 24)
{
print FILE2 "sp_iqrebuildindex " . "'" . $tname . "','" ;
print FILE2 $line2 . "'; \n\n" ;
print FILE2 "commit; \n\n" ;
$line2 = ' ' ;
$i = 0 ;
}
}
if ($line1 =~ /create table/i)
{ $print_ind = 1 ; }
}
if ($i > 0)
{
print FILE2 "sp_iqrebuildindex " . "'" . $tname . "','" ;
print FILE2 $line2 . "'; \n\n" ;
print FILE2 "commit; \n\n" ;
}
close(FILE1);
close(FILE2);
print "\n\n Processing DDL file ... DONE ... $fname2\n\n" ;
