Query to find in-place alter pending tables running forever

HI ALL, IHAC who is planning to upgrade from IDS 7.31 UD6 to 9.40 FC4. Now as part of the upgrade plan, we would like to run a dummy update for tables which are in in-place alter pending state. We have 5 prodction instances on two separate unix servers. On one of the servers, the query is running fine and returned the result set pretty fast. But on the other instance, it is just sleeping forever. I tried running set explain on and the place where it is running slow, it is doing a sequential scan on one of the tables. Now my question is, how can I change the behavior to do index scan. here is what I am doing. 1. Set OPTCOMPIND to 0. 2. Run the query dbaccess sysmaster << EOF 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 b.dbsname database, b.tabname table from systabnames b, pp where partn = partnum; EOF 3. Here is the set explain where it is running fine QUERY: ------ 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 Estimated Cost: 6 Estimated # of Rows Returned: 90 1) informix.sysdbstab: INDEX PATH (1) Index Keys: dbsnum (Key-Only) Lower Index Filter: informix.sysdbstab.dbsnum > 0 2) informix.syspaghdr: INDEX PATH Filters: informix.syspaghdr.pg_next != 0 (1) Index Keys: pg_partnum pg_pagenum Lower Index Filter: informix.syspaghdr.pg_partnum = 1048576 * informix.sysdbstab.dbsnum + 1 NESTED LOOP JOIN QUERY: ------ select b.dbsname database, b.tabname table from systabnames b, pp where partn = partnum Estimated Cost: 10 Estimated # of Rows Returned: 10 1) informix.pp: SEQUENTIAL SCAN (Serial, fragments: ALL) 2) informix.b: INDEX PATH (1) Index Keys: partnum Lower Index Filter: informix.b.partnum = informix.pp.partn NESTED LOOP JOIN 4. Here is the explain output where it is sleeping forever QUERY: ------ 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 Estimated Cost: 30 Estimated # of Rows Returned: 4 1) informix.syspaghdr: SEQUENTIAL SCAN Filters: informix.syspaghdr.pg_next != 0 2) informix.sysdbstab: INDEX PATH Filters: informix.syspaghdr.pg_partnum = 1048576 * informix.sysdbstab.dbsnum + 1 (1) Index Keys: dbsnum (Key-Only) Lower Index Filter: informix.sysdbstab.dbsnum > 0 NESTED LOOP JOIN Any ideas what I should be doing to make it run fast. Regards, Vineet __________________________________ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com sending to informix-list