topic Re: Syntax for using SAS PROC SQL with Teradata TPT, SQL passthrough & LDAP in SAS Data Management

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

TeraGrapher — Wed, 11 Jan 2017 13:16:45 GMT

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?

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

RW9 — Wed, 11 Jan 2017 13:26:00 GMT

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.

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

TeraGrapher — Wed, 11 Jan 2017 13:31:16 GMT

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.

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

RW9 — Wed, 11 Jan 2017 13:57:06 GMT

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.

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

TeraGrapher — Wed, 11 Jan 2017 14:36:59 GMT

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.

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

LinusH — Wed, 11 Jan 2017 15:22:07 GMT

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.

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

TeraGrapher — Wed, 11 Jan 2017 15:36:32 GMT

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

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

LinusH — Thu, 12 Jan 2017 13:14:11 GMT

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#n0aiq25zc8u8u6n1i81my0a24sd3.htm

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

TeraGrapher — Thu, 12 Jan 2017 14:08:46 GMT

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

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

SASKiwi — Thu, 12 Jan 2017 22:37:52 GMT

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

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

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

TeraGrapher — Mon, 16 Jan 2017 11:04:01 GMT

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

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

JBailey — Tue, 17 Jan 2017 16:54:38 GMT

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

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

TeraGrapher — Wed, 18 Jan 2017 07:20:40 GMT

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