Re: Export/Import Database - Help??

In Message-Id: <392a42$jhv@arcadia.informatik.uni-muenchen.de> Dr. Richard Spitz (spitz@ana.med.uni-muenchen.de) wrote: > Bill Ennis (ennis@ssd.comm.mot.com) wrote: > : Warning: If you are using Online you will lose the dbspace/extent > : information ;( However, it does fit all tables into one extent ;)). > > How can Online fit all tables into one extent? AFAIK, dbimport > uses the default extent size of 16k, so large tables will get > split into many little pieces, and performance will degrade serious- > ly if the number of extents exceeds 8 for a table. > > Could one of the gurus please shed some light on this? > > Regards, Richard > -- > +----------------------------+-------------------------------------------+ > | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | > | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3421 | > | Klinikum Grosshadern | FAX : +49-89-7095-8886 | > | 81366 Munich, Germany | | > +----------------------------+-------------------------------------------+ You must specify the extent size when you create the new table. There are some very squirrelly tricks, though! Specifically: create table "owner".table_name ( ingot_index integer not null, rsd_mam char(1), rsd_slbgagd smallfloat, rsd_slbgagf smallfloat )in hl_tables_2 EXTENT SIZE 100000 NEXT SIZE 50000; revoke all on "owner".table_name from "public"; ALTER TABLE "owner".table_name LOCK MODE (ROW); create index "owner".table_name on "owner".table_name (ingot_index); You may choose a specific dbspace for you data--and you should--else it will go into the root dbspace with logs, some temp info, and such. If you have too much data in the root dbspace it can *badly* crash your whole db, instead of just returning a "no more space" error pertaining to that one table. The dbspace in this example is hl_tables_2. You may specify extent size, in KILOBYTES, during creation. Watch your units here, because the DBA is used to working in pages or sectors, and this will burn you. Likewise, the procedure for calculating table sizes works in pages (Informix Guide to SQL, Tutorial, December 1991 pg 10-12) so if your platform is like ours we need to double the size in K to get pages. The "EXTENT SIZE" tells how big to make the first extent, the "NEXT SIZE" tells how big to make the first next extent. If you size your dbspace and table correctly you won't have more than one extent. Lock mode is not preserved (under < v6.0) so you must fix that manually, too. We always use lock mode (row). Page locking is usually very bad in that you cannot predict what will be locked. If the lock is held very long you can suspend whole systems (ask me how I know that!) Also, locks are cheap (have the DBA run the locks parameter WAY up. I set them from the default value of 2000 to at least 10 or 20 thousand. The max is more than a quarter-million. (Informix OnLine Admin Guide, v5.0, December 1991 pg 1-32) BTW, we are using RSAM Version 5.00.UD1 under DEC OSF/1 on an Alpha. Bill Ennis once offered a script to help you with sizing a table. You may email him and see if the offer's still good. The calculations are tedious, but somebody has to do it. If you already have a table, and you *know* how big it is then skip all that calculation stuff. While we're at it, watch out for filling up your logical logs when you load up large tables. Of course, that's just the tip of the DBA iceberg :) Good luck, __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | | Reynolds Metals Co. "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________| PS: Walt, we are having to upgrade my node's O/S to fix this naming problem after we changed our domain name. That guy will get to it first thing next week (does tomorrow ever come?) In the meantime I'll just be pretty quiet, except for stuff I can't resist (like this!) Thanks.