SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

Reply
Occasional Contributor
Posts: 9

Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

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?

Super User
Super User
Posts: 7,404

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

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.  

Occasional Contributor
Posts: 9

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

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.

Super User
Super User
Posts: 7,404

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

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.

 

Occasional Contributor
Posts: 9

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

[ Edited ]

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.

Super User
Posts: 5,257

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

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.

Data never sleeps
Occasional Contributor
Posts: 9

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

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

Super User
Posts: 5,257

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

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

 

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

Data never sleeps
Occasional Contributor
Posts: 9

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

@LinusH - no login errors, not mentioned that - LDAP is working perfectly fine in general use.

Super User
Posts: 3,110

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

So have you been through the Teradata-specific documentation like this?

 

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

 

Occasional Contributor
Posts: 9

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

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

 

SAS Employee
Posts: 203

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

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

 

 

Occasional Contributor
Posts: 9

Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP

@JBailey, thanks for this. I will read and apply if I can.

Ask a Question
Discussion stats
  • 12 replies
  • 1003 views
  • 1 like
  • 5 in conversation