08-14-2017 11:54 PM
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.
08-15-2017 12:25 AM
See the DB specific docs.
Things to note - functions that don't get passed through and tables that are local vs server.
08-15-2017 04:31 AM
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.
08-15-2017 02:18 AM
08-15-2017 08:23 AM
@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.
08-15-2017 09:11 AM
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!).