Re: Clearing log in SE/ESQL

In <3403@esun179.gdc.com> brunner@gdc.COM (Paul Brunner) writes: >I am looking for a way to clear the database transaction log in the SE >without shutting down all ESQL applications for the period of time it takes >to do a total database backup, and then starting the database. >The scenario I would like to follow is to run a program on a periodic basis >that: > 1. Waits until it can gain exclusive access to the database, > 2. locks all the tables, > 3. turns off database logging, > 3. clears the database log file, > 4. starts the database, > 5. then releases all of the locks. Well, I've run into a similar problem, but I'm not attacking it with the same approach. For example, you don't seem to be worried about the integrity of the log at all. If you flush the log without doing a backup then you are subject to losing all those transactions that you flushed since you did you last backup. If this is the case why not just set the logfile name to /dev/null? Also, if you are going to lock all of the tables instead of doing a "database exclusive" then be careful to use a "set lock mode to wait" or you'll be in trouble. I have a database that is 165MB right now. It is used to keep track of all uucp traffic on a system that transfers 600MB a month with 200 other systems. I have programs that update the database from uucp logfiles processsed in the background, programs that accept updates through mail, and programs that update the database in the background with information on traffic through an RJE gateway. I have to backup the database, but I can't afford to have any of these updates fail. I also can't see going to all the trouble to shut these suckers down each time. All these background update programs open and close the database regularly rather than keeping it open all the time. What I'm going to do is: modify all the programs to adhere to a lockfile scheme write an ESQL/C program to run the backup process and make it adhere to the lockfile scheme This scheme will be: Update programs will look for a uucp-style lockfile (/tmp/LCK..<dbname>). It will go into a sleep/retry loop until there is no lockfile and a "database" statement succeeds. These programs will not create a lockfile because they don't need exclusive access. The backup program will make a new lockfile. Then it will sleep for a few seconds to eliminate a race condition. Then it will go into a sleep/"database exclusive" loop until it succeeds. Then it will do the backup, close the database, and remove the lockfile. The race condition described above is: 1. Update program finds no lockfile 2. Backup program creates lockfile 3. Backup program does "database exclusive" 4. Update program tries a "database" and fails I know the update program's criteria (no lock and SUCCESSFUL "database" statement) will handle this problem, but I think the few second pause in the backup program after the lockfile is created is a friendly added touch. To really be nice you would have the backup program put it's PID in the lockfile and have the update programs check to see that the process identified in an existing lockfile is alive. You also have to have the backup program do the same if a lockfile exists. Now there are still some problems here. You say "how do I run this unattended?". Well, my logfile is 33MB after 11 days. My 165MB of data backs-up to 38MB in a compressed cpio. I could just stomp the logfile first, then have the program run a: "find <dir> -print | cpio -o | compress > backupfile" If I run this every 11 days the backup is taking the room the logfile had and the sysadmin can get E-Mailed a message saying "it's backed-up to disk, put it on tape and delete it". Of course, if you can spare the space you should do the backup and test it before you stomp the log. Still, if you insist on your approach. Why not using a command like: " > logfile " This will certainly truncate it, and with all the security you're giving up with the approach you talked about. -- -----------------------+--------------------------------------------------- Charles "Chip" Yamasaki| The opinions expressed here are my own and are not chip@osh1.OSHA.GOV | supported or even generally accepted by OSHA. :-) -----------------------+---------------------------------------------------