Database Sequence Pointers for Zero Downtime Upgrades
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database upgrade procedures face challenges in handling tables with sequence fields, leading to duplicate numbers and difficulties in preserving sequence field values during upgrades, especially when deploying new software in parallel to old software with minimal downtime.
Innovation Solution
The solution involves enabling concurrent access to sequences across different access schemas using sequence pointers and table links, allowing for the creation of application triggers that manage updates and inserts, and utilizing external sequences to ensure sequence consistency across tables during the upgrade process.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If upgrade procedures create clones of tables and run database triggers to replicate data from production to upgrade copy, then minimal downtime is achieved, but duplicate numbers are generated in sequence fields
Solution Approach 1:
The patent segments the sequence management by creating separate sequence pointers for the production schema and upgrade schema. Each schema maintains its own sequence pointer (PROD_SEQUENCE_PTR and UPGRADE_SEQUENCE_PTR) that independently tracks sequence values, preventing duplicates while enabling parallel operations in both schemas during the upgrade process.
Solution Approach 2:
The patent introduces sequence pointers as intermediary objects that mediate between the production and upgrade schemas. These pointers act as intermediaries that manage sequence value generation, ensuring that both schemas can operate concurrently without generating duplicate sequence numbers by coordinating through the pointer mechanism.
2Stability of the object's composition
If one sequence is used for both old table and new table during upgrade, then sequence consistency is maintained, but concurrent access between different access schemas becomes problematic
Solution Approach 1:
The patent segments the single sequence into multiple sequence pointers, each associated with a specific access schema. The production schema has PROD_SEQUENCE_PTR while the upgrade schema has UPGRADE_SEQUENCE_PTR, allowing each schema to independently manage its sequence values while maintaining overall consistency through coordinated pointer management.
Solution Approach 2:
The patent adds a schema dimension to sequence access by creating sequence pointers that are schema-specific. Instead of a flat single-sequence model, the system introduces a schema-level dimension where each access schema has its own pointer to the underlying sequence, enabling concurrent access while preserving consistency.
3Productivity
If tables are cloned for upgrade procedures, then parallel operation is enabled, but memory consumption increases due to data duplication
Solution Approach 1:
The patent uses selective copying by creating sequence pointers that reference the underlying sequence rather than duplicating entire table structures. The upgrade schema clones only the necessary metadata and structure definitions while sharing the actual data storage through the pointer mechanism, reducing memory consumption compared to full table cloning.
Solution Approach 2:
The patent makes the underlying sequence table multi-functional by allowing it to serve both production and upgrade schemas simultaneously through different pointers. The same physical sequence table fulfills multiple roles, eliminating the need for separate sequence storage for each schema and thereby reducing overall memory consumption.
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. Concurrent access is provided for each access schema to at least one sequence in the data schema to both the first version of the database application and the second version of the database application. Related apparatus, systems, techniques and articles are also described.