topic Re: Implicit pass through--which queries are send/not send to Teradata in SAS Data Management

Implicit pass through--which queries are send/not send to Teradata

kiranv_ — Tue, 15 Aug 2017 03:54:37 GMT

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

Re: Implicit pass through--which queries are send/not send to Teradata

Reeza — Tue, 15 Aug 2017 04:25:54 GMT

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

See the DB specific docs.

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

Re: Implicit pass through--which queries are send/not send to Teradata

kiranv_ — Tue, 15 Aug 2017 04:41:47 GMT

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

Re: Implicit pass through--which queries are send/not send to Teradata

AndrewHowell — Tue, 15 Aug 2017 06:18:44 GMT

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.

Re: Implicit pass through--which queries are send/not send to Teradata

LinusH — Tue, 15 Aug 2017 08:31:58 GMT

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

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.

Re: Implicit pass through--which queries are send/not send to Teradata

kiranv_ — Tue, 15 Aug 2017 12:23:59 GMT

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

Re: Implicit pass through--which queries are send/not send to Teradata

LinusH — Tue, 15 Aug 2017 13:11:46 GMT

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!).