IDS Forum

Checking for in place alters

Posted By: Rob.B.Schmitz@mail.sprint.com
Date: Wednesday, 22 January 2003, at 9:23 a.m.

Hello All,

We are planning to upgrade from IDS 9.21.UC4XI to 9.21.UC7X5 on two of
our production servers (Sun). This upgrade corrects a memory issue.
When we performed this upgrade on development, we discovered that any
tables with an "in place alter" condition caused all sorts of problems
(assert fails, corruption, etc.) after the upgrade. We learned our
lesson and we are using a script to detect in place alters. This is the
same script we used when we went from 7.x to 9.x.

I have two questions. The first: Is the script that discovers in place
alters in 7.x still valid in 9.x? Essentially, the script produces an
"oncheck -pT" command for many, but not all, tables. Then it executes
the onchecks and determines if there are multiple versions of the
schema. If there are, then it generates 90% of the update statement to
correct the situation. Here is the script:

---------------------------

dbaccess sysmaster <<! 2>/dev/null | sed "s/(expression) *//" >
ipa9.check

set isolation to dirty read;

select pg_partnum + pg_pagenum - 1 partn
from syspaghdr, sysdbspaces a
where pg_partnum = 1048576 * a.dbsnum + 1 and pg_next != 0
into temp pp with no log;

select 'oncheck -pT '||trim(b.dbsname)||':'||trim(b.tabname)
from systabnames b, pp
where partn = partnum;
!

chmod 744 ipa9.check

ipa9.check \
| awk ' /TBLspace Report for/ { Table=$4 }
/(oldest)/ { if ($3 != "0")
{
print "update ", Table, " set
<col1>=<col1> where 1=1;"
next
}
getline
while ( $2 != "(current)" )
{
if ($2 != "0")
{
print "update", Table,
"set <col1>=<col1> where 1=1;"
next
}
getline
}
}'

----------------------------------------------

I rewrote this script slightly for Version 9 because SQL output is
formatted differently between Version 7 and 9. I am very rusty on my
internals and I am not sure if the value being used in the first select
is still valid for Version 9.x. It may be responsible for the fact that
not all tables are selected for the "oncheck -pT" - a curious aspect.

The second question: Is there a better way to do this? The "oncheck
-pT" can run for hours on large tables.

I am assuming that the only way to eliminate the condition is to run an
update statement that doesn't change anything on the identified tables.
For example, "update mytable set mycol1=mycol1 where 1=1". But, again,
this can take some time for large tables, especially if we cannot turn
off logging and we have to break it into a series of update statements
for small sets of rows.

Any ideas or suggestions would be most welcome. Thanks

Rob Schmitz
Rob.B.Schmitz@mail.sprint.com

Messages In This Thread

• Checking for in place alters
  Rob.B.Schmitz@mail.sprint.com -- Wednesday, 22 January 2003, at 9:23 a.m.
  • Re: Checking for in place alters
    Rajib Sarkar -- Wednesday, 22 January 2003, at 9:55 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.