The Table Loader code generator ignores any Oracle hints in a SELECT statement for an input table that uses an SQL query


SAS® Data Integration Studio 4.905 does not observe any Oracle hints (or ORHINTS) for an input table that uses the Table Loader transformation with an SQL query:

However, the code generator does observe the ORHINTS option for the output table:

The relevant code in the log file looks like the following:

proc sql ;

/* insert new records into the controller table */
insert into oralib.T7613516934_OUT1
(ORHINTS = "/* + PARALLEL (T7613516934_OUT1 ,2) */",
Name, Sex, Age, Height, Weight)
select
Name, Sex, Age, Height, Weight
from &etls_lastTable
;

quit;

However, the code should look like the following:

proc sql ;

/* insert new records into the controller table */
insert into oralib.T7613516934_OUT1
(ORHINTS = "/* + PARALLEL (T7613516934_OUT2 ,2) */",
Name, Sex, Age, Height, Weight)
select
Name, Sex, Age, Height, Weight
from &etls_lastTable (&etls_tableOptions)
;

quit;

In the automatically created code, the ORHINTS option is properly assigned to the macro variable %etls_tableOptions. However, the option is not used in the INSERT statement:

%macro etls_loader;
%let etls_tableOptions = %nrquote(ORHINTS = "/* + PARALLEL (T7613516934_IN ,2) */");

The only workaround is to select User written body for the Code generation mode setting of the Table Loader properties for the transformation:

Then append (&etls_tableOptions) to from &etls_lastTable within the INSERT statement.