RE: reorganizing dbspaces

You've gotten lots of good advice so far, but I'm going to throw in my .02 anyway. I recently did a couple of DB reorgs, the most recent on a very critical 24x7 system. I made extensive use of some of Art Kagel's utilities that are in his utils2_ak package on the IIUG site (http://www.iiug.org). Specifically: -- I used "myschema" to generate a "better" schema file than what dbschema provides, since myschema can provide current and recommended extent sizes, fixes the constraint-naming mess, and has a few other nice features. Because I wanted to do a create-table, load, create-indexes for each table, I had to manually dissect the myschema output to separate files, two for each table (a create-table file and a create-indexes file). Not sure, but I think Art's latest version of myschema now has an option to do this for you. -- I created a second instance to hold the new, re-organized database. Of course, you need enough memory/disk space on your box to have two instances up at the same time. This second instance was set up using link names for the chunks; spreading out the chunks across different physical disks (the old database was ALL on ONE physical disk!); fragmenting large tables; segregating physical and logical logs; increasing extent sizes to allow for growth; etc. -- For loading the data into the "new" database, I decided to use Art's "dbcopy" utility to copy the data from the database in the old instance to the database in the new instance. This bypasses the dbimport / dbexport scenario, eliminating the need for more disk space for the .unl files, and skipping the little idiosyncrasies of those utilities. -- I rolled all of this into a shell script, which I ran on the day of the conversion. It worked slick!! After all the data was moved over, I took down both instances, modified the onconfig file for the new instance to give it the SERVERNUM, DBSERVERNAME, and DBSERVERALIASES of the old instance, and brought that instance back up. Then I used Art's "dostats" to do the update statistics. This method worked well for us, and, I believe, saved us a lot of critical "down" time. Of course, I did test out this whole thing several times on our development servers, so there would be no surprises on the production boxes. Thanks for the utilities, Art! They helped to make this extremely critical DB reorg go very smoothly. HTH Paul Mosser -----Original Message----- From: Greg Dewinter [mailto:gdewinter@spf.fairchildsemi.com] Sent: Thursday, October 21, 1999 5:23 AM To: informix-list@iiug.org Subject: 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

You're welcome, Paul. The utilities grew over time to fill bits and pieces of the needs you saw as I ran into them myself so they just seem to fit. Would that I had planned the whole thing in a coordinated manner. Then I could appear the true genius ;-). Oh, yes the latest myschema does indeed permit the splitting of the schema into pre and post-load schemas. I finally ran into that one once too often and got fed up with editing schemas by hand. I even played with the idea of creating two complete schemas with the appropriate commands commented out. Then I came to my senses. :-) BTW the package utils4_ak contains AWK scripts for automating things like parsing a schema and outputting a set of dbcopy, load, unload, drop, drop&create, drop&create&load, rename, or ul.ec commands. It is a good companion to utils2_ak. I keep then in a directory called unload which has a subdir for each database that I regularly do safety unloads for so I can quickly do: myschema -d database | awk -f ../mkunl.awk | dbaccess database FWIW. Glad I could be of service. Art S. Kagel mosserp@WellsFargo.COM wrote: > > You've gotten lots of good advice so far, but I'm going to throw in my .02 > anyway. I recently did a couple of DB reorgs, the most recent on a very > critical 24x7 system. I made extensive use of some of Art Kagel's utilities > that are in his utils2_ak package on the IIUG site (http://www.iiug.org). > > Specifically: > > -- I used "myschema" to generate a "better" schema file than what dbschema > provides, since myschema can provide current and recommended extent sizes, > fixes the constraint-naming mess, and has a few other nice features. > Because I wanted to do a create-table, load, create-indexes for each table, > I had to manually dissect the myschema output to separate files, two for > each table (a create-table file and a create-indexes file). Not sure, but I > think Art's latest version of myschema now has an option to do this for you. > > -- I created a second instance to hold the new, re-organized database. Of > course, you need enough memory/disk space on your box to have two instances > up at the same time. This second instance was set up using link names for > the chunks; spreading out the chunks across different physical disks (the > old database was ALL on ONE physical disk!); fragmenting large tables; > segregating physical and logical logs; increasing extent sizes to allow for > growth; etc. > > -- For loading the data into the "new" database, I decided to use Art's > "dbcopy" utility to copy the data from the database in the old instance to > the database in the new instance. This bypasses the dbimport / dbexport > scenario, eliminating the need for more disk space for the .unl files, and > skipping the little idiosyncrasies of those utilities. > > -- I rolled all of this into a shell script, which I ran on the day of the > conversion. It worked slick!! After all the data was moved over, I took > down both instances, modified the onconfig file for the new instance to give > it the SERVERNUM, DBSERVERNAME, and DBSERVERALIASES of the old instance, and > brought that instance back up. Then I used Art's "dostats" to do the update > statistics. > > This method worked well for us, and, I believe, saved us a lot of critical > "down" time. Of course, I did test out this whole thing several times on > our development servers, so there would be no surprises on the production > boxes. > > Thanks for the utilities, Art! They helped to make this extremely critical > DB reorg go very smoothly. > > HTH > > Paul Mosser > > -----Original Message----- > From: Greg Dewinter [mailto:gdewinter@spf.fairchildsemi.com] > Sent: Thursday, October 21, 1999 5:23 AM > To: informix-list@iiug.org > Subject: 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 is> extremely 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

Thanks everyone for all your help - I've just become a dba about 6 months ago when our other one left and I have learnt so much from this newsgroup its just fantastic (did some of the courses too). It also great to know you can check out your ideas with other people that have already implemented them. thanks again Bridget Reitsma PS Maybe one day I'll be answering question on this newgroup :)

In article <380F78EB.E8981636@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >You're welcome, Paul. The utilities grew over time to fill bits and >pieces of the needs you saw as I ran into them myself so they just seem to >fit. Would that I had planned the whole thing in a coordinated manner. >Then I could appear the true genius ;-). Oh, yes the latest myschema does >indeed permit the splitting of the schema into pre and post-load schemas. >I finally ran into that one once too often and got fed up with editing >schemas by hand. > >I even played with the idea of creating two complete schemas with the >appropriate commands commented out. Then I came to my senses. :-) > >BTW the package utils4_ak contains AWK scripts for automating things like >parsing a schema and outputting a set of dbcopy, load, unload, drop, >drop&create, drop&create&load, rename, or ul.ec commands. It is a good Sounds useful, can't see utils_ak4 on www.iiug.org yet though! Can you e-mail me a copy at work (The other secret e-mail address). I don't want to give out the other address as by not give out my work address, any work related question here do not affect company confidenilaity. Especially as I change names of everything work related so it is not traceable! >companion to utils2_ak. I keep then in a directory called unload which >has a subdir for each database that I regularly do safety unloads for >so I can quickly do: > >myschema -d database | awk -f ../mkunl.awk | dbaccess database > I've already sent Art patches from my work address for getting myschema to work against SE and old compilers. I'll play with this. Useful as we are converting lots of customer to the latest version of the our product for y2k compliance. I'm spending all my time converting customer live databases to test systems on-site so they can begin testing. utils_ak4 sounds just like what I need...I play with it ASAP. I am Step 1 ------- create empty database add tables using dbschema of our development database (which also adds indexes). drop indexes convert data Check this bit completed ok (can take 16hrs to get here). Step 2 ------ add indexes dostats -d <database name> If this all works (The record so far is >36hrs to complete Step 1 + Step 2!) then backup and get users testing. >FWIW. > >Glad I could be of service. > >Art S. Kagel > -- David Williams

Hi David, Oops, sorry I was sure I'd submitted utils4_ak last month with everything else. I have sent it in now and Walt will likely post it to the site today if he is not busy, otherwise in his usual efficient fashion it should be there by Monday PM. Art S. Kagel David Williams wrote: > > In article <380F78EB.E8981636@bloomberg.net>, Art S. Kagel > <kagel@bloomberg.net> writes > >You're welcome, Paul. The utilities grew over time to fill bits and > >pieces of the needs you saw as I ran into them myself so they just seem to > >fit. Would that I had planned the whole thing in a coordinated manner. > >Then I could appear the true genius ;-). Oh, yes the latest myschema does > >indeed permit the splitting of the schema into pre and post-load schemas. > >I finally ran into that one once too often and got fed up with editing > >schemas by hand. > > > >I even played with the idea of creating two complete schemas with the > >appropriate commands commented out. Then I came to my senses. :-) > > > >BTW the package utils4_ak contains AWK scripts for automating things like > >parsing a schema and outputting a set of dbcopy, load, unload, drop, > >drop&create, drop&create&load, rename, or ul.ec commands. It is a good > > Sounds useful, can't see utils_ak4 on www.iiug.org yet though! > Can you e-mail me a copy at work (The other secret e-mail address). > > I don't want to give out the other address as by not give out my work > address, any work related question here do not affect company > confidenilaity. Especially as I change names of everything work > related so it is not traceable! > > >companion to utils2_ak. I keep then in a directory called unload which > >has a subdir for each database that I regularly do safety unloads for > >so I can quickly do: > > > >myschema -d database | awk -f ../mkunl.awk | dbaccess database > > > > I've already sent Art patches from my work address for getting > myschema to work against SE and old compilers. I'll play > with this. Useful as we are converting lots of customer to > the latest version of the our product for y2k compliance. I'm > spending all my time converting customer live databases to > test systems on-site so they can begin testing. > > utils_ak4 sounds just like what I need...I play with it ASAP. > > I am > > Step 1 > ------- > create empty database > add tables using dbschema of our development database > (which also adds indexes). > drop indexes > convert data > > Check this bit completed ok (can take 16hrs to get here). > > Step 2 > ------ > add indexes > dostats -d <database name> > > If this all works (The record so far is >36hrs to complete Step 1 + > Step 2!) then backup and get users testing. > > >FWIW. > > > >Glad I could be of service. > > > >Art S. Kagel > > > -- > David Williams