With the benefit of a lot of experience, we developed the following process which, properly maintained, works flawlessly every time. It is a lot of work to set up but has the benefit of doing exactly what you specify.
1. Create a file that contains all of the CREATE TABLE commands to reconstruct your database (except, of course, the system tables.) 2. Create a file that has all of the commands to create the primary and foreign keys in the proper order. 3. Create a file that has all of the commands to create the triggers. 4. Create a file that has all of the commands to create the views. 5. Create three tables in your database: one to hold the data required to create indexes, one to hold the data to create autonumbers, and one to hold the data to create comments. In our case, the index table has an autonumber index, a column for the table name on which the index will be built, a column for the column name on which the index will be built, a column for the index name itself, a column for the SIZE parameter for indexes on text columns, and a column to hold the second column name in a composite index. The comment and autonumber tables are similarly structured. Now, write the code to run a cursor through these tables and perform the actions. 6. Create a file that has all of the commands to create any SATTACHed tables. 7. Now, put it all together in one command file with the following process: a. CONNECT the database with STATICDB OFF and MULTI OFF. b. Output to a file and UNLOAD DATA. Since everything else is handled otherwise, you only need to unload the data. OUTPUT SCREEN. c. DISConnect the database and rename the original database. d. RUN the structure file from step 1. This builds the general structure. e. RUN the data file from 7b. This loads the data, including the data in your index, autonumber and comments tables. f. RUN the file to build the keys from step 2. g. RUN the files in turn from step 5 to create the indexes and autonumbers. HOLD OFF on the comments for now. h. RUN the file from step 3 to create the triggers. i. RUN the file from step 4 to create the views. j. RUN the file from step 6 to create the SATTACHes, if appropriate. k. RUN the file from step 5 to create the comments. By saving this until last you can load the comments on views and SATTACHed tables. You now have a completely fresh copy of your database. Note that if you wished you could include tables in the database to handle the keys, views, triggers and SATTACHes as well, leaving the structure file as the only externally-required file aside from the code to make it all happen. As I noted, it is a lot of work to set this up. The process began back in the 3.x days when we were having issues with the structure that survived a RELOAD or even an UNLOAD/RUN. In the end, it was so reliable a method that it survived and was elaborated upon over the years. Note that most of these external files were created from an UNLOAD STRUCTURE. As subsequent changes to the database have been made, though, all we need to do is maintain the files or the data in the "helper" tables. I would note that this is also a good tool for keeping databases at multiple sites in sync structurally. Whenever we have a major rollout, we write code to change the structure from the before state to the after state. We then run that code followed by the rebuild process above using the new structure files. Finally, if something in this process fails, you know there is an issue that needs to be found and fixed. Emmitt Dove Converting Systems Architect Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Friday, February 04, 2011 08:43 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Illegal table name Also Jim: You keep saying that this is the first time it's happened in a long time, but I don't think you can assume that an UNLOAD works the same way every time. I imagine it could unload the tables and views in different orders each time. I know I'll get criticized for this, but I'll bet I could count on 5 fingers the number of times I've had to unload a database for any of my clients over the 18 years I've been a consultant. I just haven't seen the problems... And I'm glad because my early experiences with unload weren't good ones, with tables being created out of order (FK tables before the PK tables), views out of order, etc... Don't know if it's a cleaner experience now, I'm sure it is. My last unload was probably a few years ago. Karen
