Two-level caching system for prepared SQL statements in a relational database management system

• US 6,115,703 A
• Filed: 05/11/1998
• Issued: 09/05/2000
• Est. Priority Date: 05/11/1998
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bireley, William Robert, Curran, Lisa R., Lobo, Adrian Brian, Lin, Fen-Ling, Wong, Jerome Quan
• Primary Examiner(s): Kulik, Paul V.
• Application Number:
  US09/075,651
• Time in Patent Office: 848 Days
• Field of Search: 707/2, 707/3, 707/4, 707/8, 707/201, 711/119, 711/122
• US Class Current: 707/704
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: Y10S 707/917 Text
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A method, apparatus, and article of manufacture for a computer implemented two-level caching system. A statement is executed in a computer. The statement is executed by the computer to manipulate data in a database stored on a data storage device connected to the computer. Initially, a first statement is prepared to create an executable structure for executing the first statement from an application. Then, the executable structure is stored in an application-level cache associated with the application. A second statement is received for execution from the application. It is determined that the second statement can be executed using the stored executable structure for the first statement. Next, the second statement is executed using the stored executable structure.

Claims

1. A method of executing a statement in a computer, the statement being executed by the computer to manipulate data in a database stored on a data storage device connected to the computer, the method comprising the steps of:

preparing a first statement to create an executable structure for executing the first statement from an application;

storing the executable structure in an application-level cache associated with the application across a commit operation;

receiving a second statement for execution from the application;

determining that the second statement can be executed using the stored executable structure for the first statement; and

executing the second statement using the stored executable structure.

2. The method of claim 1, wherein the step of storing the executable structure further comprises the steps of:

storing all text of the first statement;

storing execution options of the first statement; and

storing a user identifier of the first statement.

3. The method of claim 2, wherein the step of determining further comprises the step of comparing the text, the execution options, and the user identifier of the first and second statements.

4. The method of claim 1, further comprising the steps of:

determining that the application-level cache does not contain an executable structure for use in executing the second received statement;

determining that a global cache contains an executable structure that can be used for executing the second received statement; and

copying the executable structure from the global cache to the application-level cache.

5. The method of claim 1, further comprising the step of disallowing a modification to any object referenced by the second statement until execution of the statement.

6. The method of claim 5, further comprising:

when receiving a request to modify an object referenced by the second statement,

waiting a predetermined amount of time for the execution of the second statement to complete; and

when the execution of the second statement does not complete within the predetermined amount of time, disallowing the request to modify the object.

7. The method of claim 1, further comprising the step of holding one or more locks beyond a commit operation for a dynamic statement.

8. The method of claim 1, further comprising the steps of:

monitoring a system limit on cached statements for all application-level caches;

determining that the system limit on cached statements for all application-level caches has been exceeded; and

discarding some of the cached statements in one or more application-level caches based on predefined criteria.

9. The method of claim 8, wherein the predefined criteria comprises how recently the cached statements were prepared.

10. The method of claim 1, further comprising;

determining that a cached statement in an application-level cache is no longer available for use;

attempting to locate the statement in a global cache; and

when the statement is not available in the global cache, preparing the statement using stored text for the statement in the application-level cache.

11. The method of claim 1, further comprising:

determining that a cached statement in an application-level cache is no longer available for use; and

preparing the statement using stored text for the statement in the application-level cache.

12. An apparatus for executing a statement, comprising:

a computer having a data storage device connected thereto, wherein the data storage device stores a database;

one or more computer programs, performed by the computer, for preparing a first statement to create an executable structure for executing the first statement from an application, for storing the executable structure in an application-level cache associated with the application across a commit operation, for receiving a second statement for execution from the application, for determining that the second statement can be executed using the stored executable structure for the first statement, and for executing the second statement using the stored executable structure.

13. The apparatus of claim 12, wherein the means for storing the executable structure further comprises:

means for storing all text of the first statement;

means for storing execution options of the first statement; and

means for storing a user identifier of the first statement.

14. The apparatus of claim 13, wherein the means for determining further comprises the means for comparing the text, the execution options, and the user identifier of the first and second statements.

15. The apparatus of claim 14, further comprising:

means for determining that the application-level cache does not contain an executable structure for use in executing the second received statement;

means for determining that a global cache contains an executable structure that can be used for executing the second received statement; and

means for copying the executable structure from the global cache to the application-level cache.

16. The apparatus of claim 12, further comprising the means for disallowing a modification to any object referenced by the second statement until execution of the statement.

17. The apparatus of claim 16, further comprising:

means for, when receiving a request to modify an object referenced by the second statement,

waiting a predetermined amount of time for the execution of the second statement to complete; and

when the execution of the second statement does not complete within the predetermined amount of time, disallowing the request to modify the object.

18. The apparatus of claim 12, further comprising the means for holding one or more locks beyond a commit operation for a dynamic statement.

19. The apparatus of claim 12, further comprising:

means for monitoring a system limit on cached statements for all application-level caches;

means for determining that the system limit on cached statements for all application-level caches has been exceeded; and

means for discarding some of the cached statements in one or more application-level caches based on predefined criteria.

20. The apparatus of claim 19, wherein the predefined criteria comprises how recently the cached statements were prepared.

21. The apparatus of claim 12, further comprising;

means for determining that a cached statement in an application-level cache is no longer available for use;

means for attempting to locate the statement in a global cache; and

means for, when the statement is not available in the global cache, preparing the statement using stored text for the statement in the application-level cache.

22. The apparatus of claim 21, further comprising;

means for determining that a cached statement in an application-level cache is no longer available for use; and

means for preparing the statement using stored text for the statement in the application-level cache.

23. An article of manufacture comprising a computer program carrier readable by a computer and embodying one or more instructions executable by the computer to perform method steps for executing a statement, the statement being performed by the computer to retrieve data from a database stored in a data storage device connected to the computer, the method comprising the steps of:

preparing a first statement to create an executable structure for executing the first statement from an application;

storing the executable structure in an application-level cache associated with the application across a commit operation;

receiving a second statement for execution from the application;

determining that the second statement can be executed using the stored executable structure for the first statement; and

executing the second statement using the stored executable structure.

24. The method of claim 23, wherein the step of storing the executable structure further comprises the steps of:

storing all text of the first statement;

storing execution options of the first statement; and

storing a user identifier of the first statement.

25. The method of claim 24, wherein the step of determining further comprises the step of comparing the text, the execution options, and the user identifier of the first and second statements.

26. The method of claim 23, further comprising the steps of:

determining that the application-level cache does not contain an executable structure for use in executing the second received statement;

determining that a global cache contains an executable structure that can be used for executing the second received statement; and

copying the executable structure from the global cache to the application-level cache.

27. The method of claim 23, further comprising the step of disallowing a modification to any object referenced by the second statement until execution of the statement.

28. The method of claim 23, further comprising:

when receiving a request to modify an object referenced by the second statement,

waiting a predetermined amount of time for the execution of the second statement to complete; and

when the execution of the second statement does not complete within the predetermined amount of time, disallowing the request to modify the object.

29. The method of claim 23, further comprising the step of holding one or more locks beyond a commit operation for a dynamic statement.

30. The method of claim 23, further comprising the steps of:

monitoring a system limit on cached statements for all application-level caches;

determining that the system limit on cached statements for all application-level caches has been exceeded; and

discarding some of the cached statements in one or more application-level caches based on predefined criteria.

31. The method of claim 30, wherein the predefined criteria comprises how recently the cached statements were prepared.

32. The method of claim 23, further comprising;

determining that a cached statement in an application-level cache is no longer available for use;

attempting to locate the statement in a global cache; and

when the statement is not available in the global cache, preparing the statement using stored text for the statement in the application-level cache.

33. The method of claim 23, further comprising;

determining that a cached statement in an application-level cache is no longer available for use; and

preparing the statement using stored text for the statement in the application-level cache.