Quickly restoring a DB for testing & Re: How to save/restore table(s)

In <23404.1682@infodn.rmi.de> wd@infodn.rmi.de (Walter Doerr) writes: > [Deletions . . .] > Problem: > > During testing things may go wrong and I would like to restore the state/ > contents of the table(s) to that what it was before my program ran. > > Since this may happen often, the save/restore operation should be fast. > > Also, the save/restore operation should be transparent to the software > package mentioned above (i.e. since I don't know the internals of the > software package, as little information as possible that is not part of > the modified data should be changed by the save/restore process. Things that > shouldn't change are for example table ownership, creation date, etc.). > > Is the fact that the database is created WITH LOG MODE ANSI working for > or against me (with regard to the problem mentioned above)??? > > Is there some kind of global, database-wide rollback command in OnLine 5.0? > > dbunload/dbload should do the trick, but it requires the use of a tape drive > and is therefore too slow. > > Any other ideas? Yes. If the problem is to rapidly restore a DB to a baseline state for testing purposes then the following has proved to be quite useful: WARNING: This will restore the whole instance so if you have other DB's in this instance you will mangle them. 1. Write zeroed out blocks throughout the portion of the raw partition that you will be using for your Informix instance. This is important for the compression step. NOTE: If you are creating your instance into a file then Informix will effectively write zeroes throughout the unused space. 2. Create your instance. 3. Create your database and load it with your baseline test data. 4. Take your instance off-line. 5. Compress your instance. a. If your instance is in a file then just compress a copy of it. b. If your instance is on a raw partition the use the following: dd if=<chunk path> bs=<PAGESIZE> count=<PAGES> skip=<OFFSET> | compress -c > dbname.base # or any compression prog. If it is not obvious how to do a restore then I advise you to get your system/DB admin to create a script for you. I would not want you to destroy a filesystem. Of course you can improve the create/restore speed by using a multiple of the PAGESIZE and adjusting PAGES and OFFSET appropriately. Also you really only need to compress/restore through the last page of data used in the baseline instance. This can be determined by "tbcheck -pe". I have restored a 500MB instance containing 200 MB's of data in less than 4 minutes and the compressed file took up less room then all the table unloads. -- Daniel Wood daniel@starsight.com The opinions expressed herein are those of myself, my employers, the carriers of this news message or any other messages for that matter, you the reader and any of your relations(whether by birth or chromosome count), all life forms and all inanimate objects.