Global caching and sharing of SQL statements in a heterogeneous application environment

• US 6,321,235 B1
• Filed: 09/29/1998
• Issued: 11/20/2001
• Est. Priority Date: 09/29/1998
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bird, Paul M.
• Primary Examiner(s): Black, Thomas
• Assistant Examiner(s): RONES, CHARLES
• Application Number:
  US09/162,164
• Publication Number:
  US 20010051956A1
• Time in Patent Office: 1,148 Days
• Field of Search: 707/8, 707/2, 707/4, 707/203, 709/203
• US Class Current: 707/802
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/955 Object-oriented
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99954 Version management

Abstract

A global cache for SQL sections and methods of accessing the cache. The global cache being at the database level and being accessible to all agents of all applications. The global cache having a static and a dynamic portion. The static portion containing section entries having both section information and section data. The dynamic portion having two sub-portions, a statement portion and a dependency portion. The dependency portion containing multiple SQL statements, multiple compilation environments for each SQL statement and multiple variations within each compilation environment. The dependency portion of the dynamic portion containing a plurality of lists of object types. Each object type having its own list, the list containing data on which variations are dependant on each object.

Claims

1. A method of operating a relational database management system, said system comprising a plurality of nodes, said method providing a global cache accessible by a plurality of agents, wherein said global cache comprises a portion including a static SQL section and a portion including a dynamic SQL section.

2. The method of claim 1, further comprising locating a section for an SQL statement within said global cache, said locating said section comprising:

a) locating the package in the static SQL portion corresponding to the package of the current application;

b) locating a section entry within said package corresponding to the SQL statement to be executed;

c) executing the section for said section entry if said section entry is for a static SQL statement; and

d) if the section for said section entry is for a dynamic SQL statement, then locating and executing the variation corresponding to the text of the SQL statement in a statement portion of the dynamic portion.

3. The method of claim 2, further comprising creating new variations in said cache, said creating new variations comprising:

a) locating the package in the static SQL portion corresponding to the package of the current application;

b) locating the section entry within said package corresponding to the SQL statement to be executed;

c) if the section for said section entry is for a dynamic SQL statement, then determining the existence of the SQL statement text, the compilation environment and the variation within the statement portion of the dynamic portion; and

d) if any or all of said SQL statement text, compilation environment or variation are not located within said statement portion, then as required;

i) creating a statement entry and adding it to the list of statement entries;

ii) creating a compilation environment and adding it to the list of compilation environments under the appropriate statement entry; and

iii) calling the SQL compiler to create a new variation and adding it to the list of variations under the appropriate compilation environment.

4. A node containing computer readable memory for storing instructions for use in the execution of the method of claim 1.

5. A medium encoded with executable program code to effect the method of claim 1.

8. The global cache of claim 1 wherein said dynamic SQL portion comprises:

a) a statement portion; and

b) a dependency portion.

9. The global cache of claim 8, wherein the static portion includes, in at least one package, a section including a section entry for a dynamic SQL request.

10. The global cache of claim 8, wherein said statement portion comprises:

a) one or more SQL text statements;

b) one or more compilation environments, each compilation environment being linked with one SQL text statement;

c) at least one variation linked to each of said compilation environments.

11. The global cache of claim 10, wherein each variation includes an indication of the validity thereof.

12. The global cache of claim 11, wherein said dependency portion comprises a plurality of lists of objects, each of said lists being specific to a single object type, each object in each of said lists containing information on which of said variations are dependent on said object.

13. The global cache of claim 11 wherein said compilation environment comprises information not contained in the catalogue tables, said information being of a type that influences the decision made by an SQL compiler when generating an access plan.

6. A global cache for SQL sections in a relational database management system, said global cache being stored in a computer readable memory and being accessible to a plurality of agents, said cache comprising:

a) a portion including a static SQL section; and

b) a portion including a dynamic SQL section.

7. The global cache of claim 6 wherein said static SQL portion comprises:

a) one or more packages; and

b) one or more section entries within each of said packages.

14. A Relational Database Management system comprising a plurality of interlinked heterogeneous nodes, each of said nodes having associated storage and processor resources wherein each node includes:

a) a global cache comprising a plurality of SQL sections;

b) means for locating said sections; and

c) means for creating new variations in said cache.

15. The system of claim 14, wherein said global cache comprises:

a) a static SQL portion; and

b) a dynamic SQL portion.

16. The system of claim 15 wherein said static portion comprises:

a) one or more packages; and

b) one or more section entries within each of said packages.

17. The system of claim 16, wherein the static portion includes, in one package, a section including a section entry for a dynamic SQL request.

18. The system of claim 17 wherein said dynamic portion comprises:

a) a statement portion; and

b) a dependency portion.

19. The system of claim 18, wherein said statement portion comprises:

a) one or more SQL text statements;

b) one or more compilation environments, each compilation environment being linked with one SQL text statement;

c) at least one variation linked to each of said compilation environments.

20. The system of claim 19, wherein each variation includes an indication of the validity thereof.

21. The system of claim 20, wherein said dependency portion comprises a plurality of lists of objects, each of said lists being specific to a single object type, each object in each of said lists containing information on which of said variations are dependent on said object.

22. The system of claim 21 wherein said compilation environment comprises information not contained in the catalogue tables, said information being of a type that influences the decision made by an SQL compiler when generating an access plan.