IDS Forum

Re: Checking for in place alters

Posted By: Rajib Sarkar
Date: Wednesday, 22 January 2003, at 9:55 a.m.

In Response To: Checking for in place alters (Rob.B.Schmitz@mail.sprint.com)

oncheck -pT should provide you with the right answers ...:-) but if you've
got a huge table with lot of fragments, it can take forever to run and also
it places a lock on the table while its running ...

I've got a script which will detect the in-place alters in your database
(without placing any locks on the table) and will also generate the update
statement for the table which you can later use to remove the in-place
alter.

Let me know, if you would be interested in getting the script ..:-)

Thanx much,

Rajib Sarkar
Advisory Support Engineer (Wells Fargo Bank)
IBM Data Management Group
Ph : (602)-217-2100
Fax: (602)-217-2100

As long as you derive inner help and comfort from anything, keep it --
Mahatma Gandhi



"Rob.B.Schmi...."
<Rob.B.Schmitz@mail. To: ids@iiug.org
sprint.com> cc:
Sent by: Subject: Checking for in place alters [76]
forum.subscriber@iiu
g.org


01/22/2003 07:23 AM






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.