best way to move user tables out of rootdbs

Hi everyone. I have a common problem here. Users have been creating tables in the rootdbs dataspace (IDS 9.4). I know there are numerous ways to move tables from dataspace A to dataspace B such as using onunload/onload or dbexport/dbimport or using dbaccess unload to file/load from file, dbload, etc. I know one must be careful when using dbaccess unload/load because of the possibility of creating long transactions upon the load part. I was thinking of either doing the following: A) dbaccess unload TABLE_A to unload_file B) TURN OFF LOGGING C) create tmp_table like TABLE_A d) dbaccess load TABLE_A from unload_file e) drop TABLE_A f) rename tmp_table to TABLE_A g) create indexes on TABLE_A h) create synoyms, triggers, as needed OR use onunload/onload Just wondering if anyone has an opinion as to why method 1 (using dbaccess unload/load) would have any potential problems and which method is better? IE: is using the onunload/onload better than using dbaccess unload/load? I know it is faster of course, which is an advantage. But aside from the speed advantage is there anything better about using one method over another? Thanks

Hi, I do not know what constraints you are under with regards to systems etc. and the sizes of the tables that you are trying to move, but have you considered using the alter fragment command? The syntax is ALTER FRAGMENT ON TABLE TABLE_A INIT IN <new db space>; Similarly for the indexes on the table ALTER FRAGMENT ON INDEX <indexname> INIT IN <new db space>; -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of WILL LANDSTROM Sent: 02 June 2008 02:52 PM To: ids@iiug.org Subject: best way to move user tables out of rootdbs [12271] Hi everyone. I have a common problem here. Users have been creating tables in the rootdbs dataspace (IDS 9.4). I know there are numerous ways to move tables from dataspace A to dataspace B such as using onunload/onload or dbexport/dbimport or using dbaccess unload to file/load from file, dbload, etc. I know one must be careful when using dbaccess unload/load because of the possibility of creating long transactions upon the load part. I was thinking of either doing the following: A) dbaccess unload TABLE_A to unload_file B) TURN OFF LOGGING C) create tmp_table like TABLE_A d) dbaccess load TABLE_A from unload_file e) drop TABLE_A f) rename tmp_table to TABLE_A g) create indexes on TABLE_A h) create synoyms, triggers, as needed OR use onunload/onload Just wondering if anyone has an opinion as to why method 1 (using dbaccess unload/load) would have any potential problems and which method is better? IE: is using the onunload/onload better than using dbaccess unload/load? I know it is faster of course, which is an advantage. But aside from the speed advantage is there anything better about using one method over another? Thanks **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Mark Tyrer said: > Hi, > > I do not know what constraints you are under with regards to systems etc. > and the sizes of the tables that you are trying to move, but have you > considered using the alter fragment command? > > The syntax is > > ALTER FRAGMENT ON TABLE TABLE_A INIT IN <new db space>;> > Similarly for the indexes on the table > > ALTER FRAGMENT ON INDEX <indexname> INIT IN <new db space>; What he said. All that other stuff is just too hard. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of WILL > LANDSTROM > Sent: 02 June 2008 02:52 PM > To: ids@iiug.org > Subject: best way to move user tables out of rootdbs [12271] > > Hi everyone. I have a common problem here. Users have been creating tables > in the rootdbs dataspace (IDS 9.4). I know there are numerous ways to move > tables from dataspace A to dataspace B such as using onunload/onload or > dbexport/dbimport or using dbaccess unload to file/load from file, dbload, > etc. I know one must be careful when using dbaccess unload/load because of > the possibility of creating long transactions upon the load part. > I was thinking of either doing the following: > A) dbaccess unload TABLE_A to unload_file > B) TURN OFF LOGGING > C) create tmp_table like TABLE_A > d) dbaccess load TABLE_A from unload_file > e) drop TABLE_A > f) rename tmp_table to TABLE_A > g) create indexes on TABLE_A > h) create synoyms, triggers, as needed > > OR use > > onunload/onload > > Just wondering if anyone has an opinion as to why method 1 (using dbaccess > unload/load) would have any potential problems and which method is better? > IE: is using the onunload/onload better than using dbaccess unload/load? I > know it is faster of course, which is an advantage. But aside from the > speed > advantage is there anything better about using one method over another? > > Thanks > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent."

Mark. The Alter Fragment statement looks promising. I'll check it out. Thanks