Editioned Database Views for Zero-Downtime Schema Upgrades
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Legacy techniques for upgrading relational database systems incur significant downtime due to their inability to automatically determine the upgrade edition of relational database objects, especially in modern environments with large storage footprints, where the system is brought down for system-wide upgrades.
Innovation Solution
A method using a data dictionary to automatically determine an upgrade edition of a relational database table by creating editioned views, allowing both old and new editions to coexist, with cross-edition triggers to manage data transformations and access, thereby enabling concurrent operation of pre-upgrade and upgraded software applications.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Extent of automation
If legacy techniques are used to upgrade relational database systems, then system-wide upgrades can be performed, but significant downtime is incurred due to inability to automatically determine upgrade edition
Solution Approach 1:
The system performs preliminary actions by creating editioned views and determining upgrade editions before the actual upgrade is needed. The data dictionary is read to identify revision identifiers and determine the current and target editions in advance, allowing the system to be prepared for upgrade without requiring downtime when the upgrade is actually executed.
Solution Approach 2:
The patent introduces editioned views as an intermediary layer between the physical database tables and the applications. These views allow automatic determination of upgrade editions by mapping to different physical tables based on edition identifiers, enabling automated upgrade detection without bringing down the system.
2Reliability
If the system is brought down for system-wide upgrades, then upgrades can be performed, but productivity is reduced due to long downtimes
Solution Approach 1:
The patent segments the database into multiple editions, where each edition represents a different version of the database schema. This segmentation allows different applications to access different editions simultaneously, enabling upgrades to be performed on individual editions without affecting the entire system's availability and productivity.
Solution Approach 2:
The system dynamically determines which edition to use based on revision identifiers found in the data dictionary. The editioned views are dynamically created or updated to point to the appropriate physical tables, allowing the system to adapt to upgrades without requiring a complete system shutdown, thus maintaining productivity.
3Measurement precision
If manual determination of upgrade edition is used, then accuracy can be maintained, but device complexity increases due to manual intervention requirements
Solution Approach 1:
The system performs self-service by automatically reading the data dictionary to identify revision identifiers and determining the current and target upgrade editions without manual intervention. The editioned views automatically map to the correct physical tables based on edition identifiers, maintaining measurement precision while reducing operational complexity.
Solution Approach 2:
The patent uses parameter changes in the form of edition identifiers and revision numbers stored in the data dictionary to automatically determine upgrade editions. By changing the edition parameter in the view definitions based on these identifiers, the system achieves accurate upgrade detection through automated parameter comparison rather than manual assessment.
Data Source
AI summary
A method, system, and computer program product using a data dictionary to automatically determine an upgrade edition of relational database table in a database installation. The editioning method commences upon receiving a data dictionary of the database comprising at least entries corresponding to a first edition of the relational database table, then reading entries of the data dictionary to identify at least one relational database table column of the relational database table that has a revision identifier. The revision identifiers are used to determine a first edition (e.g., a run edition) and a second edition (e.g., an upgrade edition). Then, the method proceeds by creating at least a first relational database table view corresponding to the first edition of the relational database table; and creating at least a second relational database table view corresponding to the upgrade edition of the relational database table using the identified relational database table column.