Database Reorganization Locking via Thread Analysis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current relational database management systems face challenges in performing online reorganization and redefinition tasks, such as tablespace reorganization and database object changes, due to the requirement for exclusive access windows, which are often unattainable in busy environments, leading to incomplete or failed operations.
Innovation Solution
A method is introduced where a thread analyzer dynamically assesses transaction log records and SQL statement costs to determine if exclusive access can be granted to database objects, allowing the online processing utility to acquire an exclusive lock, thereby enabling the completion of reorganization and redefinition tasks without manual intervention.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If manual intervention by DBAs is used to defer the outage phase, then the REORG utility can enter the outage phase at an optimal time, but manual intervention is required and some busy tablespaces never have an idle period during which an exclusive lock can be manually granted
Solution Approach 1:
The system implements an automated thread analyzer that monitors database objects and automatically determines when to grant exclusive locks to REORG utilities, eliminating the need for manual DBA intervention. The thread analyzer continuously assesses transaction log records, SQL statement costs, and active units of work to make intelligent decisions about lock timing.
Solution Approach 2:
The system uses feedback mechanisms by continuously monitoring transaction log records, SQL statement execution costs, and active units of work. This feedback loop allows the system to dynamically adjust lock granting decisions based on real-time database conditions, ensuring optimal timing for exclusive access without manual intervention.
2Productivity
If parameters are provided to unconditionally force out the processes accessing the tablespace, then the REORG utility can complete its operation, but the forcefully terminated processes can cause potential issues with the application
Solution Approach 1:
The system dynamically adjusts the locking strategy based on real-time conditions. Instead of unconditionally forcing processes out, the thread analyzer continuously monitors database activity and adapts the lock granting decision to current transaction volumes, SQL statement costs, and active units of work, allowing graceful handling of active processes.
Solution Approach 2:
The system changes parameters dynamically by evaluating transaction log record counts, SQL statement cost sums, and active unit of work metrics. These parameter changes enable the system to determine the optimal moment to grant exclusive locks, balancing REORG completion needs with application stability requirements.
3Reliability
If exclusive access is required at the final phase of the REORG utility operation, then the utility can complete successfully, but very busy tablespaces may never obtain a window of low update activity where access can be completely drained
Solution Approach 1:
The system performs preliminary assessment by analyzing transaction log records and active units of work before granting exclusive locks. The thread analyzer prepares by monitoring database conditions and identifying optimal lock timing in advance, allowing the REORG utility to transition smoothly into the outage phase when conditions are favorable.
Solution Approach 2:
The system replaces the mechanical waiting approach (passively waiting for natural idle periods) with an intelligent automated system. The thread analyzer actively monitors and assesses database conditions, using transaction log analysis and cost-based evaluation to identify optimal lock opportunities, substituting passive mechanical waiting with active intelligent decision-making.
Data Source
AI summary
An approach is provided for managing a reorganization of a database object. Because a reorganization utility cannot acquire exclusive access to the object to complete the reorganization, determinations are made: an amount of transaction log records corresponding to active units of work using the database object is determined to be less than a first threshold; a sum of costs of SQL statements executed in the units of work is determined to be less than a second threshold; and a priority of the reorganization is determined to indicate a greatest priority among multiple priorities. Based on the determinations, (1) a reorganization lock providing exclusive access to the database object is acquired, (2) process(es) holding lock(s) on the database object experience a lockout error, a rollback of the process(es) is issued, and the lock(s) are released, and (3) the utility completes the reorganization based on the lock(s) being released.