BookmarkSubscribeRSS Feed
Amahareek
Fluorite | Level 6

Dears,

 

we face an issue with SAS/Access to Oracle when we do some processing on SAS Datasets then get back to Oracle connection 

ORA-03135, ORA-03134 session timeout 

we have an issue that we can't increase session timeout as the client refuse to do this 

they request to redesign our work we don't have anytime to do this 

is there is any option which makes connection always open like ping every specifc time or whatever 

 

appericate your help 

5 REPLIES 5
kiranv_
Rhodochrosite | Level 12

we faced similar issue one year ago, where one of the query was taking long and often they were connection time out. This has to do with Oracle settings, where in connections settings has to changed at oracle side. Oracle team refused to change the settings and We finally optimized our query to circumvent the issue.

Shmuel
Garnet | Level 18

Depending on resources and on the work type - 

can you import the relevant data from oracle to a sas table and then use it instead the oracle table ?

Ksharp
Super User

Maybe your Oracle is hibernated .

Patrick
Opal | Level 21

@Amahareek

"We have not time to..." is a weak argument in a consulting situation.

 

What I probably would do:

1. Some Internet research what the default/best practice timeout settings for Oracle are. If your customer is clearly below and hasn't specified a requirement that your processes must run within a defined time then you've got ammunition to make a case (=dear customer either increase the timeout setting for our batch user or find additional money/time for changes required for your site speciflc but not specified and not best practice timeout settings).

2. Try to understand why your customer says "no" and don't take the first answer as the final answer. It needs sometimes a bit of explaining why the general rule doesn't apply for a specific case.

3. Talk to the right people. Only because some ivory tower techy guy says no doesn't mean that this is the final decision. Convince the person who can overrule the initial "no" - and that's normally a senior manager on the business side who's in the end generating the salary for the IT guy and the IT guy's manager.

4. Performance improve your code! Sometimes that's quite simple; i.e.if it's pass-through SQL then using hints to parallelize execution on the Oracle side can make quite a difference. You can eventually also ask the customer's DBA to optimize your Oracle code.

 

 

when we do some processing on SAS Datasets then get back to Oracle connection 

How much effort would it be to change your code to create a SAS data set and then establish a new Oracle connection?

 

My experience with customers:

If you can explain why you need something changed and this makes business sense then there is almost always a solution. The customer will help you and even accept extra cost and/or delay.

But if you just try to get away with bad code/a bad solution then you're in trouble and you will eventually have to absorb the extra cost yourself and/or loose reputation.

Alessandro
Fluorite | Level 6

I already found this problem on some clients and SAS environments using SGDBs like Oracle, Sybase or SQL Server and the solution was the same for all issue.

Data Bank settings for response after a long time.

My recomendation is to use SAS passthrough connection, because all process will be resolved on data bank tier with a minor time and minor "I/O" traffic, or talk with your DBA for change the Oracle settings.

 

Best regards

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2715 views
  • 1 like
  • 6 in conversation