SAS MA Idle DBMS Sessions

[IqbalS]

Hi,

I have an intermittent issue explained below which I’m hoping someone in the community can advise on:

The Environment:

SAS Application: SAS Marketing Automation 9.3 (Customer Intelligence Studio 5.41)

DBMS: Teradata 14.00.04.07

The Issue:

Occasionally Marketing Automation applications (specifically CI Studio as this is the most heavily used on site) will freeze when running DB queries, upon investigation the root cause is found to be excessive (Idle) database sessions attributed to the SAS user. The number of database sessions (spawned by SAS) encroaches on the limit of our Teradata environment impacting processing times severely. The issue is resolved by killing the idle sessions (DBA task) or restarting the SAS application servers.

A SAS track has been raised, and my own investigations are focusing on the nature of the queries/sessionscreated. The SAS environment is used primarily to run scheduled CI campaigns; both campaigns and the associated schedule/batch change very little and run daily without problems, but this issue will crop up intermittently. This leads me to think that the issue may lie with how the application is managing sessions/DB queries rather than the queries themselves. I’d be grateful if you could offer any advice on the following:

• Which SAS process/processes manage opening/closing of DB session and how to track this on the application servers/Management Console or similar?

• Settings or configuration elements worth reviewing?

Many Thanks

Iqbal

[LinusH]

Not into MA/CI much, but from the general SAS part.

Do you assign Teradata librefs automatically in the autoexec or through metaautoresources?

Then try the defer=yes option.

If you have multiple librefs to Teradata from single SAS session, try connection=sharedread.

[jakarman]

Every libname and every sql will open one or more sessions threads by default.

The sharing of sessions will limit the number. When using td volatile tables you will need a full shared one (global)

Not needing those libraries in all cases then use defer=yes  it will open only when needed. Also handy on delaying the autentication.

Slow long running tasks is an other possible cause running for days ublimited by not having the correct options like bulk load will not release resources.

[IqbalS]

Thank you both, I'll look into the LIBNAME options you've suggested. FYI the libref is assigned automatically and the DB options currently used are: BULKLOAD=YES, SLEEP=1, TENACITY=1, TPT=NO