return to PRS Technologies website
<![CDATA[
################################################################################
#
# Module: reorg4.sh
# Author: Peter R. Schmidt
# Description: Rebuild an informix table to eliminate extents
# or move to a different dbspace
# Version 3: Designed to handle tables with fragment by expression
# Version 4: Reset the "NEXT" extent size AFTER the re-org id completed.
# Thanks Tim Gerrity - why didn't I think of that ?
#
# Change Log
#
# Date Name Description.................
# 04/28/98 Peter R. Schmidt Start Program
# 08/19/99 Peter R. Schmidt Improvements
# 12/07/99 Peter R. Schmidt Anticpate tables with same name to reside in
# multiple databases at the same time
# 12/08/99 Peter R. Schmidt Add notes based on real-world experiences.
# 07/21/01 Peter R. Schmidt Add notes about long transaction errors
# 05/18/05 Peter R. Schmidt Handle fragment by expression
# 05/25/05 Peter R. Schmidt Reset the "NEXT" extent size AFTER the re-org id completed
#
################################################################################
PAGESIZE=2048
BOLD=`tput smso`
NORMAL=`tput rmso`
TEMP1="reorg3.1.tmp"
TEMP2="reorg3.2.tmp.sql"
DBSPACE_EXCLUDE="XXXXXXXXXXXXX" # OPTION: LIST ANY EXCLUDED DBSPACES HERE !!! (used in a where clause)
rm -f $TEMP1 $TEMP2
tput clear
echo "Rebuild an Informix table to eliminate extents"
echo "or move to a different dbspace."
echo
echo "Note: this procedure will move the entire table into "
echo "a single non-fragmented dbspace using the alter fragment statement."
echo
echo "This seems to be a much better alternative then unloading, dropping, "
echo "recreating and reloading the table."
echo
echo "Note: Since you are unable to alter the initial extent size,"
echo "you must be satisfied with changing only the NEXT extent."
echo "The consequence of this, is that when you reorg a table that"
echo "initially had undersized extents, the new first extent will be"
echo "created with the existing (small) size, but all additional extents",
echo "will be created with the new NEXT (larger) size. Also, if you are "
echo "lucky, the new extents will fall into contiguous spaces and you will "
echo "end up with fewer larger extents."
echo
echo "Press <Enter> for more notes..."
read answer
echo
echo "Note 1: reorganizing a table that uses a large amount of disk space,"
echo "but with a small number of rows (because of deleted records)"
echo "seems to work really fast."
echo
echo "Note 2: this procedure will fill up your logical logs."
echo "Make sure that continuous logging to tape is running, or"
echo "disable transaction logging before running this procedure."
echo
echo "Note 3: Long transactions ARE a problem (updated 7/21/2001)."
echo "The alter table creates one long transaction."
echo "If the table to be changed is as big as half of your logical logs,"
echo "you will need to disable transaction logging or add more logs."
echo
echo "Note 4: What happens if you request an next extent size larger then"
echo "the largest available contiguous space ? Informix will grab the "
echo "largest space it can and continue on without a problem."
echo
echo "Note 5: How much total free space do you need ? I think you may need"
echo "as much free space as the size of the table you are reorganizing."
echo "(Unless there are many deleted rows)"
echo
echo "Press <Enter> for more notes..."
read answer
echo
echo "Note 6: If the table is already fragmented by expression, then this"
echo "version will retain the original fragmentation scheme. In this case,"
echo "you cannot use this program to relocate the table to a different dbspace."
echo
if [ $LOGNAME != informix ]
then
echo
echo "Note: you must be logged on as informix to run this procedure."
exit 1
fi
#------------------------------------------------------------------------------
while true
do
while true
do
echo "Note: using a PAGE SIZE of: $PAGESIZE"
echo
echo "Enter the Informix tablename to reorganize (or INTERRUPT to stop)."
read TABLENAME
echo
echo "Checking - please stand by..."
COUNT=`dbaccess sysmaster 2>/dev/null <<-EOF
select count(distinct dbsname) count from systabnames
where tabname = "$TABLENAME";
EOF`
# echo "Debug: count=$COUNT"
COUNT=`echo $COUNT | cut -d" " -f2` # cut column name from list
if [ $COUNT = 0 ]
then
echo
echo "Sorry, tabname: $BOLD$TABLENAME$NORMAL does not seem to exist!"
echo
continue
fi
if [ $COUNT = 1 ]
then
echo
echo "Getting current database for this table..."
DATABASE=`dbaccess sysmaster 2>/dev/null <<-!
select distinct dbsname
from systabnames
where tabname = "$TABLENAME";
!`
DATABASE=`echo $DATABASE| cut -d" " -f2-` # cut column name from select results
echo
echo "Note: This table resides in the $BOLD$DATABASE$NORMAL database."
else
echo
echo "Hmmmmm - Interesting. It appears that this table exists in multiple databases."
echo "Please stand by while I check further..."
DATABASE_LIST1=`dbaccess sysmaster 2>/dev/null <<-!
select dbsname
from systabnames
where tabname = "$TABLENAME";
!`
DATABASE_LIST1=`echo $DATABASE_LIST1 | cut -d" " -f2-` # Eliminate column name from list
echo
echo "The table $BOLD$TABLENAME$NORMAL resides in the following databases:"
echo $BOLD
echo $DATABASE_LIST1 | tr " " "\n"
echo $NORMAL
while true
do
echo
echo "Please specify which database you wish to change."
read DATABASE
COUNT=`echo $DATABASE_LIST1 | grep $DATABASE | wc -l`
if [ $COUNT != 1 ]
then
echo
echo "Sorry, $DATABASE is not a valid database for this table."
echo "Please try again."
continue
else
break
fi
done
fi
COUNTSYS=`echo $TABLENAME | cut -c1-3 | egrep "^sys" | wc -l`
COUNTSYS=`expr $COUNTSYS + 0`
if [ $COUNTSYS != 0 ]
then
echo
echo "Sorry, SYSTEM tables (beginning with 'sys') "
echo "may not be changed with this procedure!"
continue
fi
break
done
#------------------------------------------------------------------------------
echo
echo "Getting previous fragmentation by expression info..."
dbschema -d $DATABASE -t $TABLENAME -ss $TEMP1 > /dev/null
awk '
BEGIN {
flag1=0
}
/fragment by expression/ {
flag1=1
}
/extent size/ {
flag1=2
}
{
if (NR == 1) {
printf "alter fragment on table %s init\n",TABLENAME
}
if (flag1==1) print $0
}
' < $TEMP1 TABLENAME=$TABLENAME > $TEMP2
FB_EXPRESSION=`grep -c "fragment by expression" $TEMP2`
if [ $FB_EXPRESSION = 0 ]; then
echo
echo "Note: table is ${BOLD}NOT${NORMAL} fragmented by expression"
else
echo
echo "Note: table is ${BOLD}fragmented by expression${NORMAL}"
fi
#------------------------------------------------------------------------------
if [ $FB_EXPRESSION = 0 ]; then
echo
echo "Table validated - getting current dbspace - please stand by..."
DBSPACE=`dbaccess sysmaster 2>/dev/null << !
select dbinfo('dbspace',partnum) dbspace
from systabnames
where tabname = "$TABLENAME" and
dbsname = "$DATABASE";
!`
DBSPACE=`echo $DBSPACE | cut -d" " -f2` # Eliminate column name from the list
echo
echo "Note: This table resides in the $BOLD$DBSPACE$NORMAL dbspace."
fi
#------------------------------------------------------------------------------
echo
echo "Checking extents - please stand by..."
EXT_INFO=`dbaccess sysmaster 2>/dev/null << !
select
dbsname,
count(*) num_of_extents,
sum(pe_size) total_size
from
systabnames, sysptnext
where
partnum = pe_partnum and
tabname = "$TABLENAME" and
dbsname = "$DATABASE"
group by 1
order by 1;
!`
EXT_INFO=`echo $EXT_INFO | tail -1`
EXTENTS=`echo $EXT_INFO | cut -d" " -f5`
PAGES=`echo $EXT_INFO | cut -d" " -f6`
KSIZE=`expr $PAGES \* $PAGESIZE \/ 1024`
#------------------------------------------------------------------------------
echo
echo "Getting previous extent settings..."
OLDNEXT=`dbaccess $DATABASE 2>/dev/null << !
select nextsize from systables where tabname = "$TABLENAME";
!`
OLDNEXT=`echo $OLDNEXT | cut -d" " -f2`
OLDFEXT=`dbaccess $DATABASE 2>/dev/null << !
select fextsize from systables where tabname = "$TABLENAME";
!`
OLDFEXT=`echo $OLDFEXT | cut -d" " -f2`
#------------------------------------------------------------------------------
if [ $FB_EXPRESSION = 0 ]; then
echo
echo "Getting available dbspaces..."
AVAIL=`dbaccess sysmaster 2>/dev/null << !
select unique dbinfo('dbspace',partnum) dbspace
from systabnames
where dbinfo('dbspace',partnum) not matches "*$DBSPACE_EXCLUDE*"
and dbinfo('dbspace',partnum) not matches "*temp*"
and dbinfo('dbspace',partnum) not matches "logical"
and dbinfo('dbspace',partnum) not matches "physical"
order by 1
!`
AVAIL=`echo $AVAIL | cut -d" " -f2-` # cut column name from list
fi
#------------------------------------------------------------------------------
echo
echo "$BOLD $NORMAL"
echo "Tablename........: $TABLENAME"
echo "Database.........: $DATABASE"
if [ $FB_EXPRESSION = 0 ]; then
echo "Dbspace..........: $DBSPACE"
fi
echo "Pages............: $PAGES"
echo "Kbytes...........: $KSIZE"
echo "First Extent.....: $OLDFEXT"
echo "Next Extent......: $OLDNEXT"
echo "# of Extents.....: $EXTENTS"
echo "$BOLD $NORMAL"
#------------------------------------------------------------------------------
if [ $FB_EXPRESSION = 0 ]; then
while true
do
echo
echo "Available DBSPACES are:"
echo $BOLD
echo $AVAIL | tr " " "\n"
echo $NORMAL
echo "Which DBSPACE will the newly reorganized table reside in ?"
echo "Note: the same DBSPACE can be re-used."
echo "Press [Enter] for same dbspace of ${BOLD}$DBSPACE${NORMAL}"
read NEWDBSPACE
if [ "${NEWDBSPACE}XXX" = "XXX" ]; then
NEWDBSPACE=$DBSPACE
fi
COUNT=`echo $AVAIL | grep $NEWDBSPACE | wc -l`
if [ $COUNT != 1 ]
then
echo
echo "Sorry, $NEWDBSPACE is not a valid DBSPACE."
echo "Please try again."
continue
else
echo
echo "New DBSPACE will be: ${BOLD}$NEWDBSPACE${NORMAL}"
echo
break
fi
done
fi
#------------------------------------------------------------------------------
## Note: no reason to get new FIRST extent, since alter fragment can't use it.
##
##while true
##do
## echo
## echo "Enter new FIRST Extent size (in Kbytes)"
## echo "Enter a whole number not less then 16"
## read FIRST
##
## if [ $FIRST -lt 16 ]
## then
## echo
## echo "Error: Minimum size is 16 K"
## continue
## fi
## break
##done
while true
do
echo
echo "Enter a NEXT extent size to be used in the defrag step (in Kbytes)"
echo "Enter a whole number not less then 16"
read NEXT
if [ $NEXT -lt 16 ]
then
echo
echo "Error: Minimum size is 16 K"
continue
fi
break
done
while true
do
echo
echo "Enter what the NEXT extent size should be set to AFTER the defrag (in Kbytes)"
echo "Enter a whole number not less then 16"
read NEXT_AFTER
if [ $NEXT_AFTER -lt 16 ]
then
echo
echo "Error: Minimum size is 16 K"
continue
fi
break
done
#------------------------------------------------------------------------------
while true
do
echo
echo "$BOLD $NORMAL"
echo "Tablename........: $TABLENAME"
echo "Database.........: $DATABASE"
echo "Pages............: $PAGES"
echo "Kbytes...........: $KSIZE"
echo "Old # of Extents.: $EXTENTS"
echo
if [ $FB_EXPRESSION = 0 ]; then
echo "Old Dbspace......: $DBSPACE"
fi
echo "Old First Extent.: $OLDFEXT"
echo "Old Next Extent..: $OLDNEXT"
echo
if [ $FB_EXPRESSION = 0 ]; then
echo "New Dbspace......: $NEWDBSPACE"
fi
## echo "New First Extent.: $FIRST"
echo "Next Extent......: $NEXT (Used during the defrag)"
echo "Next Extent......: $NEXT_AFTER (Set to AFTER the defrag)"
echo "$BOLD $NORMAL"
echo
echo "OK ? (y/n)"
read answer
case $answer in Y|y|N|n) break;; esac
done
#------------------------------------------------------------------------------
# Ok - Enough messing around - get to the real stuff
#------------------------------------------------------------------------------
if [ $answer = Y -o $answer = y ]
then
echo
echo "Reorganizing table: $TABLENAME, please stand by..."
dbaccess $DATABASE <<-EOF
alter table $TABLENAME modify NEXT size $NEXT;
EOF
if [ $? != 0 ]
then
echo
echo "Error: program canceled!"
echo "Press <Enter> to continue:"
read answer
exit 1
fi
if [ $FB_EXPRESSION = 0 ]; then # NOT FRAGMENTED BY EXPRESSION
dbaccess $DATABASE <<-EOF
alter fragment on table $TABLENAME init in $NEWDBSPACE;
EOF
if [ $? != 0 ]
then
echo
echo "Error: program canceled!"
echo "Press <Enter> to continue:"
read answer
exit 1
fi
else
dbaccess $DATABASE $TEMP2 # FRAGMENTED BY EXPRESSION
if [ $? != 0 ]
then
echo
echo "Error: program canceled!"
echo "Press <Enter> to continue:"
read answer
exit 1
fi
fi
dbaccess $DATABASE <<-EOF
alter table $TABLENAME modify NEXT size $NEXT_AFTER;
EOF
if [ $? != 0 ]
then
echo
echo "Error: program canceled!"
echo "Press <Enter> to continue:"
read answer
exit 1
fi
else
echo "Note: No changes made."
exit
fi
#------------------------------------------------------------------------------
echo
echo "Reorganization completed - getting new information - please stand by..."
#------------------------------------------------------------------------------
if [ $FB_EXPRESSION = 0 ]; then
DBSPACE=`dbaccess sysmaster 2>/dev/null <<-EOF
select dbinfo('dbspace',partnum) dbspace
from systabnames
where tabname = "$TABLENAME" and
dbsname = "$DATABASE";
EOF`
DBSPACE=`echo $DBSPACE | cut -d" " -f2`
fi
#------------------------------------------------------------------------------
echo
echo "Checking new extents - please stand by..."
EXT_INFO=`dbaccess sysmaster 2>/dev/null <<-EOF
select
dbsname,
count(*) num_of_extents,
sum(pe_size) total_size
from
systabnames, sysptnext
where
partnum = pe_partnum and
tabname = "$TABLENAME" and
dbsname = "$DATABASE"
group by 1
order by 1
EOF`
EXT_INFO=`echo $EXT_INFO | tail -1`
DATABASE=`echo $EXT_INFO | cut -d" " -f4`
EXTENTS=`echo $EXT_INFO | cut -d" " -f5`
PAGES=`echo $EXT_INFO | cut -d" " -f6`
KSIZE=`expr $PAGES \* $PAGESIZE \/ 1024`
#------------------------------------------------------------------------------
echo
echo "Getting new extent settings..."
NEXT=`dbaccess $DATABASE 2>/dev/null <<-EOF
select nextsize from systables where tabname = "$TABLENAME";
EOF`
NEXT=`echo $NEXT | cut -d" " -f2`
FIRST=`dbaccess $DATABASE 2>/dev/null <<-EOF
select fextsize from systables where tabname = "$TABLENAME";
EOF`
FIRST=`echo $FIRST | cut -d" " -f2`
#------------------------------------------------------------------------------
echo
echo "$BOLD New Configuration $NORMAL"
echo "Tablename........: $TABLENAME"
echo "Database.........: $DATABASE"
echo "Pages............: $PAGES"
echo "Kbytes...........: $KSIZE"
echo "# of Extents.....: $EXTENTS"
if [ $FB_EXPRESSION = 0 ]; then
echo "New Dbspace......: $DBSPACE"
fi
echo "New First Extent.: $FIRST"
echo "New Next Extent..: $NEXT"
echo "$BOLD $NORMAL"
echo
echo "Press <Enter> to continue"
read answer
rm -f $TEMP1 $TEMP2
done
################################################################################
]]>