SQL Catalog-Based Heterogeneous Data Gateway
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Accessing and querying data from disparate databases with different protocols is challenging, as existing systems struggle to handle simultaneous queries across multiple data stores structured according to different protocols, such as DB2, Oracle, and SQL Server, requiring multiple queries and complicating data retrieval.
Innovation Solution
A system that uses a logical name-to-address lookup table or logical name-to-connection metadata map to identify and connect to multiple databases, breaking down a single query into sub-queries and combining responses to provide a unified query response, utilizing the catalog part of an SQL identifier for abstraction and transparent data access.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If data is stored in disparate databases with different protocols, then data diversity and storage flexibility are improved, but query complexity and system complexity increase
Solution Approach 1:
The patent introduces a gateway as an intermediary component that sits between the client application and multiple disparate databases. This gateway translates standard SQL queries into protocol-specific queries for each database type (DB2, Oracle, SQL Server, etc.), eliminating the need for clients to handle multiple protocols directly. The gateway maintains a metadata catalog that maps logical database names to physical database connections, enabling transparent routing and query transformation across heterogeneous data sources.
2Reliability
If multiple queries are generated to access data from disparate data stores, then data access completeness is improved, but operation time and processing overhead increase
Solution Approach 1:
The patent merges multiple protocol-specific queries into a single standard SQL query interface. The gateway consolidates access to multiple disparate databases through a unified query mechanism, allowing clients to issue one query against a logical database name rather than generating separate queries for each physical database. This merging reduces the number of query generation steps and simplifies the access process while maintaining complete data retrieval capability across all connected databases.
3Productivity
If logical name-to-address lookup is implemented, then data access efficiency is improved, but system complexity and memory requirements increase
Solution Approach 1:
The patent implements preliminary action by pre-establishing a metadata catalog that contains logical database names and their corresponding physical database connection information before any client queries are processed. This catalog is populated in advance with mappings between logical names and actual database connections, allowing the gateway to perform quick lookups and route queries efficiently without complex real-time decision-making. The preliminary setup of this naming structure simplifies subsequent query routing operations.
Data Source
AI summary
Systems and methods are provided for providing data from a plurality of databases stored on a plurality of disparate servers. A query is received for data from a plurality of databases stored on a plurality of disparate servers. An address for a first server is resolved that stores a first database identified by the catalog field in the query using a logical name-to-connection metadata map. A first sub-query is transmitted to the first server based on the received query, and a first sub-query response is received from the first server. The resolving, transmitting, and receiving are repeated for a second server. The first sub-query response and the second sub-query response are combined to generate a query response according to the received query, and the query response is transmitted.