I have a couple of Issues I am observing regarding Implicit Sql Passthrough in SAS.
1) The Option NOIPASSTHRU on the PROC SQL Statement does not always DISABLE Implicit Pass Through.
2) Sometimes even though the Sql query is passed to the RDBMS for Processing there is no Message in the Log suggesting this has occured.
I would like to demonstrate the above using a Volatile Table in Teradata loaded with some Sample Data.
Here is the Code :
option debug=DBMS_SELECT sastrace=',,,d'
sastraceloc=saslog no$stsuffix fullstimer ;
Title;
Libname _ALL_ Clear;
/* Set global connection for all tables. */
libname VOLTBL teradata user="Test" password="Test123" server="T_DEV" connection=global;
/* Create a volatile tables */
proc sql;
connect to teradata(user="Test" password="Test123" server="T_DEV" connection=global);
execute (CREATE VOLATILE TABLE TEMP (PAT_ID CHAR(1),
Amt INT)
ON COMMIT PRESERVE ROWS) by teradata;
execute (COMMIT WORK) by teradata;
Disconnect from Teradata;
quit;
/* Insert data into one of the above table */
Proc sql;
connect to teradata(user="Test" password="Test123" server="T_DEV" connection=global);
execute (INSERT INTO TEMP VALUES('A',100)) by teradata;
execute (INSERT INTO TEMP VALUES('B',200)) by teradata;
execute (INSERT INTO TEMP VALUES('C',300)) by teradata;
execute (INSERT INTO TEMP VALUES('D',400)) by teradata;
execute (INSERT INTO TEMP VALUES('E',500)) by teradata;
execute (INSERT INTO TEMP VALUES('',600)) by teradata;
execute (INSERT INTO TEMP VALUES(NULL,700)) by teradata;
execute (INSERT INTO TEMP VALUES('F',800)) by teradata;
execute (INSERT INTO TEMP VALUES(NULL,NULL)) by teradata;
execute (COMMIT WORK) by teradata;
Disconnect from Teradata;
Quit;
Now that the Sample Data has been Created i want to proceed with the above 2 Issues i am observing .
First I want to demonstrate the 2nd Issue using the below Code :
Title1 '***** Result when generated Sql is passed to Teradata for Processing *****';
Proc Sql;
Select * from VOLTBL.TEMP where PAT_ID is NULL;
Quit;
This is the Result :
This is the SAS Log :
66
67
68 Title1 '***** Result when generated Sql is passed to Teradata for Processing *****';
69
70 Proc Sql;
71
72
73 Select * from VOLTBL.TEMP where PAT_ID is NULL;
TERADATA_12: Prepared: on connection 0
SELECT * FROM "TEMP"
4 The SAS System 12:40 Tuesday, January 31, 2017
TERADATA: SELECT * FROM "TEMP"
TERADATA: trqacol- No casting. Raw row size=5, Casted size=9, CAST_OVERHEAD_MAXPERCENT=20%
TERADATA_13: Prepared: on connection 0
SELECT "PAT_ID","Amt" FROM "TEMP" WHERE ("PAT_ID" IS NULL )
TERADATA: SELECT "PAT_ID","Amt" FROM "TEMP" WHERE ("PAT_ID" IS NULL )
TERADATA_14: Executed: on connection 0
SELECT "PAT_ID","Amt" FROM "TEMP" WHERE ("PAT_ID" IS NULL )
TERADATA: trget - rows to fetch: 2
TERADATA: trforc: COMMIT WORK
74
75 Quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
user cpu time 0.01 seconds
system cpu time 0.01 seconds
memory 5293.21k
OS Memory 24484.00k
Timestamp 01/31/2017 12:40:58 PM
Step Count 31 Switch Count 74
Page Faults 0
Page Reclaims 58
Page Swaps 0
Voluntary Context Switches 250
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0
Now as you see from the above Log there is No Message suggesting that the Sql Query is being sent To Teradata for Processing.
I was expecting to see Messages like this :
DEBUG: SQL Implicit Passthru stmt has been prepared successfully.
DEBUG: SQL Implicit Passthru stmt used for fetching data.
ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.
I know the Query was sent to Teradata for Processing because ONLY the 2 records where the value of PAT_ID is NULL is returned which is what one would expect.
Question : So why are the above Messages not printed in the SAS Log? Does this mean it is sometimes not printed even though it is an Implicit Sql Through?
Now going back to the 1st Issue . I am using the following Code .This time i have the Option NOIPASSTHRU in the PROC SQL Statement.
Title1 '***** Result when generated Sql is passed to Teradata for Processing using NOIPASSTHRU Option *****';
Proc Sql NOIPASSTHRU;
Select * from VOLTBL.TEMP where PAT_ID is NULL;
Quit;
This is the Result :
Here is the SAS Log :
78 Title1 '***** Result when generated Sql is passed to Teradata for Processing using NOIPASSTHRU Option *****';
79
80 Proc Sql NOIPASSTHRU;
81
82
83 Select * from VOLTBL.TEMP where PAT_ID is NULL;
TERADATA_15: Prepared: on connection 0
SELECT * FROM "TEMP"
TERADATA: SELECT * FROM "TEMP"
TERADATA: trqacol- No casting. Raw row size=5, Casted size=9, CAST_OVERHEAD_MAXPERCENT=20%
TERADATA_16: Prepared: on connection 0
SELECT "PAT_ID","Amt" FROM "TEMP" WHERE ("PAT_ID" IS NULL )
TERADATA: SELECT "PAT_ID","Amt" FROM "TEMP" WHERE ("PAT_ID" IS NULL )
TERADATA_17: Executed: on connection 0
SELECT "PAT_ID","Amt" FROM "TEMP" WHERE ("PAT_ID" IS NULL )
TERADATA: trget - rows to fetch: 2
TERADATA: trforc: COMMIT WORK
84
5 The SAS System 12:40 Tuesday, January 31, 2017
85 Quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 5278.93k
OS Memory 24740.00k
Timestamp 01/31/2017 12:40:58 PM
Step Count 32 Switch Count 74
Page Faults 0
Page Reclaims 40
Page Swaps 0
Voluntary Context Switches 249
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
Now if you observe the presence of the NOIPASSTHRU Option does not make a difference ie the Implicit Pass through is not disabled and as consequence the query is again sent to Teradata for processing resulting again in the Same Result as in the Previous Code without this Option.
Question : So does the Option NOIPASSTHRU not always work as expected? If yes, then when does it work?
Now I want to demonstrate a Final Scenario where I Force the Processing to take in the SAS Session instead of in Teradata. This is what i would have expected with the NOIPASSTHRU Option as shown above .
Here is Code :
libname VOLTBL1 teradata user="test" password="test123" server="T_DEV" connection=global direct_sql=nowhere;
Title1 '***** Result when generated Sql is passed to Teradata for Processing using DIRECT_SQL=NOWHERE Option *****';
Proc Sql;
Select * from VOLTBL1.TEMP where PAT_ID is NULL;
Quit;
Here is the Result :
Here is the SAS Log :
24 libname VOLTBL1 teradata user="test" password=XXXXXX server="T_DEV" connection=global direct_sql=nowhere;
NOTE: Libref VOLTBL1 was successfully assigned as follows:
Engine: TERADATA
Physical Name: "T_DEV"
25
26 Title1 '***** Result when generated Sql is passed to Teradata for Processing using DIRECT_SQL=NOWHERE Option *****';
27
28 Proc Sql;
29
30
31 Select * from VOLTBL1.TEMP where PAT_ID is NULL;
TERADATA_21: Prepared: on connection 0
SELECT * FROM "TEMP"
TERADATA: SELECT * FROM "TEMP"
ACCESS ENGINE: The value of the DIRECT_SQL libname option is set to NOWHERE. The WHERE clause will not be passed to the DBMS for
processing.
TERADATA: trqacol- No casting. Raw row size=5, Casted size=9, CAST_OVERHEAD_MAXPERCENT=20%
TERADATA_22: Prepared: on connection 0
SELECT "PAT_ID","Amt" FROM "TEMP"
TERADATA: SELECT "PAT_ID","Amt" FROM "TEMP"
TERADATA_23: Executed: on connection 0
SELECT "PAT_ID","Amt" FROM "TEMP"
TERADATA: trget - rows to fetch: 9
TERADATA: trforc: COMMIT WORK
32
33 Quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 5277.93k
OS Memory 24484.00k
Timestamp 01/31/2017 01:33:24 PM
Step Count 34 Switch Count 78
Page Faults 0
Page Reclaims 135
Page Swaps 0
Voluntary Context Switches 268
Involuntary Context Switches 4
Block Input Operations 0
Block Output Operations 0
Now as seen above you see that there is Message in the Log suggesting the Query was not passed to Teradata but instead the processing occured in the SAS Session :
ACCESS ENGINE: The value of the DIRECT_SQL libname option is set to NOWHERE. The WHERE clause will not be passed to the DBMS for processing.
Also if you see the Result now has 3 records ie 2 Records where the PAT_ID is NULL and one additional Record where PAT_ID= ' ' and since in SAS both a NULL value and a missing value are the treated the same we thus have 3 Records in the Result.
Can someone please shed some light on the above Behaviour of Implicit Passthrough in SAS? I want to make sure i understand the Behaviour well.
Thanks.
Not sure about your main question, but when I was reading your code it reminded me of something I read last week while troubleshooting some SQL passthrough issues, which is that SAS handles requests to temporary/volatile tables differently than regular tables in some cases. Not sure if that's what's going on here, but thought it may be worth looking into, or at least trying your code using permanent tables to see if the behavior is any different.
@Sven111 When i use Implicit Sql against a Regular Teradata View/Table i do see the following Message :
DEBUG: SQL Implicit Passthru stmt has been prepared successfully.
DEBUG: SQL Implicit Passthru stmt used for fetching data.
ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.
But then again the Option NOIPASSTHRU does not work to disable the Implicit Pass Through . In this case though i do not see the above Messages in the Log .
Very Strange to see such a Behaviour.
I Just wish the Messages were consistent even with a Volatile Table and also the Option NOIPASSTHRU would work as expected.
I suggest that SAS Tech Support would be your best option to progress this.
@SASKiwi SAS Tech Support very Often do not answer such questions in a satisfactory Manner and i feel that oflate the Support standard has diminished and therefore for such questions i usually come to such Forums where i can find seasoned SAS professionals who may provide more Insight into such Questions .
One thing I do see is if the Run the following code with the keyword distinct in the PROC SQL Query:
Title1 '***** Result when generated Sql is passed to Teradata for Processing using distinct keyword *****';
Proc Sql;
Select distinct * from VOLTBL.TEMP where PAT_ID is NULL;
Quit;
I do see the Implicit Passthrough Message in the SAS Log as highlighted below.
DEBUG: SQL Implicit Passthru stmt has been prepared successfully.
DEBUG: SQL Implicit Passthru stmt used for fetching data.
ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.
67 Title1 '***** Result when generated Sql is passed to Teradata for Processing using distinct keyword *****';
68
69 Proc Sql;
70
71 Select distinct * from VOLTBL.TEMP where PAT_ID is NULL;
TERADATA_12: Prepared: on connection 0
SELECT * FROM "TEMP"
TERADATA: SELECT * FROM "TEMP"
TERADATA_13: Prepared: on connection 0
select distinct TXT_1."PAT_ID", TXT_1."Amt" from "TEMP" TXT_1 where TXT_1."PAT_ID" is null
TERADATA: select distinct TXT_1."PAT_ID", TXT_1."Amt" from "TEMP" TXT_1 where TXT_1."PAT_ID" is null
DEBUG: SQL Implicit Passthru stmt has been prepared successfully.
TERADATA: trforc: COMMIT WORK
DEBUG: SQL Implicit Passthru stmt used for fetching data.
ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.
TERADATA_14: Executed: on connection 0
select distinct TXT_1."PAT_ID", TXT_1."Amt" from "TEMP" TXT_1 where TXT_1."PAT_ID" is null
TERADATA: trget - rows to fetch: 2
TERADATA: trforc: COMMIT WORK
72 Quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 5279.00k
OS Memory 25252.00k
Timestamp 02/01/2017 05:58:51 PM
Step Count 138 Switch Count 72
Page Faults 0
Page Reclaims 145
Page Swaps 0
Voluntary Context Switches 244
Involuntary Context Switches 3
Block Input Operations 0
Block Output Operations 0
The Result from above is as expected :
From the above behaviour we can infer that the presence of distinct keyword causes the Implicit Passthrough Message to be printed in the SAS Log . The Output Result is still the same as without this keyword.
Question: Does this Mean that without the Distinct keyword the sql query is still passed to the Teradata even though there is no explicit messages stating this has occured ? Or does this mean that without the distinct the Sql query is passed to the Teradata by some other means other than Implicit Passthrough?
@pchegoor.That's a shame. My recent experiences with Tech Support are quite the opposite. My perception is they have improved significantly.
did you get any reply back from SAS? I have same question as I think that message is not printed in log even implicit pass through happened but just want to sure.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.