Sql passthrough share connection?

Reply
Super Contributor
Posts: 418

Sql passthrough share connection?

Hello everyone.  Does anyone know if multiple proc sql pass through operations share the same connection?   In other words, say you were trying to connect to a sql database multiple times in a script like below (dumbed down version).

Connection #1

libname mydata odbc dsn="&prodconnectstr." schema=dbo bulkload=yes DBMAX_TEXT=32000 ;

proc sql;

create table Findnewloans as

select loannumber

from mydata.LoanTable

where processed=0;

quit;

run;

/*Do some sas stuff such as run models, move results to other databases, etc..*/

Connection #2:

/*Run Database Stored Procedure to update the loan table to set all loans to a status of "processed =1"*/

proc sql;

connect to odbc(dsn=&prodconnectstr.);

execute (Execute UpdateLoanTable ) by odbc;

disconnect from odbc;

quit;

run;

Connection #3

proc sql;

insert into mydata.OtherTable as

select loan from Findnewloans;

quit;

run;

My company is actually having problems with the connections to our database, and we are having timeout issues. It turns out this is due to our network and it something that is out of my control.  However given that a connection can fail (network too busy, firewall rejects it, etc.) and the connection fails sporatically (it might fail 1-2% of the time)..

Is it then possible that the first connection would work, the second connection would fail, and hte third connection would then work?

Thanks for your time and support.

If anyone is curious I have some pretty large scripts (1,000's+ of lines) that are basically workflow programs where I read data from databases, and use statuses to determine workflow (models to run, etc..).  This becomes a HUGE problem if the connections are independent (which I think they are) for exactly the reason I describe above.  Basically in this example, the second phase didn't trigger so the loans I wanted to flag as processed=1 remain as 0, and the next time the code is run these loans are run again which should not happen.

If this is possible, basically any time I run any kind of operation that is a connection to the database, I need to check to make sure the connection actually worked using &SqlRc, etc..   Furthermore if a connection failed, I need to keep trying the connection, or basically undo every operation that affected a database record before this connection failed.

Thanks everyone!

Super Contributor
Posts: 418

Re: Sql passthrough share connection?

Allow me to clarify this question.  I know there is the option of "connection=Global". however what happens if you use this option in completely seperate proc sql pass thorugh statements. For example if you have.

proc sql;

connect to odbc(dsn=&prodconnectstr. Connection=Global);

execute (Execute UpdateLoanTable ) by odbc;

disconnect from odbc;

quit;

run;

proc sql;

connect to odbc(dsn=&prodconnectstr. Connection=Global);

execute (Execute Someotherscript) by odbc;

disconnect from odbc;

quit;

run;

Since I disconnect within Each proc sql statement, does the connection=Global still use only one global connection for the odbc connection through the dsn... or does global only apply to that sql step?  The documentation seems to imply both so I am a little confused on it.

Just so everyone knows this is a simplifed example, and in the actual example there is hundreds of lines of code between these connection so I cannot simply execute them in the same connection.

Thanks again!

Esteemed Advisor
Posts: 5,194

Re: Sql passthrough share connection?

I haven't done with this situation myself, but I may help interpret the documentation.

First, I'm pretty sure that a libname = connection. I have no idea if you can get a timeout condition but the connection itself can recover.

CONNECTION=GLOBAL means that multiple libname (or connect to) share the same connection. But you are probably right, after QUIT in PROC SQL, you probably loose that connection.

My opinion is that you should prioritize fixing time-out problems, and not changing your syntax for quite uncertain conditions.

Data never sleeps
Super Contributor
Posts: 418

Re: Sql passthrough share connection?

Hey Linus thanks for your input.  As an fyi to you a libname is a connection and it can timeout (I know that because it happened to me today!)

While I completely agree that fixing the timeout issues is the #1 priority, it is completely out of my control. The last time my company had this issue it took the server people over 2 months to figure out what was wrong.

Some of my processes use more than 20+ pass through operations, many of which pull data, many others update data, and much sas code is done between these connections.

So the ability for a process to fail in between any of these 20 pass through connections is extremely scary, because a failure means i need to roll back every other step that has succeeded. This basically means I need to impliment my own logging system in order to achieve this. Frankly that's just not a solution I'm okay with unless absolutely necessary. That is weeks if not months of work on every program I write.

While I agree that in theory assuming that the connection will work is much easier and should be how things work, from my seat I have no control over this.  Based upon that i'm trying to come up with a solution to allow my code to roll back processes if a connection fails, OR better yet to simply only test the connection once at the start of the script, and then use the same connection throughout the script so I don't have to figure out the roll back process as it can be extremely complicated.

Again tho thanks for your input!

Respected Advisor
Posts: 3,059

Re: Sql passthrough share connection?

I work in a very similar environment to you - using SQL Server a lot from SAS with lots of passthru-type connections as well as LIBNAMEs. In my experience timeouts are almost exclusively caused by firewalls between servers. We no longer suffer these problems because we have firewall rules in place that will allow us to continue processing for at least 8 hours without timing out. It is usually the IT network specialists that look after this - could you be talking to the wrong people?

And I think Linus is right too. A passthru connection is only open until you QUIT the SQL procedure. The same would apply to clearing an SQL Server LIBNAME.

Super Contributor
Posts: 418

Re: Sql passthrough share connection?

Hello SASKiwi! You are correct actually. The last time this happened and the process got fixed it was the IT network specialists that had to fix it, and it was a firewall problem (when a security system got patched it cleared all the exceptions or something along those lines).

So in general this isn't a problem anymore, HOWEVER I still wnat to know if there is a better way to handle this problem in SAS.  I really dislike the idea that you have to assume that your connections are going to work in order for your program to work (if you have independent Connections).

So I am more curious about is : if you work in an environment where you use lots of pass through connections to update databases.... and your connections  can fail. along the way at some frequency > 0%.. what is the best route to fix this problem (besdies fixing the connection issues themselves)?

Do we need to do error checking at every pass through connection, and if one fails then roll back the ones that didn't fail?  How would I do this? If the connection is failing, the ability to roll back the previous transactions would likely fail as well....

Is this an inherent limitation within SAS?

Thanks!

Respected Advisor
Posts: 3,059

Re: Sql passthrough share connection?

It's a bit difficult to offer advice when I don't know the complete picture however....

I'm a great believer in keeping things simple - the KISS principle.

I not a great fan of adding extra checks into processes to detect problems that may or may not happen as all it does is complicate things and adds more code to support.

What about the SAS ERRORABEND option? I'd run the SQL Server load jobs in batch mode with the ERRORABEND set. That means SAS will abort and stop at the first error so it means you have less fix ups to do.

Also are you familiar with SQL Server temporary tables? These are named #MyTemporaryTable. What you could do is first load all of your SAS data into SQL Server temporary tables. If anything breaks here then just delete the temporary tables and repeat the load. Your program could check for the existence of these tables and delete them before starting to re-create them.

With the updating of the permanent tables you could wrap them in a commit/rollback step using PASSTHRU EXECUTE and custom SQL so that if anything breaks the database will automatically rollback all of the changes.

So the objective is to end up with a repeatable process, so if it breaks you just start again at the beginning - I think batch jobs are the best way to go for this.

Esteemed Advisor
Posts: 5,194

Re: Sql passthrough share connection?

The question of roll-back: it's a RDBMS feature, and I can't see that SAS differs from any other client that might connect to the database, and also suffers from disconnects/time-outs.

However, SAS lets you specify a number of libname/data set options to manage roll-back strategies (like CONNECTION=). Examine these parameters thoroughly, perhaps together with the DBA, before change the default behavior.

Data never sleeps
Super Contributor
Posts: 418

Re: Sql passthrough share connection?

Hello SASKiwi.  I agree with the KISS principle as much as possible, and that's why I really don't want to write any of this custom code and was hoping for a global option change to be honest (haha).

I currently do use testing for inserting into permanent tables, so if my insert fails I have code that tests the insert and re-runs it (up to 10 times) and then then an email will go out letting me know what part of the program failed (so i can go in and fix it hopefully).

I was not aware of the ERRORABEND option, so I will look into that option as it sounds like a great way for me to do what i'm trying to do!

For your other point, all of my database updates are actually rolled into stored procedures that are registered as transactions with an execute level of Serializable.  So if part of one update fails it's okay because the database will in-deed roll it back.

The bigger problem I have is when these updates are seperated by significant other code.  For example I run an update against the database (through a transaction that has rollback) and then I run 5 minutes of other code, and then another update (against through a transaction with rollback), and this one fails.  By definition the first update was successful from 5 minutes ago, and this one needs to now be rolled back... SO I need to know where the code failed from so I can maintain what to un-do....

Super User
Super User
Posts: 6,309

Re: Sql passthrough share connection?

Not sure about ODBC connections but with TERADATA you can keep the connection open between PROC SQL steps by defining a LIBNAME.

Not sure if it will solve your problem.  Perhaps you can break your process into distinct steps and at each one start an new connection using LIBNAME . You can test the return code to make sure the connection worked (or even loop trying every few seconds until it does work).

* Open the connection ;

LIBNAME MYDATA TERADATA ... CONNECTION=GLOBAL;

proc sql;

  connect to teradata (... connection=global) ;

...

quit;

proc sql ;

  connect to teradata (... connection=global);

...

quit;

* Close the connection ;

LIBNAME MYDATA clear ;

Ask a Question
Discussion stats
  • 9 replies
  • 376 views
  • 0 likes
  • 4 in conversation