# US 12,222,942 B2 · Remote datasource-based optimization of procedure-based multi-datasource queries > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 12,222,942 B2 - Document ID: US12222942B2 - Status: Granted - Kind: B2 - Application number: 17/483,976 - Filed: 2021-09-24 - Published: 2023-03-30 - Granted: 2025-02-11 - Earliest claimed: 2021-09-24 - Assignees: International Business Machines Corporation - Applicants: International Business Machines Corporation - Inventors: Hai Jun Shen; Chang Sheng Liu; Lei Cui; Ya Qiong Liu - Web page: https://patentgrep.com/patent/US12222942B2 ## CPC - G06F 16/24542: {Plan optimisation} - G06F 16/2458: Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries - G06F 16/24537: {of operators} - G06F 16/256: {in federated or virtual databases} - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract An approach is provided for optimizing multi-datasource queries in a networked computing environment. A procedure that contains a set of queries designed to access a specific set of data from a plurality of datasources in a virtualized hybrid storage environment (e.g., a virtualized hybrid cloud) is obtained. A set of mapped store procedures is created for the set of datasources referenced in the procedure. Each mapped store procedure includes a subset of queries that are applicable to a corresponding datasource from the set of queries in the procedure. These mapped store procedures are forwarded to the corresponding datasource for storage on the corresponding datasource. In response to a running of the procedure, execution of the mapped store procedure is commenced on each of the corresponding datasources on which they are stored. ## Claims ### Claim 1 A method for optimizing multi-datasource queries in a networked computing environment, comprising: obtaining a procedure comprising a plurality of placeholder values, that contains a set of queries designed to access a specific set of data from a plurality of datasources in a virtualized hybrid storage environment; creating, at a data virtualization layer, a set of mapped store procedures for the set of datasources referenced in the procedure for mapping from the plurality of placeholder values based on translation of each query of the set of queries to the specific set of data, each mapped store procedure comprising a subset of queries that are applicable to a corresponding datasource from the set of queries in the procedure; forwarding, from the data virtualization layer, each mapped store procedure of the set of mapped store procedures to the corresponding datasource for storage on the corresponding datasource; and commencing, in response to a running of the procedure, an execution on each of the corresponding datasources of the mapped store procedure stored thereon. ### Claim 2 (depends on 1) The method of claim 1, wherein the set of queries access data in a first datasource and a second datasource in the virtualized hybrid storage environment, and wherein the creating of the set of mapped store procedures creates a first mapped store procedure that includes a first subset of queries that are applicable to the first datasource and a second mapped store procedure that includes a second subset of queries that are applicable to the second datasource. ### Claim 3 (depends on 1) The method of claim 1, further comprising: retrieving a set of results from the execution of the corresponding mapped store procedure on of the corresponding datasources; and aggregating the set of results to obtain a final result set. ### Claim 4 (depends on 3) The method of claim 3, further comprising: retrieving, in response to a presence of an intermediate aggregating function, in the set of queries, an intermediate of results from the execution of the corresponding mapped store procedure on of the corresponding datasources; performing the aggregating function at the data virtualization layer; forwarding aggregating function results to each of the datasources; and continuing the execution on each of the corresponding datasources of the mapped store procedure stored thereon based on the aggregating function results. ### Claim 5 (depends on 1) The method of claim 1, the creating further comprising: substituting a set of naming attribute in the subset of queries from nicknames found in the procedure to local naming attributes specific to a corresponding datasource; transforming the subset of queries that are applicable to the corresponding datasource from a query language contained in the procedure to a second query language that corresponds to the corresponding datasource to get a set of transformed queries; and saving the set of transformed queries with the local naming attributes as the remaining elements as the mapped store procedure. ### Claim 6 (depends on 1) The method of claim 1, further comprising replacing a set of queries in the procedure with a set of calls to the set of mapped store procedures. ### Claim 7 (depends on 1) The method of claim 1, wherein the networked computing environment is a cloud computing environment, and wherein the virtualized hybrid storage environment is a virtualized hybrid cloud. ### Claim 8 A system for optimizing multi-datasource queries in a networked computing environment, comprising: a memory medium comprising instructions; a bus coupled to the memory medium; and a processor coupled to the bus that when executing the instructions causes the system to perform a method, comprising: obtaining a procedure comprising a plurality of placeholder values, that contains a set of queries designed to access a specific set of data from a plurality of datasources in a virtualized hybrid storage environment; creating, at a data virtualization layer, a set of mapped store procedures for the set of datasources referenced in the procedure for mapping from the plurality of placeholder values based on translation of each query of the set of queries to the specific set of data, each mapped store procedure comprising a subset of queries that are applicable to a corresponding datasource from the set of queries in the procedure; forwarding, from the data virtualization layer, each mapped store procedure of the set of mapped store procedures to the corresponding datasource for storage on the corresponding datasource; and commencing, in response to a running of the procedure, an execution on each of the corresponding datasources of the mapped store procedure stored thereon. ### Claim 9 (depends on 8) The system of claim 8, wherein the set of queries access data in a first datasource and a second datasource in the virtualized hybrid storage environment, and wherein the creating of the set of mapped store procedures creates a first mapped store procedure that includes a first subset of queries that are applicable to the first datasource and a second mapped store procedure that includes a second subset of queries that are applicable to the second datasource. ### Claim 10 (depends on 8) The system of claim 8, the method further comprising: retrieving a set of results from the execution of the corresponding mapped store procedure on of the corresponding datasources; and aggregating the set of results to obtain a final result set. ### Claim 11 (depends on 10) The system of claim 10, the method further comprising: retrieving, in response to a presence of an intermediate aggregating function, in the set of queries, an intermediate of results from the execution of the corresponding mapped store procedure on of the corresponding datasources; performing the aggregating function at the data virtualization layer; forwarding aggregating function results to each of the datasources; and continuing the execution on each of the corresponding datasources of the mapped store procedure stored thereon based on the aggregating function results. ### Claim 12 (depends on 8) The system of claim 8, the creating further comprising: substituting a set of naming attribute in the subset of queries from nicknames found in the procedure to local naming attributes specific to a corresponding datasource; transforming the subset of queries that are applicable to the corresponding datasource from a query language contained in the procedure to a second query language that corresponds to the corresponding datasource to get a set of transformed queries; and saving the set of transformed queries with the local naming attributes as the remaining elements as the mapped store procedure. ### Claim 13 (depends on 8) The system of claim 8, further comprising replacing a set of queries in the procedure with a set of calls to the set of mapped store procedures. ### Claim 14 (depends on 8) The system of claim 8, wherein the networked computing environment is a cloud computing environment, and wherein the virtualized hybrid storage environment is a virtualized hybrid cloud. ### Claim 15 A computer program product embodied in a computer readable storage medium that implements a method for optimizing multi-datasource queries in a networked computing environment, the method comprising: obtaining a procedure comprising a plurality of placeholder values, that contains a set of queries designed to access a specific set of data from a plurality of datasources in a virtualized hybrid storage environment; creating, at a data virtualization layer, a set of mapped store procedures for the set of datasources referenced in the procedure for mapping from the plurality of placeholder values based on translation of each query of the set of queries to the specific set of data, each mapped store procedure comprising a subset of queries that are applicable to a corresponding datasource from the set of queries in the procedure; forwarding, from the data virtualization layer, each mapped store procedure of the set of mapped store procedures to the corresponding datasource for storage on the corresponding datasource; and commencing, in response to a running of the procedure, an execution on each of the corresponding datasources of the mapped store procedure stored thereon. ### Claim 16 (depends on 15) The computer program product of claim 15, wherein the set of queries access data in a first datasource and a second datasource in the virtualized hybrid storage environment, and wherein the creating of the set of mapped store procedures creates a first mapped store procedure that includes a first subset of queries that are applicable to the first datasource and a second mapped store procedure that includes a second subset of queries that are applicable to the second datasource. ### Claim 17 (depends on 15) The computer program product of claim 15, the method further comprising: retrieving a set of results from the execution of the corresponding mapped store procedure on of the corresponding datasources; and aggregating the set of results to obtain a final result set. ### Claim 18 (depends on 17) The computer program product of claim 17, the method further comprising: retrieving, in response to a presence of an intermediate aggregating function, in the set of queries, an intermediate of results from the execution of the corresponding mapped store procedure on of the corresponding datasources; performing the aggregating function at the data virtualization layer; forwarding aggregating function results to each of the datasources; and continuing the execution on each of the corresponding datasources of the mapped store procedure stored thereon based on the aggregating function results. ### Claim 19 (depends on 15) The computer program product of claim 15, the creating further comprising: substituting a set of naming attribute in the subset of queries from nicknames found in the procedure to local naming attributes specific to a corresponding datasource; and transforming the subset of queries that are applicable to the corresponding datasource from a query language contained in the procedure to a second query language that corresponds to the corresponding datasource to get a set of transformed queries; saving the set of transformed queries with the local naming attributes as the remaining elements as the mapped store procedure. ### Claim 20 (depends on 15) The computer program product of claim 15, further comprising replacing a set of queries in the procedure with a set of calls to the set of mapped store procedures.