Create/Drop Index ONLINE

Has anyone had any experience with the create/drop index ONLINE in IDS 10. I’ve taken it for a spin and have observed the following. Indexes can be built ONLINE if there is DML running against the table for which an index is being created, however:- Only 1 ONLINE index build can run at a time within the one database. Other non-online index builds may be done simultaneously however attempting another simultaneous ONLINE index build can cause a blocked checkpoint in the DBServer. Using drop index ONLINE the index will not be dropped until any DML which was running prior to the DROP INDEX being issued has completed if that DML is using the index being dropped. eg: order by indexed column. While the DROP INDEX is blocked by a reader, access to system catalogues ( sysindexes ) is blocked so you cannot (for example) do an info for table through dbaccess. Once a CREATE INDEX ONLINE or DROP INDEX ONLINE statement is running other, inserts, updates and deletes to the associated table may fail with 271: Could not insert new row into the table. 113: ISAM error: the file is locked.but sometimes they do work. haven't figured it out yet. You just never know your luck. The only blue sky I can see with this command is it could be useful for implementing a new index on an unidexed column during production runs, however, dropping and or creating indexes online can cause other DML to fail or to block the DBServer. Something like an ONLINE REPLACE INDEX, which built a new index whilst maintaining the old index then switch over to the new one and drop the old one, would be a hoot.

Stuart We have not done DROP but have done CREATE INDEX ONLINE. If any program has a prepared SQL on that table it will fail at the next attempt to read data - Table has been dropped, altered or created. Which version of IDS 10 are you using. We have created indexes on 10.00.FC4. MW -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of STUART MCCANN Sent: Thursday, 22 February 2007 10:13 a.m. To: ids@iiug.org Subject: Create/Drop Index ONLINE [8464] Has anyone had any experience with the create/drop index ONLINE in IDS 10. I've taken it for a spin and have observed the following. Indexes can be built ONLINE if there is DML running against the table for which an index is being created, however:- Only 1 ONLINE index build can run at a time within the one database. Other non-online index builds may be done simultaneously however attempting another simultaneous ONLINE index build can cause a blocked checkpoint in the DBServer. Using drop index ONLINE the index will not be dropped until any DML which was running prior to the DROP INDEX being issued has completed if that DML is using the index being dropped. eg: order by indexed column. While the DROP INDEX is blocked by a reader, access to system catalogues ( sysindexes ) is blocked so you cannot (for example) do an info for table through dbaccess. Once a CREATE INDEX ONLINE or DROP INDEX ONLINE statement is running other, inserts, updates and deletes to the associated table may fail with 271: Could not insert new row into the table. 113: ISAM error: the file is locked.but sometimes they do work. haven't figured it out yet. You just never know your luck. The only blue sky I can see with this command is it could be useful for implementing a new index on an unidexed column during production runs, however, dropping and or creating indexes online can cause other DML to fail or to block the DBServer. Something like an ONLINE REPLACE INDEX, which built a new index whilst maintaining the old index then switch over to the new one and drop the old one, would be a hoot. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.