dropping sequence objects and default db space

Hi there - Running IDS 9.4FC5 on Solaris v.9 Two questions: 1) do sequences get stored by default in default db spaces? Can I specify a space other than default db space to store them in? 2) Why aren't sequences dropped when I drop a database? -- I ask because I created a sequence, and using ISA, I saw my sequence being stored in default db space. I then dropped my database all together and notice that my sequence is still preserved in the Chunk Table Report in ISA...the database was successfully dropped, so seeing my sequence still around is strange to me. I then re-created a database with the same name and see my sequence is still represented in ISA default db space table report in the same db (same because now I not only see my sequence in default db space in that db, but also see all the sys tables that I expect to see there as well). I queried syssequences in my newly created db with the same name, and there is no such sequence in syssequences. I tried dropping the sequence from the newly created database (with the same name as before) with no luck (object not found)...which I expected. So how do I drop it--do I have to do so explicitly before dropping the database? Is this just an ISA issue? Thanks a bunch, Sierra M.

There is a bug in v9 (fixed in 10) where the DROP DATABASE statement does not mop up the sequence extent(s) from the DBSpace where database catalogues were created. You have to remember to DROP SEQUENCE first. If you are really lucky and the sequence is the only thing left in the dbspace where it was created, then to reclainm the space, you can corrupt the dbspace (eg rm chunk) bounce informix (set ONDBSPACEDOWN option to 2, I think) and onmode -O at the blocked checkpoint. Failing that, you will have to move all other objects in the dbspace to somewhere else and then do the above trick. If there are system catalogues in that dbspace you will have to DROP DATABASE(s) again if you want to reclaim all the dbspace space. Alternativley, you could just leave the old sequence in place, you will be able to create a new sequence (with the same name if you like) in the new database (with the same name if you like). This way you end up with 2 objects in the dbspace (visible with oncheck -pe), but only one is in use. A small piece of bagage (8pages) to carry and avoids the above nail biting steps.

Stuart- Thanks so much for the info. Do you know if there isa way in v9 to store the sequence in a db space other than defaultdb?--from my convos with IBM it sounds like there is no way to specify a db space for a sequence other than defaultdbs. Thanks again, Sierra On Mon, 13 Nov 2006, STUART MCCANN wrote: > > There is a bug in v9 (fixed in 10) where the DROP DATABASE statement does not > mop up the sequence extent(s) from the DBSpace where database catalogues were > created. You have to remember to DROP SEQUENCE first. > > If you are really lucky and the sequence is the only thing left in the dbspace > where it was created, then to reclainm the space, you can corrupt the dbspace > (eg rm chunk) bounce informix (set ONDBSPACEDOWN option to 2, I think) and > onmode -O at the blocked checkpoint.> > Failing that, you will have to move all other objects in the dbspace to > somewhere else and then do the above trick. If there are system catalogues in > that dbspace you will have to DROP DATABASE(s) again if you want to reclaim > all the dbspace space. > > Alternativley, you could just leave the old sequence in place, you will be > able to create a new sequence (with the same name if you like) in the new > database (with the same name if you like). This way you end up with 2 objects > in the dbspace (visible with oncheck -pe), but only one is in use. A small > piece of bagage (8pages) to carry and avoids the above nail biting steps. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Sierra, I have not seen any way this could be done. The sequence is placed in the dbspace where the database was created ( rootdbs by default or using IN dbspace clause). So, it hangs with the system catalogues. I suspect you will just have to live with that unless you can find another way. Stuart McCann Phone: 02 6332-8285 stuart.mccann@lands.nsw.gov.au -----Original Message----- From: Sierra A.T. Moxon [mailto:staylor@cs.uoregon.edu] Sent: Wednesday, 15 November 2006 2:56 AM To: Stuart McCann Subject: Re: dropping sequence objects and default db space [7812] Stuart- Thanks so much for the info. Do you know if there isa way in v9 to store the sequence in a db space other than defaultdb?--from my convos with IBM it sounds like there is no way to specify a db space for a sequence other than defaultdbs. Thanks again, Sierra On Mon, 13 Nov 2006, STUART MCCANN wrote: > > There is a bug in v9 (fixed in 10) where the DROP DATABASE statement does not > mop up the sequence extent(s) from the DBSpace where database catalogues were > created. You have to remember to DROP SEQUENCE first. > > If you are really lucky and the sequence is the only thing left in the dbspace > where it was created, then to reclainm the space, you can corrupt the dbspace > (eg rm chunk) bounce informix (set ONDBSPACEDOWN option to 2, I think) and > onmode -O at the blocked checkpoint.> > Failing that, you will have to move all other objects in the dbspace to > somewhere else and then do the above trick. If there are system catalogues in > that dbspace you will have to DROP DATABASE(s) again if you want to reclaim > all the dbspace space. > > Alternativley, you could just leave the old sequence in place, you will be > able to create a new sequence (with the same name if you like) in the new > database (with the same name if you like). This way you end up with 2 objects > in the dbspace (visible with oncheck -pe), but only one is in use. A small > piece of bagage (8pages) to carry and avoids the above nail biting steps. > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Department of Lands. This email message has been swept by MIMEsweeper for the presence of computer viruses. ***************************************************************