I have a DB2 database that uses social security number as employee
number, and it is the primary key in over 200 tables. I need to change the
primary key to another type of employee number. How do I do this? There are over
300 million rows to update accross the tables. This is a payroll system
which I can only have offline for 24 hours!
QUESTION POSED ON: 23 SEP 2004
QUESTION ANSWERED BY:
Craig Mullins
Well, there may not be an easy way to implement such a major change to your
database without incurring an outage, but I have some guidance. A lot
depends on the amount of data and the ability you may have to introduce
changes in a staged manner.
But here is some advice:
1) Identify the column or columns that you will be using as the employee
identifier PK. If possible,
make it a single column as that will simplify your process.
2) If the column does not already exist in the EMPLOYEE table, add it to
that table first - without
changing the existing primary key. Add the data - making sure that it is
unique. Create a UNIQUE
INDEX on the column to ensure that the data stays unique once it is
added.
3) If the column already exists in the EMPLOYEE table run some data
profiling queries to make sure
that the data is unique. When satisified that it is unique add a UNIQUE
INDEX to the data.
4) Now you have what amounts to an alternate key in the EMPLOYEE table.
Foreign keys can refer to
a parent table's UNIQUE key - they do not have to refer to the PRIMARY
key.
5) Add the column to the other tables. This can be done over time in a
staged manner choosing a table
or group of tables to change without changing all of them at once. Make
sure that the data being
added actually refers to existing data in the UNIQUE key in the EMPLOYEE
table. You can add the
FOREIGN KEY constraint immediately after altering the table to add the
column but before adding
the data to ensure that the data conforms to the constraint.
6) Choose another table or group of tables and continue.
7) Once complete, drop the primary key specification and foreign keys. You
should also consider dropping
the actual foreign key columns because they are no longer guaranteed to
be accurate - of course, this
will cause a huge outage (and you are trying to avoid that). It would
also likely require massive changes
to your application programs.
Of course, this is just a simplified template for the work that needs to be
done. There will be many other issues for you to consider and work through,
not the least of which is making changes to your application program logic
to insert/modify these new columns in conformance to the constraints.
Related Topics:
Search Advice from more than 250 TechTarget Experts
Your question may have already been answered! Browse or search more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice
ASSOCIATED RSS FEEDS
 |
|
Expert Answer Center: Expert Q&As |
|
View all RSS Feeds | RSS Info
|
