Re: reorgainising dbspaces

Bridget Reitsma wrote: > Hi > > This weekend I intend to reorg some of my dbspaces, at the moment I > have 3 databases all in the same dbspace with the indexes in a > separate dbspace. So I have decided to get smart and move them > around. We are using 730UC5 with Raid 1+0. > > Here's the plan > level 0 archive Verify the archive finish successfully Update statistics low for every table in the database - this isextremely important, counts are used to verify the data being imported. Another very good thing to do, is to adjust the extent sizes on the tables within the database. If nothing else, create a script that runs through the database altering the next extent size on any highly fragmented tables. > > export all the databases Verify the exports completed correctly! > > drop dbspaces > level 0 archive > create dbspaces Set PSORT_NPROCS and PDQPRIORITY so that index build will gain from these values. See Below > > import databases If these databases use logging import them with out logging and turn logging on after they are built. > > update stat's > test > level 0 archive > > I have just a few quick questions: > First when reorging the dbspace is it best to have one dbspace per 2 > gig chunk or one dbspace per database or option c something else? This would be a pesonal choice of a DBA. However, the fewer dbspace and chunks, the easier you may find it to administer the database. > > What is the dbimport command to import tables into separate dbspaces > (I couldn't find it in the books but I assuming it something like > dbimport 'database name' -d 'dbspace name' -t 'table name' - can > someone point me in the direction of which book it is in (I have all > the informix manuals and it has managed to elude me). dbimport <database> [-c] [-q] [-d <dbspace>] This will create that database in the <dbspace> provided, however, if you have created tables with "IN" clauses they will still be created in the <dbspace> of the "IN" clause, or fail if the <dbspace> doesn't exist. You may have to edit the <database>.sql file in you .exp directory to make sure you have the appropriate "IN" claused specified. If the databases have a large number of object (tables, columns, indexes....) you may need to increase the size of the system catalog tables to better accomodate all the data without created a large number of extents on the catalog tables. > Also how does the plan look - have I missed anything? > > Thanks for all you help - I just need to check it with the gurus > before I do it to our production databases - didn't get a chance to do > it to our test databases. > > cheer Bridget > Have fun, Greg