Method, system, and program for using control data structures when performing operations with respect to a database

• US 6,604,097 B2
• Filed: 07/26/2001
• Issued: 08/05/2003
• Est. Priority Date: 09/07/2000
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Teng, James Zu-Chia, Turnbaugh, Stephen Walter, Shyam, Kalpana, Phoenix, San Yu, Watts, Julie Ann
• Primary Examiner(s): Mizrahi, Diane D.
• Assistant Examiner(s): Wu, Yicun
• Application Number:
  US09/915,867
• Publication Number:
  US 20020029210A1
• Time in Patent Office: 740 Days
• Field of Search: 707/1, 707/2, 707/3, 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 performing operations with respect to a database, comprising:

generating a result table for an open scrollable cursor including data from a subset of entries in a base table having data in one or more columns that satisfy a query predicate clause;

generating at least one control data structure associated with a plurality of base table entries and at least one scrollable cursor, wherein the result table for each scrollable cursor associated with the control data structure includes data from at least one of the base table entries associated with the control data structure;

receiving a request to delete one base table entry; and

if the base table entry to be deleted is associated with one control data structure, then indicating in the associated control data structure tat the base table entry was deleted.

2. The method of claim 1, wherein one scrollable cursor is associated with multiple control data structures if the result table entries include data from base table entries associated with the multiple control data structures.

3. The method of claim 1, wherein one control data structure is provided for each tablespace in the database, wherein each scrollable cursor associated with one control data structure has result table entries including data from entries maintained in the tablespace for which the control data structure is provided.

4. The method of claim 1, further comprising:

processing an open statement for one scrollable cursor, wherein the result table is generated in response to processing the open statement;

determining each control data structure associated with base table entries having data included in the generated result table; and

associating the scrollable cursor initiated by the open statement with each determined control data structure.

5. The method of claim 4, wherein associating the scrollable cursor with each determined control structure comprises adding an identifier of the scrollable cursor to each determined control structure.

6. The method of claim 4, wherein indicating that the base table entry was deleted comprises setting a flag associated with the deleted base table entry to indicate that the associated base table entry was deleted.

7. The method of claim 5, further comprising:

receiving a request to insert data at one target base table entry;

determining at least one control data structure associated with the target base table entry; and

if one determined control data structure indicates that the target base table entry was deleted, then inserting the data at another base table entry.

8. The method of claim 7, further comprising:

inserting the data at the target base table entry if the target base table entry is not associated with one control data structure indicating that the target base table entry was deleted.

9. The method of claim 7, further comprising:

determining whether one open scrollable cursor is associated with one determined control data structure that is associated with the target base table entry; and

inserting the data at the target base table entry if there is no open scrollable cursor associated with one determined control data structure.

10. The method of claim 9, wherein the data is inserted at another base table entry if there is one open scrollable cursor associated with one control data structure associated with the target base table entry and if the control data structure indicates that the target base table entry was deleted.

13. The method of claim 1, wherein the base table entry is identified by a row identifier (RID).

14. The method of claim 1, wherein using the control data structures prevents one scrollable cursor from performing an operation on one result table entry corresponding to one base table entry that was deleted.

11. The method of claim wherein the data is inserted at the target base table entry if there is no open scrollable cursor associated with one determined control structure associated with the target base table entry and if one control data structure indicates that the target base table entry is deleted.

12. The method of claim 11, further comprising:

indicating that the target base table entry is not deleted after inserting the data at the target base table entry.

15. A system for performing operations with respect to a database, comprising:

a storage device including a base table;

a computer readable medium;

means for generating a result table in the computer readable medium for an open scrollable cursor including data from a subset of entries in a base table having data in one or more columns tat satisfy a query predicate clause;

means for generating at least one control data structure in the computer readable medium associated with a plurality of base table entries and at least on scrollable cursor, wherein the result table for each scrollable cursor associated with the control data structure includes data from at least one of the base table entries associated with the control data structure;

means for receiving a request to delete one base table entry; and

means for indicating in the associated control data structure that the base table entry was deleted if the base table entry to be deleted is associated with one control data structure.

16. The system of claim 15, wherein one scrollable cursor is associated with multiple control data structures if the result table entries include data from base table entries associated with the multiple control data structures.

17. The system of claim 15, wherein one control data structure is provided for each tablespace in the database, wherein each scrollable cursor associated with one control data structure has result table entries including data from entries maintained in the tablespace for which the control data structure is provided.

18. The system of claim 15, further comprising:

means for processing an open statement for one scrollable cursor, wherein the result table is generated in response to processing the open statement;

means for determining each control data structure associated with base table entries having data included in the generated result table; and

means for associating the scrollable cursor initiated by the open statement with each determined control data structure.

19. The system of claim 18, wherein the means for associating the scrollable cursor with each determined control structure adds an identifier of the scrollable cursor to each determined control structure.

20. The system of claim 19, wherein the means for indicating that the base table entry was deleted sets a flag associated with the deleted base table entry to indicate that the associated base table entry was deleted.

21. The system of claim 19, further comprising:

means for receiving a request to insert data at one target base table entry;

means for determining at least one control data structure associated with the target base table entry; and

means for inserting the data at another base table entry if one determined control data structure indicates that the target base table entry was deleted.

22. The system of claim 21, further comprising:

means for inserting the data at the target base table entry if the target base table entry is not associated with one control data structure indicating that the target base table entry was deleted.

23. The system of claim 21, further comprising:

means for determining whether one open scrollable cursor is associated with one determined control data structure that is associated with the target base table entry; and

means for inserting the data at the target base table entry if there is no open scrollable cursor associated with one determined control data structure.

24. The system of claim 23, wherein the data is inserted at another base table entry if there is one open scrollable cursor associated with one control data structure associated with the target base table entry and if the control data structure indicates that the target base table entry was deleted.

25. The system of claim 23, wherein the data is inserted at the target base table entry if there is no open scrollable cursor associated with one determined control structure associated with the target base table entry and if one control data structure indicates that the target base table entry is deleted.

26. The system of claim 25, further comprising:

means for indicating that the target base table entry is not deleted after inserting the data at the target base table entry.

27. The system of claim 15, wherein the base table entry is identified by a row identifier (RID) in the control data structure.

28. The system of claim 15, wherein using the control data structures prevents one scrollable cursor from performing an operation on one result table entry corresponding to one base table entry that was deleted.

29. An article of manufacture for performing operations with respect to a database by:

generating a result table for an open scrollable cursor including data from a subset of entries in a base table having data in one or more columns that satisfy a query predicate clause;

generating at least on control data structure associated with a plurality of base table entries and at least on scrollable cursor, wherein the result table for each scrollable cursor associated with the control data structure includes data from at least one of the base table entries associated with the control data structure;

receiving a request to delete one base table entry; and

if the base table entry to be deleted is associated with one control data structure, then indicating in the associated control data structure that the base table entry was deleted.

30. The article of manufacture of claim 29, wherein one scrollable cursor is associated with multiple control data structures if the result table entries include data from base table entries associated with the multiple control data structures.

31. The article of manufacture of claim 29, wherein one control data structure is provided for each tablespace in the database, wherein each scrollable cursor associated with one control data structure has result table entries including data from entries maintained in the tablespace for which the control data structure is provided.

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

processing an open statement for one scrollable cursor, wherein the result table is generated in response to processing the open statement;

determining each control data structure associated with base table entries having data included in the generated result table; and

associating the scrollable cursor initiated by the open statement with each determined control data structure.

33. The article of manufacture of claim 32 wherein associating the scrollable cursor with each determined control structure comprises adding an identifier of the scrollable cursor to each determined control structure.

34. The article of manufacture of claim 33, wherein indicating that the base table entry was deleted comprises setting a flag associated with the deleted base table entry to indicate that the associated base table entry was deleted.

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

receiving a request to insert data at one target base table entry;

determining at least one control data structure associated with the target base table entry; and

if one determined control data structure indicates that the target base table entry was deleted, then inserting the data at another base table entry.

36. The article of manufacture of claim 35, further comprising:

inserting the data at the target base table entry if the target base table entry is not associated with one control data structure indicating that the target base table entry was deleted.

37. The article of manufacture of claim 35, further comprising:

determining whether one open scrollable cursor is associated with one determined control data structure that is associated with the target base table entry; and

inserting the data at the target base table entry if there is no open scrollable cursor associated with one determined control data structure.

38. The article of manufacture of claim 37, wherein the data is inserted at another base table entry if there is one open scrollable cursor associated with one control data structure associated with the target base table entry and if the control data structure indicates that the target base table entry was deleted.

39. The article of manufacture of claim 37 wherein the data is inserted at the target base table entry if there is no open scrollable cursor associated with one determined control structure associated with the target base table entry and if one control data structure indicates that the target base table entry is deleted.

40. The article of manufacture of claim 39, further comprising:

indicating that the target base table entry is not deleted after inserting the data at the target base table entry.

41. The article of manufacture of claim 29, wherein the base table entry is identified by a row identifier (RID).

42. The article of manufacture of claim 29, wherein using the control data structures prevents one scrollable cursor from performing an operation on one result table entry corresponding to one base table entry that was deleted.