Advice sought

Hi all, I have a database on our system (called archdb) that is currently empty. We are going to begin populating this with data. (Moving "old" data from our live dB to archdb) I ran some basic tests on archdb, and discovered a bad index on one table. As the dB is empty I dropped and recreated the table, and cured the problem. However... We have rootdbs and dbs1. As I thought one never puts anything other than system stuff in rootdbs I created the table in dbs1. On further checking I discovered that the people who set up the our system put "live" and some other dBs in dbs1, but for some reason they put archdb in rootdbs. So...I now have archdb in rootdbs, apart from one table, and that's in dbs1. My options, as I see them are these. 1. Leave everything as it is now. 2. Drop and recreate the one table, putting it in rootdbs. 3. Drop the database archdb and recreate it in dbs1 Suggestions/comments as to the best option to choose, and any dire consequences inherent in any of the above options gratefully received. Here's the output from onstat -d Informix Dynamic Server 2000 Version 9.21.UC2 -- On-Line -- Up 36 days 17:52:11 -- 688128 Kbytes Dbspaces address number flags fchunk nchunks flags owner name 9c5187d0 1 0x1 1 1 N informix rootdbs 9c5575f0 2 0x1 2 2 N informix dbs1 2 active, 2047 maximum Chunks address chk/dbs offset size free bpages flags pathname 9c518918 1 1 0 125000 81641 PO- /dev/infdisk1 9c557320 2 2 125000 875000 32610 PO- /dev/infdisk1 9c557488 3 2 0 1000000 999997 PO- /dev/infdisk2 3 active, 2047 maximum Regards, Malcolm Garbett IT Manager "This e-mail and any files transmitted with it is confidential. If you are not the intended recipient, you should not use, copy or disclose its contents, but should immediately notify the sender and delete the e-mail. The statements and opinions expressed here may not represent those of the company. Whilst we run anti-virus software on all e -mails we are not liable for any loss or damage and the recipient is advised to run their own anti-virus software". Newall Measurement Systems Ltd.

Hi, hmm. It pretty much depends on what you're going to do to/with your archdb once it's been filled, and how much data will be filled in which table/dbspace ... If I'd be you, I'd probably drop archdb and re-create it in exactly the same way it's been created elsewhere - if only to keep things conform for future administration, running same scripts on different machines, etc. (Otherwise you'd always have to think of this setup being different, i.e. you'd have to add chunks not to dbs1 but to rootdbs to accomodate more data in archdb, etc.) Probably I'd ask the people who did the setup before doing the drop/re-create ... Just in case they actually did this with some thought in their minds ... :) Actually it's just an DB (System) Admin thing. The average "SQL-user" will not notice the difference (except if there would be significant performance differences between disks ...). Hope it helps, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions "Malcolm Gar...." <malcolm.g@newall.co.uk> Sent by: forum.subscriber@iiug.org 17.01.2003 12:08 To: ids@iiug.org cc: Subject: Advice sought [25] Hi all, I have a database on our system (called archdb) that is currently empty. We are going to begin populating this with data. (Moving "old" data from our live dB to archdb) I ran some basic tests on archdb, and discovered a bad index on one table. As the dB is empty I dropped and recreated the table, and cured the problem. However... We have rootdbs and dbs1. As I thought one never puts anything other than system stuff in rootdbs I created the table in dbs1. On further checking I discovered that the people who set up the our system put "live" and some other dBs in dbs1, but for some reason they put archdb in rootdbs. So...I now have archdb in rootdbs, apart from one table, and that's in dbs1. My options, as I see them are these. 1. Leave everything as it is now. 2. Drop and recreate the one table, putting it in rootdbs. 3. Drop the database archdb and recreate it in dbs1 Suggestions/comments as to the best option to choose, and any dire consequences inherent in any of the above options gratefully received. Here's the output from onstat -d Informix Dynamic Server 2000 Version 9.21.UC2 -- On-Line -- Up 36 days 17:52:11 -- 688128 Kbytes Dbspaces address number flags fchunk nchunks flags owner name 9c5187d0 1 0x1 1 1 N informix rootdbs 9c5575f0 2 0x1 2 2 N informix dbs1 2 active, 2047 maximum Chunks address chk/dbs offset size free bpages flags pathname 9c518918 1 1 0 125000 81641 PO- /dev/infdisk1 9c557320 2 2 125000 875000 32610 PO- /dev/infdisk1 9c557488 3 2 0 1000000 999997 PO- /dev/infdisk2 3 active, 2047 maximum Regards, Malcolm Garbett IT Manager "This e-mail and any files transmitted with it is confidential. If you are not the intended recipient, you should not use, copy or disclose its contents, but should immediately notify the sender and delete the e-mail. The statements and opinions expressed here may not represent those of the company. Whilst we run anti-virus software on all e -mails we are not liable for any loss or damage and the recipient is advised to run their own anti-virus software". Newall Measurement Systems Ltd.

Drop the database and recreate it in dbs1 or better yet create a dbspace specifically for that one database. This has the advantage the since you can restore a single dbspace you will be able to restore that one database without affecting the others if needed. In addition, the extents for tables in archdb will not become interleaved with those of the other databases. Art S. Kagel ----- Original Message ----- From: Malcolm Gar.... <malcolm.g@newall.co.uk> At: 1/17 7:37 > Hi all, > > I have a database on our system (called archdb) that is currently empty. > > We are going to begin populating this with data. (Moving "old" data from > our live dB to archdb) > > I ran some basic tests on archdb, and discovered a bad index on one table. > As the dB is empty I dropped and recreated the table, and cured the problem. > > However... > > We have rootdbs and dbs1. As I thought one never puts anything other than > system stuff in rootdbs I created the table in dbs1. On further checking I > discovered that the people who set up the our system put "live" and some > other dBs in dbs1, but for some reason they put archdb in rootdbs. > > So...I now have archdb in rootdbs, apart from one table, and that's in dbs1. > > My options, as I see them are these. > > 1. Leave everything as it is now. > 2. Drop and recreate the one table, putting it in rootdbs. > 3. Drop the database archdb and recreate it in dbs1 > > Suggestions/comments as to the best option to choose, and any dire > consequences inherent in any of the above options gratefully received. > > Here's the output from onstat -d > > Informix Dynamic Server 2000 Version 9.21.UC2 -- On-Line -- Up 36 days > 17:52:11 -- 688128 Kbytes > > Dbspaces > address number flags fchunk nchunks flags owner name > 9c5187d0 1 0x1 1 1 N informix rootdbs > 9c5575f0 2 0x1 2 2 N informix dbs1 > 2 active, 2047 maximum > > Chunks > address chk/dbs offset size free bpages flags pathname > 9c518918 1 1 0 125000 81641 PO- /dev/infdisk1 > 9c557320 2 2 125000 875000 32610 PO- /dev/infdisk1 > 9c557488 3 2 0 1000000 999997 PO- /dev/infdisk2 > 3 active, 2047 maximum > > Regards, > Malcolm Garbett > IT Manager > > "This e-mail and any files transmitted with it is confidential. If you are > not the intended recipient, you should not use, copy or disclose its > contents, but should immediately notify the sender and delete the e-mail. > The statements and opinions expressed here may not represent those of the > company. Whilst we run anti-virus software on all e -mails we are not > liable for any loss or damage and the recipient is advised to run their own > anti-virus software". > Newall Measurement Systems Ltd.

Malcolm, It appears you made the basic error of creating a table IN dbspacename. By default the table should alswys be created in the same dbspace as the original dbspace. That is best achieved by leaving out the IN dbspace clause. To correct the problem I would recommend either a dbexport/dbimport of the offending database with the -ss option to retain extent sizes etc, or doing the same thing manually through dbschema -ss, unload, drop database, crate database in correct dbspace, rebuild tables from dbschema output, reload tables, recreate indexes from the dbschema output. The manual method might be faster especially if there are TEXT or BYTE dataatypes. I would also advise that you might consider creating a TEMP dbspace. This will improve log utilisation when doing SELECT ... ORDER BY statements which create temporary tables. Currently these are logged, whereas temp dbspaces are not. Hope this helps Malcolm