topic Re: Implicit Sql Pass Through Behaviour in SAS in SAS Programming

Implicit Sql Pass Through Behaviour in SAS

pchegoor — Tue, 31 Jan 2017 21:01:50 GMT

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.

Re: Implicit Sql Pass Through Behaviour in SAS

Sven111 — Tue, 31 Jan 2017 22:32:27 GMT

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.

Re: Implicit Sql Pass Through Behaviour in SAS

pchegoor — Wed, 01 Feb 2017 20:00:16 GMT

@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.

Re: Implicit Sql Pass Through Behaviour in SAS

SASKiwi — Wed, 01 Feb 2017 20:04:44 GMT

I suggest that SAS Tech Support would be your best option to progress this.

Re: Implicit Sql Pass Through Behaviour in SAS

pchegoor — Thu, 02 Feb 2017 01:46:38 GMT

@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?

Re: Implicit Sql Pass Through Behaviour in SAS

SASKiwi — Thu, 02 Feb 2017 00:39:45 GMT

@pchegoor.That's a shame. My recent experiences with Tech Support are quite the opposite. My perception is they have improved significantly.

Re: Implicit Sql Pass Through Behaviour in SAS

nkm123 — Mon, 24 Apr 2017 22:25:59 GMT

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.