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

• US 6,598,041 B1
• Filed: 09/07/2000
• Issued: 07/22/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: Fuller, Georgia Carroll, Watts, Julie Ann, Kyu, William Y., Desai, Paramesh, Hoa, Peter, Ou, Jennifer Wha-Yuh, Katayama, Eric Naoto, Crone, Christopher John, Bernal, Margaret Ann, Shyam, Kalpana
• Primary Examiner(s): Mizrahi, Diane D.
• Assistant Examiner(s): Wu, Yicun
• Application Number:
  US09/656,558
• Time in Patent Office: 1,048 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 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 and a column for each column in a query select list;

receiving a fetch request to fetch a specified row in the result table;

determining whether to use the location identifier for the specified row to return data to the fetch request;

if the location identifier is determined to be used, then using the location identifier to access the row in the base table corresponding to the specified row in the result table to return requested data from the corresponding row in the base table to the fetch request; and

if the location identifier is determined to not be used, then returning data from the specified row in the result table to the fetch request.

2. The method of claim 1, wherein the query select list is provided from a structured language query (SQL) SELECT statement and the query predicate clause is provided from an SQL WHERE statement.

3. The method of claim 1, wherein generating the result table further comprises generating, for each row, a delete flag indicating whether the corresponding row in the base table was deleted.

4. The method of claim 1, wherein generating the result table further comprises generating, for each row, an update flag indicating whether the corresponding row in the base table no longer qualifies according to the query predicate clause as a result of an update to the data in the row in the base table.

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

6. The method of claim 1, wherein generating the result table further comprises generating a column in the result table for each expression in the query select list comprising a mathematical operation performed with respect to one or more base table columns.

7. The method of claim 6, wherein generating the result table further comprises generating a column in the result table for each column included in an expression operation not included in the query select list, whereby the generated column includes values used in the expression operation.

8. The method of claim 1, wherein generating the result table further comprises generating:

a first control block for the column in the result table including the location identifier of the corresponding row in the base table, wherein the first control block indicates that the location identifier column is not involved in a compare operation when accessing data in the result table and that the column does not include data corresponding to data in a column in the corresponding row in the base table; and

an additional control block for each base table column in the query select list indicating that the column is involved in the compare operation when accessing data in the result table and the corresponding column number in the base table; and

further comprising using the control blocks to construct the result table.

9. The method of claim 8, wherein generating the result table further comprises generating for each column in the result table:

an additional control block for each expression in the query select list that comprises an operation performed with respect to one or more columns in the base table indicating that the column is involved in a compare operation when accessing data in the result table and that the column does not directly correspond to one column in the base table.

10. The method of claim 1, further comprising generating a database INSERT command to insert data from columns in base table rows that satisfy the query predicate clause into corresponding columns in result table rows.

11. The method of claim 10, wherein generating the database INSERT command further comprises:

generating an insert list including an entry for each column in the query select list;

generating an insert statement to insert data from the base table columns listed in the query select list into the corresponding result table columns in the insert list;

generating the query predicate clause into the insert statement; and

executing the insert statement to insert data from the base table columns in the query select list into the corresponding result table columns in the insert list, wherein data is inserted from those base table rows having data that satisfies the query predicate clause in the insert statement.

12. The method of claim 10, wherein generating the database INSERT command further comprises:

generating a code into the insert statement to cause a return of the location identifier of the base table row when inserting base table rows into the result table, wherein the returned location identifier is inserted in the location identifier column in the result table.

13. The method of claim 11, wherein the code comprises including an entry for column zero in the insert list.

14. The method of claim 10, wherein generating the database INSERT command further comprises:

generating a code into the insert statement to generate a number indicating an order of an entry in the result table.

15. The method of claim 10, wherein the INSERT command is generated in response to an OPEN cursor command to open a scrollable static cursor.

16. 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.

17. The method of claim 1, wherein the result table is accessed by a static scrollable cursor, wherein updates to rows in the result table are reflected in the corresponding base table rows and wherein updates to the base table rows are considered when accessing the result table and reflected in the result table.

61. The method of claim 1, wherein the location identifier is used to return data from the corresponding row in the base table if the fetch request indicates fetch sensitive and wherein the data is returned from the specified row in the result table if the fetch request indicates fetch insensitive.

62. The method of claim 1, wherein the result table is generated in response to a scrollable cursor database command.

63. The method of claim 1, wherein the location identifier is used to return data from the corresponding row in the base table if the fetch request indicates to return data including changes to the corresponding row in the base table and wherein the data is returned from the specified row in the result table if the fetch request indicates to return data that does not include changes to the corresponding row from the base table.

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

means for 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 and a column for each column in a query select list;

means for receiving a fetch request to fetch a specified row in the result table;

means for determining whether to use the location identifier for the specified row to return data to the fetch request;

means for using, if the location identifier is determined to be used, the location identifier to access the row in the base table corresponding to the specified row in the result table to return requested data from the corresponding row in the base table to the fetch request; and

means for returning, if the location identifier is determined to not be used, data from the specified row in the result table to the fetch request.

19. The system of claim 18, wherein the query select list is provided from a structured language query (SQL) SELECT statement and the query predicate clause is provided from an SQL WHERE statement.

20. The system of claim 18, wherein the means for generating the result table further comprises means for generating, for each row, a delete flag indicating whether the corresponding row in the base table was deleted.

21. The system of claim 18, wherein the means for generating the result table further comprises means for generating, for each row, an update flag indicating whether the corresponding row in the base table no longer qualifies according to the query predicate clause as a result of an update to the data in the row in the base table.

22. The system of claim 18, wherein the result table comprises a database table.

23. The system of claim 18, wherein the means for generating the result table further comprises means for generating a column in the result table for each expression in the query select list comprising a mathematical operation performed with respect to one or more base table columns.

24. The system of claim 23, wherein the means for generating the result table further comprises means for generating a column in the result table for each column included in an expression operation not included in the query select list, whereby the generated column includes values used in the expression operation.

25. The system of claim 18, wherein the means for generating the result table further comprises means for generating:

a first control block for the column in the result table including the location identifier of the corresponding row in the base table, wherein the first control block indicates that the location identifier column is not involved in a compare operation when accessing data in the result table and that the column does not include data corresponding to data in a column in the corresponding row in the base table; and

an additional control block for each base table column in the query select list indicating that the column is involved in the compare operation when accessing data in the result table and the corresponding column number in the base table; and

further comprising using the control blocks to construct the result table.

26. The system of claim 25, wherein the means for generating the result table further comprises means for generating for each column in the result table:

an additional control block for each expression in the query select list that comprises an operation performed with respect to one or more columns in the base table indicating that the column is involved in a compare operation when accessing data in the result table and that the column does not directly correspond to one column in the base table.

27. The system of claim 18, further comprising means for generating a database INSERT command to insert data from columns in base table rows that satisfy the query predicate clause into corresponding columns in result table rows.

28. The system of claim 27, wherein the means for generating the database INSERT command further comprises:

means for generating an insert list including an entry for each column in the query select list;

means for generating an insert statement to insert data from the base table columns listed in the query select list into the corresponding result table columns in the insert list;

means for generating the query predicate clause into the insert statement; and

means for executing the insert statement to insert data from the base table columns in the query select list into the corresponding result table columns in the insert list, wherein data is inserted from those base table rows having data that satisfies the query predicate clause in the insert statement.

29. The system of claim 27, wherein the means for generating the database INSERT command further comprises:

means for generating a code into the insert statement to cause a return of the location identifier of the base table row when inserting base table rows into the result table, wherein the returned location identifier is inserted in the location identifier column in the result table.

30. The system of claim 29, wherein the code comprises including an entry for column zero in the insert list.

31. The system of claim 27, wherein the INSERT command is generated in response to an OPEN cursor command to open a scrollable static cursor.

32. The system of claim 27, wherein the means for generating the database INSERT command further comprises:

generating a code into the insert statement to generate a number indicating an order of an entry in the result table.

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

34. The system of claim 18, wherein the result table is accessed by a static scrollable cursor, wherein updates to rows in the result table are reflected in the corresponding base table rows and wherein updates to the base table rows are considered when accessing the result table and reflected in the result table.

65. The system of claim 18, wherein the location identifier is used to return data from the corresponding row in the base table if the fetch request indicates fetch sensitive and wherein the data is returned from the specified row in the result table if the fetch request indicates fetch insensitive.

66. The system of claim 18, wherein the result table is generated in response to a scrollable cursor database command.

67. The system of claim 18, wherein the location identifier is used to return data from the corresponding row in the base table if the fetch request indicates to return data including changes to the corresponding row in the base table and wherein the data is returned from the specified row in the result table if the fetch request indicates to return data that does not include changes to the corresponding row from the base table.

35. A program for making data available to an application program, wherein the program comprises code embedded in a computer readable medium capable of causing a computer to perform:

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 and a column for each column in a query select list;

receiving a fetch request to fetch a specified row in the result table;

determining whether to use the location identifier for the specified row to return data to the fetch request;

if the location identifier is determined to be used, then using the location identifier to access the row in the base table corresponding to the specified row in the result table to return requested data from the corresponding row in the base table to the fetch request; and

if the location identifier is determined to not be used, then returning data from the specified row in the result table to the fetch request.

36. The program of claim 35, wherein the query select list is provided from a structured language query (SQL) SELECT statement and the query predicate clause is provided from an SQL WHERE statement.

37. The program of claim 35, wherein generating the result table further comprises generating, for each row, a delete flag indicating whether the corresponding row in the base table was deleted.

38. The program of claim 35, wherein generating the result table further comprises generating, for each row, an update flag indicating whether the corresponding row in the base table no longer qualifies according to the query predicate clause as a result of an update to the data in the row in the base table.

39. The program of claim 35, wherein the result table comprises a database table.

40. The program of claim 35, wherein generating the result table further comprises generating a column in the result table for each expression in the query select list comprising a mathematical operation performed with respect to one or more base table columns.

41. The program of claim 40, wherein generating the result table further comprises generating a column in the result table for each column included in an expression operation not included in the query select list, whereby the generated column includes values used in the expression operation.

42. The program of claim 35, wherein generating the result table further comprises generating:

a first control block for the column in the result table including the location identifier of the corresponding row in the base table, wherein the first control block indicates that the location identifier column is not involved in a compare operation when accessing data in the result table and that the column does not include data corresponding to data in a column in the corresponding row in the base table; and

an additional control block for each base table column in the query select list indicating that the column is involved in the compare operation when accessing data in the result table and the corresponding column number in the base table; and

further comprising using the control blocks to construct the result table.

43. The program of claim 42, wherein generating the result table further comprises generating for each column in the result table:

an additional control block for each expression in the query select list that comprises an operation performed with respect to one or more columns in the base table indicating that the column is involved in a compare operation when accessing data in the result table and that the column does not directly correspond to one column in the base table.

44. The program of claim 42, further comprising generating a database INSERT command to insert data from columns in base table rows that satisfy the query predicate clause into corresponding columns in result table rows.

45. The program of claim 44, wherein generating the database INSERT command further comprises:

generating an insert list including an entry for each column in the query select list;

generating an insert statement to insert data from the base table columns listed in the query select list into the corresponding result table columns in the insert list;

generating the query predicate clause into the insert statement; and

executing the insert statement to insert data from the base table columns in the query select list into the corresponding result table columns in the insert list, wherein data is inserted from those base table rows having data that satisfies the query predicate clause in the insert statement.

46. The program of claim 44, wherein generating the database INSERT command further comprises:

generating a code into the insert statement to cause a return of the location identifier of the base table row when inserting base table rows into the result table, wherein the returned location identifier is inserted in the location identifier column in the result table.

47. The program of claim 45, wherein the code comprises including an entry for column zero in the insert list.

48. The program of claim 44, wherein generating the database INSERT command further comprises:

generating a code into the insert statement to generate a number indicating an order of an entry in the result table.

49. The program of claim 44, wherein the INSERT command is generated in response to an OPEN cursor command to open a scrollable static cursor.

50. The program of claim 42, wherein the location identifier identifies a page location including the record and a unique identifier of the record in the page.

51. The program of claim 42, wherein the result table is accessed by a static scrollable cursor, wherein updates to rows in the result table are reflected in the corresponding base table rows and wherein updates to the base table rows are considered when accessing the result table and reflected in the result table.

69. The program of claim 35, wherein the location identifier is used to return data from the corresponding row in the base table if the fetch request indicates fetch sensitive and wherein the data is returned from the specified row in the result table if the fetch request indicates fetch insensitive.

70. The program of claim 35, wherein the result table is generated in response to a scrollable cursor database command.

71. The program of claim 35, wherein the location identifier is used to return data from the corresponding row in the base table if the fetch request indicates to return data including changes to the corresponding row in the base table and wherein the data is returned from the specified row in the result table if the fetch request indicates to return data that does not include changes to the corresponding row from the base table.

52. A computer readable medium accessible to a computer database program, comprising:

a base database table; and

a result table 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 row in the base table and a column for each column in a query select list, wherein, in response to a fetch request, if the location identifier is determined to be used, then the location identifier is used to access the row in the base table corresponding to the specified row in the result table to return requested data from the base table to the fetch request or if the location identifier is determined to not be used, then data is returned from the specified row in the result table to the fetch request.

53. The computer readable medium of claim 52, wherein the result table further includes, for each row, a delete flag indicating whether the corresponding row in the base table was deleted.

54. The computer readable medium of claim 52, wherein the result table further includes, for each row, an update flag indicating whether the corresponding row in the base table no longer qualifies according to the query predicate clause as a result of an update to the data in the row in the base table.

55. The computer readable medium of claim 52, wherein the result table comprises a database table.

56. The computer readable medium of claim 52, wherein the result table further includes a column for each expression in the query select list comprising a mathematical operation performed with respect to one or more base table columns.

57. The computer readable medium of claim 56, wherein the result table further comprises a column for each column included in an expression operation not included in the query select list, whereby the generated column includes values used in the expression operation.

58. The computer readable medium of claim 52, further comprising:

a first control block for the column in the result table including the location identifier of the corresponding row in the base table, wherein the first control block indicates that the location identifier column is not involved in a compare operation when accessing data in the result table and that the column does not include data corresponding to data in a column in the corresponding row in the base table; and

an additional control block for each base table column in the query select list indicating that the column is involved in the compare operation when accessing data in the result table and the corresponding column number in the base table, wherein the control blocks are used to construct the result table.

59. The computer readable medium of claim 58, further comprising:

an additional control block for each expression in the query select list that comprises an operation performed with respect to one or more columns in the base table indicating that the column is involved in a compare operation when accessing data in the result table and that the column does not directly correspond to one column in the base table.

60. The computer readable medium of claim 52, wherein the location identifier identifies a page location including the record and a unique identifier of the record in the page.

73. The computer readable medium of claim 52, wherein the location identifier is used to return data from the corresponding row in the base table if the fetch request indicates fetch sensitive and wherein the data is returned from the specified row in the result table if the fetch request indicates fetch insensitive.

74. The computer readable medium of claim 52, wherein the result table is generated in response to a scrollable cursor database command.

75. The computer readable medium of claim 52, wherein the location identifier is used to return data from the corresponding row in the base table if the fetch request indicates to return data including changes to the corresponding row in the base table and wherein the data is returned from the specified row in the result table if the fetch request indicates to return data that does not include changes to the corresponding row from the base table.

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

generating a result table in response to a database scrollable cursor command 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 and a column for each column in a query select list;

receiving a fetch request to fetch a specified row in the result table, wherein the fetch requests indicates to fetch sensitive or insensitive;

if the fetch request indicates to fetch sensitive, then using the location identifier to access the row in the base table corresponding to the specified row in the result table to return requested data from the corresponding row in the base table to the fetch request; and

if the fetch requests indicates to fetch insensitive, then returning data from the specified row in the result table to the fetch request.

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

means for generating a result table in response to a database scrollable cursor command 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 and a column for each column in a query select list;

means for receiving a fetch request to fetch a specified row in the result table, wherein the fetch requests indicates to fetch sensitive or insensitive;

means for using, if the fetch request indicates to fetch sensitive, the location identifier to access the row in the base table corresponding to the specified row in the result table to return requested data from the corresponding row in the base table to the fetch request; and

means for returning data, if the fetch requests indicates to fetch insensitive, data from the specified row in the result table to the fetch request.

72. A program for making data available to an application program, wherein the program comprises code embedded in a computer readable medium capable of causing a computer to perform:

generating a result table in response to a database scrollable cursor command 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 and a column for each column in a query select list;

receiving a fetch request to fetch a specified row in the result table, wherein the fetch requests indicates to fetch sensitive or insensitive;

if the fetch request indicates to fetch sensitive, then using the location identifier to access the row in the base table corresponding to the specified row in the result table to return requested data from the corresponding row in the base table to the fetch request; and

if the fetch requests indicates to fetch insensitive, then returning data from the specified row in the result table to the fetch request.