proc sql prallel processing unsing rsubmit

[Asbon]

Hi, 

Need help with below coding.

can we connect oracle server using rsubmit miltiple time for prallel processing. or is there any code enhancement required

*  Parallel Processing *;

options sascmd="sas";

*  Prosess 1 *;

signon task1;

rsubmit task1 wait=no;

proc sql;

connect to oracle (user="&id" orapw="&pw" path="@path" preserve_comments);

create table aaa as

select * from connection to oracle

(SELECT * from abc);

disconnect from oracle;

quit;

endrsubmit;

*  Prosess 2 *;

signon task2;

rsubmit task2 wait=no;

proc sql;

connect to oracle (user="&id" orapw="&pw" path="@path" preserve_comments);

create table bbb as

select * from connection to oracle

(select * fro xyz);

disconnect from oracle;

quit;

endrsubmit;

*  Prosess 3 *;

signon task3;

rsubmit task3 wait=no;

proc sql;

connect to oracle (user="&id" orapw="&pw" path="@path" preserve_comments);

create table ccc as

select * from connection to oracle

(select * from www );

disconnect from oracle;

quit;

endrsubmit;

waitfor _all_;

signoff _all_;

help is appreciated.

Thanks.

[SASKiwi]

That looks about right. Have you tried it?

[Asbon]

below is the error that i'm getting.
macro variables are not getting resolved. with in sql passthrough.
if it one rsubmit it's working fine.

NOTE: Remote submit to TASK1 commencing.
1 proc sql;
2 connect to oracle (user="&id" orapw="&pw" path="@path"
2 ! preserve_comments);
WARNING: Apparent symbolic reference id not resolved.
WARNING: Apparent symbolic reference pw not resolved.
WARNING: Apparent symbolic reference id not resolved.
WARNING: Apparent symbolic reference pw not resolved.
ERROR: ORACLE connection error: ORA-01017: invalid username/password; logon
denied.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.

[sbxkoenk]

Base SAS + SAS/CONNECT - A simple method to generate load on any number of licensed cores
Posted 04-08-2021 05:46 AM | by SimonWilliams

https://communities.sas.com/t5/SAS-Communities-Library/Base-SAS-SAS-CONNECT-A-simple-method-to-gener...

But the below blog is MUCH easier to understand :

Running SAS programs in parallel using SAS/CONNECT®

By Leonid Batkhan on SAS Users January 13, 2021

https://blogs.sas.com/content/sgf/2021/01/13/running-sas-programs-in-parallel-using-sas-connect/

Koen

[Patrick]

That looks about right. Just be aware that the rsubmit blocks run in their own SAS session and though any work table you create is in WORK of the child session. 

You need to either load such a WORK table to the parent WORK or easier write it to a disk location that's also directly accessible by the parent session.

[Asbon]

below is the error that i'm getting.
macro variables are not getting resolved. with in sql passthrough.
if it one rsubmit it's working fine.

NOTE: Remote submit to TASK1 commencing.
1 proc sql;
2 connect to oracle (user="&id" orapw="&pw" path="@path"
2 ! preserve_comments);
WARNING: Apparent symbolic reference id not resolved.
WARNING: Apparent symbolic reference pw not resolved.
WARNING: Apparent symbolic reference id not resolved.
WARNING: Apparent symbolic reference pw not resolved.
ERROR: ORACLE connection error: ORA-01017: invalid username/password; logon
denied.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.

[SASKiwi]

Looks like your macro variables ID and PW are not defined in all SAS sessions. You can define them once in the parent session, then do this to push them to the other sessions:

%let ID = MyID;
%let PW = MyPW;

signon task1;

%syslput _all_ / remote = task1;