📄 reorg1.sh.shtml
字号:
<HTML><HEAD><TITLE>PRS Technologies, Inc. - script: reorg1.sh</TITLE><META NAME="Description" content="PRS Technologies Inc. - Rebuild an Informix table to eliminate extents"><STYLE TYPE="text/css"><!--A.CLASS1 {background-color: #000066; color: white; letter-spacing: 0.2ex; font-size: 14pt; text-decoration: none;}A:hover.CLASS1 { background-color: #ffffcc; color: black;}--></STYLE><SCRIPT>defaultStatus = "PRS Technologies Inc. - script: reorg1.sh";</SCRIPT></HEAD></CENTER><CENTER><TABLE border=0 cellspacing=0 cellpadding=0 width=90%><TR><TD> <CENTER> <IMG SRC="../gif/prs_tech_top_of_page1.gif" border=0 align=center alt="PRS Technologies Home Page"> </CENTER></TD></TR><TR><TD> <CENTER> <TABLE border=2 cellspacing=0 cellpadding=0> <TR> <td><A HREF="../index.shtml" CLASS=CLASS1> Home </A> <td><A HREF="../resume.shtml" CLASS=CLASS1> Associates </A> <td><A HREF="../references.shtml" CLASS=CLASS1> References </A> <td><A HREF="../downloads.shtml" CLASS=CLASS1> Downloads </A> <td><A HREF="../elite1.shtml" CLASS=CLASS1> Elite </A> <td><A HREF="../links.shtml" CLASS=CLASS1> Links </A> <td><A HREF="../contacts.shtml" CLASS=CLASS1> Contact Us </A> </TR> </TABLE> </CENTER></TD></TR></TABLE></CENTER><BR><CENTER><TABLE border=0 cellpadding=4 bgcolor=e7e7ce><tr><td><font color=#000066 size=+2><B>reorg1.sh</B></font></td></tr></table></CENTER><XMP>################################################################################## Module: reorg1.sh# Author: Peter R. Schmidt# Description: Rebuild an Informix table to eliminate extents# or move to a different dbspace## 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#################################################################################PAGESIZE=2048BOLD=`tput smso`NORMAL=`tput rmso` tput clearecho "Rebuild an Informix table to eliminate extents"echo "or move to a different dbspace."echoecho "Note: this procedure will move the entire table into "echo "a single non-fragmented dbspace using the alter fragment statement."echoecho "This seems to be a much better alternative then unloading, dropping, "echo "recreating and reloading the table."echoecho "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."echoecho "Press <Enter> for more notes..."read answerechoecho "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."echoecho "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."echoecho "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."echoecho "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."echoecho "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)"echoif [ $LOGNAME != informix ]then echo echo "Note: you must be logged on as informix to run this procedure." exit 1fi#------------------------------------------------------------------------------while truedowhile truedo 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(*) count from systabnames where tabname = "$TABLENAME"; EOF` 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 dbsnamefrom systabnameswhere 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 dbsnamefrom systabnameswhere 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 breakdone#------------------------------------------------------------------------------echoecho "Table validated - getting current dbspace - please stand by..."DBSPACE=`dbaccess sysmaster 2>/dev/null << !select dbinfo('dbspace',partnum) dbspacefrom systabnameswhere tabname = "$TABLENAME" and dbsname = "$DATABASE";!`DBSPACE=`echo $DBSPACE | cut -d" " -f2` # Eliminate column name from the listechoecho "Note: This table resides in the $BOLD$DBSPACE$NORMAL dbspace." #------------------------------------------------------------------------------echoecho "Checking extents - please stand by..."EXT_INFO=`dbaccess sysmaster 2>/dev/null << !select dbsname, count(*) num_of_extents, sum(pe_size) total_sizefrom systabnames, sysptnextwhere partnum = pe_partnum and tabname = "$TABLENAME" and dbsname = "$DATABASE"group by 1order 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`#------------------------------------------------------------------------------echoecho "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`#------------------------------------------------------------------------------echoecho "Getting available dbspaces..."AVAIL=`dbaccess sysmaster 2>/dev/null << !select unique dbinfo('dbspace',partnum) dbspacefrom systabnamesorder by 1!`AVAIL=`echo $AVAIL | cut -d" " -f2-` # cut column name from list#------------------------------------------------------------------------------while truedo echo echo "$BOLD $NORMAL" echo "Tablename........: $TABLENAME" echo "Database.........: $DATABASE" echo "Dbspace..........: $DBSPACE" echo "Pages............: $PAGES" echo "Kbytes...........: $KSIZE" echo "First Extent.....: $OLDFEXT" echo "Next Extent......: $OLDNEXT" echo "# of Extents.....: $EXTENTS" echo "$BOLD $NORMAL" 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." read NEWDBSPACE 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 break fidone#------------------------------------------------------------------------------## 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##donewhile truedo echo echo "Enter new NEXT extent size (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 breakdone#------------------------------------------------------------------------------while truedo echo echo "$BOLD $NORMAL" echo "Tablename........: $TABLENAME" echo "Database.........: $DATABASE" echo "Pages............: $PAGES" echo "Kbytes...........: $KSIZE" echo "Old # of Extents.: $EXTENTS" echo echo "Old Dbspace......: $DBSPACE" echo "Old First Extent.: $OLDFEXT" echo "Old Next Extent..: $OLDNEXT" echo echo "New Dbspace......: $NEWDBSPACE"## echo "New First Extent.: $FIRST" echo "New Next Extent..: $NEXT" echo "$BOLD $NORMAL" echo echo "OK ? (y/n)" read answer case $answer in Y|y|N|n) break;; esacdone #------------------------------------------------------------------------------if [ $answer = Y -o $answer = y ]then echo echo "Reorganizing table: $TABLENAME, please stand by..."dbaccess $DATABASE <<-EOFalter table $TABLENAME modify NEXT size $NEXT;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 echo "Note: No changes made." exit fi#------------------------------------------------------------------------------echoecho "Reorganization completed - getting new information - please stand by..."DBSPACE=`dbaccess sysmaster 2>/dev/null <<-EOFselect dbinfo('dbspace',partnum) dbspacefrom systabnameswhere tabname = "$TABLENAME" and dbsname = "$DATABASE";EOF`DBSPACE=`echo $DBSPACE | cut -d" " -f2`#------------------------------------------------------------------------------echoecho "Checking new extents - please stand by..."EXT_INFO=`dbaccess sysmaster 2>/dev/null <<-EOFselect dbsname, count(*) num_of_extents, sum(pe_size) total_sizefrom systabnames, sysptnextwhere partnum = pe_partnum and tabname = "$TABLENAME" and dbsname = "$DATABASE"group by 1order by 1EOF`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`#------------------------------------------------------------------------------echoecho "Getting new extent settings..."NEXT=`dbaccess $DATABASE 2>/dev/null <<-EOFselect nextsize from systables where tabname = "$TABLENAME";EOF`NEXT=`echo $NEXT | cut -d" " -f2`FIRST=`dbaccess $DATABASE 2>/dev/null <<-EOFselect fextsize from systables where tabname = "$TABLENAME";EOF`FIRST=`echo $FIRST | cut -d" " -f2`#------------------------------------------------------------------------------echoecho "$BOLD New Configuration $NORMAL"echo "Tablename........: $TABLENAME"echo "Database.........: $DATABASE"echo "Pages............: $PAGES"echo "Kbytes...........: $KSIZE"echo "# of Extents.....: $EXTENTS"echo "New Dbspace......: $DBSPACE"echo "New First Extent.: $FIRST"echo "New Next Extent..: $NEXT"echo "$BOLD $NORMAL"echoecho "Press <Enter> to continue"read answerdone################################################################################</XMP><CENTER><TABLE border=2 cellspacing=0 cellpadding=0><TR><td><A HREF="../index.shtml" CLASS=CLASS1> Home </A><td><A HREF="../resume.shtml" CLASS=CLASS1> Associates </A><td><A HREF="../references.shtml" CLASS=CLASS1> References </A><td><A HREF="../downloads.shtml" CLASS=CLASS1> Downloads </A><td><A HREF="../elite1.shtml" CLASS=CLASS1> Elite </A><td><A HREF="../links.shtml" CLASS=CLASS1> Links </A><td><A HREF="../contacts.shtml" CLASS=CLASS1> Contact Us </A></TR></TABLE></CENTER></BODY></HTML>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -