BookmarkSubscribeRSS Feed
kiranv_
Rhodochrosite | Level 12

 

Hi,

I am trying to understand, when implicit sql pass through for Teradata is used, what type of queries or SAS/functions that are not passed to Teradata(queries with remerging statistics are not passed to teradata). I have read paper by Howard Plemmons https://www.mwsug.org/proceedings/2015/BB/MWSUG-2015-BB-03.pdf

and paper by Fred Levine http://www2.sas.com/proceedings/sugi26/p110-26.pdf and also paper by @JBailey http://www.scsug.org/wp-content/uploads/2013/11/The-SQL-Tuning-Checklist-Jeff-Bailey-SAS.pdf and also looked into SAS/Access for relation databases http://support.sas.com/documentation/cdl/en/acreldb/69580/PDF/default/acreldb.pdf

 

All this articles inform  me that distinct, upcase and joins are usually passed to Teradata to name a few. This list of queries sent to teradata is too small. I also know that by using options sastrace=',,,d' nostsuffix, we can find whether a query has been passed to teradata or not. 

 

I would like to know whether there is comprehensive list that tells us which string or numeric function that are not passsed to teradata. Does regular expressions in SAS are passed to teradata.

 

Is this information available anywhere or is it we need to try out each scenario.(I also understand that there are lot of SAS statistical functions that cannot be  mapped to Teradata).

 

Please let me know your expert opinions.

 

Thanks

 

6 REPLIES 6
Reeza
Super User

https://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p0lpu4mxo0lkrcn17...

 

See the DB specific docs. 

 

Things to note - functions that don't get passed through and tables that are local vs server. 

kiranv_
Rhodochrosite | Level 12

Thank you so much @Reeza. Do you know whether regular expressions are passed or not.

LinusH
Tourmaline | Level 20

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p0lpu4mxo0lkrcn17i...

I would assume that a function that is not on this list will not passed down to Teradata.

Teradata do seem to have regular expression functions, but I'm not the one to tell whether they matches the functionality of the SAS ones. 

Ans as already been said, sastrace (and msglevel=i) will tel what gets passed and not.

Data never sleeps
AndrewHowell
Moderator
Without wishing to appear self-promoting, have a look at a SAS Global Forum paper I presented a couple of years ago on the SASTRACE option:

https://support.sas.com/resources/papers/proceedings15/3269-2015.pdf

I can't specifically state whether or not a particular expression will be passed through to the database (in your case Teradata) but using the SASTRACE option will reveal the underlying SQL statement(s) passed through to the database.

Best of luck.
kiranv_
Rhodochrosite | Level 12

@AndrewHowell thank you,  that was good paper. Do you know what type of queries that are generally not passed to DBMS. Just looking for some benchmarking.

 

Does any of datastep constructs that are passed to DBMS.

LinusH
Tourmaline | Level 20

Not as part of a normal SAS/ACCESS connection. With in Database licence  I think DS2 steps can be executed within the target database.

But, BY and WHERE statements are passed regardless of the surrounding step type (PROC, data step or SQL). BY will trigger source DBMS sorting. WHERE filtering will take place if syntax can be evaluated by the source (function and formats...). I think that DROP and KEEP will be part of the implicit SQL (sastrace!).

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1082 views
  • 4 likes
  • 4 in conversation