Method, system, and program for processing a fetch request for a target row at an absolute position from a first entry in a table

• US 6,721,731 B2
• Filed: 07/26/2001
• Issued: 04/13/2004
• Est. Priority Date: 09/07/2000
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cornwell, Karelle Lynne, Shyam, Kalpana, Phoenix, San Yu
• Primary Examiner(s): Mizrahi, Diane D.
• Assistant Examiner(s): WU, YICUN
• Application Number:
  US09/915,783
• Publication Number:
  US 20020032678A1
• Time in Patent Office: 992 Days
• Field of Search: 707/1, 707/2, 707/4, 707/7, 707/8, 707/10, 707/100, 707/101, 707/102, 707/533, 717/114, 345/467, 345/160, 345/784, 345/787, 305/15, 358/1.1, 296/031.4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

Provided is a method, system, program, and data structures for making data available to an application program. A result table is generated including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause. The result table includes, for each row, a location identifier of the corresponding row in the base table and a column for each column in a query select list. The result table is accessed to return requested data from the base table to the application program.

Claims

1. A method for making data available to an application program, comprising:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch absolute request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

determining a relative distance from the current row to the target row;

determining whether the current row is within a threshold distance from the target row; and

performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row to implement the fetch absolute request if the current row is within the threshold distance from the target row.

2. The method of claim 1, further comprising:

if the distance between the current row and target row exceeds the threshold distance, then performing;

(i) repositioning the cursor row pointer to the first entry in the result table; and

(ii) moving the cursor row pointer from the first entry in the result table the absolute distance to the target row in the result table.

3. The method of claim 1, wherein the threshold distance comprises a number of result table rows, wherein the current row is within the threshold distance from the target row if the number of rows between the current row pointer and the target row is less than the threshold distance number of rows.

4. The method of claim 1, wherein the threshold distance comprises a number of sequential pages, wherein the current row is within the threshold distance from the target row if a number of sequential pages between a page including the current row and a page including the target row is less than the threshold distance number of pages.

5. The method of claim 1, wherein performing the fetch operation further comprises:

determining whether the target row is available to return; and

returning the target row to the application program if the target row is available to return.

10. The method of claim 1, wherein the result table comprises a database table.

11. The method of claim 1, wherein the absolute distance specified in the fetch request specifies movement of the cursor row pointer one or more rows preceding the current result table row addressed by the cursor row pointer.

6. A method for making data available to an application program, comprising:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

determining a relative distance from the current row to the target row;

performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row;

determining whether the target row is available to return;

returning the target row to the application program if the target row is available to return; and

returning a code to the application program in response to the fetch request indicating that the requested row in the result table is not available if the target row in the result table is no longer available to return in response to the fetch request.

7. A method for making data available to an application program, comprising:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause, wherein the result table includes, for each row, a location identifier of the corresponding row in the base table;

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

determining a relative distance from the current row to the target row;

performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row;

determining whether the target row is available to return;

returning the target row to the application program if the target row is available to return by;

(i) using the location identifier for the target row to access the row in the base table corresponding to the target row;

(ii) updating the target row in the result table with the accessed row in the base table; and

(iii) returning the data from the accessed row in the base table to the application program in response to the fetch request.

8. The method of claim 7, wherein performing the fetch operation further comprises:

using the location identifier for the target row to access the row in the base table corresponding to the target row; and

determining whether the accessed row in the base table qualifies according to the query predicate clause, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table has been updated.

9. A method for making data available to an application program, comprising:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

determining a relative distance from the current row to the target row;

performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row;

determining whether a delete flag for the target row indicates whether the corresponding row in the base table was deleted, wherein the target row in the result table is no longer available to return in response to the fetch request if the delete flag indicates that the corresponding row in the base table was delete; and

returning the target row to the application program if the target row is available to return.

12. A system for making data available to an application program, comprising:

a storage device including a base table;

a computer readable medium;

means for generating a result table in the computer readable medium including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

means for maintaining a cursor row pointer that points to a current row in the result table;

means for receiving a fetch absolute request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

means for determining a relative distance from the current row to the target row;

means for determining whether the current row is within a threshold distance from the target row; and

means for performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row to implement the fetch absolute request if the current row is within the threshold distance from the target row.

13. The system of claim 12, further comprising:

means for performing if the distance between the current row and target row exceeds the threshold distance;

(i) repositioning the cursor row pointer to the first entry in the result table; and

(ii) moving the cursor row pointer from the first entry in the result table the absolute distance to the target row in the result table.

14. The system of claim 12, wherein the threshold distance comprises a number of result table rows, wherein the current row is within the threshold distance from the target row if the number of rows between the current row pointer and the target row is less than the threshold distance number of rows.

15. The system of claim 12, wherein the threshold distance comprises a number of sequential pages, wherein the current row is within the threshold distance from the target row if a number of sequential pages between a page including the current row and a page including the target row is less than the threshold distance number of pages.

16. The system of claim 12, wherein the means for performing the fetch operation further performs:

determining whether the target row is available to return; and

returning the target row to the application program if the target row is available to return.

21. The system of claim 12, wherein the result table comprises a database table.

22. The system of claim 12, wherein the absolute distance specified in the fetch request specifies movement of the cursor row pointer one or more rows preceding the current result table row addressed by the cursor row pointer.

17. A system for making data available to an application program, comprising:

a storage device including a base table;

a computer readable medium;

means for generating a result table in the computer readable medium including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

means for maintaining a cursor row pointer that points to a current row in the result table;

means for receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

means for determining a relative distance from the current row to the target row; and

means for performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row;

means for determining whether the target row is available to return;

means for returning the target row to the application program if the target row is available to return; and

means for returning a code to the application program in response to the fetch request indicating that the requested row in the result table is not available to return in response to the fetch request.

18. A system for making data available to an application program, comprising:

a storage device including a base table;

a computer readable medium;

means for generating a result table in the computer readable medium including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause, wherein the result table includes, for each row, a location identifier of the corresponding row in the base table

means for maintaining a cursor row pointer that points to a current row in the result table;

means for receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

means for determining a relative distance from the current row to the target row; and

means for performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row;

means for determining whether the target row is available to return;

means for returning the target row to the application program if the target row is available to return by;

(i) using the location identifier for the target row to access the row in the base table corresponding to the target row;

(ii) updating the target row in the result table with the accessed row in the base table; and

(iii) returning the data from the accessed row in the base table to the application program in response to the fetch request.

19. The system of claim 18, wherein the means for performing the fetch operation further performs:

using the location identifier for the target row to access the row in the base table corresponding to the target row; and

determining whether the accessed row in the base table qualifies according to the query predicate clause, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table has been updated.

20. A system for making data available to an application program, comprising:

a storage device including a base table;

a computer readable medium;

means for generating a result table in the computer readable medium including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

means for maintaining a cursor row pointer that points to a current row in the result table;

means for receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

means for determining a relative distance from the current row to the target row; and

means for performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row;

means for determining whether a delete flag for the target row indicates whether the corresponding row in the base table was deleted, wherein the target row in the result table is no longer available to return in response to the fetch request if the delete flag indicates that the corresponding row in the base table was deleted; and

means for returning the target row to the application program if the target row is available to return.

23. An article of manufacture including code for making data available to an application program by:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch absolute request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

determining a relative distance from the current row to the target row;

determining whether the current row is within a threshold distance from the target row; and

performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row to implement the fetch absolute request if the current row is within the threshold distance from the target row.

24. The article of manufacture of claim 23, further comprising:

if the distance between the current row and target row exceeds the threshold distance, then performing;

(i) repositioning the cursor row pointer to the first entry in the result table; and

(ii) moving the cursor row pointer from the first entry in the result table the absolute distance to the target row in the result table.

25. The article of manufacture of claim 23, wherein the threshold distance comprises a number of result table rows, wherein the current row is within the threshold distance from the target row if the number of rows between the current row pointer and the target row is less than the threshold distance number of rows.

26. The article of manufacture of claim 23, wherein the threshold distance comprises a number of sequential pages, wherein the current row is within the threshold distance from the target row if a number of sequential pages between a page including the current row and a page including the target row is less than the threshold distance number of pages.

27. The article of manufacture of claim 23, wherein performing the fetch operation further comprises:

determining whether the target row is available to return; and

returning the target row to the application program if the target row is available to return.

32. The article of manufacture of claim 23, wherein the result table comprises a database table.

33. The article of manufacture of claim 23, wherein the absolute distance specified in the fetch request specifies movement of the cursor row pointer one or more rows preceding the current result table row addressed by the cursor row pointer.

28. An article of manufacture including code for making data available to an application program by:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

determining a relative distance from the current row to the target row;

performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row;

determining whether the target row is available to return;

returning the target row to the application program if the target row is available to return; and

returning a code to the application program in response to the fetch request indicating that the requested row in the result table is not available if the target row in the result table is no longer available to return in response to the fetch request.

29. An article of manufacture including code for making data available to an application program by:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause, wherein the result table includes, for each row, a location identifier of the corresponding row in the base table

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

determining a relative distance from the current row to the target row;

performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row;

determining whether the target row is available to return; and

returning the target row to the application program if the target row is available to return by;

(i) using the location identifier for the target row to access the row in the base table corresponding to the target row;

(ii) updating the target row in the result table with the accessed row in the base table; and

(iii) returning the data from the accessed row in the base table to the application program in response to the fetch request.

30. The article of manufacture of claim 29, wherein performing the fetch operation further comprises:

using the location identifier for the target row to access the row in the base table corresponding to the target row; and

determining whether the accessed row in the base table qualifies according to the query predicate clause, wherein the target row in the result table is no longer available to return in response to the fetch request if the corresponding row in the base table has been updated.

31. An article of manufacture including code for making data available to an application program by:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

maintaining a cursor row pointer that points to a current row in the result table;

receiving a fetch request from the application program to reposition the cursor row pointer to a target row in the result table that is at an absolute distance from a first entry in the result table and return the data at the target row to the application program;

determining a relative distance from the current row to the target row;

performing a fetch relative operation to move the cursor row pointer by the determined relative distance from the current row to the target row;

determining whether a delete flag for the target row indicates whether the corresponding row in the base table was delete wherein the target row in the result table is no longer available to return in response to the fetch request if the delete flag indicates that the corresponding row in the base table was delete; and

returning the target row to the application program if the target row is available to return.