REFERENCE_15_FK on AVC_TRANSACTION (CUSTOMER_ID ASC)
REFERENCE_21_FK on AVC_TRANSACTION (USER_ID ASC, CUSTOMER_ID ASC)
REFERENCE_16_FK on BUSINESS_USER (CUSTOMER_ID ASC)
REFERENCE_17_FK on CLIENT_CUSTOMER_LINE_OF_SERVICE (CUSTOMER_ID ASC)
WEBEXTR_CUSTOMER_F on WEB_EXTRACT_RULES (CUSTOMER_ID ASC)
1. Disable bb monitoring. (no bogus email alerts)
2. Stop shredders. (XML)
3. backup database.
4. define copy tables
(AVC_TRANSACTION2,BUSINESS_USER2, CLIENT_CUSTOMER_LINE_OF_SERVICE2, CUSTOMER2,
SUB_CUSTOMER2, WEB_EXTRACT_RULES2, WEB_VOLUME2).
5. copy 7 tables to new tables just defined.
6. drop restrict on drop to 7 tables
7. drop all indexes, constraints on 7 original tables.
AVC_TRANSACTION,BUSINESS_USER, CLIENT_CUSTOMER_LINE_OF_SERVICE, CUSTOMER,
SUB_CUSTOMER, WEB_EXTRACT_RULES, WEB_VOLUME.
8. drop original 7 tables.
9. recreate original 7 tables with expanded column definition.
10. copy the 7 copy tables back to the 7 original tables.
11. add constraints, indexes back in to the original 7 tables.
12. add restrict on drop to 7 tables.
13. drop 7 copy tables.
14. backup database.
15. start shredders.
16. re-enable bb monitoring.
You have documented your implementation approach very well .. Make sure, you document your backout approach also. In addition test this implementation and backout on a test system - a small subset of data will do .
I would take the following approach - Not very different except that I have mentioned about SET INTEGRITY (or have you analysed dependencies and have concluded there will not be a need )
1) Backup database. If it is online, before starting the change process, archive all logs and make a note of the point in time I should recover if I have to use the backup. Also verify all logs are available.
2) Export data from the original tables. db2look on the database. - I would consider using the exported data and DDL as another and better backout resource.
3) Drop indexes and constraints - primary and FK constraints. (Drop the indexes only if the name of the indexes is important, otherwise, drop just the FK constriants)
4) Rename tablespaces of the original tables. Rename tables with a suffix _old (eg. AVC_TRANSACTION_old)
5) Create tabelspace with the original name and resized, if necessary to fit in expanded columns.
6) Create the tables and indexes with the new definition. Create primary keys and any foreign keys on those tables (do not create FKs which refer these tables)
7) LOAD the data into the new tables with REPLACE, including Statistics Collection (This will make it fast and save logspace)
8) Create foreign keys on other tables which reference the newly created ones. (If these keys exist, LOAD REPLACE will not be possible)
10) SET INTEGRITY on the tables that are in check pending state (STATUS 'C' in syscat.tables). The parent tables will have to be checked before the child tables.
11) Rebind packages.
12) Test now.
Once testing is done, drop the _old tables and the tablespaces.
1) Drop the newly created tables, indexes, constraints and tablespaces
2) Rename the _old tables to the original name. Rename the tablespaces also.
3) Create indexes
4) RUNSTATS on them
5) Create primay and foreign keys
6) Do SET INTEGRITY on the check pending tables.
7) Rebind Packages
If for some reason, this backout fails, use the DDL and the exported data.
If this again fails, go to the database backup and logs.
Visit the new-look IDUG Website , register to gain access to the excellent content.