Dropping columns from a table with minimized unavailability

#Users of relational databases have existing tables of data that contain obsolete columns. The dropping of a column from a table encompasses two main parts. The first part is the modification of the metadata, i.e., catalog definition, associated with the table's schema definition, and the second part is the reorganization of the table's data to remove the dropped column data. In existing approaches, the table is placed in a restrictive state for the reorganization, which means that the data is unavailable between the time the metadata is modified to reflect the dropped column and the time that the table data reorganization is completed. For tables with substantial amounts of data, the process of reorganizing may result in an unacceptable outage.

#According to one embodiment of the present invention, a method drops columns from a table, where the columns in the table are each associated with a column number. The method executes a statement to drop a given column in the table and defers an application of the statement to drop the given column until a reorganization of a current data set comprising the table. The method performs the reorganization of the current data set to include the application of the statement to drop the given column. In performing the reorganization, the method updates column numbers for columns in each row of the table using a mapping data structure to remove the given column, loads the plurality of data rows with the updated column numbers into a shadow data set, applies to the shadow data set any changes to the table that are concurrent with the reorganization, updates a schema definition of the table with the updated column numbers, and switches the current data set to the shadow data set.

#In one aspect of the present invention, the switching of the current data set to the shadow data set further includes updating of column numbers stored in any dependent objects of remaining columns in the table and dropping any dependent objects associated with the given column.

#FIG. 1 illustrates a system for dropping columns from a table with data availability according to embodiments of the present invention.

#FIG. 2 is a flowchart illustrating a method for dropping columns from a table with data availability according to embodiments of the present invention.

#FIG. 3 illustrates an example of a correlation array as a mapping data structure according to embodiments of the present invention.

#As will be appreciated by one skilled in the art, aspects of the present invention may be embodied as a system, method or computer program product. Accordingly, aspects of the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment (including firmware, resident software, micro-code, etc.) or an embodiment combining software and hardware aspects that may all generally be referred to herein as a “circuit,” “module” or “system.” Furthermore, aspects of the present invention may take the form of a computer program product embodied in one or more computer readable medium(s) having computer readable program code embodied thereon.

#Any combination of one or more computer readable medium(s) may be utilized. The computer readable medium may be a computer readable signal medium or a computer readable storage medium. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. More specific examples (a non-exhaustive list) of the computer readable storage medium would include the following: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the context of this document, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.

#A computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device.