topic Re: Submitting a long running job in batch mode from a stored process in Developers

Submitting a long running job in batch mode from a stored process

ScottBass — Thu, 03 May 2012 11:26:19 GMT

Hi,

We submit our stored processes via the Stored Process Web Application. My particular stored process is using prompts to provide a user interface to the user. However, since the actual processing can take a long time (30-60 minutes) to run, the "heavy lifting" is done by a SAS job running in batch. The actual results will be emailed back to the user as a PDF attachment.

Since the code isn't that long, I've included it below. What I'd like to know is if this is best practice, or if there is a better approach? In particular, the documentation says:

_ACTION

Used By

Web Clients

Description

Specifies an action for the Web application to take. Possible values for this variable are as follows:

BACKGROUND
executes the stored process in the background.

Can someone explain what this means exactly? How is "background" different than "not background"? The documentation doesn't make this clear.

Here's the code:

Stored Process (the prompts are dynamic lists based on SASHELP.SHOES for Region, Subsidiary, and Product):

%STPBEGIN;

OPTIONS VALIDVARNAME=ANY;

*  End EG generated code (do not edit this line);

/*
Set all necessary macro variables.
If they are macro variables that you want sent to the batch job,
best practice is to create them all with the same prefix.
I am using the prefix "batch_".
Otherwise you need to change the PROC SQL code below.
*/

* macro variables sent to the batch job ;
%let batch_username = &_username;
%let batch_jobname  = Test SAS Batch Job;

/*
Create a Stored Process (STP) session, which allocates the temporary SAVE library.
This library is used to pass parameters between the STP and the batch job.

While we are creating the STP session, also create the confirmation message.
Note that the "email when the job finishes" must be generated by the batch job,
not this Stored Process.
*/

data message;
  * session timeout is in seconds - 2 minutes should be plenty ;
  if _n_ = 1 then do;
    if libref('SAVE') ne 0 then do;
      rc = stpsrv_session('create',2*60);
    end;
  end;
  infile datalines truncover;
  input message $200.;
  keep message;
  datalines4;
Job &batch_jobname submitted at %sysfunc(strip(%sysfunc(datetime(),datetime18.))).
You will receive an email when the job finishes.
;;;;
run;

libname save list;

/*
This macro variable must be set AFTER the session is created.
*/

%let batch_save_library=%sysfunc(pathname(save));

/*
Now copy the desired parameters (macro variables) to the SAVE library.
This dataset will be used by the batch job to recreate the macro variables.
*/
proc sql noprint;
  create table save.parameters as
    select
      name
      ,value
    from
      dictionary.macros
    where
      name like 'BATCH_%'
  ;
quit;

/*
Setup the parameters for invoking SAS, then launch the SAS job.
You MUST pass in the path to the SAVE library location via sysparm.

My preference is to use a configuration file, rather than command line options,
with ALL other job configuration (eg. -sysin) done in the configuration file.
This gives the maintainer of the code ONE place to go to change the batch job options.
However, you can configure this however you wish.
*/
%let sas=C:\SAS\Config\Lev1\SASApp\sas.bat;
%let config=&batch_config_file;  /* if you have created a hidden STP parameter,
                                    otherwise hard code this here */
%let sysparm=&batch_save_library;
%let options=;

* invoke the SAS batch job ;
systask command "&sas -config &config -sysparm ""&sysparm"" &options" nowait;

/*
Now display the confirmation message to the user.
The options nomerror and noserror suppress warning messages in the log
for unresolved macro and unresolved macro variable references.
HTML code in particular can have tokens (eg. &program=...) that look like macro variables
but are HTML tokens instead.
*/
%let options=%sysfunc(getoption(merror,keyword)) %sysfunc(getoption(serror,keyword));
options nomerror noserror;
data message;
  set message;
  message=resolve(message);
run;
options &options;

title;
options nonumber nodate;
proc report data=message nowd noheader center ls=96 spacing=0;
  columns message;
  define  message / width=96 " ";
run;

*  Begin EG generated code (do not edit this line);
;*';*";*/;quit;
%STPEND;

*  End EG generated code (do not edit this line);

And here's a sample SAS batch job:

/*
Allocate the SAVE library passed in via sysparm
*/
libname save "%scan(&sysparm,1,%str(^|))";




/*
Set macro variables passed in to the batch job by the Stored Process
*/
data _null_;
   set save.parameters;
   call symputx(name,value,"G");
run;




/*
The rest of your program goes here...
You would process the macro variables just like you would in a stored process.
*/




* Print macro variables ;
options nocenter;
proc sql;
  select
    name,value
  from
    dictionary.macros
  where
    name like 'BATCH_%' and scope="GLOBAL"
  order by
    name
  ;
quit;




* Print SASHELP.SHOES filtered on input parameters ;
* Note:  %stp_seplist processes the STP parms, creating a separated list ;

options mprint;
%macro stp;
  %local where;
  %let where=%sysfunc(sum(&batch_region_count,&batch_product_count,&batch_subsidiary_count));
  options noserror nomerror;
  data subset;
    set sashelp.shoes;
    %if (&where) %then %do;
      where 1
    %end;
    %if (&batch_region_count) %then %do;
      and region in (%stp_seplist(batch_region))
    %end;
    %if (&batch_product_count) %then %do;
      and product in (%stp_seplist(batch_product))
    %end;
    %if (&batch_subsidiary_count) %then %do;
      and subsidiary in (%stp_seplist(batch_subsidiary))
    %end;
    %if (&where) %then %do;
      ;
    %end;
  run;
  options serror merror;


  proc print;
  run;
%mend;
%stp




/*
Send email that the job has finished.
*/




* Create message dataset ;
data mail;
   length parm $15 line $1000;
   infile datalines dsd dlm="|" truncover;
   input parm line;
   datalines4;
| Your SAS job &batch_jobname has completed with return code rc=&sysrc..
;;;;
run;


%sendmail(to=&batch_username,subject=%str(Job &batch_jobname has completed),content_type=text/html)

Re: Submitting a long running job in batch mode from a stored process

Bago — Thu, 03 May 2012 11:37:44 GMT

Hey Scott,

Did you have a look at Usage Note 39114: Tips for running a SAS® Stored Process in Background mode using SAS® 9.2 ? The Note explains what running in "background" means.

Kind regards,

Bago

Re: Submitting a long running job in batch mode from a stored process

ScottBass — Thu, 03 May 2012 11:48:58 GMT

Thanks Bago! I'm experienced in SAS programming but less so with Stored Processes. All Usage Notes, SGF's papers, useful links, etc. are more than welcome!!!

Re: Submitting a long running job in batch mode from a stored process

ScottBass — Thu, 03 May 2012 12:04:36 GMT

I've reviewed the Usage Note above. Note that we don't have the complete EBI installation. So, we don't have the Information Delivery Portal, Web Report Studio, or Information Map Studio. We do have the Stored Process Web Application and WebDAV (although we haven't been using WebDAV at all).

This URL does work though:

http://myserver.com:8080/SASContentServer/repository/default/sasdav/Users/sbass/

although there is no content 😉

Re: Submitting a long running job in batch mode from a stored process

SASBIROX — Thu, 03 May 2012 12:40:54 GMT

Scott:

When the code runs in the background - it allows the user to break away from the Web Browser session. Else if you started running the stored process and closed the browser -- then your STP stops. Long running STPs obviously creates a great chance that the user will close the browser without realizing what he/she did.

I'm blogging about STP topics at Business Intelligience Notes for SAS BI Users and have co-authored a one-spot reference book about learning Stored Processes that discussing topics such as this one.

Tricia

Re: Submitting a long running job in batch mode from a stored process

ScottBass — Thu, 03 May 2012 13:13:56 GMT

Hi,

I'm happy to buy the book, as long as it's appropriate to my environment - no IDP, WRS, IMS, AMO, etc. SPWA and WebDAV (Jack Rabbit) only.

Thanks,

Scott

Re: Submitting a long running job in batch mode from a stored process

Quentin — Thu, 03 May 2012 13:27:11 GMT

Hi Scott,

I too am in the process of learning about stored processes after years of writing SAS code. So I'm going to respond to your question with more questions, and a few suggestions.

My main question is why you chose to set off a batch job? Perhaps only because the job runs a long time? (And agree, you might be better off looking into other background processing options).

I hadn't seen people using stored procedures to kick off batch jobs, so curious to what others think of this approach? On the one hand, it's a tempting approach to me. Because as a newbie to stored processes, I would once again feel confident that I could control things like autoexec, config.sas, etc., without worrying about how these are set by an administrator. I would also get a nice log file back, without needing to use proc printto or looking for server logs.

But on the other hand, relying on kicking off a new SAS session feels a little bit like running home to what feels comfortable as a SAS programmer. Rather than pushing for new approaches as a SAS developer. And I can see down side to the approach, e.g. time to start up a new session, and more importantly by-passing of meta data security in the the new session (cuz it knows not of the meta data, right?). Will look forward to seeing what those with more experience have to say.

For resources I've found handy, I would have found Both of Tricia Aanderud and Angela Hall's books helpful (Building BI Using SAS and 50 Keys to Learning SAS Stored Processes). If you have to choose one, I would get the Stored Process book since you don't have the full EBI setup. It's great. Works through nice examples. I see Tricia already recommended her Blog. I've also found Don Henderson's blog to be a great resource: http://hcsbi.blogspot.com. I just ordered Don's book, Building Web Application with SAS/Intrnet. While it's a few years old, a friend of mind recommended it, and much of the content/logic/process is the same whether you are using the new BI tools / stored procedures or writing your own code. And from seeing Don's blog, and SAS commuity postings, and responses to my questions here in the forums, I'm sure it won't disappoint.

HTH,

--Q.

Re: Submitting a long running job in batch mode from a stored process

ScottBass — Fri, 04 May 2012 10:30:59 GMT

Hi,

I've thought about this a bit more, and wanted to know if the details of background processing are documented anywhere? For example, does it spawn a new process under the sassrv credentials? Can I change the text returned to the user when I launch a background STP?

Also, if I launch a batch process myself as documented above, and close my browser, will it kill the batch session (I suppose I could just try this...)

Thanks,

Scott

Re: Submitting a long running job in batch mode from a stored process

ScottBass — Fri, 04 May 2012 10:42:14 GMT

Hi Quentin,

I'm used to this model in other web based products. The main one that comes to mind is PeopleSoft. The web interface allows you to select a task, set some options, and submit it asynchronously. You can then use another web interface to monitor the status of running tasks.

The builtin background processing looks promising, now that I have a better understanding of what it does. The SAS documentation is a bit "sparse" in this regard. But hey, that's what usage notes are for, to fill in all the holes in the doc 😉 Anyway, no sense reinventing the wheel if it's already perfectly round. But I would love to know the details between the builtin background processing vs. my approach as documented above.

The batch SAS job is started with metaautoresource "SASApp", so it will have the same libname settings as an EG workspace server session. AFAIK, it will honor metadata security, although this isn't really a concern in my scenario.

I'll probably order the 50 Keys to Learning SAS Stored Processes book once Tricia gets back to me and confirms that it's suitable to my environment.

Regards,

Scott

Re: Submitting a long running job in batch mode from a stored process

SASBIROX — Fri, 25 May 2012 23:31:34 GMT

Scott:

Here's a link to the site where you can download the table of contents:

http://www.bi-notes.com/the-50-keys-to-learning-sas-stored-processes/

Two of the chapters discuss using stored processes with the BI tools and the web. The other information would be useful to learning how to write stored processes and giving you some ideas of the many ways these can be used.

Tricia

Re: Submitting a long running job in batch mode from a stored process

thesasgeek — Thu, 12 Jul 2012 12:45:45 GMT

This background processing method is very useful for me in an web application that I'm building as previously my browser wasn't showing it had finished processing.

Whilst the adding the _action=background to the URL to get the stored process working in the background, how can I know if it has error?

I'm rather dubious of setting something up without a method of being able to flag errors.

There seems to be a mention of the 'Stored Process Alerts Portlet' but when I artificially trigger an error, nothing is flagged in it.

In addition, as I currently open a new page through a link to call the stored process (e.g. target=_blank), is there a way of calling the stored process and then closing the window?

Re: Submitting a long running job in batch mode from a stored process

ScottBass — Fri, 13 Jul 2012 00:17:13 GMT

Hi,

Since my original post, I've progressed this project to its conclusion, including a couple SAS TS tracks, and can provide more comments. These comments also address some of Quentin's questions above.

I did continue with my approach, i.e. STP submitting a secondary SAS batch job, rather than reworking the STP for background processing. Some of the reasons:

1) I have more control over the spawned SAS session: output log location (rather than having the very long SAS log buried within the STP log), memory allocation for the batch SAS process, I can run it under another metadata userid, etc.

2) I have more control over the message displayed to the user. I wanted more than just "Your job <whatever> has been submitted for background processing". In particular, I wanted to let me know to expect an email with an attachment. If SAS made it easier to customize the message returned from a background STP, on a per-STP basis, I would have considered the builtin background processing more strongly.

3) If something was completely haywire with the job, it's easier to kill the process via Process Explorer, rather than bringing down the entire STP server (I have RDP access to the remote SAS server).

I learned (via SAS TS) that, when the STP server starts, an authentication token gets created to authenticate to the metadata server. However, when the STP server launched the SAS batch process (with the metaautoresource option), this authentication token for the metadata server was expired. I was getting an invalid credentials message in the SAS batch session, and none of my pre-assigned libraries were being allocated. The solution is to invoke the SAS batch process with the -metauser and -metapass options.

I had a few issues with quoting in building the SAS invocation command, esp. quoting such that macro parameters would be expanded. See the attached stp_batch_submit macro.

My STPs had a hidden parameter batch_config_file with the path to a SAS config file, which contained all the invocation options for the batch job. The STPs were streaming, not package, since I'm displaying a confirmation message back to the user.

The STP code itself is quite simple:

%* resolve embedded %sysget function call ;

%let batch_config_file=%unquote(&batch_config_file);

%stp_batch_submit(

config=&batch_config_file

,mvars=name like 'BATCH_%' or name like '_ODS%'

/* ,messagefile=C:\SAS\Config\%sysget(lev)\SASApp\SASEnvironment\SASCode\Saspgms\sample_stp.html */

);

Of course, all the heavy lifting is done in the %stp_batch_submit macro.

@sasgeek, regarding your question "...is there a way of calling the stored process and then closing the window", you can do so with javascript. In my code, I'm displaying a javascript alert, then closing the window. I wouldn't want to close the window immediately, or the user wouldn't see the message! See the stp_batch_submit macro for example code. Note: the javascript code has slightly different characteristics depending on browser (IE, Chrome, Firefox).

Finally, I wrote some macros to help with this process, which I've attached to this post. Wherever you see "parmv", substitute your own macro parameter validation code.

HTH,

Scott

Re: Submitting a long running job in batch mode from a stored process

Quentin — Fri, 13 Jul 2012 14:53:20 GMT

Thanks for posting, Scott! This will be fun weekend reading. You should consider developing it into a user-group paper.

A couple questions (that might be obvious once I dive in).

1. If the stored process in running on stored process server (as SASSRV), I assume the spawned session also runs as SASSRV? Is there a Unix- way to spawn a SAS session and have it run as somebody else? I would love to have the sesion run as the actual user, rather than SASSRV. (I know you can specify a different metadata identify for the spawned session).

2. So when you spawn the session, I see where you are specifying the metadata identity in stp_batch_submit.sas:

%* We need to specify the user credentials for the spawned SAS job :
%let parms=&parms -metauser someuser -metapass {sas002}somepassword;

/*Invoke the SAS batch job.*/
systask command " ""&sas"" &parms " nowait;

Are you hard coding -metauser and (encoded) -metapass?

Or if you want to just pass these along from whoever called the stored process, can you do that somehow? (I see SPWA creates &_METAUSER but obviously not &_METAPASS)

Obviously users could enter a password in a prompt, but ot sure you would really want to have users enter their password into a stored process prompt, where it could be easily revealed...

Good stuff, thanks for sharing your results!

-Q.

Re: Submitting a long running job in batch mode from a stored process

Quentin — Fri, 13 Jul 2012 14:58:14 GMT

Hi SASGeek,

I think the best way to flag errors in SAS is to scan the log. There are lots of NOTES/WARNINGS that I consider errors which SAS does not. Tricia Aanderud was kind enough to let me write a guest post-on her blog suggesting an approach to automated log-scanning in a stored process setting.

http://www.bi-notes.com/2012/07/stored-process-ignore-log-your-own-peril/

If you take a look, please let me know if you have any thoughts/suggestions.

--Q.

Re: Submitting a long running job in batch mode from a stored process

ScottBass — Mon, 16 Jul 2012 01:00:42 GMT

Hi Quentin,

1. If the stored process is running on stored process server (as SASSRV), I assume the spawned session also runs as SASSRV? Is there a Unix- way to spawn a SAS session and have it run as somebody else? I would love to have the sesion run as the actual user, rather than SASSRV. (I know you can specify a different metadata identify for the spawned session).

Yes the spawned session runs as sassrv, but I changed the metadata userid to be our production user so the STP would have metadata access to all required libraries. This was fine for my needs.

For the Unix question, see the "su" and "sudo" commands. However, you'd still need a password to switch to another user, unless you were running the su command as root. Running the STP process as root (or the equivalent) would open all sorts of security issues and I'm not recommending it. I would have a chat with your Unix sysadmin / security person if you want to do this.

However, if you're on SAS 9.3, IIRC SAS 9.3 has added streaming output to the workspace server. So, you could have your STP run from a workspace server. You'd have a slight performance hit as the workspace server started, but it would run under the userid who logged in to the SPWA. I think this is correct; I recall reading this in a "What's new in SAS 9.3" document. If this does work as I think it would (I'm still on SAS 9.2 so can't test it), that's what I would do if it were critical that the STP run under the credentials of the logged in user.

2. So when you spawn the session, I see where you are specifying the metadata identity in stp_batch_submit.sas:
%* We need to specify the user credentials for the spawned SAS job :
%let parms=&parms -metauser someuser -metapass {sas002}somepassword;
/*Invoke the SAS batch job.*/
systask command " ""&sas"" &parms " nowait;

Are you hard coding -metauser and  (encoded) -metapass?
Or if you want to just pass these along from whoever called the stored process, can you do that somehow? (I see SPWA creates &_METAUSER but obviously not &_METAPASS)
Obviously users could enter a password in a prompt, but ot sure you would really want to have users enter their password into a stored process prompt, where it could be easily revealed...

I hardcoded the metauser and metapass in the macro, but I didn't have to do so. In my environment, this does not pose a security risk. However, if it did pose a risk, I would move these settings to the SAS configuration file, which is created by the developer of the STP (which in my case is me). Or, the caller of the macro could add them as options. In either case, the STP author would need to know the password of the specified metadata identity.

See http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/input.html (scroll to the bottom) for information on securing sensitive information such as passwords. So, other than the hassle of requiring the user to enter his/her password in a prompt (say to feed to the sudo command), you should be able to do this in SAS in a secure manner.

HTH,

Scott

Re: Submitting a long running job in batch mode from a stored process

Quentin — Mon, 16 Jul 2012 12:34:46 GMT

Thanks Scott,

Yah, the ability to have workspace server send streaming results is what has me most excited about 9.3. It will solve a lot of my security concerns. The password masking stuff is neat (thanks for the link), but I think in my setting, any little ad-hoc app that collected passwords might raise eyebrows. Hopefully we'll get 9.3 set up soon.

--Q.

Re: Submitting a long running job in batch mode from a stored process

Quentin — Mon, 16 Jul 2012 20:51:10 GMT

Actually, Looks like I was wrong about 9.3 being the answer to my prayers.

From:

http://support.sas.com/documentation/cdl/en/stpug/62758/HTML/default/viewer.htm#stpugwhatsnew93.htm

Looks like the Workspace Server can't replay images. Very unfortunate.

The SAS Workspace Server supports stored processes with streaming output, except stored processes that use sessions or replay (such as embedded images in streaming output).

It also mentions a global macro var _METAPASS. Does that mean that when a user logs into SPWA, their password would be saved in &_METAPASS (in an encrypted or at least encoded form?).

Reserved global macro variables _ARCHIVE_PATH, _ARCHIVE_NAME, and _GENERATED_NAME have been added for publishing to WebDAV and Sharepoint. The _DEBUG_FILE reserved macro variable has been added for publishing to Sharepoint. Reserved global macro variables _FOLDER_PATH, _METAPASS, and _METAUSER have been added for publishing to subscribers. For more information, see Using Reserved Macro Variables

--Q.

Re: Submitting a long running job in batch mode from a stored process

Yura2301 — Wed, 19 Dec 2012 16:02:56 GMT

Hi Scott,

I’ve just briefly read all posts higher and I probably has easier solution for this task.

So recently I had very similar task that you had, initially I implemented it using only STP sessions only:

One STP session executes macro that runs for a few hours, another session – observe log file that this macro generates all this long time, and in the end, when log file become not in use by first STP session - second STP session parse this log file and send needed info to needed person via sms/email etc.

But during testing I’ve found serious issue of such approach – these two STP sessions locked two sas processes that manages all another STPs, that impact STP response time, it become bigger that is not acceptable.

Then I tried implement variant similar to your approach, but quickly refuse it because of it include changing sessions config files that has hardcoded some servers names etc. , and also probably such approach will be hard maintain , support and promote between servers.

So I decided join “batch processing” with “STP possibilities” to implement the task.

From STP I just create one table(or file, it’s not important) on some server path.

Then I created small flow via SAS management console, this flow has one file event, - it waits until the file(or table) created by STP arrive on specific path on server.

After this file arrives – Flow runs.

Flow has two jobs, first job run this long time executed macro, in the end this job delete this table(file) created by STP.

After this job finished successfully or fails - second jobs starts , this job executes parsing logs generated after first job executing, and send needed info to needed person via sms/mail etc.

Off course there are a lot of validations etc things . that I didn’t mentioned in this post, but shortly idea is from STP creates only some table(file) on specific place on server and create small and simple flow that waits for this file occurs(arrive) on this place.

As I said higher flow can be easy created and scheduled via SMC, and testing flow can be done from flow manager, that probably should be installed on your machine, if not - it’s really easy install and configure it.

Benefits of such solution obviously – no need change any config files(session macros etc.) – easy maintain and support etc.

Minus is the fact that should be run some kind of demon( in my case it’s flow that waits for file arrives) that all time observe some directory on server, but I’m even not sure it is the minus, because it’s usual approach in scheduling process.

Hope this my post will help you a little bit if you would like change smth. In you actual functionaliti

Thanks!

Re: Submitting a long running job in batch mode from a stored process

Quentin — Fri, 21 Dec 2012 14:35:30 GMT

THanks for sharing, Yura.

I had been playing with scheduled flows in SMC, but hadn't realized I could kick of an event based on the arrival of a file in a directory. Nifty approach to the problem.

--Q.

Re: Submitting a long running job in batch mode from a stored process

Yura2301 — Fri, 21 Dec 2012 15:01:40 GMT

Hi,

Actually it's very eazy realize event on file: arrives, exists, have some specific size...

Via SMC or Flow editor you should just choose "file event" and then choose needed value from dropdown(arrive, exist etc.) and put the path to needed file on Server.

Thanks!