I am trying to assist a customer to improve some very large extracts from Teradata to SAS (9.4) by using TPT/Export.
They need to use LDAP (and are successfully using AuthDomain) and SQL pass-through to use specific Teradata SQL (and currently using).
I believe that the syntax would be similar to the following (I am just learning SAS and pulled this out from the various manuals), but I am being told that this is not correct:
LIBNAME MyTd Teradata TDPID=QA1 AUTHDOMAIN=TeradataAuth FastExport=Yes DBSliceParm=(THREADED_APPS,2) Mode=Teradata Database=QA1_PL_SAS_VIEWS LogDb=SB_PRICING;
PROC SQL;
CONNECT USING MyTd as MyTd_1;
CREATE TABLE work.term AS
SELECT *
FROM CONNECTION TO MyTd_1
(
SELECT PolicyNo,
QuoteNo,
NumTerm,
GwTermNo,
ModelNo,
curmodel,
MIN(CAST(PeriodStartDate AS date)) AS date_inc,
MAX(CAST(PeriodEndDate AS date)) AS date_exp
FROM policy
WHERE numterm IS NOT NULL
AND ModelNo IS NOT NULL
GROUP BY PolicyNo, QuoteNo, NumTerm, GWTermNo, ModelNo, curmodel
ORDER BY PolicyNo, NumTerm, GwTermNo, ModelNo;
);
DISCONNECT FROM MyTd_1;
QUIT;
Any ideas as to if this is actually possible and how to get this to work?
Well, not used teradata, however the syntax shoud be similar to how we used to connect to Oracle, something along the lines of:
proc sql; connect to db (<connection details eg username/password); create table WORK.TERM as select * from connection to db ( <your database query here> ); disconnect from db; quit;
So I don't see anything majorly out, other than the connect to db.
Unfortunately that's been tried and SAS just uses ODBC for accessing Teradata and TPT is not used. Extracting millions and in some cases billions of rows using ODBC is just too slow.
Thanks anyway.
Ah, ok. Your maybe best of speaking with your SAS rep then, TPT (Terradata Parallel Transporter) seems to be a Terradata specific function, so might need some specific software/drivers. Can't help any further I am afraid.
For information:
Teradata Parallel Transporter (TPT) is a Teradata supplied utility for loading data into and exporting data from Teradata quickly. It is available for use from SAS although the SAS documentation is somewhat lacking in detail.
TPT is already installed on the SAS server in question and tested outside of SAS to prove that it as indeed been installed correctly. Just can't get the syntax correct to use it with SAS, LDAP and SQL pass-through.
Already following up via the SAS adminstrators and I thought I'd try here as well.
Well, I guess that your libname syntax is sufficient. But it requires that the global option metaserver is set, and of course that you have sufficient registrations in metadata.
@LinusH, thanks for responding. Being a SAS newbie I am not sure what you are referring to when you mention "global option metaserver".
However if this is in relation to the use of LDAP and/or LIBNAME, then this is already in use successfully and so should be O.K.
If you are not aware of any metadata server, you probably don't have one. So if that's the case, you can't use AUTHDOMAIN as I understand it, hence the login errors...
@LinusH - no login errors, not mentioned that - LDAP is working perfectly fine in general use.
So have you been through the Teradata-specific documentation like this?
@SASKiwi, yes this community has only been tried after exausting the documentation. What is available is incomplete or not very explicit about TPT/API and the Export operator.
Hi @TeraGrapher
I think the use of explicit pass-through is messing up the code. TPT functionality must be invoked by SAS. This code is going to have problems, but it should give you an idea.
LIBNAME MyTd Teradata TDPID=QA1 AUTHDOMAIN=TeradataAuth FastExport=Yes DBSliceParm=(THREADED_APPS,2) Mode=Teradata Database=QA1_PL_SAS_VIEWS LogDb=SB_PRICING;
PROC SQL;
CONNECT USING MyTd as MyTd_1;
CREATE TABLE work.term AS
SELECT PolicyNo,
QuoteNo,
NumTerm,
GwTermNo,
ModelNo,
curmodel,
MIN(CAST(PeriodStartDate AS date)) AS date_inc,
MAX(CAST(PeriodEndDate AS date)) AS date_exp
FROM mytd.policy
WHERE numterm IS NOT NULL
AND ModelNo IS NOT NULL
GROUP BY PolicyNo, QuoteNo, NumTerm, GWTermNo, ModelNo, curmodel
ORDER BY PolicyNo, NumTerm, GwTermNo, ModelNo;
QUIT;
There is an old'ish paper, which needs to be updated, that may help you with this. It shows many examples of TPT.
https://support.sas.com/resources/papers/proceedings11/142-2011.pdf
@JBailey, thanks for this. I will read and apply if I can.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.