Re: Outstanding Inplace Alter Script Issue
On Mar 19, 6:05 pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Friends,
Looks like the script was not tested on ver 10. Finally I
could find the problem. It is not getting the version and count
correctly. I will post the script soon after fixing it.
David,
Thanks for the tip. May I know why the max table version
limit is 255 ?
--Chavan Koya
The version is stored in a single byte on the Partition header page.
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 Wed, Mar 24, 2010 at 2:53 PM, Koya <chavan1@gmail.com> wrote:
> On Mar 19, 6:05 pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote:
> > 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.- Hide quoted
> text -
> >
> > - Show quoted text -
>
> Friends,
> Looks like the script was not tested on ver 10. Finally I
> could find the problem. It is not getting the version and count
> correctly. I will post the script soon after fixing it.
> David,
> Thanks for the tip. May I know why the max table version
> limit is 255 ?
>
> --Chavan Koya
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On Mar 24, 3:02 pm, Art Kagel <art.ka...@gmail.com> wrote:
> The version is stored in a single byte on the Partition header page.
>
> 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 Wed, Mar 24, 2010 at 2:53 PM, Koya <chav...@gmail.com> wrote:
> > On Mar 19, 6:05 pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote:
> > > 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.- Hide quoted
> > text -
>
> > > - Show quoted text -
>
> > Friends,
> > Looks like the script was not tested on ver 10. Finally I
> > could find the problem. It is not getting the version and count
> > correctly. I will post the script soon after fixing it.
> > David,
> > Thanks for the tip. May I know why the max table version
> > limit is 255 ?
>
> > --Chavan Koya
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text -
>
> - Show quoted text -
Art,
Thank you for the info. So,One byte can have max value 255 in
binary.
Friends,
This script is working on 10.00.FC10.
--Chavan Koya
#!/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`
log