We successfully changed filters to indexes on 3 large application group tables. this included historical and new additions. We had a problem on 1 application where the automated update encountered a DB2 lock after changing several tables and the processess quit responding. When we tried the process again we recevied a message indicatating that the index exists and the change failed at that point. We conctacted IBM and the response was for us to manually do the index changes on the tables that did not get changed because of the DB2 lock. The solution they provided appeared to work successfully.
Following is their solution to the problem.
--------------------------------------------------
This is working as designed. The index already exists, so DB2 will complain if you try to create it again.
We suggest you to update the Application Group Field to an index and NOT update all the existing tables. Then you can manually update the existing tables with an index on that column.
Updating the all existing tables with a new index from the OnDemand Administrator will require a lot of DB2 resources. Loading data to the Application Group being updated should be stopped. If this update fails at any point along the way, the results could be unpreditable. So, you could be in a state now where some tables have the index defined and some don't.
So, the only way to fix it up is to manually add the index to the existing tables
----------------------------------------------------
When the field is changed from a filter to an index, the administrator asks if you would like to update the existing tables. If the administrator selects no, then it will save the change for the Application Group and NOT update the tables.
Then, when the next table is created, it will add this index to it.
To add the index manually to the existing tables, they will need to first do a "DESCRIBE INDEXES FOR TABLE <table_name>". If the index they want to add is not in the list, then they will need to "CREATE INDEX <index_name> ON TABLE <table_name> (<column name>).
Example: "CREATE INDEX JAA206_0 ON TABLE JAA206 (FCFORMAT)"
Then they will have to do a RUNSTATS ON TABLE JAA206 to update the statistics.
