topic Re: How do SAS/Access connections work? in SAS Programming

How do SAS/Access connections work?

BobD — Tue, 22 Jan 2013 18:12:37 GMT

Does anyone know what happens behind the scenes with SAS/Access connections to Teradata via a LIBNAME statement?

For example, consider this code block:

libname TDATA teradata user=xxx password=xxx server='myserver' database='MYDB';

data a;

set TDATA.table1

TDATA.table2

TDATA.table3;

run;

The question is: how many database connections are made? Does SAS understand this as a UNION operation and convert it as such using one connection, or does it make three separate database connections? If so, then what about SAS MERGE operations, or syntax like this:

data a;

set TDATA.table1;

set TDATE.table2;

run;

I'm asking because our environment is going to enforce a new rule that no more that five concurrent sessions are allowed per user ID. I need to understand how sessions are allocated in SAS so we can review our code and advise our users of this change.

Thanks,

Bob

Re: How do SAS/Access connections work?

Linlin — Tue, 22 Jan 2013 19:28:10 GMT

Hi ,

I think only one connection. Once you have the libname assigned, you have access to all the tables in the library.

Re: How do SAS/Access connections work?

Haikuo — Tue, 22 Jan 2013 20:25:37 GMT

I don't have the answer, but one way to find it out quick and easy is to check with your Teradata DBA, after running your test code, just ask for the log or let DBA check it for you.

Haikuo

Re: How do SAS/Access connections work?

LinusH — Wed, 23 Jan 2013 10:06:19 GMT

Agree with https://communities.sas.com/people/Linlin and https://communities.sas.com/people/Hai.kuo about no of connections.

About what SAS actually sends down to the database is fairly documented for each Access engine.

For SAS datasteps, I think that WHERE, BY and pehaps KEEP/DROP dataset options are sent to the source. The rest of the data step syntax differ so much from SQL, so it would be quite cumbersome to translate to Teradata SQL.

So in your case, I don't think SAS will let Teradata know that there is UNION kind of thing going on, it will trigger two table scans, stream the result sequentially to the data step, which handles the processing.

To actually see what is sent to Teradata, use the SASTRACE options.

Re: How do SAS/Access connections work?

SASKiwi — Wed, 23 Jan 2013 19:14:41 GMT

Check the SAS/ACCESS documentation for Teradata (V 9.3 here):

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

There are options for using more than one database connection simultaneously to speed up queries - generally for loading data, but you can set the minimum and maximum connections allowed:

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