DBIDIRECTEXEC - Lets the SQL pass-through facility optimize handling of SQL statements by passing them directly to the database for execution. Our examples will show msglevel=i. This option will print a message stating that the statement was executed by the database (in this post - Oracle).
There is another option which turns this off - NODBIDIRECTEXEC.
Enabling DBIDIRECTEXEC can greatly increase the performance of your SAS. This is best shown using examples.
Let's setup the examples by creating a simple test table. We can use SASHELP.CARS for this.
1 libname myora oracle path=oraserv user=myuser password=XXXXXX;
NOTE: Libref MYORA was successfully assigned as follows:
Engine: ORACLE
Physical Name: exadata
2 /* create a table to play with */
3 data myora.cars;
4 set sashelp.cars;
5 run;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set MYORA.CARS has 428 observations and 15 variables.
NOTE: DATA statement used (Total process time):
real time 3.44 seconds
cpu time 0.09 seconds
CREATE TABLE AS SELECT Statement Example
For Oracle, NODBIDIRECTEXEC is the default. This means we will need to set DBIDIRECTEXEC. We will do this and run a CREATE TABLE AS SELECT test. The key here is to ensure that the entire CTAS statement is run in Oracle.
6
7 /* set dbidirectexec and ensure that we can see what is happening */
8 options sastrace=',,,d' sastraceloc=saslog nostsuffix;
9 options dbidirectexec sql_ip_trace=note msglevel=i;
10
11 /* create an empty table like the cars one */
12 /* this entire statement should be run in oracle - data should not pass through sas */
13 proc sql;
14 create table myora.cars_CTAS as select * from myora.cars;
ORACLE_1: Prepared: on connection 0
SELECT * FROM CARS_CTAS
ORACLE_2: Prepared: on connection 0
SELECT * FROM CARS
ORACLE_3: Executed: on connection 1
CREATE TABLE cars_CTAS as select TXT_1."MAKE", TXT_1."MODEL", TXT_1."TYPE", TXT_1."ORIGIN",
TXT_1."DRIVETRAIN", TXT_1."MSRP", TXT_1."INVOICE", TXT_1."ENGINESIZE", TXT_1."CYLINDERS",
TXT_1."HORSEPOWER", TXT_1."MPG_CITY", TXT_1."MPG_HIGHWAY", TXT_1."WEIGHT", TXT_1."WHEELBASE",
TXT_1."LENGTH" from CARS TXT_1
ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
ORACLE_4: Prepared: on connection 0
SELECT * FROM CARS_CTAS
SQL_IP_TRACE: The CREATE statement was passed to the DBMS.
15 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.76 seconds
cpu time 0.06 seconds
SCHEMA used: myuser
ORACLE_5: Prepared: on connection 1
SELECT OBJECT_NAME ,OBJECT_TYPE FROM USER_OBJECTS OBJ WHERE (OBJ.OBJECT_TYPE IN
('TABLE','VIEW'))
ORACLE_6: Executed: on connection 1
SELECT statement ORACLE_5
INSERT AS SELECT Statement Example
Next, let's take a look at an INSERT AS SELECT (IAS) statement. The setup for this test is to create an empty copy of the CARS table and then run the IAS statement. This statement should be run entirely in Oracle.
16 /* insert as select example */
17 /* need an empty table for the example - this should be handled entirely in oracle */
18 proc sql;
19 create table myora.cars_IAS as select * from myora.cars where 0=1;
ORACLE_7: Prepared: on connection 0
SELECT * FROM CARS_IAS
ORACLE_8: Prepared: on connection 0
SELECT * FROM CARS
ORACLE_9: Executed: on connection 1
CREATE TABLE cars_IAS as select TXT_1."MAKE", TXT_1."MODEL", TXT_1."TYPE", TXT_1."ORIGIN",
TXT_1."DRIVETRAIN", TXT_1."MSRP", TXT_1."INVOICE", TXT_1."ENGINESIZE", TXT_1."CYLINDERS",
TXT_1."HORSEPOWER", TXT_1."MPG_CITY", TXT_1."MPG_HIGHWAY", TXT_1."WEIGHT", TXT_1."WHEELBASE",
TXT_1."LENGTH" from CARS TXT_1 where 0 = 1
ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
ORACLE_10: Prepared: on connection 0
SELECT * FROM CARS_IAS
SQL_IP_TRACE: The CREATE statement was passed to the DBMS.
20 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
SCHEMA used: myuser
ORACLE_11: Prepared: on connection 1
SELECT OBJECT_NAME ,OBJECT_TYPE FROM USER_OBJECTS OBJ WHERE (OBJ.OBJECT_TYPE IN
('TABLE','VIEW'))
ORACLE_12: Executed: on connection 1
SELECT statement ORACLE_11
21
22 /* insert as select should be handled entirely in oracle */
23 /* the hint makes this a direct path load - think bulk load */
24 proc sql;
25 insert into myora.cars_IAS (ORHINTS='/*+ APPEND PARALLEL*/')
26 select * from myora.cars;
ORACLE_13: Prepared: on connection 0
SELECT /*+ APPEND PARALLEL*/ * FROM CARS_IAS
ORACLE_14: Prepared: on connection 0
SELECT * FROM CARS
ORACLE_15: Executed: on connection 1
insert /*+ APPEND PARALLEL*/ into CARS_IAS ("MAKE", "MODEL", "TYPE", "ORIGIN", "DRIVETRAIN",
"MSRP", "INVOICE", "ENGINESIZE", "CYLINDERS", "HORSEPOWER", "MPG_CITY", "MPG_HIGHWAY",
"WEIGHT", "WHEELBASE", "LENGTH") select TXT_2."MAKE", TXT_2."MODEL", TXT_2."TYPE",
TXT_2."ORIGIN", TXT_2."DRIVETRAIN", TXT_2."MSRP", TXT_2."INVOICE", TXT_2."ENGINESIZE",
TXT_2."CYLINDERS", TXT_2."HORSEPOWER", TXT_2."MPG_CITY", TXT_2."MPG_HIGHWAY", TXT_2."WEIGHT",
TXT_2."WHEELBASE", TXT_2."LENGTH" from CARS TXT_2
ORACLE: 428 rows inserted/updated/deleted.
ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
27 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 1.37 seconds
cpu time 0.06 seconds
UPDATE Statement Example
This example shows updating a subset of the data. Notice that you don't see a SQL_IP_TRACE message here.
28 /* update example */
29 /* the update statement should be passed entirely to oracle */
30 proc sql;
31 update myora.cars_IAS
32 set make='bogus'
33 where make='Audi';
ORACLE_16: Prepared: on connection 0
SELECT * FROM CARS_IAS
ORACLE_17: Executed: on connection 1
update CARS_IAS set CARS_IAS."MAKE" = 'bogus' where CARS_IAS."MAKE" = 'Audi'
ORACLE: 19 rows inserted/updated/deleted.
ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
34 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
DELETE Statement Example
This example shows deleting a subset of the data stored in the table.
35
36 /* delete example */
37 proc sql;
38 delete from myora.cars_IAS
39 where make='bogus';
ORACLE_18: Prepared: on connection 0
SELECT * FROM CARS_IAS
ORACLE_19: Executed: on connection 1
delete from cars_IAS where cars_IAS."MAKE" = 'bogus'
ORACLE: 19 rows inserted/updated/deleted.
ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
SQL_IP_TRACE: The DELETE was passed to the DBMS.
40 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
Delete the entire contents of the the table. It is important that this happen all in Oracle. That being said, there are better ways to do this in Oracle (TRUNCATE statement).
41 /* delete all the data in the table - but it doesn't reset the highwater mark */
42 /* explicit truncate command would be a better choice */
43 proc sql;
44 delete from myora.cars_IAS;
ORACLE_20: Prepared: on connection 0
SELECT * FROM CARS_IAS
ORACLE_21: Executed: on connection 1
delete from cars_IAS
ORACLE: 409 rows inserted/updated/deleted.
ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
SQL_IP_TRACE: The DELETE was passed to the DBMS.
45 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
NODBIDIRECTEXEC Example
Just for fun, let's look at an example where the statement is not passed to Oracle. The SQL_IP_TRACE message tells us that the statement was not passed to Oracle. In fact, SAS read the data and then passed it back to Oracle. We know this because of the litany of INSERT statements. This is not good. Take some time to compare this to the IAS statement that was handled entirely by Oracle.
46 /* nodbidirectexec - what happens? */
47
48 options nodbidirectexec;
49 proc sql;
50 insert into myora.cars_IAS (ORHINTS='/*+ APPEND PARALLEL*/')
51 select * from myora.cars;
WARNING: This SQL statement is not allowed to be passed directly to the DBMS for processing
because the DBIDIRECTEXEC system option is not turned on by the user or is
temporarily turned off by Proc SQL.
ORACLE_22: Prepared: on connection 2
SELECT /*+ APPEND PARALLEL*/ * FROM CARS_IAS
ORACLE_23: Prepared: on connection 0
SELECT * FROM CARS
SQL_IP_TRACE: None of the SQL was directly passed to the DBMS.
ORACLE_24: Executed: on connection 0
SELECT statement ORACLE_23
ORACLE_25: Prepared: on connection 2
INSERT /*+ APPEND PARALLEL*/ INTO CARS_IAS
("MAKE","MODEL","TYPE","ORIGIN","DRIVETRAIN","MSRP","INVOICE","ENGINESIZE","CYLINDERS","HORSEPO
WER","MPG_CITY","MPG_HIGHWAY","WEIGHT","WHEELBASE","LENGTH") VALUES
(:"MAKE",:"MODEL",:"TYPE",:"ORIGIN",:"DRIVETRAIN",:"MSRP",:"INVOICE",:"ENGINESIZE",:"CYLINDERS"
,:"HORSEPOWER",:"MPG_CITY",:"MPG_HIGHWAY",:"WEIGHT",:"WHEELBASE",:"LENGTH")
ORACLE_26: Executed: on connection 2
INSERT statement ORACLE_25
ORACLE_27: Executed: on connection 2
INSERT statement ORACLE_25
ORACLE_28: Executed: on connection 2
INSERT statement ORACLE_25
ORACLE_29: Executed: on connection 2
INSERT statement ORACLE_25
... snip
ORACLE_67: Executed: on connection 2
INSERT statement ORACLE_25
ORACLE_68: Executed: on connection 2
INSERT statement ORACLE_25
ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
NOTE: 428 rows were inserted into MYORA.CARS_IAS.
ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
52 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 3.34 seconds
cpu time 0.18 seconds
Here is the SAS code in case you want to try this...
libname myora oracle path=oraserv user=myuser password=mypassword;
/* create a table to play with */
data myora.cars;
set sashelp.cars;
run;
/* set dbidirectexec and ensure that we can see what is happening */
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options dbidirectexec sql_ip_trace=note msglevel=i;
/* create an empty table like the cars one */
/* this entire statement should be run in oracle - data should not pass through sas */
proc sql;
create table myora.cars_CTAS as select * from myora.cars;
quit;
/* insert as select example */
/* need an empty table for the example - this should be handled entirely in oracle */
proc sql;
create table myora.cars_IAS as select * from myora.cars where 0=1;
quit;
/* insert as select should be handled entirely in oracle */
/* the hint makes this a direct path load - think bulk load */
proc sql;
insert into myora.cars_IAS (ORHINTS='/*+ APPEND PARALLEL*/')
select * from myora.cars;
quit;
/* update example */
/* the update statement should be passed entirely to oracle */
proc sql;
update myora.cars_IAS
set make='bogus'
where make='Audi';
quit;
/* delete example */
proc sql;
delete from myora.cars_IAS
where make='bogus';
quit;
/* delete all the data in the table - but it doesn't reset the highwater mark */
/* explicit truncate command would be a better choice */
proc sql;
delete from myora.cars_IAS;
quit;
/* nodbidirectexec - what happens? */
/* This does not work properly;) */
options nodbidirectexec;
proc sql;
insert into myora.cars_IAS (ORHINTS='/*+ APPEND PARALLEL*/')
select * from myora.cars;
quit;
Interesting article. I do a lot of back and forth between Oracle and SAS, it took me a lot of time to learn most of the stuff you wrote up since it's not in one convenient place. I did notice you used the SAS option SQL_IP_TRACE, which I can't find very much documentation on from SAS's help or elsewhere. Can you point me to someplace that explains this option and the values it can take?
This is cool stuff. I guess this option is available for other RDBMS too.
Hi @kiranv_
It sure is. Check out: DBIDIRECTEXEC SAS System Option
Here is the list of supported data sources: Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, Impala, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Teradata, Vertica
DBIDIRECTEXEC is the default for Amazon Redshift
NODBIDIRECTEXEC is the default for all the others. That is why I say DBIDIRECTEXEC is like GOFAST=yes.
Hi @JBailey, did you ever write up that article on SQL_IP_TRACE? If so could you send me a link?
I am not sure whether the code will passed to DBMS, if we have SAS specific function.
Hi @kiranv_
It depends on if the SAS function will pass. It depends on which database you are using. This is covered in the doc.
Pay attention to SQL_FUNCTIONS=. Setting it to all increases the number of functions that can be passed to the DBMS.
The best way to tell if the function is going to pass is to use the following OPTIONS line to see what happens.
OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog NOSTSUFFIX;
Thanks for reply. I was thinking too it might depend on functions. Just completed your paper on same topic in SUGI 2017. By the way, I am huge fan of your papers. just want to say your papers have impacted my learning and indirectly they have impact on companies I have worked . Thank you sir.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.