ALTER TABLE copy
Long time listener, first time caller... We are running IDS 7.20. As I understand it, as part of adding columns to a table during an ALTER TABLE command, the IDS needs to make a copy of the existing table. My question to the group is where is this copy stored? Does IDS treat this copy as a temporary table and it's location can then be controlled via the DBSPACETEMP parameter? Thanks, Gary
I don't believe that any alteration to the tables are needed, it's just a DDL change. However, in cases where a table is physically changed, such as ALTER TABLE ... FRAGMENT INIT, the old table structure is retained in situ until the new one is written successfully, and the new one is written to the same dbspace (unless you've specified a different one). This is why it's essential that you have at least as much free space available in the destination dbspace as the table originally took. Gary Pace <gary.g.pace@boeing.com> wrote in message news:3A3A45E1.D6740E43@boeing.com... > Long time listener, first time caller... > > We are running IDS 7.20. As I understand it, as part of adding columns > to a table during an ALTER TABLE command, the IDS needs to make a copy > of the existing table. > > My question to the group is where is this copy stored? Does IDS treat > this copy as a temporary table and it's location can then be controlled > via the DBSPACETEMP parameter? > > Thanks, > > Gary
Gary Pace wrote: > > Long time listener, first time caller... > > We are running IDS 7.20. As I understand it, as part of adding columns > to a table during an ALTER TABLE command, the IDS needs to make a copy > of the existing table. > > My question to the group is where is this copy stored? Does IDS treat > this copy as a temporary table and it's location can then be controlled > via the DBSPACETEMP parameter? IDS 7.20 is pre-in-place-alter so yes ANY alter of the physical structure of a table (ie anything more than say removing NOT NULL or such) will require the entire table to be copied. What happens is that an unnamed table is created with the new structure and the data is moved from it's current table to the new one. Once complete the original table is dropped, the new one and its indexes are renamed and you are back in business. As Neil points out except for a ALTER FRAGMENT ... INIT IN ... to a new dbspace you will need enough free space in the table's current dbspace to hold the second copy temporarily. Art S. Kagel