08-10-2017 06:50 AM
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
08-10-2017 07:17 AM
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.
08-11-2017 07:33 AM - edited 08-11-2017 08:12 AM
"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.
09-05-2017 10:19 AM
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.