Method, system, and program for processing modifications to data in tables in a database system

• US 6,694,305 B2
• Filed: 07/26/2001
• Issued: 02/17/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: Crone, Christopher John, Bernal, Margaret Ann, Watts, Julie Ann, Shyam, Kalpana, Phoenix, San Yu, Teng, James Zu-Chia
• Primary Examiner(s): Mizrahi, Diane D.
• Assistant Examiner(s): Wu, Yicun
• Application Number:
  US09/915,866
• Publication Number:
  US 20020029209A1
• Time in Patent Office: 936 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: 707/690
• 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 processing modifications to data in a database, comprising:

generating a result table in the database including rows corresponding to a subset of rows in a base table in the database 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;

receiving a request to modify one target row in the result table;

determining whether one corresponding row in the base table corresponding to the target row in the result table has been modified by;

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

(ii) determining whether the data in the columns in the query select list of the accessed row from the base table satisfy the query predicate clause, wherein the requested modification to the target row in the result table is not performed if the accessed row from the base table does not satisfy the query predicate clause; and

determining whether to perform the requested modification to the target row based on whether the corresponding row in the base table was modified.

2. The method of claim 1, wherein the requested modification may comprise one of a request to update or delete the target row from the result table.

3. The method of claim 1, wherein determining whether the corresponding row in the base table has been modified further comprises:

determining whether the corresponding row in the base table was deleted, wherein the requested modification to the target row in the result table is not performed if the corresponding row in the base table was deleted.

4. The method of claim 3, wherein determining whether the corresponding row in the base table was deleted comprises determining whether a delete flag for the target row in the result table indicates whether the corresponding row in the base table was deleted.

5. The method of claim 1, further comprising:

setting an update flag for the target row in the result table if the accessed row from the base table does not satisfy the query predicate clause.

6. The method of claim 5, wherein determining whether the corresponding row in the base table has been modified further comprises:

determining whether the update flag for the target row indicates that the corresponding base table row has been updated, wherein the requested modification is not performed if the update flag indicates that the base table row has been updated and the requested modification comprises a request to delete the target row from the result table.

7. The method of claim 6, wherein each row in the result table further includes a delete flag indicating whether the corresponding row in the base table has been deleted, wherein the requested modification to the target row is not performed if the delete flag indicates that the corresponding row in the base table was deleted.

8. The method of claim 1, wherein determining whether the corresponding row in the base table has been modified further comprises:

determining whether the target row of the result table and corresponding row in the base table are different, wherein the requested modification to the target row is not performed if the target row and corresponding row are different.

9. The method of claim 8, wherein determining whether the corresponding row in the base table has been modified 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 from the base table satisfies the query predicate clause before determining whether the target row and corresponding row in the base table are different, wherein the requested modification to the target row in the result table is not performed if the accessed row from the base table has been modified.

10. The method of claim 1, wherein determining whether the corresponding row in the base table has been modified further comprises:

determining whether data in the corresponding row in the base table has changed, wherein the requested modification to the target row is not performed if the data in the corresponding row in the base table has changed.

11. The method of claim 1, wherein the query predicate clause comprises an SQL WHERE statement.

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

13. The method of claim 1, wherein the location identifier identifies a page location including the record and a unique identifier of the record in the page.

14. A system for processing modifications to data in a database, comprising:

a computer readable medium including the database;

a storage device;

a base table in the database stored in the storage device;

means for generating a result table in the database 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 of the corresponding row in the base table;

means for receiving a request to modify one target row in the result table;

means for determining whether one corresponding row in the base table corresponding to the target row in the result table has been modified by;

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

(ii) determining whether the data in the columns in the query select list of the accessed row from the base table satisfy the query predicate clause, wherein the requested modification to the target row in the result table is not performed if the accessed row from the base table does not satisfy the query predicate clause; and

means for determining whether to perform the requested modification to the target row based on whether the corresponding row in the base table was modified.

15. The system of claim 14, wherein the requested modification may comprise one of a request to update or delete the target row from the result table.

16. The system of claim 14, wherein the means for determining whether the corresponding row in the base table has been modified further performs:

determining whether the corresponding row in the base table was deleted, wherein the requested modification to the target row in the result table is not performed if the corresponding row in the base table was deleted.

17. The system of claim 16, wherein the means for determining whether the corresponding row in the base table was deleted determines whether a delete flag for the target row in the result table indicates whether the corresponding row in the base table was deleted.

18. The method of claim 14, further comprising:

means for setting an update flag for the target row in the result table if the accessed row from the base table does not satisfy the query predicate clause.

19. The system of claim 18, wherein the means for determining whether the corresponding row in the base table has been modified performs:

determining whether the update flag for the target row indicates that the corresponding base table row has been updated, wherein the requested modification is not performed if the update flag indicates that the base table row has been updated and the requested modification comprises a request to delete the target row from the result table.

20. The system of claim 19, wherein each row in the result table further includes a delete flag indicating whether the corresponding row in the base table has been deleted, wherein the requested modification to the target row is not performed if the delete flag indicates that the corresponding row in the base table was deleted.

21. The system of claim 14, wherein the means for determining whether the corresponding row in the base table has been modified further performs:

determining whether the target row of the result table and corresponding row in the base table are different, wherein the requested modification to the target row is not performed if the target row and corresponding row are different.

22. The system of claim 21, wherein the means for determining whether the corresponding row in the base table has been modified 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 from the base table satisfies the query predicate clause before determining whether the target row and corresponding row in the base table are different, wherein the requested modification to the target row in the result table is not performed if the accessed row from the base table has been modified.

23. The system of claim 14, wherein the means for determining whether the corresponding row in the base table has been modified further performs:

determining whether data in the corresponding row in the base table has changed, wherein the requested modification to the target row is not performed if the data in the corresponding row in the base table has changed.

24. The system of claim 14, wherein the query predicate clause comprises an SQL WHERE statement.

25. The system of claim 14, wherein the result table comprises a database table.

26. The system of claim 14, wherein the location identifier identifies a page location including the record and a unique identifier of the record in the page.

27. An article of manufacture including code for processing modifications to in a database data by:

generating a result table in the database including rows corresponding to a subset of rows in a base table in the database 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;

receiving a request to modify one target row in the result table;

determining whether one corresponding row in the base table corresponding to the target row in the result table has been modified by;

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

(ii) determining whether the data in the columns in the query select list of the accessed row from the base table satisfy the query predicate clause, wherein the requested modification to the target row in the result table is not performed if the accessed row from the base table does not satisfy the query predicate clause; and

determining whether to perform the requested modification to the target row based on whether the corresponding row in the base table was modified.

28. The article of manufacture of claim 27, wherein the requested modification may comprise one of a request to update or delete the target row from the result table.

29. The article of manufacture of claim 27, wherein determining whether the corresponding row in the base table has been modified further comprises:

determining whether the corresponding row in the base table was deleted, wherein the requested modification to the target row in the result table is not performed if the corresponding row in the base table was deleted.

30. The article of manufacture of claim 29, wherein determining whether the corresponding row in the base table was deleted comprises determining whether a delete flag for the target row in the result table indicates whether the corresponding row in the base table was deleted.

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

setting an update flag for the target row in the result table if the accessed row from the base table does not satisfy the query predicate clause.

32. The article of manufacture of claim 31, wherein determining whether the corresponding row in the base table has been modified further comprises:

determining whether the update flag for the target row indicates that the corresponding base table row has been updated, wherein the requested modification is not performed if the update flag indicates that the base table row has been updated and the requested modification comprises a request to delete the target row from the result table.

33. The article of manufacture of claim 32, wherein each row in the result table further includes a delete flag indicating whether the corresponding row in the base table has been deleted, wherein the requested modification to the target row is not performed if the delete flag indicates that the corresponding row in the base table was deleted.

34. The article of manufacture of claim 27, wherein determining whether the corresponding row in the base table has been modified further comprises:

determining whether the target row of the result table and corresponding row in the base table are different, wherein the requested modification to the target row is not performed if the target row and corresponding row are different.

35. The article of manufacture of claim 34, wherein determining whether the corresponding row in the base table has been modified 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 from the base table satisfies the query predicate clause before determining whether the target row and corresponding row in the base table are different, wherein the requested modification to the target row in the result table is not performed if the accessed row from the base table has been modified.

36. The article of manufacture of claim 27, wherein determining whether the corresponding row in the base table has been modified further comprises:

determining whether data in the corresponding row in the base table has changed, wherein the requested modification to the target row is not performed if the data in the corresponding row in the base table has changed.

37. The article of manufacture of claim 27, wherein the query predicate clause comprises an SQL WHERE statement.

38. The article of manufacture of claim 27, wherein the result table comprises a database table.

39. The article of manufacture of claim 27, wherein the location identifier identifies a page location including the record and a unique identifier of the record in the page.