- Remote DBA
- Disaster Recovery
- Migration Services
- Request Info
- DBMS Courses
- Request Info
- Tech Articles
- Version Info
- Ask SCI's Experts
SCI recently implemented a schema change for a leading provider of MES (Manufacturing Execution Systems) that enhances database performance and provides new functionality. Besides the obvious requirement “modify the schema,” the changes had to be designed to eliminate any downtime for the change and minimal code changes. Could it be done? Well, almost – the changes will require less than 5 minutes of downtime to implement!
Many DBMS developers take great pains to avoid the dreaded “schema change.” But why are they so afraid? Believe it or not, extensive schema changes are possible and do not require significant downtime to implement. We proved that schema changes do not mean extensive downtime or application changes.
The requirement was to add new fields to the database (easy, this involves only a change to the metadata), and to modify the sort keys of existing sets. The later requires that the existing index structures (data) be modified to reflect the new sort key – at some sites, this may involve updating millions of records. The database restructuring utility (DRU) allows sort keys to be changed – but requires more downtime than allowed. So, how did SCI design the schema enhancements so they could be accomplished in less than 5 minutes of total down time? Here’s how…
To accomplish this, we needed to devise a plan to avoid changes requiring offline modification of data. Thus, we could only add new fields & sets and limit changes to existing sets to “metadata-only” changes (such as changing the insertion & retention modes).
Adding the new fields was easy. Simply extract the schema(s) definitions from the database, add the fields and update the database with the revised schema(s). Total downtime: approximately 1 minute.
To accomplish the “set change,” we created a new set with the new sort keys which we would later use an ALIAS clause in all subschemas to effectively rename the new set using the old set’s name – this avoided the need to make extensive program changes. This set change was actually performed while adding the new fields.
A special program was needed that would connect all existing data into the new set – this could be ran online. Once all existing records had been connected to the new set, the ALIAS clause was implemented allowing all existing programs to reference the new set with the old name (no program changes were required). At this point the application is now using the new set!
There were two sets that served essentially the same purpose at this point – the old set, and the new set. To avoid the overhead of maintaining BOTH sets, another metadata-only change was needed – changing the INSERTION mode to MANUAL and the RETENTION mode to OPTIONAL. This change required another brief period of exclusive access to the database (less than 1 minute). Programs would no longer automatically connect new records to the old set – and we were free to disconnect the existing data from the old set!
To disconnect the old set, a temporary subschema was needed (remember, the existing subschemas have an ALIAS clause that points to the new set – the old set is “invisible” through these subschemas). This temporary subschema was actually added when we added the new field and set (at the beginning of the process). A special program was written that would disconnect the old set online.
Finally, once all of the existing data was disconnected from the old set, the temporary subschema was no longer needed. To remove this, we needed another moment of downtime – less than 1 minute to remove the temporary subschema.
Job accomplished! I said at the beginning that this was easy – in fact, this was a lot easier to perform than it was to describe! The easy part was the technical part – the hard part was writing this article!