Method and system for preserving an original table schema

• US 7,174,353 B2
• Filed: 10/24/2003
• Issued: 02/06/2007
• Est. Priority Date: 10/24/2003
• Status: Expired due to Fees

• 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.
• Primary Examiner(s): Ali; Mohammad
• Application Number:
  US10/693,184
• Publication Number:
  US 20050091233A1
• Time in Patent Office: 1,201 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-205
• US Class Current: 707/640
• 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 method for preserving an original schema of a table comprising a plurality of rows, the method comprising:

storing the original schema of the table in a designated table prior to performing a first schema change on the table, wherein the plurality of rows in the table are not self-describing prior to performance of the first schema change;

performing the first schema change on the table to create a new schema for the table;

defining each of one or more rows that are inserted into or updated in the table after performance of the first schema change using the new schema;

making each of the one or more inserted or updated rows a self-describing row by storing metadata describing the new schema in the row; and

rebuilding the table using a valid backup copy of the table,

wherein, when a row of the valid backup copy is self-describing, metadata stored in the row is used to rebuild a corresponding row of the table,

wherein, when the row of the valid backup copy is not self-describing, the original schema stored in the designated table is used to rebuild the corresponding row of the table, and

wherein a backup copy of the table is valid when data in the backup copy is not corrupted or otherwise unsuitable for data recovery.

2. The method of claim 1, further comprising:

removing the original schema of the table from the designated table responsive to all of the rows in the table being self describing.

3. The method of claim 1, wherein the designated table is a catalog table.

4. The method of claim 1, wherein having each row in the table defined by metadata stored in the row or by the original schema stored in the designated table allows a backup copy of the table that was created after performance of the first schema change and before insertion or update of the one or more rows to be used for data recovery.

5. A computer program product comprising a computer readable storage medium, the computer readable storage medium including a computer readable program for preserving an original schema of a table comprising a plurality of rows, wherein the computer readable program when executed on a computer causes the computer to:

store the original schema of the table in a designated table prior to performing a first

schema change on the table, wherein the plurality of rows in the table are not self-describing

prior to performance of the first schema change;

perform the first schema change on the table to create a new schema for the table;

define each of one or more rows that are inserted into or updated in the table after performance of the first schema change using the new schema;

make each of the one or more inserted or updated rows a self-describing row by storing metadata describing the new schema in the row; and

rebuild the table using a valid backup copy of the table,

wherein, when a row of the valid backup copy is self-describing, metadata stored in the row is used to rebuild a corresponding row of the table, and

wherein, when the row of the valid backup copy is not self-describing, the original schema stored in the designated table is used to rebuild the corresponding row of the table, and

wherein a backup copy of the table is valid when data in the backup copy is not corrupted or otherwise unsuitable for data recovery.

6. The computer program product of claim 5, wherein the computer readable program when executed on the computer further causes the computer to:

remove the original schema of the table from the designated table responsive to all of the rows in the table being self describing.

7. The computer program product of claim 5, wherein the designated table is a catalog table.

8. The computer program product of claim 5, wherein having each row in the table defined by metadata stored in the row or by the original schema stored in the designated table allows a backup copy of the table that was created after performance of the first schema change and before insertion or update of the one or more rows to be used for data recovery.

9. A system for preserving an original schema of a table comprising a plurality of rows, the system comprising:

a designated table operable to store the original schema of the table, the original schema being stored in the designated table prior to performance of a first schema change on the table,

wherein the plurality of rows in the table are not self-describing prior to performance of the first schema change;

a first mechanism operable to;

perform the first schema change on the table to create a new schema for the table,

define each of one or more rows that are inserted into or updated in the table after performance of the first schema change using the new schema, and

make each of the one or more inserted or updated rows a self-describing row by storing metadata describing the new schema in the row; and

a second mechanism operable to rebuild the table using a valid backup copy of the table,

wherein, when a row of the valid backup copy is self-describing, metadata stored in the row is used to rebuild a corresponding row of the table,

wherein, when the row of the valid backup copy is not self-describing, the original schema stored in the designated table is used to rebuild the corresponding row of the table, and

wherein a backup copy of the table is valid when data in the backup copy is not corrupted or otherwise unsuitable for data recovery.

10. The system of claim 9, further comprising:

a third mechanism operable to remove the original schema of the table from the designated table responsive to all of the rows in the table being self-describing.

11. The system of claim 9, wherein the designated table is a catalog table.

12. The system of claim 9, wherein having each row in the table defined by metadata stored in the row or by the original schema stored in the designated table allows a backup copy of the table that was created after performance of the first schema change and before insertion or update of the one or more rows to be used for data recovery.