System and method for executing a large object fetch query against a database

• US 7,039,651 B2
• Filed: 04/29/2003
• Issued: 05/02/2006
• Est. Priority Date: 02/26/2003
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cotner, Curt L., Begg, Robert A., Vincent, Timothy J., Ostler, Paul A., Gross, Brent P.
• Primary Examiner(s): Ali, Mohammad
• Application Number:
  US10/425,851
• Publication Number:
  US 20040167879A1
• Time in Patent Office: 1,099 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-205, 709/226
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2219 Large Object storage; Management thereof
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure
• CPC Class Codes: Y10S 707/99946 Object-oriented database structure network

Abstract

A database server helps to streamline the retrieval of LOB values by deciding to send a locator in replacement of the LOB value, or the LOB value itself, depending upon the specific LOB value being retrieved. A threshold value is determined in a fetch query, and the LOB sizes below that threshold are sent as values in a corresponding fetch response, and the lengths above are sent as locators in the fetch response. Indicators are inserted in a fetch parameter of the response to inform the receiving client the form of retrieval that was used for each requested LOB value being returned.

Claims

1. A method for directing a database management system to execute a fetch query, comprising:

receiving the fetch query that contains at least one large object fetch request and a predefined threshold parameter associated with the large object fetch request;

accessing a selected large object value that corresponds to the large object fetch request of the fetch query;

comparing the selected large object value with the predefined threshold value;

returning a fetch response that corresponds to the large object fetch request according to a return operation from a pair of return operations; and

wherein the pair of return operations comprises;

a first return operation that returns the selected large object value in the large object fetch response, if the large object value is less than the predefined threshold value; and

a second return operation that returns a large object locator that is associated with the large object value in the large object fetch response, if the large object value is greater than the predefined threshold value;

wherein the fetch response comprises a fetch parameter associated with the large object fetch response;

placing an indicator in the fetch parameter; and

wherein the indicator comprises one of two distinct return states, a first state that is placed by the first return operation and a second state that is placed by the second return operation.

2. The method according to claim 1, wherein the first state comprises a large object indicator that indicates the presence of the large object value in the fetch response.

3. The method according to claim 2, wherein the second state comprises a large object locator that indicates the location of a large object value remaining for a subsequent fetch operation.

4. The method according to claim 1, further comprising a plurality of fetch parameters, wherein each of the fetch parameters comprises an inline parameter associated with a respective large object fetch response.

5. The method according to claim 4, further comprising representing the large object fetch request by a column in the fetch query.

6. The method according to claim 1, further comprising using a fetch parameter in the fetch query to confirm if a fetch request is a large object fetch request, prior to accessing the selected large object value.

7. The method according to claim 5, wherein the predefined threshold parameter is an inline parameter associated with the large object fetch request.

8. The method according to claim 1, wherein the predefined threshold parameter is a default value associated with the database management system.

16. The computer program product according to claim 1, wherein the predefined threshold parameter is a default value associated with the database management system.

9. A computer program product having instruction codes for directing a database management system to execute a fetch query, comprising:

a first set of instruction codes for receiving the fetch query that contains at least one large object fetch request and a predefined threshold parameter associated with the large object fetch request;

a second set of instruction codes for accessing a selected large object value that corresponds to the large object fetch request of the fetch query;

a third set of instruction codes for corn paring the selected large object value with the predefined threshold value;

a fourth set of instruction codes for returning a fetch response that corresponds to the large object fetch request according to a return operation from a pair of return operations; and

wherein the pair of return operations comprises;

a first return operation that returns the selected large object value in the large object fetch response, if the large object value is less than the predefined threshold value; and

a second return operation that returns a large object locator that is associated with the large object value in the large object fetch response, if the large object value is greater than the predefined threshold value;

wherein the fetch response comprises a fetch parameter associated with the large object fetch response;

a fifth set of instruction codes for placing an indicator in the fetch parameter; and

wherein the indicator comprises one of two distinct return states, a first state that is placed by the first return operation and a second state that is placed by the second return operation.

10. The computer program product according to claim 9, wherein the first state comprises a large object indicator that indicates the presence of the large object value in the fetch response.

11. The computer program product according to claim 10, wherein the second state comprises a large object locator that indicates the location of a large object value remaining for a subsequent fetch operation.

12. The computer program product according to claim 9, further comprising a plurality of fetch parameters, wherein each of the fetch parameters comprises an inline parameter associated with a respective large object fetch response.

13. The computer program product according to claim 12, further comprising a sixth set of instruction codes for representing the large object fetch request by a column in the fetch query.

14. The computer program product according to claim 9, wherein the fetch query comprises a fetch parameter that confirms if a fetch request is a large object fetch request prior to accessing the selected large object value.

15. The computer program product according to claim 13, wherein the predefined threshold parameter is an inline parameter associated with the large object fetch request.

17. A system for directing a database management system to execute a fetch query, comprising:

means for receiving the fetch query that contains at least one large object fetch request and a predefined threshold parameter associated with the large object fetch request;

means for accessing a selected large object value that corresponds to the large object fetch request of the fetch query;

means for comparing the selected large object value with the predefined threshold value;

means for returning a fetch response that corresponds to the large object fetch request according to a return operation from a pair of return operations; and

wherein the pair of return operations comprises;

a first return operation that returns the selected large object value in the large object fetch response, if the large object value is less than the predefined threshold value; and

a second return operation that returns a large object locator that is associated with the large object value in the large object fetch response, if the large object value is greater than the predefined threshold value;

wherein the fetch response comprises a fetch parameter associated with the large object fetch response;

means for placing an indicator in the fetch parameter; and

wherein the indicator comprises one of two distinct return states, a first state that is placed by the first return operation and a second state that is placed by the second return operation.

18. The system according to claim 17, wherein the first state comprises a large object indicator that indicates the presence of the large object value in the fetch response.

19. The system according to claim 18, wherein the second state comprises a large object locator that indicates the location of a large object value remaining for a subsequent fetch operation.

20. The system according to claim 17, further comprising a plurality of fetch parameters, wherein each of the fetch parameters comprises an inline parameter associated with a respective large object fetch response.

21. The system according to claim 20, further comprising means for representing the large object fetch request by a column in the fetch query.

22. The system according to claim 17, wherein the fetch query comprises a fetch parameter that confirms if a fetch request is a large object fetch request prior to accessing the selected large object value.

23. The system according to claim 21, wherein the predefined threshold parameter is an inline parameter associated with the large object fetch request.

24. The system according to claim 17, wherein the predefined threshold parameter is a default value associated with the database management system.