break one dbspace in to two dbspace

Posted in 2003

Summary

The poster had data and indexes together in a single dbspace and wanted to split them so data sits in one dbspace and indexes in another. Suggested approach: create the new dbspace, then either use ALTER TABLE ... INIT to move the tables (leaving detached indexes in the original space, an approach suited to when tables are being fragmented at the same time), or more simply drop each index and recreate it IN the new dbspace. The rest of the thread was banter; no further detail was given.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Storage & Space Management

hiten.bhawsar — Wed, 1 Oct 2003 17:50:44 +0530 — source: Usenet: comp.databases.informix

Hi all I have one dbspace where I have all data and indexes I wanted to created one more dbspace one dbspace has the data in it one dbspace has the indexes in it how do that ? regard sending to informix-list

↪ replying to hiten.bhawsar

Paul Watson — Wed, 01 Oct 2003 13:47:27 +0100 — source: Usenet: comp.databases.informix

Create another dbspaces and then use alter table .. init .. command to move the tables. Assuming you have detached indexes then they will still be in the original dbspace. "hiten.bhawsar" wrote: > > Hi all > > I have one dbspace where I have all data and indexes > > I wanted to created one more dbspace > > one dbspace has the data in it > one dbspace has the indexes in it > > how do that ? > > regard > > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

↪ replying to Paul Watson

Obnoxio The Clown — Wed, 01 Oct 2003 18:57:13 +0100 — source: Usenet: comp.databases.informix

Paul Watson wrote: > Create another dbspaces and then use alter table .. init .. command > to move the tables. Assuming you have detached indexes then they > will still be in the original dbspace. Are you on drugs, Paul? (Again? :o) Why not create the new dbspace, drop the index and recreate it in the new space? > "hiten.bhawsar" wrote: >> >> Hi all >> >> I have one dbspace where I have all data and indexes >> >> I wanted to created one more dbspace >> >> one dbspace has the data in it >> one dbspace has the indexes in it >> >> how do that ? -- Ciao, The Obnoxious One "Ogni uomo mi guarda come se fossi una testa di cazzo"

↪ replying to Obnoxio The Clown

Paul Watson — Wed, 01 Oct 2003 19:45:03 +0100 — source: Usenet: comp.databases.informix

Doh!!! No - Double Doh!! The main reason is that last god knows how many times I've needed to do this it was because the tables where being fragmented at the same time. Next you'll be telling me you can rename a database :-)) Obnoxio The Clown wrote: > > Paul Watson wrote: > > > Create another dbspaces and then use alter table .. init .. command > > to move the tables. Assuming you have detached indexes then they > > will still be in the original dbspace. > > Are you on drugs, Paul? (Again? :o) > > Why not create the new dbspace, drop the index and recreate it in the new > space? > > > "hiten.bhawsar" wrote: > >> > >> Hi all > >> > >> I have one dbspace where I have all data and indexes > >> > >> I wanted to created one more dbspace > >> > >> one dbspace has the data in it > >> one dbspace has the indexes in it > >> > >> how do that ? > > -- > Ciao, > The Obnoxious One > > "Ogni uomo mi guarda come se fossi una testa di cazzo" -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

↪ replying to Obnoxio The Clown

Neil Truby — Wed, 1 Oct 2003 20:45:45 +0100 — source: Usenet: comp.databases.informix

"Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message news:blf3q8$b5buf$2@ID-64669.news.uni-berlin.de... > Paul Watson wrote: > > > Create another dbspaces and then use alter table .. init .. command > > to move the tables. Assuming you have detached indexes then they > > will still be in the original dbspace. > > Are you on drugs, Paul? (Again? :o) > > Why not create the new dbspace, drop the index and recreate it in the new > space? More typing.

↪ replying to Neil Truby

Paul Watson — Wed, 01 Oct 2003 23:04:07 +0100 — source: Usenet: comp.databases.informix

Neil Truby wrote: > > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message > news:blf3q8$b5buf$2@ID-64669.news.uni-berlin.de... > > Paul Watson wrote: > > > > > Create another dbspaces and then use alter table .. init .. command > > > to move the tables. Assuming you have detached indexes then they > > > will still be in the original dbspace. > > > > Are you on drugs, Paul? (Again? :o) > > > > Why not create the new dbspace, drop the index and recreate it in the new > > space? > > More typing. I get paid by the letter:-) -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Related threads

Glossary

dbspace