best procedure for table moving one dbspace to other

Hi Frequently some of dbspaces are reaching danger level for available space and we are doing manually, dropping the table and creating the structure in other dbspace and loading the data with dbload command. I would like to know the best and simple procedure for table and its data moving one dbspace to other dbspace. Is it any utilities are available in iiug to simply data moving one dbspace to other dbspace ? Any suggestions ? thanks in advance. Ravi Yarlagadda

"Yarlagadda, Ravi" wrote: > Hi > > Frequently some of dbspaces are reaching danger level for available space > and we are doing manually, dropping the table and creating the structure in > other dbspace and loading the data with dbload command. > > I would like to know the best and simple procedure for table and its data > moving one dbspace to other dbspace. > Is it any utilities are available in iiug to simply data moving one dbspace > to other dbspace ? > > Any suggestions ? > > thanks in advance. > Ravi Yarlagadda take a look at alter table init I think I remeber Art telling me once that you can use a statement like alter table <tabname> init fragment by round robin in <dbspacename> please dont use this exact statement without verifying the syntax is correct, this will essentially move the specified table from its current location to the specified dbspacename. keep in mind that this will lock the entire table.

In article <389334E8.E03179F4@nycap.rr.com>, "Matthew H. Devlin III" <mhdevlin@nycap.rr.com> wrote: > "Yarlagadda, Ravi" wrote: > > > Hi > > > > Frequently some of dbspaces are reaching danger level for available space > > and we are doing manually, dropping the table and creating the structure in > > other dbspace and loading the data with dbload command. > > > > I would like to know the best and simple procedure for table and its data > > moving one dbspace to other dbspace. > > Is it any utilities are available in iiug to simply data moving one dbspace > > to other dbspace ? > > > > Any suggestions ? > > > > thanks in advance. > > Ravi Yarlagadda > > take a look at alter table init > I think I remeber Art telling me once that you can use a statement like > alter table <tabname> init > fragment by round robin in <dbspacename> > > please dont use this exact statement without verifying the syntax is correct, > this will essentially move the specified table from its current location to > the specified dbspacename. > keep in mind that this will lock the entire table. > > Mr Devlin is indeed correct, ALTER FRAGMENT can be used to move a table. You can see a lucid description of this process in some of Mr. Kagel's posts. You can omit the "BY ROUND ROBIN" or other fragmentation expression if you just want to move the table. I did this by mistake once when using ALTER FRAGMENT ON TABLE <tabname> INIT IN <dbspacename> to rebuild a table after deleting a lot of rows, typed the wrong dbspace and the table was moved to the space I had said (instead of the space I meant it to go in...) It will lock the table as Mr Devlin points out and it could be locked for a while if it's a large table. If you've being following the procedure unload, drop, create, load in that order then you should find ALTER FRAGMENT faster and neater so won't need to look for ways to make the period of unavailability shorter. -- Andrew Pearson - un animal avec beaucoup de fonctions interactives. Parlez et riez ensemble. Il connait 800 mots et bruits. R'agit ' la lumi're et au bruit. Ses movemements sont tr's r'alistes! Version anglais. Sent via Deja.com http://www.deja.com/ Before you buy.

echo "alter fragment on table TABLE init in DBSPACE" | dbaccess -e DB

It should be alter fragment on table <tabname> init in <new-dbspace> It works with Dynamix Server, but not with Workgroup-Edition. It would be nice to here some comments about doing this with WGS. I had the problem once. In article <389334E8.E03179F4@nycap.rr.com>, "Matthew H. Devlin III" <mhdevlin@nycap.rr.com> writes: > "Yarlagadda, Ravi" wrote: > .. >> >> I would like to know the best and simple procedure for table and its data >> moving one dbspace to other dbspace. .. > > take a look at alter table init > I think I remeber Art telling me once that you can use a statement like > alter table <tabname> init > fragment by round robin in <dbspacename> >

> Mr Devlin is indeed correct, ALTER FRAGMENT can be used to move a > table. You can see a lucid description of this process in some of Mr. > Kagel's posts. You can omit the "BY ROUND ROBIN" or other fragmentation > expression if you just want to move the table. > > I did this by mistake once when using ALTER FRAGMENT ON TABLE <tabname> > INIT IN <dbspacename> to rebuild a table after deleting a lot of rows, > typed the wrong dbspace and the table was moved to the space I had said > (instead of the space I meant it to go in...) > > It will lock the table as Mr Devlin points out and it could be locked > for a while if it's a large table. > > If you've being following the procedure unload, drop, create, load in > that order then you should find ALTER FRAGMENT faster and neater so > won't need to look for ways to make the period of unavailability > shorter. I am curious about the general consensus of the group on this method. I've been working with this kind of situation with large tables for a while, and have come to the conclusion that the alter table (or alter index to cluster to rebuild a table in some cases) isn't the way to go with large tables. The issue is that the engine does not appear to parallelize the alter table statement. I've had much better success with large tables doing the unload with HPL, drop, rebuild (without indexes or constraints), load with HPL, and the building the indexes and constraints manually in parallel. I'll admit, we started this process in 7.23.XX, and I have been remiss in testing whether the alter process is parallelized in 7.31.XX. Has anyone come to similar conclusions? I'd be interested... -- Dan Michaelis Database Administrator dan@kax.com Sent via Deja.com http://www.deja.com/ Before you buy.

In WGS which does not support fragmentation, and so apparently ALTER FRAGMENT, you will have to either use ALTER INDEX...TO CLUSTER or export/ import. Art S. Kagel "Tommi Mäkitalo" wrote: > > It should be > alter fragment on table <tabname> init in <new-dbspace>> > It works with Dynamix Server, but not with Workgroup-Edition. > It would be nice to here some comments about doing this with WGS. > I had the problem once. > > In article <389334E8.E03179F4@nycap.rr.com>, > "Matthew H. Devlin III" <mhdevlin@nycap.rr.com> writes: > > "Yarlagadda, Ravi" wrote: > > > .. > >> > >> I would like to know the best and simple procedure for table and its data > >> moving one dbspace to other dbspace. > .. > > > > take a look at alter table init > > I think I remeber Art telling me once that you can use a statement like > > alter table <tabname> init > > fragment by round robin in <dbspacename> > >

In article <8748j3$kjb$1@nnrp1.deja.com>, Dan Michaelis <dan_michaelis@my-deja.com> wrote: > > > Mr Devlin is indeed correct, ALTER FRAGMENT can be used to move a > > table. You can see a lucid description of this process in some of Mr. > > Kagel's posts. You can omit the "BY ROUND ROBIN" or other > fragmentation > > expression if you just want to move the table. > > > > I did this by mistake once when using ALTER FRAGMENT ON TABLE > <tabname> > > INIT IN <dbspacename> to rebuild a table after deleting a lot of rows, > > typed the wrong dbspace and the table was moved to the space I had > said > > (instead of the space I meant it to go in...) > > > > It will lock the table as Mr Devlin points out and it could be locked > > for a while if it's a large table. > > > > If you've being following the procedure unload, drop, create, load in > > that order then you should find ALTER FRAGMENT faster and neater so > > won't need to look for ways to make the period of unavailability > > shorter. > > I am curious about the general consensus of the group on this method. > I've been working with this kind of situation with large tables for a > while, and have come to the conclusion that the alter table (or alter > index to cluster to rebuild a table in some cases) isn't the way to go > with large tables. The issue is that the engine does not appear to > parallelize the alter table statement. I've had much better success > with large tables doing the unload with HPL, drop, rebuild (without > indexes or constraints), load with HPL, and the building the indexes and > constraints manually in parallel. I'll admit, we started this process > in 7.23.XX, and I have been remiss in testing whether the alter process > is parallelized in 7.31.XX. > > Has anyone come to similar conclusions? I'd be interested... I have the same opinion. The best way for moving large tables is HPL. Also ALTER FRAGMENT ON TABLE ' INIT IN is going via transaction that doesn't make it faster then HPL. Maybe using RAW (nonlogging tables) in 7.31 can help in this case. Anyway it's slower for large tables than HPL and I think ALTER INIT is good only for small and medium tables. Eugene Nechayev Database Administrator Sent via Deja.com http://www.deja.com/ Before you buy.

In article <874e22$ou3$1@nnrp1.deja.com>, Eugene Nechayev <4new@my-deja.com> wrote: > In article <8748j3$kjb$1@nnrp1.deja.com>, > Dan Michaelis <dan_michaelis@my-deja.com> wrote: > > > > > Mr Devlin is indeed correct, ALTER FRAGMENT can be used to move a > > > table. You can see a lucid description of this process in some of > Mr. > > > Kagel's posts. You can omit the "BY ROUND ROBIN" or other > > fragmentation > > > expression if you just want to move the table. > > > > > > I did this by mistake once when using ALTER FRAGMENT ON TABLE > > <tabname> > > > INIT IN <dbspacename> to rebuild a table after deleting a lot of > rows, > > > typed the wrong dbspace and the table was moved to the space I had > > said > > > (instead of the space I meant it to go in...) > > > > > > It will lock the table as Mr Devlin points out and it could be > locked > > > for a while if it's a large table. > > > > > > If you've being following the procedure unload, drop, create, load > in > > > that order then you should find ALTER FRAGMENT faster and neater so > > > won't need to look for ways to make the period of unavailability > > > shorter. > > > > I am curious about the general consensus of the group on this method. > > I've been working with this kind of situation with large tables for a > > while, and have come to the conclusion that the alter table (or alter > > index to cluster to rebuild a table in some cases) isn't the way to go > > with large tables. The issue is that the engine does not appear to > > parallelize the alter table statement. I've had much better success > > with large tables doing the unload with HPL, drop, rebuild (without > > indexes or constraints), load with HPL, and the building the indexes > and > > constraints manually in parallel. I'll admit, we started this process > > in 7.23.XX, and I have been remiss in testing whether the alter > process > > is parallelized in 7.31.XX. > > > > Has anyone come to similar conclusions? I'd be interested... > > I have the same opinion. The best way for moving large tables is > HPL. Also ALTER FRAGMENT ON TABLE ' INIT IN is going via > transaction that doesn't make it faster then HPL. Maybe using RAW > (nonlogging tables) in 7.31 can help in this case. Anyway it's slower > for large tables than HPL and I think ALTER INIT is good only for small > and medium tables. > > Eugene Nechayev > Database Administrator > Point taken - I will know next time. I was thinking in terms of my own tiny little database where no table has more than about half a million rows. The original poster probably implied that his tables were larger by saying that they have space problems. I've never used HPL - and I'm thinking of rewriting the scripts which we use to create a copy of our live database which is only about 1.2 gig in size. Would you recomend HPL, dbexport, or creating the new database and SELECTing the data across with a little script, or some other method? Andrew Pearson - un animal avec beaucoup de fonctions interactives. Parlez et riez ensemble. Il connait 800 mots et bruits. R'agit ' la lumi're et au bruit. Ses movemements sont tr's r'alistes! Version anglais. Sent via Deja.com http://www.deja.com/ Before you buy.

In article <3895B8D7.EABBE85E@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >In WGS which does not support fragmentation, and so apparently ALTER >FRAGMENT, you will have to either use ALTER INDEX...TO CLUSTER or export/ >import. > Hurrrumph! alter fragment does work on Workgroup Edition, I have used it on Workgroup Edition at our own site (workgroup edition installed by me from the original CD) and several customer sites. However..you can only init into 1 dbspace. Not supporting fragmentation means a table in >1 dbspace. >Art S. Kagel > >"Tommi Mäkitalo" wrote: >> >> It should be >> alter fragment on table <tabname> init in <new-dbspace>>> >> It works with Dynamix Server, but not with Workgroup-Edition. >> It would be nice to here some comments about doing this with WGS. >> I had the problem once. >> >> In article <389334E8.E03179F4@nycap.rr.com>, >> "Matthew H. Devlin III" <mhdevlin@nycap.rr.com> writes: >> > "Yarlagadda, Ravi" wrote: >> > >> .. >> >> >> >> I would like to know the best and simple procedure for table and its data >> >> moving one dbspace to other dbspace. >> .. >> > >> > take a look at alter table init >> > I think I remeber Art telling me once that you can use a statement like >> > alter table <tabname> init >> > fragment by round robin in <dbspacename> >> > -- David Williams