DB2 V8 FP2 on AIX 5.1

over 2 million row table

We need to alter a table for one field from CHAR 4 to CHAR 10 but the problem is this field is referenced in many other tables with constraints, indexes etc etc.

What I would like to know what is the best way to update the table(s) where this fiels is referenced.

We have come up with a strategy but are worried about the transaction log filling up (this has bitten us in the past) and if this is the best way to go about doing it as well?

Any feedback would be appreciated.


Mark



Here is the way we were thinking about doing and it and some more background information:

DB2 Change for CUSTOMER_ID – Change from CHAR(4) to CHAR(10)

Scope:

CUSTOMER_ID appears in the following tables:

AVC_TRANSACTION
BUSINESS_USER
CLIENT_CUSTOMER_LINE_OF_SERVICE
CUSTOMER
SUB_CUSTOMER
WEB_EXTRACT_RULES
WEB_VOLUME

It appears in the following key table/constraints

BUSINESS_USER
constraint P_IDENTIFIER_1 primary key (USER_ID, CUSTOMER_ID)

CLIENT_CUSTOMER_LINE_OF_SERVICE
constraint P_IDENTIFIER_1 primary key (CLIENT_ID, CUSTOMER_ID, LINE_OF_SERVICE_ID)

CUSTOMER
constraint P_IDENTIFIER_1 primary key (CUSTOMER_ID)


WEB_EXTRACT_RULES
constraint P_KEY_1 primary key (RULE_ID, EXTRACT_ENABLED_DATE, LINE_OF_SERVICE_ID,
USER_ID, CLIENT_ID, CUSTOMER_ID, FOLDER_NAME_ORDER)

WEB_VOLUME
constraint P_KEY_1 primary key (ARCHIVE_SITE_ID, VOLUME_DATE, CLIENT_ID, LINE_OF_SERVICE_ID,
ADJUSTMENT_FLAG, USER_ID, CUSTOMER_ID, WEB_TYPE, FOLDER_NAME)


It appears in the following foreign key table/constraints

AVC_TRANSACTION
constraint F_REFERENCE_15 foreign key (CUSTOMER_ID)
references CUSTOMER (CUSTOMER_ID)

constraint F_REFERENCE_21 foreign key (USER_ID, CUSTOMER_ID)
references BUSINESS_USER (USER_ID, CUSTOMER_ID)

BUSINESS_USER
constraint F_REFERENCE_16 foreign key (CUSTOMER_ID)
references CUSTOMER (CUSTOMER_ID)

CLIENT_CUSTOMER_LINE_OF_SERVICE
constraint F_REFERENCE_17 foreign key (CUSTOMER_ID)
references CUSTOMER (CUSTOMER_ID)

WEB_EXTRACT_RULES
constraint F_WEBEXTR_CUSTOMER foreign key (CUSTOMER_ID)
references CUSTOMER (CUSTOMER_ID)



It appears in the following indexes

WEBEXTR_CUSTOMER_F
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)



Implementation Approach

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.

Backout:
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.

HTH

Sathyaram