BookmarkSubscribeRSS Feed
IqbalS
Calcite | Level 5

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

3 REPLIES 3
LinusH
Tourmaline | Level 20

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.

Data never sleeps
jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
IqbalS
Calcite | Level 5

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

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 2857 views
  • 6 likes
  • 3 in conversation