topic Run oracle procedure in parallel in sas macro in SAS Programming

Run oracle procedure in parallel in sas macro

MG18 — Wed, 20 Jun 2018 06:26:01 GMT

Hello!

Is it possible to transform this sql statement to be carried out in parallel? so each oracle procedure will execute differently on same server ,Please suggest way.

%MACRO LOAD_DATA_TO_LAN;

PROC SQL;
CONNECT TO ORACLE(USER="xxxx" PASSWORD="xxxx" PATH="&lan_pt");
EXECUTE(EXECUTE INC_load_LAN_ASSOCIATE_DIM) by ORACLE;
EXECUTE(EXECUTE INC_LAN_ACCOUNT_DIM_LOAD) by ORACLE;
EXECUTE(EXECUTE FIN_INC_LAN_PARTY_DIM_LOAD) by ORACLE;
EXECUTE(EXECUTE INC_LOAD_PARTY_ACCOUNT_BRIDGE) by ORACLE;
EXECUTE(EXECUTE INC_LOAD_FSC_TRANCTION_DIM) by ORACLE;
EXECUTE(EXECUTE INC_LOAD_FSC_CASH_FLOW_FACT) by ORACLE;
EXECUTE(EXECUTE INC_LOAD_LAN_EOD_TRANSACTIONS) by ORACLE;

DISCONNECT FROM ORACLE;
QUIT;

%MEND LOAD_DATA_TO_LAN;

%LOAD_DATA_TO_LAN;

many thanks in advance.

Re: Run oracle procedure in parallel in sas macro

LinusH — Wed, 20 Jun 2018 06:58:15 GMT

I don't exactly how PROC SQL is working in this regard, but my guess would be that it waits after each statement to finish and get the return code.

On way would to use MP CONNECT, and have each EXECUTE to done in it's own RSUBMIT and PROC SQL block.

Re: Run oracle procedure in parallel in sas macro

RW9 — Wed, 20 Jun 2018 07:47:38 GMT

I AM GOING TO WRITE MY REPLY LIKE THIS AS SHOUTING IS FINE IN CODE.

THIS:

%MACRO LOAD_DATA_TO_LAN;

%MEND LOAD_DATA_TO_LAN;

%LOAD_DATA_TO_LAN;

dOES ABSOLUTELY NOTHING HERE, THERE IS NO GENERAL CODE TO NEED A MACRO.

Ok, I think that is point made about coding all in uppercase, its bad and makes reading harder.

These:

EXECUTE(EXECUTE INC_load_LAN_ASSOCIATE_DIM) by ORACLE;

simply run an oracle procedure in the database. So the question is why these need to be sent from SAS? Can the database itself not be setup to run these, its a bit like having someone else hold your car key and getting them to come round and start your car each time, no value in it.

Drop this part totally and speak with the oracle admin to get this setup on the database, scheduled most likely.

Re: Run oracle procedure in parallel in sas macro

MG18 — Wed, 20 Jun 2018 09:20:45 GMT

HI ,

I understand your concern but till i want to keep this code as it is .

Re: Run oracle procedure in parallel in sas macro

MG18 — Wed, 20 Jun 2018 09:22:36 GMT

Hi All ,

Without macro below mentioned code is running in parallel with rsubmit option .

options autosignon=yes sascmd="sas";

rsubmit task1 wait=no sysrputsync=yes;
PROC SQL;
CONNECT TO ORACLE(USER="AMLLAND" PASSWORD="amlland" PATH="DPDCNUFSASDB:1521/sasut");
EXECUTE(EXECUTE INC_load_LAN_ASSOCIATE_DIM) by ORACLE;
DISCONNECT FROM ORACLE;
QUIT;
%sysrput pathtask1=%sysfunc(pathname(work));
endrsubmit;
rsubmit task2 wait=no sysrputsync=yes;
PROC SQL;
CONNECT TO ORACLE(USER="AMLLAND" PASSWORD="amlland" PATH="DPDCNUFSASDB:1521/sasut");
EXECUTE(EXECUTE INC_LAN_ACCOUNT_DIM_LOAD) by ORACLE;
DISCONNECT FROM ORACLE;
QUIT;
%sysrput pathtask2=%sysfunc(pathname(work));
endrsubmit;

rsubmit task3 wait=no sysrputsync=yes;
PROC SQL;
CONNECT TO ORACLE(USER="AMLLAND" PASSWORD="amlland" PATH="DPDCNUFSASDB:1521/sasut");
EXECUTE(EXECUTE FIN_INC_LAN_PARTY_DIM_LOAD) by ORACLE;
DISCONNECT FROM ORACLE;
QUIT;
%sysrput pathtask2=%sysfunc(pathname(work));
endrsubmit;

rsubmit task4 wait=no sysrputsync=yes;
PROC SQL;
CONNECT TO ORACLE(USER="AMLLAND" PASSWORD="amlland" PATH="DPDCNUFSASDB:1521/sasut");
EXECUTE(EXECUTE INC_LOAD_PARTY_ACCOUNT_BRIDGE) by ORACLE;
DISCONNECT FROM ORACLE;
QUIT;
%sysrput pathtask2=%sysfunc(pathname(work));
endrsubmit;

rsubmit task5 wait=no sysrputsync=yes;
PROC SQL;
CONNECT TO ORACLE(USER="AMLLAND" PASSWORD="amlland" PATH="DPDCNUFSASDB:1521/sasut");
EXECUTE(EXECUTE INC_LOAD_FSC_TRANCTION_DIM) by ORACLE;
DISCONNECT FROM ORACLE;
QUIT;
%sysrput pathtask2=%sysfunc(pathname(work));
endrsubmit;

rsubmit task6 wait=no sysrputsync=yes;
PROC SQL;
CONNECT TO ORACLE(USER="AMLLAND" PASSWORD="amlland" PATH="DPDCNUFSASDB:1521/sasut");
EXECUTE(EXECUTE INC_LOAD_FSC_CASH_FLOW_FACT) by ORACLE;
DISCONNECT FROM ORACLE;
QUIT;
%sysrput pathtask6=%sysfunc(pathname(work));
endrsubmit;

rsubmit task7 wait=no sysrputsync=yes;
PROC SQL;
CONNECT TO ORACLE(USER="AMLLAND" PASSWORD="amlland" PATH="DPDCNUFSASDB:1521/sasut");
EXECUTE(EXECUTE INC_LOAD_LAN_EOD_TRANSACTIONS) by ORACLE;
DISCONNECT FROM ORACLE;
QUIT;
%sysrput pathtask7=%sysfunc(pathname(work));
endrsubmit;

signoff task1;
signoff task2;
signoff task3;
signoff task4;
signoff task5;
signoff task6;
signoff task7;

We want to use the rsumbit option inside the macro is it possible ?

Many thanks in advnaces.

Re: Run oracle procedure in parallel in sas macro

RW9 — Wed, 20 Jun 2018 09:31:04 GMT

Yes, it should be:

http://support.sas.com/kb/23/986.html

Macro does nothing itself, just generates some plain text, which then goes in to the SAS compiler. Only note there appears to be macro variables need to be on the end machine, see the note above.

Re: Run oracle procedure in parallel in sas macro

LinusH — Wed, 20 Jun 2018 10:18:28 GMT

Looking at your initial code, macro is unnecessary.

Looking at your new code, macro could actually make your code a lot simpler, avoiding repeating loads of statements.

Re: Run oracle procedure in parallel in sas macro

MG18 — Wed, 20 Jun 2018 10:57:14 GMT

hi all ,

we are ok with above running code without macro also but we are facing below problem too .

this above code is getting used in one of thejob (with LOAD_LAN_DATA_test.sas file ) and which is getting called as below :-

/sasconf/Lev1/SASApp/BatchServer/sasbatch.sh -autoexec "/sasaml/sascodes/amlpreprocessor.sas" -log /sasaml/sascodes/AJ/Batch_load/Lan_date_load_test_#Y.#m.#d_#H.#M.#s.log -batch -noterminal -logparm "rollover=session" -sysin /sasaml/sascodes/AJ/Batch_load/LOAD_LAN_DATA_test.sas

since above code is running individually correct but failing when it is getting called in above command with below mentioned error :-

NOTE: AUTOEXEC processing completed.

1 options autosignon=yes sascmd="sas";
2 rsubmit task1 wait=no sysrputsync=yes;
NOTE: Remote signon to TASK1 commencing (SAS Release 9.03.01M2P081512).
ERROR: Execution of the Command 'sas -DMR -SET SASCLIENTPORT 1 -SET SASDAEMONPORT 42947 -SET SASDAEMONHOST localhost -SET INHERIT 0
-NOTERMINAL -noobjectserver -objectserverparms "''"' failed.
ERROR: The specified executable module either cannot be located or it is not a valid executable.
ERROR: A communication subsystem partner link setup request failure has occurred.
ERROR: Cannot start remote process.
ERROR: Command could not be executed.
NOTE: Subsequent lines will be ignored until ENDRSUBMIT.

What could be the reason for this ?? how can we solve this problem ?

Re: Run oracle procedure in parallel in sas macro

Kurt_Bremser — Wed, 20 Jun 2018 13:50:58 GMT

Looks like the target server does not have the correct path to the SAS executable in its PATH. Specify the fully qualified pathname for sas.

Edit: if you are starting lots of processes in parallel, you might overrun a per-user limit for processes set by the system.

Re: Run oracle procedure in parallel in sas macro

MG18 — Thu, 21 Jun 2018 05:55:27 GMT

HI ,

How to check the correct path to the SAS executable in its PATH and how can i make changes in it ?

whether i need to change my code or server configuration ? Please advice/

Re: Run oracle procedure in parallel in sas macro

Kurt_Bremser — Mon, 25 Jun 2018 12:44:47 GMT

@MG18 wrote:

HI ,

How to check the correct path to the SAS executable in its PATH and how can i make changes in it ?

whether i need to change my code or server configuration ? Please advice/

Log on to the server that is the target of the remote logon via SAS/CONNECT, and issue

which sas

there. This will reveal the absolute path to the SAS executable, if the current user has it in its PATH environment variable.

Take this whole fully qualified path name and use it in the sascmd= option.