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 3
fi

##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; \
field8=version_field+8; \
print
$field1$field2","$field5$field6$field7$field8; \
}'`

version=`echo $return_val|cut -d"," -f1|tr '[a-f]' '[A-F]'`
count=`echo $return_val|cut -d"," -f2|tr '[a-f]' '[A-F]'`
version=`echo "16i$version p"|dc`
count=`echo "16i$count p"|dc`
let sum=$sum+$count
iter=`expr $iter + 1`
startbytes=`expr $startbytes + 20`

if [ "$output" = "ver" ];then
if [ $version = 0 ];then
echo "\t\t $version (oldest)\t $count" >> $OUTPUT_FILE
else
echo "\t\t $version \t $count" >> $OUTPUT_FILE
fi
fi

if [ count -gt 0 ];then
candidate_flag=1;
fi
version1=$version
count=0
version=0
done

## To take care of the last version of pages
##
version1=`expr $version1 + 1`
let remain=$numdata-$sum

if [ "$output" = "ver" ];then
echo "\t\t $version1 (current) $remain" >> $OUTPUT_FILE
fi

if [ remain -eq numdata ];then
if [ $fgnum -eq 0 ];then
echo "$database:$table is up to date..NO NEED TO RUN UPDATE\n" >>
$OUTPUT_FILE
else
echo "$database:$table:Fragment#$fgnum is up to date..NO NEED TO RUN
UPDATE\n" >> $OUTPUT_FILE
fi
else
if [ $fgnum -eq 0 ];then
echo "$database:$table has outstanding IPAs ..PLEASE RUN UPDATE\n"
>> $OUTPUT_FILE
else
echo "$database:$table:Fragment#$fgnum has outstanding IPAs
...PLEASE RUN UPDATE\n" >> $OUTPUT_FILE
fi
fi

done
}

##Parse the arguments
set -- `getopt d:t:o: $*`
if [ $? -ne 0 ];then
usage `basename $0`
exit 2
fi

if [ $# -eq 1 ];then
usage `basename $0`
exit 2
fi

for i in $*
do
case $i in
-d) database=$2
shift 2;;
-t) table=$2
shift 2;;
-o) display=$2
if [ "$display" != "sum" -a "$display" != "ver" ];then
usage `basename $0`
exit 2
fi
shift 2;;
--) shift;break;;
esac
done

# Do some checking
if [ "x$database" = "x" ];then
echo "Please enter <Database Name> .."
exit 1
fi

if [ "x$table" = "x" ];then
table=`echo ALL`;
fi

if [ "x$display" = "x" ];then
display=`echo sum`
fi

## Check if engine is on-line or not
status=`onstat -|$GREP -e 'On-Line' -e 'Read'|wc -l`
if [ status -ne 1 ];then
echo "Engine must be in On-Line mode"
exit 1
fi

## if table=ALL get a list of all the tables

if [ $table = "ALL" ];then
sql_stmt=`echo "UNLOAD TO tab.out \
SELECT tabname, partnum ,hex(partnum)\
FROM systables \
WHERE tabid > 99 \
AND tabtype = 'T' \
AND partnum !=0 \
UNION \
SELECT b.tabname, partn, HEX(partn) \
FROM sysfragments a, systables b \
WHERE fragtype = 'T' \
AND a.tabid = b.tabid;"`;
else
sql_stmt=`echo "UNLOAD TO tab.out \
SELECT tabname, partnum, HEX(partnum) \
FROM systables \
WHERE tabid > 99 and tabtype = 'T' \
AND partnum !=0
AND tabname = '$table'\
UNION \
SELECT b.tabname, partn, hex(partn) \
FROM sysfragments a, systables b \
WHERE fragtype = 'T' \
AND a.tabid = b.tabid \
AND b.tabname = '$table';"`;
fi

## Execute the SQL
echo $sql_stmt|dbaccess $database - 2>/dev/null

if [ ! -s "tab.out" ];then
echo "No Table(s) found in database $database or no Connect permission"
exit 1
fi

## Main loop to check for IPA
db_version=`onstat -|cut -d"." -f1,2|$AWK '{print $NF;}'`
if [ "x$db_version" = "x" ];then
echo "Unable to detect version, defaulting to 9.30"
db_version=9.30
fi
prev_table=
for record in `cat tab.out`
do
table=`echo $record|cut -d"|" -f 1`
partnum=`echo $record|cut -d"|" -f 2`
hexpartnum=`echo $record|cut -d"|" -f 3`
if [ "x$prev_table" = "x" ];then
prev_table=$table
sql_stmt=`echo "UNLOAD TO count.out \
SELECT count(*) \
FROM sysfragments a, systables b \
WHERE a.fragtype='T' \
AND a.tabid = b.tabid \
AND b.tabname= '$table'"`
echo $sql_stmt|dbaccess $database - 2>/dev/null
is_frag=`cat count.out|cut -d"|" -f1`
is_frag=`echo "$is_frag"|$AWK '{print int($1);}'`
if [ $is_frag -ne 0 ];then
fragnum=1
else
fragnum=0
fi
fi
if [ "$prev_table" != "$table" ];then
prev_table=$table
sql_stmt=`echo "UNLOAD TO count.out \
SELECT count(*) \
FROM sysfragments a, systables b \
WHERE a.fragtype='T' \
AND a.tabid = b.tabid \
AND b.tabname='$table'"`
echo $sql_stmt|dbaccess $database - 2>/dev/null
is_frag=`cat count.out|cut -d"|" -f1`
is_frag=`echo "$is_frag"|$AWK '{print int($1);}'`
if [ $is_frag -ne 0 ];then
fragnum=1
else
fragnum=0
fi
fi

count_alters $table $partnum $hexpartnum $database $display $fragnum
$db_version
if [ $is_frag -ne 0 ];then
fragnum=`expr $fragnum + 1`
fi
if [ candidate_flag -eq 1 ];then
echo "Creating UPDATE command file to USE..."
echo "UNLOAD TO col.out \
SELECT b.colname \
FROM systables a, syscolumns b \
WHERE a.tabname = '$table' \
AND a.tabid = b.tabid \
AND b.coltype NOT IN (6,262)
AND NOT EXISTS (SELECT 9 \
FROM sysindexes c \
WHERE c.tabid = b.tabid \
AND (b.colno = c.part1 OR \
b.colno = c.part2 OR \
b.colno = c.part3 OR \
b.colno = c.part4 OR \
b.colno = c.part5 OR \
b.colno = c.part6 OR \
b.colno = c.part7 OR \
b.colno = c.part8 OR \
b.colno = c.part9 OR \
b.colno = c.part10 OR \
b.colno = c.part11 OR \
b.colno = c.part12 OR \
b.colno = c.part13 OR \
b.colno = c.part14 OR \
b.colno = c.part15 OR \
b.colno = c.part16));" | dbaccess
$database - 2>/dev/null
if [ ! -s "col.out" ];then
echo "Could not unload columns for the table $table from $database"
continue
fi
sed "s/|//g" < col.out > now;mv now col.out
coln=`cat col.out | head -1`
if [ "x$coln" = "x" ];then
echo "UNLOAD TO col.out \
SELECT b.colname \
FROM systables a, syscolumns b \
WHERE a.tabname = '$table' \
AND a.tabid = b.tabid;"| dbaccess $database - 2>/dev/null
sed "s/|//g" < col.out > now;mv now col.out
coln=`cat col.out | head -1`
fi
echo "update $table set $coln=$coln where 1=1;">> $UPDATE_FILE
echo "select count(*) from $table;">> $COUNT_FILE
fi
done

cat $UPDATE_FILE|uniq > now;mv now $UPDATE_FILE
rm -f col.out oncheck.out tab.out rec.out record.out count.out
echo "Processing complete ....\n"
mv $OUTPUT_FILE ${database}_$OUTPUT_FILE
mv $UPDATE_FILE ${database}_$UPDATE_FILE
mv $COUNT_FILE ${database}_$COUNT_FILE
echo "Please check the ${database}_$OUTPUT_FILE and ${database}_$UPDATE_FILE
....."
set +x



Regards

Colin

There are 10 types of people in the world, those that understand binary and
those that don't





>From: "tomcaml@xxxxxxxxx" <tomcaml@xxxxxxxxx>
>Reply-To: "tomcaml@xxxxxxxxx" <tomcaml@xxxxxxxxx>
>To: informix-list@xxxxxxxx
>Subject: Re: dummy update
>Date: 17 Nov 2005 18:35:08 -0800
>
>i am migrating to v10
>but the guide for 9.4 says this under "remove outstanding in-place
>alters"
>
> the db server cannot convert if there are any outstanding
>in-place alters
> remove outstanding in-place alters before converting by
>running a 'dummy' update statement against each table
>
>pointed that direction by infx support - does not seem quite right but
>that is what it says
>
>and the thread is correct, it will not work to do so against SERIAL
>type so thanks much for the replies!
>
>
>T
sending to informix-list
.

Relevant Pages

  • Re: Newbie Three
    ... echo "tesgform"; ... echo "suspected alien invasion case topic table"; ... echo "suspected alien invasion unknown group name"; ...
    (comp.lang.php)
  • Newbie Three
    ... echo "tesgform"; ... echo "suspected alien invasion case topic table"; ... echo "suspected alien invasion unknown group name"; ...
    (comp.lang.php)
  • Re: Apple Remote Desktop root vulneravility
    ... If you are trying to run a remote install script such as the Adobe Silent installer, use the lock screen feature in ARD. ... # umount any previous mounted installer images ... trap 'cd "$"' EXIT ... echo "This version of MacOS is not supported." ...
    (Bugtraq)
  • Re: Shell Script to Remove Old Files
    ... Note it needs a TSM server: ... # Verify command line arguments ... echo "\nError: $STARTDIR does not exist.\n" ...
    (comp.unix.admin)
  • Re: make buildworld issue on 6.2 stable
    ... *** Error code 1 ... echo "You do not have permission to use this script" ... echo "running make installkernel" ...
    (freebsd-stable)