Date: Thu, 23 Dec 2010 20:35:26 +0100
Reply-To: Allen Ziegenfus <aziegenfus@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Allen Ziegenfus <aziegenfus@GMAIL.COM>
Subject: Re: Stored Process execution question (answer)
In-Reply-To: <201012211851.oBLIFKjD015536@wasabi.cc.uga.edu>
Content-Type: text/plain; charset=us-ascii
Hi Suzanne,
I am not sure exactly what you are looking for, but I was able to whip up some code that changes the execution location for a Stored Process - no guarantees on the error handling here though.
1. First we have to find out what servers are available and how to address them. The following data step produces a file with all Workspace and Stored Process servers with their URIs. You will have to add in the details for the authentication (metauser etc.)
option metauser=xxxxxx
metapass=xxxxxxx
metaserver="xxxxxxx"
metaport=8561;
data logical_servers;
length uri name publictype $256;
nobj=1;
n=1;
do while(nobj >= 0);
nobj=metadata_getnobj("omsobj:LogicalServer?LogicalServer",n,uri);
rc=metadata_getattr(uri,"Name",name);
rc=metadata_getattr(uri,"PublicType",publictype);
if nobj > 0 and publictype in ("LogicalServer.Workspace","LogicalServer.StoredProcess")then do;
put uri= name=;
output;
end;
n=n+1;
end;
run;
2. Next, pick which server should be used for the Stored Process execution. You need the URI value for this server - it should look something likeOMSOBJ:LogicalServer\A5HMEL8K.AU00000I
3. The following two macros will query and change the execution server for the Stored Process. You can use them like this:
%query_server(name of stored process)
%change_server(name of stored process, uri of server)
For example:
%query_server(test_stp)
%change_server(test_stp,OMSOBJ:LogicalServer\A5HMEL8K.AU00000H)
%query_server(test_stp)
%macro query_server(stp_name);
data _null_;
length name uri queryuri $256;
name =""; uri="";
queryuri=cats("omsobj:ClassifierMap?@Name=", "'", "&stp_name","'");
rcqry=metadata_getnasn(queryuri,"ComputeLocations", 1, uri);
rc=metadata_getattr(uri,"Name",name);
put rcqry= uri= rc= name=;
run;
%mend;
%macrochange_server(stp_name,uri);
data _null_;
length queryuri $256;
queryuri=cats("omsobj:ClassifierMap?@Name=", "'", "&stp_name","'");
rc=metadata_setassn(queryuri,"ComputeLocations", "Replace","&uri");
put rc=;
run;
%mend;
Allen Ziegenfus
Am 21.12.2010 um 19:51 schrieb Suzanne McCoy <suzanne.mccoy@CATALINAMARKETING.COM>:
> Hi Suzanne,
>
> You asked:
>
> What we need to know is: can 1 SP, regardless of where it is registered
> in metadata, route the work that needs to be done to a different physical
> server to execute via the logical workspace server that is defined to that
> physical server?
>
> The short answer is NO. The execution server defined in metadata for a
> stored process cannot be redirected "on the fly". You can %include other
> stored process code (just the .sas program, no metadata info applies) in a
> stored process, but as you suspected will always run within the confines
> of the execution server defined in metadata for the parent stored process
> (where the %include was submitted). You can chain stored processes
> together, with different execution servers defined, but they would still
> be separate stored processes.
> http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/viewer
> .htm#datapass.htm
>
> I hope that sufficiently answers your question.
>
> Best regards,
> SAS Tech Support
>
>
> On Mon, 20 Dec 2010 18:34:14 -0500, Suzanne McCoy
> <suzanne.mccoy@CATALINAMARKETING.COM> wrote:
>
>> I already have this question as a track with SAS Tech Support. If anyone
>> on the list has input I would love to hear it. For those of you that are
>> not using the BI or EBI suite in version 9.2(TS2(M3) AIX 64 bit) this may
>> or may not be of interest but definitely does not apply.
>> ~ Suzanne
>>
>>
>> My question to Tech Support:
>> This is going to be a saga so please bear with me. We haven't done this
>> installation yet so this question is installation conceptual versus
>> physical.
>>
>> We have a 6 server architecture design. It consists of 1 physical
>> metadata server, 1 physical mid-tier server and 4 physical compute
>> servers. The user interface executes via a stored process. That stored
>> process must be registered in metadata to allow streaming output to
>> _webout, which requires that it be registered to 1 physical server and
>> therefore 1 stored process server in metadata.
>>
>> Is there anyway to force the macros being executed via the 1 stored
>> process to execute on a different physical workspace server? My take is
>> that the stored process server and the workspace server are physically
>> defined when the spplication server is defined in metadata and that the
>> stored process that is running can only execute on the stored process
>> server that was selected when the SP is registered in metadata.
>>
>> We are looking at this with SAS Grid Manager as part of the mix. None of
>> the data is in a SAS dataset, always in Netezza or Oracle with saswork
>> used only at a minimum. The databases are accessed via explicit pass-thru
>> (execute (xyz)) by Oracle or by Netezza. Even with grid manager, aren't
>> we limited to the SP server as registered in SAS Metadata?
>>
>> We know we can register 4 stored process servers and based on a round-
>> robin interface up front, route different users to different stored
>> process servers. What we need to know is: can 1 SP, regardless of where
>> it is registered in metadata, route the work that needs to be done to a
>> different physical server to execute via the logical workspace server that
>> is defined to that physical server?
>>
>> We are supposed to install this architecture in January, 2011 so your
>> timely response will be greatly appreciated.
>
