Date:         Tue, 28 Sep 2010 11:18:46 -0400
Reply-To:     Proc Me <procme@CONCEPT-DELIVERY.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Proc Me <procme@CONCEPT-DELIVERY.COM>
Subject:      Re: Monitor a spooling file

Richard,

Thanks for replying to this.

The issue is that I don't want to wait until the Proc SQL step has finished:
the query execution time is about 2 hours, and spool time a further 2 hours.
I want to kick off a second job when the database's resources are released
at the end of query execution. This would allow me to run my second query in
parallel with the spooling of the first, I don't want the database to run it
in parallel with execution of the first, and I'd like to avoid waiting two
hours whilst the data spools.

The code posted earlier in this thread works fine, ..., except in the
environment where I want to use it, which is a Windows 2k3 server running a
9.2 spawner as a Windows service with the noxcmd option. In vain I tried,
stopping and restarting the spawner with the allowxcmd option, but suspect I
may need to uninstall and reinstall the spawner service.

I have come up with the solution below, using modulen(). It relies heavily
on some of the Windows API code that you have posted on your website. I
think this is a temporary fix: I read a not of yours on my travels that
suggests that modulen() will be secured in the near future.

Stylists who object to links may wish to avert their eyes, I think that in
this instance the link structure helps reveal what is happening, but that
may just be a matter of taste.

So, Richard, thank you very much for scattering over the interweb all of the
pieces I needed to create a solution,

Proc Me

*--- GetFileAttributesExA.sas ---;
filename SASCBTBL 'work.windows.api.source';

data _null_;
  file SASCBTBL;
  input;
  put _infile_;
cards4;
*-------------------------------------------------------------;

routine GetFileAttributesExA
   module=kernel32
   minarg=8
   maxarg=8
   stackpop=called
   returns=long
;
arg 1 input char         format=$cstr200. ;      * lpFileName;
arg 2 input num  fdstart format=pib4. byvalue ;  * fInfoLevelId (should be 0);
                                         * lpFileInformation;
arg 3 num output FDSTART format=pib4. ;  * dwFileAttributes ;
arg 4 num output         format=pib8. ;  * ftCreationTime;
arg 5 num output         format=pib8. ;  * ftLastAccessTime;
arg 6 num output         format=pib8. ;  * ftLastWriteTime;
arg 7 num output         format=pib4. ;  * nFileSizeHigh;
arg 8 num output         format=pib4. ;  * nFileSizeLow;
;;;;
run;

*--- monitor.sas ---;
/* Parameters */
%let monitor_file   = test.txt; /* File to monitor                */
%let num_times      = 3;        /* Number of times to check file  */
%let snooze         = 60;       /* Seconds to wait between checks */
%let size_threshold = 1000000;  /* File size threshold in bytes   */

/* Load Win API routine */
%include 'GetFileAttributesExA.sas';

data _null_;

   link parms;      /* Log parameters                  */
   link init;       /* Initialize variables            */
   /* Run for num_times, unless threshold exceeded     */
   do _n_ = 1 to &num_times until(filesize gt &size_threshold);
      link getter;  /* Get file info                   */
      link logger;  /* Update log                      */
      link sleeper; /* Sleep if threshold not exceeded */
   end;
   stop;            /* Done                            */

parms:
   putlog "NOTE: File to monitor is &monitor_file";
   putlog "NOTE: Monitoring: check the file &num_times times, every &snooze
seconds, until the file size exceeds &size_threshold bytes.";
   return;

init:
   length myFilename $270;
   myFilename = "&monitor_file";
   GetFileExInfoStandard = 0;
   fileAttributes = 0;
   creationTime = 0;
   lastAccessTime = 0;
   lastWriteTime = 0;
   nFileSizeHigh = 0;
   nFileSizeLow = 0;
   filesize = 0;
   return;

looper:
   return;

getter:
   rc = modulen ('GetFileAttributesExA', myFilename, GetFileExInfoStandard
               , fileAttributes
               , creationTime
               , lastAccessTime
               , lastWriteTime
               , nFileSizeHigh
               , nFileSizeLow
               );
   filesize = nFileSizeLow + nFileSizeHigh * 2**32;
   return;

logger:
   t = put(time(), time11.2);
   putlog 'NOTE: Test ' _n_ '- File size:  ' filesize:comma12. 'bytes';
   putlog 'NOTE: Time stamp: ' t;
   return;

sleeper:
   if filesize lt &size_threshold then call sleep(&snooze,1);
   return;

run;