Method, system, and program for processing a fetch request for a target row in a table that precedes as current row

• US 6,754,653 B2
• Filed: 07/26/2001
• Issued: 06/22/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: Watts, Julie Ann, Bonner, Charles Roy, Shyam, Kalpana, Phoenix, San Yu
• Primary Examiner(s): Mizrahi, Diane D.
• Assistant Examiner(s): Wu, Yicun
• Application Number:
  US09/915,868
• Publication Number:
  US 20020029211A1
• Time in Patent Office: 1,062 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, 365/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 tat 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 precedes the current row in the result table and return the data at the target row to the application program;

scanning a current page including the current row to determine if the target row is in the current page, wherein a plurality of pages including result table rows are organized into segments, wherein the pages within each segment include sequential rows from the result table such that the rows between the pages within one segment are sequential;

if the current page does not include the target row, determining whether a previous page including result table rows preceding a first row in the current page includes the target row, wherein the previous page includes rows in the result table that sequentially precede the first row in the current page, and wherein if the current page is not a first page in a current segment including the current page, then the previous page comprises the page in the current segment including result table rows immediately preceding the result table rows in the current page;

repositioning the cursor row pointer to point to the target row in the result table, wherein the cursor row pointer is repositioned to point to the target row in the previous page if the target row is in the previous page; and

performing the fetch request at the target row addressed by the repositioned cursor row pointer.

2. The method of claim 1, further comprising:

repositioning the cursor row pointer to address the target row in the page including the target row.

3. The method of claim 1, further comprising:

setting the current page to the previous page if the previous page does not include the target row and performing another iteration of determining the previous page and determining whether the previous page includes the target row until the page including the target row is located.

4. The method of claim 1, further comprising:

if the current page is the first page in the current segment, then determining one segment including one page having result table rows immediately preceding the result table rows in the current page, wherein the previous page comprises the page in the determined segment having result table rows immediately preceding the result table rows in the current page in the current segment.

5. The method of claim 4, further comprising:

providing a segment map indicating an ordering of segments that provides the result table rows in sequential ordering;

wherein determining the segment including the page having result table rows immediately preceding the result table rows in the first page in the current segment further comprises determining one segment preceding the current segment according to the ordering indicated in the segment map.

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

7. The method of claim 1, wherein 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.

8. The method of claim 1, further comprising:

detecting fetch requests that are part of a backward sequential access of result table rows preceding the current result table row;

determining at least one additional page including result table rows that precedes a first row in the current page; and

staging into memory the determined at least one additional page to make the result table rows therein available to subsequent fetch requests that are part of the backward sequential access.

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, wherein the result table includes, for each row, a location identifier identifying 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 precedes the current row in the result table and return the data at the target row to the application program;

repositioning the cursor row pointer to point to the target row in the result table;

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

performing the fetch request at the target row addressed by the repositioned cursor row pointer if the target row is available to return, wherein the location identifier is used to access the row in the base table corresponding to the target row if the target row is available to return.

10. The method of claim 9, wherein performing the fetch request further comprises:

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 state of the corresponding row in the base table indicates that the target row is available to return.

11. The method of claim 9, wherein if the target row in the result table is available to return, then further performing:

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

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

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

12. The method of claim 9, wherein performing the fetch request 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.

13. 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 the 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 precedes the current row in the result table and return the data at the target row to the application program;

means for scanning a current page including the current row to determine if the target row is in the current page, wherein a plurality of pages including result table rows are organized into segments, wherein the pages within each segment include sequential rows from the result table such that the rows between the pages within one segment are sequential;

means for determining whether a previous page including result table rows preceding a first row in the current page includes the target row if the current page does not include the target row, wherein the previous page includes rows in the result table that sequentially precede the first row in the current page, and wherein if the current page is not a first page in a current segment including the current page, then the previous page comprises the page in the current segment including result table rows immediately preceding the result table rows in the current page;

means for repositioning the cursor row pointer to point the target row in the result table wherein the cursor row pointer is repositioned to point to the target row in the previous page if the target row is in the previous page; and

means for performing the fetch request at the target row addressed by the repositioned cursor row pointer.

14. The system of claim 13, further comprising:

means for repositioning the cursor row pointer to address the target row in the page including the target row.

15. The system of claim 13, further comprising:

means for setting the current page to the previous page if the previous page does not include the target row and performing another iteration of determining the previous page and determining whether the previous page includes the target row until the page including the target row is located.

16. The system of claim 13, wherein a plurality of pages including result table rows are organized into segments, wherein the pages within each segment include sequential rows from the result table such that the rows between the pages within one segment are sequential, and wherein the means for determining the previous page including rows in the result table sequentially preceding the current page performs:

if the current page is not a first page in a current segment including the current page, then the previous page comprises the page in the current segment including result table rows immediately preceding the result table rows in the current page.

17. The system of claim 16, further comprising:

means for determining one segment including one page having result table rows immediately preceding the result table rows in the current page if the current page is the first page in the current segment, wherein the previous page comprises the page in the determined segment having result table rows immediately preceding the result table rows in the current page in the current segment.

18. The system of claim 17, further comprising:

means for providing a segment map indicating an ordering of segments that provides the result table rows in sequential ordering;

wherein the means for determining the segment including the page having result table rows immediately preceding the result table rows in the first page in the current segment further performs determining one segment preceding the current segment according to the ordering indicated in the segment map.

19. The system of claim 13, wherein the result table comprises a database table.

20. The system of claim 13, wherein 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.

21. The system of claim 13, further comprising:

means for detecting fetch requests that are part of a backward sequential access of result table rows preceding the current result table row;

means for determining at least one additional page including result table rows that precedes a first row in the current page; and

means for staging into memory the determined at least one additional page to make the result table rows therein available to subsequent fetch requests that are part of the backward sequential access.

22. 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 the 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 identifying the corresponding raw 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 precedes the current row in the result table and return the data at the target row to the application program;

means for repositioning the cursor row pointer to point to the target row in the result table;

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 performing the fetch request at the target row addressed by the repositioned cursor row pointer if the target row is available to return, wherein the location identifier is used to access the row in the base table corresponding to the target row if the target row is available to return.

23. The system of claim 22, wherein the means for performing the fetch request further performs:

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 if the state of the corresponding row in the base table indicates that the target row is available to return.

24. The system claim 22, means for performing if the target row in the result table is available to return:

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

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

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

25. The system of claim 22, wherein the means for performing the fetch request 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.

26. 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 precedes the current row in the result table and return the data at the target row to the application program;

scanning a current page including the current row to determine if the target row is in the current page, wherein a plurality of pages including result table rows are organized into segments, wherein the pages within each segment include sequential rows from the result table such that the rows between the pages within one segment are sequential;

if the current page does not include the target row, determining whether a previous page including result table rows preceding a first row in the current page includes the target row, wherein the previous page includes rows in the result table that sequentially precede the first row in the current page, and wherein if the current page is not a first page in a current segment including the current page, then the previous page comprises the page in the current segment including result table rows immediately preceding the result table rows in the current page;

repositioning the cursor row pointer to point to the target row in the result table, wherein the cursor row pointer is repositioned to point to the target row in the previous page if the target row is in the previous page; and

performing the fetch request at the target row addressed by the repositioned cursor row pointer.

27. The article of manufacture of claim 26, further comprising:

repositioning the cursor row pointer to address the target row in the page including the target row.

28. The article of manufacture of claim 26, further comprising:

setting the current page to the previous page if the previous page does not include the target row and performing another iteration of determining the previous page and determining whether the previous page includes the target row until the page including the target row is located.

29. The article of manufacture of claim 26, further comprising:

if the current page is the first page in the current segment, then determining one segment including one page having result table rows immediately preceding the result table rows in the current page, wherein the previous page comprises the page in the determined segment having result table rows immediately preceding the result table rows in the current page in the current segment.

30. The article of manufacture of claim 29, further comprising:

providing a segment map indicating an ordering of segments that provides the result table rows in sequential ordering;

wherein determining the segment including the page having result table rows immediately preceding the result table rows in the first page in the current segment further comprises determining one segment preceding the current segment according to the ordering indicated in the segment map.

31. The article of manufacture of claim 26, wherein the result table comprises a database table.

32. The article of manufacture of claim 26, wherein 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.

33. The article of manufacture of claim 26, further comprising:

detecting fetch requests that are part of a backward sequential access of result table rows preceding the current result table row;

determining at least one additional page including result table rows that precedes a first row in the current page; and

staging into memory the determined at least one additional page to make the result table rows therein available to subsequent fetch requests that are part of the backward sequential access.

34. 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 identifying the corresponding row in the base table;

maintaining a cursor row pointer tat 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 precedes the current row in the result table and return the data at the target row to the application program;

repositioning the cursor row pointer to point to the target row in the result table;

determining whether a delete flag for the target row indicates whether the corresponding row in the base table was deleted, wherein the target tow 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 case table was deleted; and

performing the fetch request at the target row addressed by the repositioned cursor row pointer if the target row is available to return, wherein the location identifier is used to access the row in the case table corresponding to the target row if the target row is available to return.

35. The article of manufacture of claim 34, wherein performing the fetch request further comprises:

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 state of the corresponding row in the base table indicates that the target row is available to return.

36. The article of manufacture of claim 34, wherein if the target row in the result table is available to return, then further performing:

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

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

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

37. The article of manufacture of claim 34, wherein performing the fetch request 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.