RE: Reducing Table Sizes

Herb Blacker wrote: > > Here's a thought...I wish to copy a database to a *test* instance, but = I > don't need the tables to be as large as those in the production DB. > What I was thinking...and I could be way off base here...is that if I > did a DBEXPORT, then edited the <dbname.sql file to reduce the number > of rows where the table is being created, and *also* edited the > corresponding <tabname.unl file to remove all but the # of rows I want = > to import - will the DBIMPORT work or not? > > ----------------------------------------------- > Herb Blacker > Cimarron, Inc. > Sr. Database Administrator > Dept. of Labor Job Corps, San Marcos, Texas > ----------------------------------------------- > Herb: Yes the DBIMPORT will work, but you will probably find it will take a = couple of goes to get right. The inherent single-transaction nature of = DBIMPORT makes it a real PITA when it falls over after completing 95% of = the task, simply because of something like an errant carriage-return in = the <dbname>.sql file. In any case, what you wind up with is just random = subsets of your production info, without the proper relationships and RI = required to properly test stuff. I have a series of scripts I use to rebuild our test and dev databases, = which uses a preset flat-file data subset of the most fundamental keys in = our database, and another flat-file containing the join-criteria required = on the tables that I require just a subset of (along the lines of): #table-name join-criteria table1 customer_id =3D sample_key table2 MOD(ROWID,5) =3D 0 ... where 'sample_key' is the column name used in the temp table I load = the first flat file into. The second example just gets every fifth row, = for tables where random data is acceptable. So to perform the rebuild, I just drop and recreate the development = database, and then generate the data from production to populate it, thus = propagating any table changes that have occurred automatically. You end = up with virtually static data (it does slowly change) in your test = environment. But the benefit of this method is that it avoids the usual = problem of test databases - new columns are added to a table, but they = are never actually populated with real data. Rather than go into huge detail here, if this is what you need, reply and = I will send you more info. Hope this helps, RET +------------------------------------------+ | Richard Thomas | | DBA - Marketing Information Systems | | Optus IT | | email: richard_thomas@yes.optus.com.au | | Ph: +61 2 9342 7188 | | "My opinions are my opinions" | +------------------------------------------+ Note if you have trouble replying to this address (we are experiencing = problems with our mail server), try posting to "ret at cornerpub dot com" = - apologies for the anti-spam, but I don't need to hear about Tonya = Harding's videos more than once a week.... ;-)