RE: RMVM for SQL index objects

Ok, I'm guilty.
I concentrated on "RMVM" to the exclusion of "SQL index objects".

If you're trying to speed up a file reorg by removing all indexes built
against the file and readding them when done then that would be DROP
INDEX.

I do not think there is an equivalent to ADDLFM.

From CREATE INDEX:

<snip>
PARTITIONED
Specifies that an index partition should be created for each data
partition defined for the table using the specified columns. The
table-name must identify a partitioned table. If the index is unique, the
columns of the index must be the same or a superset of the columns of the
data partition key. PARTITIONED is the default if the index is not unique
and the table is partitioned.
NOT PARTITIONED
Specifies that a single index should be created that spans all of the
data partitions defined for the table. The table-name must identify a
partitioned table. NOT PARTITIONED is the default if the index is unique
and the table is partitioned. An index on a table that is not partitioned
is also by default not partitioned.

If an encoded vector index is specified, NOT PARTITIONED is not
allowed.
</snip>

I think this is saying that if you specify NOT PARTITIONED then you've
basically done an ADDLFM to the index which spans all partitions of the
table. If you specify PARTITIONED then there's a one to one member in the
index for each partition in the table.

I do not see a clause on DROP INDEX to just remove the partitions
(members). Therefore if you go whilly nilly dropping the complete index
object you will lose any custom security you've specified on that object.
Keep in mind that some indexes may be done that way to keep people out of
the physical (table) and only allow them to access it via the index
(logical file) so that you can omit certain columns and whatnot.

You can submit a RFE to come up with a way to remove a partition out of an
index to speed up the file reorg.
Or maybe a better alternative is to figure out another way to do this,
like a RFE to allow you to specify the maintenance of the index to be
delayed until first touch of the index. Much like 'access path
maintenance' on CHGLF.


Rob Berendt