DATA Step, Macro, Functions and more

Implicit Sql Pass Through Behaviour in SAS

Reply
Frequent Contributor
Posts: 86

Implicit Sql Pass Through Behaviour in SAS

[ Edited ]

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 :

 

 Image1.png

 

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 :

 

Image2.png

 

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 :

 

Image3.png

 

 

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.

Frequent Contributor
Posts: 82

Re: Implicit Sql Pass Through Behaviour in SAS

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.

Frequent Contributor
Posts: 86

Re: Implicit Sql Pass Through Behaviour in SAS

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

Super User
Posts: 3,102

Re: Implicit Sql Pass Through Behaviour in SAS

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

Frequent Contributor
Posts: 86

Re: Implicit Sql Pass Through Behaviour in SAS

[ Edited ]

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

 

Image4.PNG

 

 

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?

Super User
Posts: 3,102

Re: Implicit Sql Pass Through Behaviour in SAS

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

Contributor
Posts: 30

Re: Implicit Sql Pass Through Behaviour in SAS

[ Edited ]

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.

Ask a Question
Discussion stats
  • 6 replies
  • 322 views
  • 0 likes
  • 4 in conversation