SQL Procedure Deployment Consistency via Component Segmentation

Solution Overview

Problem

There is no conventional approach to ensure consistent behavior and performance of SQL procedures when deployed from a source DBMS to a target DBMS, as existing methods are not applicable to internal stored procedures and can cause behavior changes due to minor modifications in the procedural logic component.

Innovation Solution

A processor-implemented method and system that generates SQL procedure source code, a symbol table, and encapsulated optimization results to regenerate run-time instructions, maintaining the association between the procedural logic and database request components, allowing for consistent deployment without regeneration of the internal representation of the procedural logic component, and preserving optimization results.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If the SQL procedure is recreated on the production system to ensure consistency, then behavior consistency is improved, but the risk of behavior changes increases due to minor modifications in the procedural logic component

Engineering Contradiction:

Improvebehavior consistencyVSAvoidbehavior changes

Core Design Contradiction:

ReliabilityVSObject-affected harmful factors

Solution Approach 1:

The patent copies the internal representation of the procedural logic component from the testing system to the production system without regeneration. This copying approach preserves the exact procedural logic behavior while allowing the database request component to be regenerated for the target system, thereby ensuring behavior consistency without introducing modifications to the procedural logic.

Inventive Principle:

Principle #26Copying

Solution Approach 2:

The patent segments the SQL procedure into two independent components: the procedural logic component (internal representation) and the database request component (run-time instructions). This segmentation allows the procedural logic to be copied as-is while the database request component can be regenerated for the target system, resolving the contradiction between consistency and adaptability.

Inventive Principle:

Principle #1Segmentation

2Adaptability or versatility

If the internal representation of the procedural logic component is regenerated on the target system, then compatibility with the target DBMS is improved, but behavior consistency with the testing system deteriorates

Engineering Contradiction:

Improvetarget DBMS compatibilityVSAvoidbehavior consistency

Core Design Contradiction:

Adaptability or versatilityVSReliability

Solution Approach 1:

The patent copies the internal representation of the procedural logic component from the testing system to the production system without regeneration. This copying approach preserves the exact procedural logic behavior while allowing the database request component to be regenerated for the target system, thereby ensuring behavior consistency without introducing modifications to the procedural logic.

Inventive Principle:

Principle #26Copying

Solution Approach 2:

The patent segments the SQL procedure into two independent components: the procedural logic component (internal representation) and the database request component (run-time instructions). This segmentation allows the procedural logic to be copied as-is while the database request component can be regenerated for the target system, resolving the contradiction between consistency and adaptability.

Inventive Principle:

Principle #1Segmentation

3Productivity

If the SQL procedure is deployed without copying the internal representation, then deployment speed is improved, but optimization results are lost

Engineering Contradiction:

Improvedeployment speedVSAvoidoptimization results

Core Design Contradiction:

ProductivityVSReliability

Solution Approach 1:

The patent copies the internal representation of the procedural logic component from the testing system to the production system without regeneration. This copying approach preserves the exact procedural logic behavior while allowing the database request component to be regenerated for the target system, thereby ensuring behavior consistency without introducing modifications to the procedural logic.

Inventive Principle:

Principle #26Copying

Data Source

PatentEP1960910B1System and method for deploying an SQL procedure

Publication Date: 2011.08.03 INTERNATIONAL BUSINESS MACHINE CORPORATION

Figure 1
Figure 2
Figure 3

AI summary

An SQL procedure deployment system deploys a query procedure comprising a procedural logic component and a database request component from a source system to a target system. At generation of the query procedure, the system generates a query procedure source code, a symbol table, and an encapsulated optimization results to regenerate run-time instructions for query statements of the database request component and make the query statements function with a deployed procedural logic component. The system maintains an association between the procedural logic component and the database request component during deployment. The system copies an internal representation of the procedural logic component to the target system without regeneration. The system regenerates the database request component to recreate the query procedure on the target system and to maintain a consistent interface between the procedural logic component and database request component.