Correlating Database Queries with Source Code via Stack Trace Analysis

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Database administrators and developers face challenges in correlating queries with their source code, especially in complex architectures, leading to difficulties in identifying poorly performing queries and understanding the impact of database schema changes, which hinders efficient database maintenance and development.

Innovation Solution

A system that uses a source code parser and debugger to identify API calls, derive query text, and generate user interface views to provide insights into query execution and database object usage, allowing for better correlation and analysis of queries with their source code.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Loss of time

If database applications are maintained using traditional manual methods, then developers have full control over coding, but the process becomes tedious and time-consuming when correlating queries with source code

Engineering Contradiction:

Improvetime to correlate queries with source codeVSAvoidease of identifying query source

Core Design Contradiction:

Loss of timeVSEase of operation

Solution Approach 1:

The patent introduces a correlation system as an intermediary that automatically links SQL queries with their corresponding source code locations. This mediator captures query execution information, stack traces, and source code metadata to create automated correlations, eliminating the manual tedious process of tracing queries back to their origin while maintaining full developer control over the coding process.

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent replaces the mechanical manual process of correlating queries with source code through stack trace analysis and automated information gathering. The system automatically captures execution context, analyzes stack traces, and maps queries to source locations without requiring manual intervention, thereby reducing time loss while preserving operational ease.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

2Adaptability or versatility

If database schema changes are made to reflect business needs, then the database can better serve business requirements, but it becomes difficult to determine which applications are affected and the cost of modification

Engineering Contradiction:

Improveadaptability of database to business needsVSAvoidcomplexity of determining application impact

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent implements a feedback mechanism that continuously monitors and records the relationships between database objects and applications. When schema changes are proposed, the system provides immediate feedback by querying the correlation data to identify which applications are affected and the extent of modifications required. This feedback loop enables informed decision-making about schema changes while maintaining adaptability to business needs.

Inventive Principle:

Principle #23Feedback

Solution Approach 2:

The patent performs preliminary analysis by pre-establishing the correlation relationships between database objects and applications before any schema changes occur. This preliminary action creates a baseline of dependencies that can be quickly queried during schema change evaluation, reducing the complexity of determining application impact while allowing the database to adapt to business requirements.

Inventive Principle:

Principle #10Preliminary action

3Loss of information

If stack traces are gathered to identify query source code, then correlation information can be obtained, but the process must be repeated every time a problem occurs and requires adding and maintaining stack traces

Engineering Contradiction:

Improveinformation about query sourceVSAvoidproductivity of developers

Core Design Contradiction:

Loss of informationVSProductivity

Solution Approach 1:

The patent performs preliminary action by capturing and storing query correlation information, stack traces, and source code metadata in advance during normal application execution. This pre-gathered information is stored in a structured format that can be quickly retrieved and analyzed when problems occur, eliminating the need to repeat the entire stack trace gathering process and maintaining high developer productivity.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent implements self-service by creating an automated system that continuously gathers, correlates, and maintains query-source code relationships without requiring manual intervention. The system automatically captures execution context, maintains correlation data, and provides instant access to query source information, thereby preventing information loss while preserving developer productivity through automation.

Inventive Principle:

Principle #25Self-service

Data Source

PatentUS10013331B2Correlating queries issued by applications with their source lines and analyzing applications for problem determination and where used analysis

Publication Date: 2018.07.03 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

Provided are techniques for invoking with a processor executing on a computer a source code parser to obtain source information that includes a first location of an Application Programming Interface (API) call and parameters of the API call in source code of a client application, where the parameters the API call do not include query text for a query that is to be used to access a database; examining a stack trace to determine a second location of the API call in the stack trace; and deriving the query of the API call and a third location of the query in the source code by identifying the query in the stack trace at the location of the API call in the stack trace.