BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mjf_unt
Calcite | Level 5

I've got a general question on the order of execution with regards to the generated PROC SQL in an Oracle Bulk Loader transformation. While I have examined the generated code in question, I am no expert at the base code and would prefer a better explanation if possible.

 

Here's what I'm trying to understand: I'm looking to use the bulk load transformation with the "Drop and recreated indexes" option enabled. Furthermore, I'm considering adding a snippet of code to the Pre-code portion of the transformation.

 

What will be the order in which these items execute? Will the DROP INDEX execute first, prior to any other actions in the code? Or does the Pre-code section take precedent?

1 ACCEPTED SOLUTION

Accepted Solutions
clwatson
SAS Employee

@mjf_unt Any code provided and enabled in the Precode and Postcode will come before (Precode) and after (Postcode) the "core" code of the transformation.  The "core" code of the transformation in this case is the Oracle Load Technique settings.  This rule is true for all DI Studio transforms.  In your scenario:

clwatson_1-1616417908792.png

That code then gets generated before the core transform code, which for the Oracle Bulk Table Loader is the settings on the Load Technique tab.

Here's some bits of the excerpts from my pictured example:

/*==========================================================================*
* Step: Oracle Bulk Table Loader A5RMRMLZ.$0000079 *
* Transform: Oracle Bulk Table Loader *
* Description: *
* *
* Source Table: CARS - SASHELP.CARS A5RMRMLZ.BK00002M *
* Target Table: CARS - oracle.CARS A5RMRMLZ.BK000002 *
*==========================================================================*/
.....
%let SYSLAST = %nrquote(SASHELP."CARS"n);

%let ETLS_SYSLAST = &SYSLAST;
/*---- Start of Pre-Process Code ----*/
data _null_;
put 'This is precode for transformation';
run;
/*---- End of Pre-Process Code ----*/

%rcSet(&syserr);
%rcSet(&sqlrc);

%let SYSLAST = &ETLS_SYSLAST;

%global etls_tableExist;
...
%let etls_lastTable = &SYSLAST;

/*---- Define load data macro ----*/

%macro etls_loader;

I've bolded the pre-code area and then the start of the "core" Oracle Bulk Loader Transformation.

So to answer your question, the pre-code specified on the Precode and Postcode tab will take precedence over any "core" functionality of DI transformations.

 

 

View solution in original post

1 REPLY 1
clwatson
SAS Employee

@mjf_unt Any code provided and enabled in the Precode and Postcode will come before (Precode) and after (Postcode) the "core" code of the transformation.  The "core" code of the transformation in this case is the Oracle Load Technique settings.  This rule is true for all DI Studio transforms.  In your scenario:

clwatson_1-1616417908792.png

That code then gets generated before the core transform code, which for the Oracle Bulk Table Loader is the settings on the Load Technique tab.

Here's some bits of the excerpts from my pictured example:

/*==========================================================================*
* Step: Oracle Bulk Table Loader A5RMRMLZ.$0000079 *
* Transform: Oracle Bulk Table Loader *
* Description: *
* *
* Source Table: CARS - SASHELP.CARS A5RMRMLZ.BK00002M *
* Target Table: CARS - oracle.CARS A5RMRMLZ.BK000002 *
*==========================================================================*/
.....
%let SYSLAST = %nrquote(SASHELP."CARS"n);

%let ETLS_SYSLAST = &SYSLAST;
/*---- Start of Pre-Process Code ----*/
data _null_;
put 'This is precode for transformation';
run;
/*---- End of Pre-Process Code ----*/

%rcSet(&syserr);
%rcSet(&sqlrc);

%let SYSLAST = &ETLS_SYSLAST;

%global etls_tableExist;
...
%let etls_lastTable = &SYSLAST;

/*---- Define load data macro ----*/

%macro etls_loader;

I've bolded the pre-code area and then the start of the "core" Oracle Bulk Loader Transformation.

So to answer your question, the pre-code specified on the Precode and Postcode tab will take precedence over any "core" functionality of DI transformations.

 

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 608 views
  • 1 like
  • 2 in conversation