Outstanding Inplace Alter Script Issue
Friends,
We are in the process of upgrading IDS 10.00FC10 to 11.5FC5
which is on Solaris 10. I have downloaded the following script from
IBM website, modified little bit to run on all databases in a instance
and create dummy update SQL statements for each database. But it looks
like there is some issue which I was not able to find out. The issue
is after updating the tables with dummy updates, when I try to run the
script again, it is still reporing that there are inplace alters and
creating dummy SQL update statements, but for some tables it is
reporting in-place alters found, NO NEED TO RUN UPDATES. I have also
given the output of oncheck -pT for the table has issue. If you can
please let me know what might be the issue. Thanks in advance.
-- Chavan Koya
SCRIPT:
======
#!/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
##
##
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.$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 "s/\\(\\.\\..*\\)//g" |sed "s/\\(.*
\\):\\(.*\\)/\\2/g" > rec.$datab
Hi Koya,
this is the important part on oncheck -pT output:
Home Data Page Version Summary
Version Count
175 (oldest) 0
176 0
177 0
178 0
179 0
180 0
181 (current) 3
As you can see all your rows are on the latest (current = 181) version
so it means the dummy updates "upgraded" all rows to the latest
version.
You're good to go for the upgrade as far as this table is concerned,
just make sure you go through all tables of all databases in this
instance to confirm them too.
HTH
Davorin
On Mar 19, 4:06 am, Davorin Kremenjas <davorin.kremen...@gmail.com>
wrote:
> Hi Koya,
>
> this is the important part on oncheck -pT output:
>
> Home Data Page Version Summary
>
> Version Count
> 175 (oldest) 0
> 176 0
> 177 0
> 178 0
> 179 0
> 180 0
> 181 (current) 3
>
> As you can see all your rows are on the latest (current = 181) version
> so it means the dummy updates "upgraded" all rows to the latest
> version.
> You're good to go for the upgrade as far as this table is concerned,
> just make sure you go through all tables of all databases in this
> instance to confirm them too.
>
> HTH
>
> Davorin
Hi Davorin,
Thanks for the reply. yes All the records are in
current version, I already completed dummy updates on this table. but
I am trying to find why the script is reporting to update the rows
again ?
-- Chavan Koya
Because the engine hasn't dropped the older TABLESPACE TABLESPACE pages for
the table yet. It's not documented when that happens, so I can't say.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Fri, Mar 19, 2010 at 3:15 PM, Koya <chavan1@gmail.com> wrote:
> On Mar 19, 4:06 am, Davorin Kremenjas <davorin.kremen...@gmail.com>
> wrote:
> > Hi Koya,
> >
> > this is the important part on oncheck -pT output:
> >
> > Home Data Page Version Summary
> >
> > Version Count
> > 175 (oldest) 0
> > 176 0
> > 177 0
> > 178 0
> > 179 0
> > 180 0
> > 181 (current) 3
> >
> > As you can see all your rows are on the latest (current = 181) version
> > so it means the dummy updates "upgraded" all rows to the latest
> > version.
> > You're good to go for the upgrade as far as this table is concerned,
> > just make sure you go through all tables of all databases in this
> > instance to confirm them too.
> >
> > HTH
> >
> > Davorin
>
> Hi Davorin,
> Thanks for the reply. yes All the records are in
> current version, I already completed dummy updates on this table. but
> I am trying to find why the script is reporting to update the rows
> again ?
>
> -- Chavan Koya
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On 19 Mar, 21:34, Art Kagel <art.ka...@gmail.com> wrote:
> Because the engine hasn't dropped the older TABLESPACE TABLESPACE pages for
> the table yet. It's not documented when that happens, so I can't say.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (a...@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KSwww.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Fri, Mar 19, 2010 at 3:15 PM, Koya <chav...@gmail.com> wrote:
> > On Mar 19, 4:06 am, Davorin Kremenjas <davorin.kremen...@gmail.com>
> > wrote:
> > > Hi Koya,
>
> > > this is the important part on oncheck -pT output:
>
> > > Home Data Page Version Summary
>
> > > Version Count
> > > 175 (oldest) 0
> > > 176 0
> > > 177 0
> > > 178 0
> > > 179 0
> > > 180 0
> > > 181 (current) 3
>
> > > As you can see all your rows are on the latest (current = 181) version
> > > so it means the dummy updates "upgraded" all rows to the latest
> > > version.
> > > You're good to go for the upgrade as far as this table is concerned,
> > > just make sure you go through all tables of all databases in this
> > > instance to confirm them too.
>
> > > HTH
>
> > > Davorin
>
> > Hi Davorin,
> > Thanks for the reply. yes All the records are in
> > current version, I already completed dummy updates on this table. but
> > I am trying to find why the script is reporting to update the rows
> > again ?
>
> > -- Chavan Koya
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
They are dropped when you rebuild the table so run "alter fragment on
table x init in <dbspace>" or fragment the table again to make this
change.
Don't forget you can only have 255 row versions for a table.