Database Sequence Management for Zero Downtime Upgrades
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database upgrade procedures face challenges in handling sequences during zero downtime upgrades, particularly in maintaining unique sequence numbers and preserving sequence field values across table upgrades, leading to potential duplicate records and increased memory consumption.
Innovation Solution
The solution involves providing concurrent access to a first sequence for both the old and new database applications through sequence pointers and table links, allowing for the migration and mapping of sequence values between tables, and using temporary sequences to manage sequence changes during the upgrade process, thereby minimizing memory consumption and avoiding duplicate records.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If a single sequence is used for both old and new tables during upgrade, then sequence management is simplified, but duplicate sequence numbers may be generated causing data integrity issues
Solution Approach 1:
The patent divides the sequence management into separate sequences for the old table and new table. The old sequence (SEQ_OLD) generates sequence numbers for the old table, while the new sequence (SEQ_NEW) generates sequence numbers for the new table. This segmentation prevents duplicate sequence numbers between tables during the upgrade process, resolving the data integrity issue while maintaining manageable complexity through structured sequence separation.
Solution Approach 2:
The patent introduces a temporary sequence (SEQ_TEMP) as an intermediary during the upgrade process. This temporary sequence is used to generate sequence numbers for the new table during the transition period, acting as a mediator between the old sequence and the final new sequence. The temporary sequence ensures that sequence numbers are uniquely generated during upgrade without causing duplicates, while being replaced by the permanent new sequence after upgrade completion.
2Productivity
If table clones are created during zero downtime upgrade, then continuous operation is maintained, but memory consumption increases due to duplicate data storage
Solution Approach 1:
The patent merges the old table and new table into a single physical table structure with a single data storage location. Both the old application schema and new application schema access the same physical table through different access schemas. This merging eliminates the need for separate physical table copies, significantly reducing memory consumption while still allowing zero downtime upgrades through schema-level isolation and controlled switching.
Solution Approach 2:
The patent creates a universal table structure that serves multiple functions: it stores data for both the old application version and the new application version simultaneously. The same physical table is accessed by both the first access schema (old version) and second access schema (new version), making the table multi-functional. This universality eliminates the need for separate table copies, reducing memory requirements while enabling continuous operation through schema-based versioning.
3Reliability
If sequence values are migrated during upgrade, then data consistency is maintained, but the upgrade process becomes more complex with additional mapping operations
Solution Approach 1:
The patent performs preliminary actions by creating the new access schema and new sequence before actually migrating data. The new sequence (SEQ_NEW) is pre-configured with the correct starting values and increment logic. The temporary sequence (SEQ_TEMP) is also pre-set up to handle sequence number generation during the migration process. These preliminary preparations simplify the actual migration operation, as the sequence infrastructure is already in place and ready to receive data consistently without requiring complex real-time mapping operations.
Data Source
AI summary
An upgrade of a first version of a database application to a second version of a database application that both have a same data schema is initiated. The first database application has a first access schema such that at least one table in the data schema is linked to the first access schema. The second version of the database application has a second access schema such that at least one database table in the data schema is linked to the second access schema. The first access schema differs from the second access schema. During the upgrade, each access schema is provided access to a first sequence in the data schema for both of the first version of the database application and the second version of the database application. Subsequent to the upgrade, the second version of the database application is provided access to a second sequence in the data schema.