Our applications creates its own temporary tables (generating unique names using a one-up number register) in a local workstation filesystem/stLocal database. The tables are dropped as the app closes, but this is leaving numbers of .Old files for tables that will never be used again. Eventually, the local 'Temp' database folder is going to fill up with *thousands* of .Old files.

Any suggestions for clearing them? As the system is a multi-executable affair, each creating its own session and often running concurrently with others, I can't just DeleteFile() them as an individual executable terminates.

AFAIK there is no way to disable the old file creation.

Looping thru the temp directory and deleting them is still the best
option - these are backup files so once created there should be nothing
locking them or otherwise needing them.  You can just delete .old files
older than X minutes/hours for example based on your logic.

Other alternative i can think of is to have all your exes generate a
unique (random) subfolder in the temp folder for temp path and on exe
shutdown you can blow up the whole folder. Of course if the exe crashes
you now have to purge folders somehow.

Raul



On 6/25/2012 7:52 AM, Charles Bainbridge wrote:
> Our applications creates its own temporary tables (generating unique names using a one-up number register) in a local workstation filesystem/stLocal database. The tables are dropped as the app closes, but this is leaving numbers of .Old files for tables that will never be used again. Eventually, the local 'Temp' database folder is going to fill up with *thousands* of .Old files.
>
> Any suggestions for clearing them? As the system is a multi-executable affair, each creating its own session and often running concurrently with others, I can't just DeleteFile() them as an individual executable terminates.
>

I think the following JOB will do roughly what you want, note that my version leaves a small selection of the "OLD" files in case they are called for. You can remove this.

BEGIN
DECLARE Crsr CURSOR FOR Stmt;
DECLARE FileName VARCHAR(120);
DECLARE StoreName VARCHAR(120);

SET StoreName = 'YourStoreName';

EXECUTE IMMEDIATE 'SET FILES STORE TO "'+StoreName+'"';
PREPARE Stmt FROM
 'SELECT
  Name,
  CreatedOn
  FROM Configuration."Files"
  WHERE Name LIKE ''%.OLD%''
  AND CreatedOn < Current_Date - INTERVAL ''3'' DAY
  ORDER BY CreatedOn';

OPEN Crsr;
FETCH FIRST FROM Crsr('Name') INTO FileName;
WHILE NOT EOF(Crsr) DO
 IF NOT (FileName IS NULL) OR NOT (FileName='') THEN
   EXECUTE IMMEDIATE
   ' DELETE FILE "'+FileName+'" FROM STORE "'+StoreName+'" ';
   END IF;
 FETCH NEXT FROM Crsr('Name') INTO FileName;
END WHILE;
END

---

Best Wishes

Adam

Charles,

<< Any suggestions for clearing them? As the system is a multi-executable
affair, each creating its own session and often running concurrently with
others, I can't just DeleteFile() them as an individual executable
terminates. >>

Sure you can - just ignore any bad result from the DeleteFile() call.  If
the application can't delete the file, then it's in use and will get cleaned
up the next time the DeleteFile() run occurs.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Sure you can - just ignore any bad result from the DeleteFile() call.  If
the application can't delete the file, then it's in use and will get cleaned
up the next time the DeleteFile() run occurs.

Thanks Tim, I know just where I can implement that.