Dynamic Stored Procedure Tool for Database Cursor Management
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Creating and modifying stored procedures is labor-intensive and time-consuming, requiring recompilation and regression testing, especially when changes affect multiple clients or underlying DB2 tables, leading to inefficiencies and increased development and maintenance time.
Innovation Solution
A dynamic stored procedure tool that retrieves input values from a calling program, dynamically retrieves a cursor from an external source, and executes it, allowing for real-time updates without recompilation, enabling flexible and efficient management of result sets and reducing the need for new stored procedures.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If the cursor is hard-coded inside the stored procedure, then the stored procedure structure is simple and easy to maintain, but the cursor cannot be changed without recompiling the entire stored procedure, leading to increased development and maintenance time
Solution Approach 1:
The patent segments the stored procedure into two independent parts: the procedure logic and the cursor definition. The cursor is extracted from the hard-coded procedure body and stored separately in a database table. This allows the cursor to be modified independently without recompiling the entire stored procedure, resolving the contradiction between structural simplicity and modification efficiency.
Solution Approach 2:
The patent makes the cursor dynamic by retrieving it from a database table at runtime based on input parameters, rather than having it fixed in the procedure code. This dynamic approach allows the cursor to change without procedure recompilation, improving productivity while maintaining procedural logic integrity.
2Reliability
If the stored procedure is recompiled whenever the underlying DB2 table structure changes, then data consistency is ensured, but unnecessary recompilation occurs even when changes are not needed, increasing maintenance overhead
Solution Approach 1:
The patent uses parameter substitution to dynamically adapt the cursor to table structure changes. Instead of recompiling the procedure, the system modifies the cursor parameters at runtime based on the actual table structure, ensuring data consistency while avoiding unnecessary recompilation overhead.
3Adaptability or versatility
If a new stored procedure is created for each client-specific cursor requirement, then each client's needs are met precisely, but the number of stored procedures increases, complicating system management and increasing maintenance burden
Solution Approach 1:
The patent creates a universal stored procedure that can serve multiple clients with different cursor requirements. By parameterizing the procedure and retrieving client-specific cursors from a database table, a single procedure handles what previously required multiple separate procedures, reducing system complexity while maintaining adaptability.
Solution Approach 2:
The patent introduces a database table as an intermediary between the stored procedure and client-specific cursor requirements. This intermediary layer allows the procedure to dynamically select and adapt to different client needs without requiring separate procedures for each client, simplifying system management.
Data Source
AI summary
The invention relates to a creating a stored procedure that retrieves its queries from a database, e.g., a DB2 table, and then prepares and executes dynamic SQL based on parameters supplied by a calling program. According to an embodiment of the present invention, a computer implemented system implements a dynamic stored procedure tool and comprises: a memory interface that accesses a database table; an interactive interface that receives a user selection; a processor, coupled to the memory interface and the interactive interface, the processor configured to perform the steps comprising: retrieve input values from a calling program; retrieve a cursor from an external source, using the input values; determine whether one or more parameter substitutions are applicable; and execute the cursor dynamically.