Checking for in place alters

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

oncheck -pT should provide you with the right answers ...:-) but if you'vegot 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