Method and system for preserving an original table schema

• US 20050091233A1
• Filed: 10/24/2003
• Published: 04/28/2005
• Est. Priority Date: 10/24/2003
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Watts, Julie A., Teng, James Z., Friske, Craig A., Ruddy, James A., Liu, Regina J.
• Granted Patent: US 7,174,353 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 11/1435 using file system or storage system metadata
• CPC Class Codes: G06F 16/213 with details for schema evolution support
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99954 Version management

Abstract

The present invention is related to a method and system for preserving an original table schema for a table in a database system that supports dynamic table schema changes. The method and system includes storing the original table schema for the table in a designated table prior to performing a schema change on the table. By storing the original table schema in the designated table, a backup copy of the table that includes rows which are not self-describing can be used for data recovery.

Claims

1. A computer readable medium containing program instructions for preserving an original table schema for a table in a database system that supports dynamic table schema changes, the program instructions for:

a) storing the original table schema for the table in a designated table prior to performing a schema change on the table.

2. The computer readable medium of claim 1, wherein the designated table is a catalog table.

3. The computer readable medium of claim 1 further including:

b) initiating a first schema change to the table before storing instruction (a); and

c) completing the first schema change to the table after the original table schema has been stored in the designated table.

4. The computer readable medium of claim 3 further including:

d) inserting a new self-describing row in the table, wherein a table definition defined by a most recent schema change is stored in metadata associated with the new self-describing row in the table.

5. The computer readable medium of claim 4 further including:

e) updating a row in the table; and

f) converting the updated row into a self-describing row, wherein the table definition defined by a most recent schema change is stored in metadata associated with the updated row in the table.

6. The computer readable medium of claim 5 further including:

g) rebuilding the table during a data recovery process by;

g1) utilizing a valid backup copy of the table;

g2) applying the original table schema stored in the designated table to a row in the valid backup copy of the table if the row is not a self-describing row; and

g3) applying the table definition stored in the metadata associated with the row if the row is a self-describing row.

7. The computer readable medium of claim 3 further comprising:

d) removing from the designated table the original table schema for the table if each row in the table is self-describing.

8. A method for preserving an original table schema for a table in a database system that supports dynamic table schema changes comprising:

a) storing the original table schema for the table in a designated table prior to performing a schema change on the table.

9. The method of claim 8, wherein the designated table is a catalog table.

10. The method of claim 8 further including:

b) initiating a first schema change to the table before storing step (a); and

c) completing the first schema change to the table after the original table schema has been stored in the designated table.

11. The method of claim 10 further including:

d) inserting a new self-describing row in the table, wherein a table definition defined by a most recent schema change is stored in metadata associated with the new self-describing row in the table.

12. The method of claim 11 further including:

e) updating a row in the table; and

f) converting the updated row into a self-describing row, wherein the table definition defined by a most recent schema change is stored in metadata associated with the updated row in the table.

13. The method of claim 12 further including:

g) rebuilding the table during a data recovery process by;

g1) utilizing a valid backup copy of the table;

g2) applying the original table schema stored in the designated table to a row in the valid backup copy of the table if the row is not a self-describing row; and

g3) applying the table definition stored in the metadata associated with the row if the row is a self-describing row.

14. The method of claim 10 further comprising:

d) removing from the designated table the original table schema for the table if each row in the table is self-describing.

15. A system for preserving an original table schema for a table in a database system that supports dynamic table schema changes, the system comprising:

a dynamic schema change mechanism in the database system for performing dynamic table schema changes; and

a designated table in the database system, wherein the original table schema for the table is stored in the designated table before the dynamic schema change mechanism performs a schema change on the table.

16. The system of claim 15, wherein the designated table is stored in a catalog in the database system.

17. The system of claim 15, wherein the dynamic schema change mechanism is configured to convert a row into a self-describing row when the row is updated, wherein the table definition defined by a most recent schema change is stored in metadata associated with the updated row in the table.

18. The system of claim 17, wherein the dynamic schema change mechanism is configured to insert a new self-describing row in the table, wherein a table definition defined by a most recent schema change is stored in metadata associated with the new self-describing row in the table.

19. The system of claim 18, wherein the database system is configured to rebuild the table during a data recovery process by utilizing a valid backup copy of the table and applying the original table schema stored in the designated table to a row in the valid backup copy of the table if the row is not a self-describing row.

20. The system of claim 15, wherein the database system is configured to remove from the designated table the original table schema for the table if each row in the table is self-describing.