Connection Limit Issues with BASE Sas 9.3 and IBM Netezza

Reply
New Contributor
Posts: 4

Connection Limit Issues with BASE Sas 9.3 and IBM Netezza

Good day GURU's,

We have migrated to 9.3 with IBM Netezza appliance, however we always experience a connection limit with the error message "49604 - Too many connections are made to the Netezza database and these connections are not being dr..."

However, this connection error still persists even after we have applied the patches mentioned and after some code revisions such as:

For the Login script

     libname SCV NETEZZA server="&rsvr" database=PROD_SCV user=&runame password="&rpwrd" UTILCONN_TRANSIENT=YES;

     libname SCV remote server=sashost rengine=netezza  roptions="server='&svr' database=PROD_SCV user='&uname' password='&pwrd'";

     ...

     Note: An actual login script is attached for reference

For the actual base sas code

     proc sql;

         connect to netezza as ntz(server="&rsvr" database=rcbc_workarea user=&runame password=&rpwrd);

         execute(create table MyTemp as select * from MYOLD where card_no <> '') by ntz;

         disconnect from ntz;

     quit;

    Note: An Actual code is attached for reference    

Additional Infos:

Current users in SAS base is : 8 (and will grow eventually)

Number of DB's being accessed/declared in the Login script : 26 LIB declarations or 26 Databases in Netezza with 10 to 50 tables per database.

Actual Scenario:

A SAS user will open SAS base and run a login script. Once connected, will write a SAS base code to connect to netezza and generate reports.

Hoping for a more solid solution from other implementations.

Thanks!

Attachment
Attachment
Valued Guide
Posts: 3,208

Re: Connection Limit Issues with BASE Sas 9.3 and IBM Netezza

* Why did you not use use defer=yes?

* What are the maximum number of connections?

* Have you considered options for sharing connections?  the message is sayin you have too many of them.

- SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition  (global read?)

* now you are setting up the connections on the local and server machine. Considered a shared libref?

8 users each opening up 26 connections on the server side and client side Would indicate you are using 200 sessions to Netezza.
After that the explicit pass through are opening new sessions on top of that to that. 

---->-- ja karman --<-----
New Contributor
Posts: 4

Re: Connection Limit Issues with BASE Sas 9.3 and IBM Netezza

Hi and thank you for initial reply. As you have asked here are my replies

* Why did you not use use defer=yes?

It was not suggested and am still waiting for some other suggestions.

* What are the maximum number of connections? =

Per Netezza , its 510 connections. It may be increased to double its size but is not recommended by IBM since the problem lies wherein connections are not terminated by SAS Access Engine. so increasing the figure is not a permanent solution.

* Have you considered options for sharing connections?  the message is sayin you have too many of them.

* - SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition  (global read?)

How to do this sharing of connections if we are using SAS base and not DI or EG?

* now you are setting up the connections on the local and server machine. Considered a shared libref?

How? It would be much appreciated if you could attach a sample code like what I did.

* 8 users each opening up 26 connections on the server side and client side Would indicate you are using 200 sessions to Netezza.

* After that the explicit pass through are opening new sessions on top of that to that. 

I see, so how do we terminate a connection from SAS base after a request of proc or data is completed so that there will be no idle connections left in Netezza. I believe from my observations using Putty in Netezza, there are always a growing number of Idle connections that are not closed, therefore consuming all the default 510 connections allowed in netezza.

Please advise how to terminate this connections. If you can check my code, it has this parts but it seems it doesn't work as expected.

connect to netezza (server="&rsvr" database=PROD_RCBC_BPY user=&runame password="&rpwrd");

    execute (   ....

              ) by netezza;

        disconnect from netezza;

Regards,

Valued Guide
Posts: 3,208

Re: Connection Limit Issues with BASE Sas 9.3 and IBM Netezza

I am seeing you are using a WIndows server with Netezza interface installed and are using the SAS/connect from SAS base to dot your work. The default setting with SAS/connect is synchronous that implies polling and a mixed SAS-log (containing both remote and local processing). I persnonally prefer asynchronous processing as it segregates those machines and isolate logs/output.

An example for changes (limited to a singel libname) could be:

options noconnectwait ;   /* communications in async mode */

rsubmit sashost;

   ...

libname SCV NETEZZA server="&rsvr" database=PROD_SCV user=&runame password="&rpwrd" UTILCONN_TRANSIENT=YES connection=global defer=yes;

   ..

endrsubmit ;

   waitfor sashost; 

   rdisplay ;     /* you will get 2 addtional screens  remote log remote output */

   /* listtask and rget are other commands to manage asynchronous connections */

  

   ...

   libname SCV slibref=SCV server=sashost ;   /* local libref use the one on the remote one  */

   ...

The defer=yes will only open a session when you are requesting that one.

The slibref approach wil force the local libname be running by the server one.

The connection=global will share dbms sessions. See the notes on this option in the SAS documentation.

The explicit pass-through you have given as sample is something that will not touch the libname defnitions.  It could be necessary to redefine some you know of the libname. Not all libname options are applicable to a SQL explicit eg defer=yes.

As you are telling it looks like session are not cleaned up, it could be there are ghost processes running on the server-side.

this can happen when the conection get broken while being processes running. They should be cleaned up.  

An other option is a Netezza session not aware the client has gone away.

You could improve: a/  your startup-code for the connection b/ the way the user password is stored and retrieved c/ namings of involved servers. As long as you are a small team that will not give much benefits.

---->-- ja karman --<-----
New Contributor
Posts: 4

Re: Connection Limit Issues with BASE Sas 9.3 and IBM Netezza

Hi your correct, we have a windows environment.

I have applied what you have suggested but are having troubles with SASMstore in some of the scripts, but when we remove the transient and global the code is running without errors again. Based from the SAS documentation their shouldn't be any problem with this, so what could be the possible cause of this?

we have implemented this async mode and the users complained that the response of the server/netezza is slower, is this suppose to happen? from what I have read on this mode the result should be faster, correct?

Also, how can we know if a connection is broken like you said? I checked the codes and all have the Disconnect from Netezza code so it's safe to assume that connections created are cleaned, correct?

what we notice is that, we now only have a few idle connections compared before. around 54 idle connections and 8 active connections.

It's given that 6 sas base users will login using the same login name "DATA_ANALYST", how many connections should be present in Netezza?

Thanks in advance.

Valued Guide
Posts: 3,208

Re: Connection Limit Issues with BASE Sas 9.3 and IBM Netezza

More questions altough it looks some things are solved (number of sessions).
1/ The libname parameters are to be tuned to the type of usage.

That is sure for the differences with read update and bulkoad.   

Check  the access-doc on that this can be a cause of the unexpected behavior. Aside global you also have more options like globalread and the connection_group.   https://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#n0i2psvqzeph0pn15...

With this you can set up a approach for common usage and for the execetpions apply overwrites at the SAS dataset level or using explictit Pass through with dedicated options or using different libname options.

This requirement of variation can be solved in code (libname) but is almost impossible in the SAS-metadataserver (Di Bi approach)

Read the join note : (when failes all data is going to SAS, your goal is executing that at netezaa)   https://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#p08h4qcwyjoo2gn12... 

Bulk-loading  is having a different set of attitude communcating to Netttezza, this kind of jobs is normally isolated to a DBA task. However if users are allowed to store results at Netezza that should be explained to them.

 

2/ The async processing should be faster, but verify the use of the windows server for the processing, not the desktop.

Check your self how it words (then instruct the users)

All SAS-code that is going to Netezza should run on the server side, use rsubmit/endrsubmit. It is the same as you were using Eguide running a WS directly on a server. I am expecting the Win-Server is near the Netezza so that is performing faster as the desktop. An other goal should be processing as much as possible within Netezza. You could need sastrace settings to verify that is working correct.

The usage of local SAS with DMS is degraded to more a terminal approach but offering a good view to the server and capable of transferring files as needed. 

---->-- ja karman --<-----
New Contributor
Posts: 4

Re: Connection Limit Issues with BASE Sas 9.3 and IBM Netezza

Hi Jaap, Thank you for taking the time and effort to reply to all my queries. I really appreciate it.

1. Yes, you are correct! it seems we need to identify the type of connection we need per library parameters depending on the requirement. For now, we have implemented the Global and remove this from a library where we encounter an error.

2. The win server is beside Netezza and uses a gigabyte switch for communicating with Netezza.  All codes uses a submit line to make sure it was executed to the win server and data preparations in Netezza. Although the assumption is that codes should be executed much faster. From our observation, the same code that takes 15 minutes is now taking 17 minutes in asynchronous mode. Am still puzzled on this.

3. This may need a thorough review of all codes, but as far as I know based from our SAS users codes, they do not have loops or long codes that may leave a connection open. Their codes are basic queries to fetch , insert or copy data from one table to another. Within each of these codes, there is always a disconnection command to make sure it was closed after execution UNLESS this disconnection command is not executed when an exception is encountered, is it? As far as I know, in other languages, there is a TRY CATCH EXCEPTION to make sure necessary cleanups are executed. can this be applied?

4. Yes your correct, 5 users with 16 libraries each gets a total of 80 connections, What's amazing is that after I have applied your suggestion on the signon scripts, from the initial 400+ connections it's now down to 80+ connections. We will just further streamline their connection declarations to just what they need in order to further lower their connections. We will also use different login names as suggested. Now we will try to address on How to remove Idle connections, hope you can further help on this dilemma. Smiley Happy

5. Am not sure if there are such special features in their SAS MAcros, but am pretty sure that what they need to do is to make sure that all SAS reports are using the same Daily Exchange Rate Macro for a standard computation and result and only 1 person is allowed to maintain it. I need to have this macro resolved as most libraries are using this macro and revising several dozen existing codes that was turnovered to new employees is not advisable. Should this was not resolve, we will be forced not to use the signon script you suggested  Smiley Sad

Again, thank you for the efforts and help!

Valued Guide
Posts: 3,208

Re: Connection Limit Issues with BASE Sas 9.3 and IBM Netezza

3/ The broken connection I meant are SAS sessions that are kept running while the desktop user has been closed down.

Checking the Win-server should show if this happens. Normally a DBMS is closing the connections when the client has gone. The only exception I have seen is some query at the DBMS that is looping and needed to be stopped seperately. A DBA at the DBMS will notice this quickly.

4/ I would expect every SAS-base session started form the desktop will build up Connections to Netezza. If one user use more SAS-base sessions it will count  the number of session in the way of an addtional users. Although you are using a singel acccount the connections are unique and I assume each one counting adding to them. If you need to be traceable/auditable at the netezza side I would advice using different accounts one for each user.

5/ The SASmstore is the direction of storing SAS macros. The default location is saswork. I am more lazy and using the sasautos storing macros as SAS-code not trying to maintain compiled versions. You are right as there should be no relation to your code execution. Are there special macro-s in the sasmstore library? http://support.sas.com/documentation/cdl/en/mcrolref/62978/HTML/default/viewer.htm#p156rt5c7ikat4n1o...

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 7 replies
  • 1342 views
  • 6 likes
  • 2 in conversation