Re: dummy update
This is a script I was sent when I asked about removing IPA's on 7.3, I
can't recall who sent it but many thanks, it has been extrememly useful.
I added the bits to generate sql to count the number of rows in each table
found so I could add something to the update to avoid running out of locks
#!/bin/ksh
## Script to find INPLACE ALTERS in a table/database without locking up
resource
##
## Command to run this would be:
## inplacealter.ksh -d <database_name> -t [ALL|<table_name>] [-o [ver|sum]]
##
## It creates an OUTPUT file called INPLACEALTER.OUT
##
##
## This program has been tested for:
## Informix Version: 7.31.[U|F]Cx, 9.21.[U|F]Cx, 9.30.[U|F]Cx, 9.40.[U|F]Cx
## Platform: Sun Solaris 2.6, 2.7, 2.8
## HP-UX 11.0, 11i
## AIX 4.3, 5.1
##
##
OUTPUT_FILE=inplacealter.out
UPDATE_FILE=inplacealter_update.sql
COUNT_FILE=inplacealter_count.sql
>$OUTPUT_FILE
>$UPDATE_FILE
>$COUNT_FILE
set +x
osname=`uname -s`
if [ $osname = "SunOS" ]; then
AWK=nawk
GREP=/usr/xpg4/bin/grep
else
AWK=awk
GREP=grep
fi
usage(){
echo "\\nUSAGE\\n\\n";
echo "$1 -d database [-t table] [-o ver|sum]\\n\\n";
echo "-d database the name of the database (required)\\n";
echo "-t [ALL|table] the name of the table (default is all tables)\\n";
echo "-o ver|sum print either verbose or summary (default) report\\n";
}
## Main routine to count the IPAs
count_alters(){
let sum=0
let remain=0
table=$1
partnum=$2
hexpartnum=$3
database=$4
output=$5
fgnum=$6
vers=$7
candidate_flag=0
if [ $fgnum -eq 0 ];then
echo "Checking $database:$table"
else
echo "Checking $database:$table:Fragment#$fgnum"
fi
majversion=`echo $vers|cut -d"." -f1`
minversion=`echo $vers|cut -d"." -f2`
## Get partnum of table as well as numbe of data pages
## Number of data pages is required because last alter
## version is not stored, but derived
##
oncheck -pt $partnum > oncheck.out
numdata=`$GREP -e "Number of data pages" -e "Partition partnum"
oncheck.out|$AWK -v PARTN="$partnum" '{if(index($0,"Number of data pages"))
\\
{ \\
numdata=$5; \\
continue; \\
} \\
if(index($0,"Partition partnum")) \\
partnum=$3; \\
if(partnum==PARTN) \\
{ \\
print numdata; \\
exit; \\
} \\
}'`
if [ $? -ne 0 ];then
echo "Failed to get DATA PAGES from oncheck output ..please check
oncheck.out"
exit 3
fi
## Break Hex Partnum into DBSPACE and LOGICAL PAGE
dbspace=`echo $hexpartnum|cut -c1-5`
partn_page=00001
dbspace=`echo $dbspace$partn_page`
logicalpage=`echo $hexpartnum|cut -c6-10`
logicalpage=`echo 0x$logicalpage`
## Dump the original partition page
oncheck -pp $dbspace $logicalpage > oncheck.out
if [ $? -ne 0 ];then
echo "Oncheck -pp $dbspace $logicalpage command failed ....."
exit 3
fi
## Get pg_next value, which is a pointer to slot 6, where alter info is
stored
##
if [ $majversion -eq 9 -a $minversion -le 30 ];then
lpage=`cat oncheck.out|$GREP PARTN|$AWK '{print $8}'|tr '[a-f]' '[A-F]'`
else
lpage=`cat oncheck.out|$GREP PARTN|$AWK '{print $9}'|tr '[a-f]' '[A-F]'`
fi
if [ $majversion -lt 9 ];then
lpage=`cat oncheck.out|$GREP PARTN|$AWK '{print $8}'|tr '[a-f]' '[A-F]'`
fi
lpage=`echo "0x$lpage"`
if [ "$lpage" = "0x0" ];then
echo "No In Place Alters Found in $database:$table" | tee -a $OUTPUT_FILE
return
else
if [ $fgnum -eq 0 ];then
echo "In-place Alters found in $database:$table ..checking details" | tee
-a $OUTPUT_FILE
else
echo "In-place alters found in $database:$table:Fragment#$fgnum
..checking details" | tee -a $OUTPUT_FILE
fi
fi
## Proceed only if IPA found ...
## Print Header information
if [ "$output" = "ver" ];then
if [ $fgnum -eq 0 ];then
echo "\\n\\t Home Data Page Summary for $database:$table
(partnum=$hexpartnum)" >> $OUTPUT_FILE
else
echo "\\n\\t Home Data Page Summary for $database:$table:Fragment#$fgnum
(partnum=$hexpartnum)" >> $OUTPUT_FILE
fi
echo "\\n\\n\\t\\t Version\\t\\t Count\\n" >> $OUTPUT_FILE
fi
## Dump the IPA information
##
##
oncheck -pp $dbspace $lpage > oncheck.out
if [ $? -ne 0 ];then
echo "Oncheck -pp $dbspace $lpage failed ..."
exit 3fi
##Check for any other ALTER PAGE following this page and continue checking
that
##till there are no pages
dlpage=`echo $lpage`
candidate_flag=0
while [ "$dlpage" != "0x0" ]
do
if [ $majversion -eq 9 -a $minversion -le 30 ];then
dlpage=`cat oncheck.out|$GREP PARTN|$AWK '{print $8;}'|tr '[a-f]'
'[A-F]'`
else
dlpage=`cat oncheck.out|$GREP PARTN|$AWK '{print $9;}'|tr '[a-f]'
'[A-F]'`
fi
if [ $majversion -lt 9 ];then
dlpage=`cat oncheck.out|$GREP PARTN|$AWK '{print $8;}'|tr '[a-f]'
'[A-F]'`
fi
dlpage=`echo "0x$dlpage"`
## Get number of bytes on the page (so that number of versions can be
## calculated)
##
numbytes=`cat oncheck.out|$AWK '{if($1 == "6") print $3;}'`
if [ numbytes -lt 20 ];then
echo "Invalid Numbytes: $numbytes"
exit 3
fi
startbytes=0
iter=0
## Go through all the IPA structures
while [ startbytes -lt numbytes ]
do
## Get the Version number and number of pages to be modified in the
## version
output_str=
cat oncheck.out|$AWK '{
p_flag = 0;
while(getline)
{
if($1 == "slot" && $2 == "6:")
{
p_flag=1;
getline;
}
if(p_flag) print $0;
}
}'|sed "s/\\(\\.\\..*\\)//g" |sed
"s/\\(.*\\):\\(.*\\)/\\2/g" > rec.out
cat rec.out|$AWK '{output_str=$0;while(getline){output_str=output_str
$0;} print output_str;}' > record.out
return_val=`cat record.out|$AWK -v iteration="$iter" '{ \\
version_field=iteration*20; \\
field1=version_field+1; \\
field2=version_field+2; \\
field5=version_field+5; \\
field6=version_field+6; \\
field7=version_field+7; \\