Dynamic Table-Level Stored Procedure for DB2 Query Automation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In the context of CICS and DB2 tables, the existing approach requires creating and maintaining a dedicated table-level stored procedure for each database table, which becomes cumbersome and time-consuming, especially when new tables are added or existing table structures change.
Innovation Solution
A computer-implemented system and method that dynamically builds and executes queries based on parameter values from a calling program, eliminating the need for dedicated table-level stored procedures by retrieving input parameters, identifying column properties, and using parameter markers to construct and execute queries, thereby reducing the need for manual maintenance and updates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a dedicated table-level stored procedure is created for each DB2 table, then the system provides structured data access and maintains data integrity, but the device complexity and maintenance burden increase significantly
Solution Approach 1:
The patent applies universality by creating a single generic table-level stored procedure that can handle multiple different DB2 tables through parameterization. Instead of having separate stored procedures for each table, one universal procedure accepts parameters such as table name, column names, and query conditions, allowing it to dynamically serve multiple tables while maintaining structured data access and integrity.
Solution Approach 2:
The patent uses parameter changes by making the stored procedure highly parameterized, where inputs include table identifiers, column specifications, and query parameters. This allows the same stored procedure structure to adapt to different tables and query requirements by changing the parameter values rather than requiring separate procedures for each scenario.
2Adaptability or versatility
If new DB2 tables are added to the system, then the database functionality is extended, but the development and maintenance time increases due to creating new stored procedures
Solution Approach 1:
When new DB2 tables are added, the universal stored procedure can immediately support them without requiring new procedure creation. The procedure accepts table names as parameters, so any table that follows the expected schema can be accessed through the same procedure, eliminating the time-consuming process of creating and testing new stored procedures for each new table.
Solution Approach 2:
The system enables self-service by allowing the stored procedure to dynamically determine table structures and generate appropriate queries based on input parameters. The procedure can automatically adapt to new tables by receiving table identifiers and column specifications as parameters, eliminating the need for manual procedure customization for each new table addition.
3Adaptability or versatility
If the structure of existing DB2 tables changes, then the database adapts to new requirements, but the stored procedures require maintenance and updates
Solution Approach 1:
The stored procedure uses parameterized inputs for column names and query structures, allowing it to automatically adapt when table structures change. When columns are added, modified, or removed from a DB2 table, the procedure can continue to function by receiving updated parameter values that reflect the new table structure, eliminating the need to modify the procedure's internal logic.
4Reliability
If multiple table-level stored procedures are maintained, then each table has dedicated data access control, but the testing and quality assurance effort increases
Solution Approach 1:
The patent merges multiple separate stored procedures into a single universal procedure that handles data access for multiple tables. This consolidation maintains data access control through parameterized table and column specifications while significantly reducing the number of procedures that need to be individually tested and validated, thereby improving testing efficiency and quality assurance productivity.
Data Source
AI summary
An embodiment of the present invention is directed to creating and implementing an innovative table-level stored procedure that builds and executes the necessary query dynamically based on the parameter values passed from the calling program.