RE: Altering DBSPACE

You can move your tables to a new space with the "ALTER FRAGMENT ON TABLE <table-name> INIT IN <dbspace-name>" command. However, this command will not change the "default" dbspace for your database. Nor will it move the related system tables. If your "dbadmin" requires that these be moved, you will indeed need to export all data, drop the database, and reimport it. Rick Bernstein -----Original Message----- From: Raymond Michalek [mailto:rfm@tpso.com] Sent: Sunday, May 28, 2000 7:38 AM To: informix-list@iiug.org Subject: Altering DBSPACE Hello group, We have created all of our tables in dbspace "st_ux01_001". Now the "dbadmin" want to move all of our tables to dbspace "st_ux01_002". In Informix dokus we didn't find any command to alter a dbspace. The only way we know to alter the dbspace is, to export all data an recreate the dbschema with the new dbspace and then reimport all data. Does anyone know an altarnative to this? Thanks for your answers Raymond

"Bernstein, Rick" wrote: > You can move your tables to a new space with the > "ALTER FRAGMENT ON TABLE <table-name> INIT IN <dbspace-name>" command. > > However, this command will not change the "default" dbspace > for your database. Nor will it move the related system tables. > If your "dbadmin" requires that these be moved, you will indeed > need to export all data, drop the database, and reimport it. > > Rick Bernstein A supposedly faster solution would be to create a new table <target> in the target dbspace (same layout as original table), use 'insert into <target> select * from <source>' to copy the table, drop the original table <source> and rename the new table to the original name. Indexes and other dependent objects would have to be recreated and you would have to exclusively lock the source table during the whole operation. Hope this helps, Heiko > > > -----Original Message----- > From: Raymond Michalek [mailto:rfm@tpso.com] > Sent: Sunday, May 28, 2000 7:38 AM > To: informix-list@iiug.org > Subject: Altering DBSPACE > > Hello group, > > We have created all of our tables in dbspace "st_ux01_001". > Now the "dbadmin" want to move all of our tables to dbspace > "st_ux01_002". > > In Informix dokus we didn't find any command to alter a dbspace. > > The only way we know to alter the dbspace is, to export all data > an recreate the dbschema with the new dbspace and then reimport all > data. > > Does anyone know an altarnative to this? > > Thanks for your answers > > Raymond