in place alters (script/output) issue
hello all -
i have gotten the dummy updates from the IPA script that need to run
before v11 migration(see below)
however, there are times i am seeing the update complete but it will
still output that i need to run the same dummy update. the workaround
for this on some small tables was to just dummy update every field and
that no longer said there were updates to run - however, now i have
tables with millions and millions of records so that is not a very
feasible workaround considering the downtime required. has anyone run
into this before and/or have suggestions?
this script is said to have the advantage of being able to find IPA's
without locking up resources - maybe it is different from another
which does locking but is more accurate, for example?
we are on ids v10 10.fc9
aix 5.3
HDR
thanks in advance
tom
--------------------------------------------
#!/usr/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
##
##
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.$database.out
numdata=`$GREP -e "Number of data pages" -e "Partition partnum"
oncheck.$database.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.$database.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.$database.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.$database.out|$GREP PARTN|$AWK '{print $8}'|tr '[a-
f]' '[A-F]'`
else
lpage=`cat oncheck.$database.out|$GREP PARTN|$AWK '{print $9}'|tr '[a-
f]' '[A-F]'`
fi
if [ $majversion -lt 9 ];then
lpage=`cat oncheck.$database.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" >> $OUTPUT_FILE
echo "No In Place Alters Found in $database:$table"
echo " " >> $OUTPUT_FILE
return
else
if [ $fgnum -eq 0 ];then
echo "In-place alters found in $database:$table ..checking details"
>> $OUTPUT_FILE
echo "In-place alters found in $database:$table ..checking details"
echo " " >> $OUTPUT_FILE
else
echo "In-place alters found in $database:$table:Fragment#
$fgnum ..checking details" >> $OUTPUT_FILE
echo "In-place alters found in $database:$table:Fragment#
$fgnum ..checking details"
echo " " >> $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.$database.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.$database.out|$GREP PARTN|$AWK '{print $8;}'|tr
'[a-f]' '[A-F]'`
else
dlpage=`cat oncheck.$database.out|$GREP PARTN|$AWK '{print $9;}'|tr
'[a-f]' '[A-F]'`
fi
if [ $majversion -lt 9 ];then
dlpage=`cat oncheck.$database.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.$database.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.$database.out|$AWK '{
p_flag = 0;
while(getline)
{
if($1 == "slot" && $2 == "6:")
{
p_flag=1;
getline;
}
if(p_flag) print $0;
}
}'|sed
No need to remove outstanding in-place alters when moving to 11.x! :-)
No need to remove outstanding in-place alters when moving to 11.x! :-)