MVSFORUMS.com

[manojagrawal]

Hello,

If we have a table for which we drop the Index and re-create it with an additional column is it a problem if the table has data? i.e., Do we have to unload the data, do the drop index, recreate the index and then load the data or can we just drop the index and recreate?

What happens to the plans associated with the tables to which this index is defined. We are using only Plans, so would it be a problem. I read that Packages have to be re-bound if we drop and re-create an index. Thanks!
_________________
Thanks & Regards,
Manoj.

[s_shivaraj]

Manoj,

If the index was created only for enforcing the primary or unique key, then the index can be dropped and can be modified, without unloading the Load.

But if the Index was created on any primary Key then u wouldn't be able to drop the Index,you can try this, use the DROP PRIMARY KEY clause or the DROP CONSTRAINT clause of the ALTER TABLE statement to remove the primary key or unique constraint. ( If you do not want to keep the primary or unique constraint then try above)

After Altering the Table, Drop the Index and modify it, with out unloading the data.

If you want to keep the primary constraint, then I think droping the table is the only solution and re build it after dropping.

In any case you need to re bound your package.
_________________
Cheers
Sivaraj S

'Technical Skill is the Master of complexity, while Creativity is the Master of Simplicity'

[bauer]

Monoj,

after dropping / recreate the index (see remarks about index with constraints) do not forget to execute RUNSTATS (and if index is clustered execute REORG).

Dropping the index plans / packages will be invalid (using the index), but executing this plans / packages an automatic rebind is done.

If the changed Index should be used in additional plans / Packages => REBIND.

regards
bauer

[CZerfas]

To answer your question concerning REBINDing:
In the DB2 catalog you have the table SYSPLANDEP, where all the DB2-objects are listed, which are chosen by the optimizer to execute your coded SQL. That includes all of the chosen indexes.
If you drop such an index, all associated plans are marked "invalid" in SYSPLAN. If such a plan is executed the next time, DB2 performs an automatic REBIND of that plan. If it succeeds, the plan is usable again, if not, the plan is marked "not executable" and you have to issue a manual REBIND (and with this receive the error messages of the REBIND attempt).

If you want to enlarge an index (with more columns included), you should issue a complete REBIND after this action, since
- doing an automatic rebinds gives the issuing transaction a performance penalty
- the automatic rebind gives you no detailed error messages if it fails
- other plans, which have not used the index before you changed it, could benefit from its new content, and an index access can be chosen by the optimzer for plans previously not using it.

regards
Christian

[manojagrawal]

Thanks for the replies.

- So I can go ahead with drop/recreate of index without unloading table data.

- I checked the SYSPLAN and other tables and found the plans associated with this index. Now after I do the drop and create, I can check the tables to see the validity and thus decide to rebind or not (Well I would be doing a rebind for most of the modules anyway)

Thanks Again!
_________________
Thanks & Regards,
Manoj.