SAS Oracle Error

[japsas100]

Dear All,

We are facing some issue on the SAS-Oracle side. We have one process which throws a timeout error sometimes. When its executed, its run in just 30 seconds and sometimes it goes into hang stage and kills later on as a timeout error.

We are joining the 3 tables and first, we are creating the distinct of few variables like this With master_dd as(select %str(/)*+ parallel(MASTER_) *%str(/) A,b  ....................., and filter out few records with where condition. in this step we are creating the table without the merge.

next step we are merging the above table with another two table from a different schema with where condition.

Its very simple join but its executed sometime and some tie its failed.

Please guide me.

[sewa_bal_gmail_com]

Dear All,

We are facing some issue on the SAS-Oracle side. We have one process which throws a timeout error sometimes. When its executed, its run in just 30 seconds and sometimes it goes into hang stage and kills later on as a timeout error.

We are joining the 3 tables and first, we are creating the distinct of few variables like this With master_dd as(select %str(/)*+ parallel(MASTER_) *%str(/) A,b  ....................., and filter out few records with where condition. in this step we are creating the table without the merge.

next step we are merging the above table with another two table from a different schema with where condition.

Its very simple join but its executed sometime and some tie its failed.

Please guide me.

.

[Reeza]

Are these posts your's as well?

https://communities.sas.com/t5/SAS-Programming/SAS-Oracle-Error/m-p/521822

[sewa_bal_gmail_com]

Yes it's mine. By mistake it's was posted with other user id. I already deleted but its still exist.
Can you please delete this post.

[Reeza]

I've merged them into one, I do not have the authorization to delete posts. Not really sure how you can accidentally post under multiple ID's you may want to contact SAS Support on that topic, or if you want to consolidate or delete an account.

---

@sewa_bal_gmail_com wrote:
Yes it's mine. By mistake it's was posted with other user id. I already deleted but its still exist.
Can you please delete this post.

---

[Patrick]

To narrow down the problem a bit:

Do you get in the SAS log some information why it fails? Do you have some SAS and/or Oracle error code and message?

And: Does it fail because you don't get a connection to Oracle or does it hang once an connection has been established?

Are there other jobs connecting to Oracle and ideally the same schema/tables with such issues?

Eventually pick one of the failed processes and ask the DBA if there has been something happening on the Oracle time during this time - and also if the query is visible on the Oracle side for this execution of the SAS job.

[japsas100]

Dear All,

We are facing some issue on the SAS-Oracle side. We have one process which throws a timeout error sometimes. When its executed, its run in just 30 seconds and sometimes it goes into hang stage and kills later on as a timeout error.

We are joining the 3 tables and first, we are creating the distinct of few variables like this With master_dd as(select %str(/)*+ parallel(MASTER_) *%str(/) A,b  ....................., and filter out few records with where condition. in this step we are creating the table without the merge.

next step we are merging the above table with another two table from a different schema with where condition.

Its very simple join but its executed sometime and some tie its failed.

Please guide me.

[Patrick]

From what you write this looks much more like an Oracle than a SAS issue. If so then you need to investigate on the Oracle side to determine what's happening. Best get in contact with the Oracle DBA for your database.

[japsas100]

Thanks for the quick reply.

We already checked with BDA and they are unable to resolve this issue. Every alternative day, process getting fail. Can you please provide any alternative method (sas prog) to solve this issue.

[SASKiwi]

@japsas100 - Please post your SAS log with any notes or errors.

BTW, please note that posting the same problem three times isn't really helpful for you or for us. We end up wasting time checking duplicate posts.