topic Re: SAS Oracle ERROR in SAS Programming

SAS Oracle Error

japsas100 — Sun, 16 Dec 2018 22:22:13 GMT

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.

SAS Oracle ERROR

sewa_bal_gmail_com — Sun, 16 Dec 2018 22:20:26 GMT

Dear All,

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.

SAS Oracle ERROR

japsas100 — Sun, 16 Dec 2018 22:23:27 GMT

Dear All,

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.

Re: SAS Oracle Error

Patrick — Sun, 16 Dec 2018 22:30:41 GMT

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.

Re: SAS Oracle Error

japsas100 — Sun, 16 Dec 2018 22:38:43 GMT

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.

Re: SAS Oracle Error

SASKiwi — Mon, 17 Dec 2018 00:39:17 GMT

@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.

Re: SAS Oracle ERROR

Reeza — Mon, 17 Dec 2018 01:30:19 GMT

Are these posts your's as well?

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

Re: SAS Oracle ERROR

sewa_bal_gmail_com — Mon, 17 Dec 2018 01:36:58 GMT

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.

Re: SAS Oracle ERROR

Reeza — Mon, 17 Dec 2018 01:55:10 GMT

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.

Re: SAS Oracle ERROR

Patrick — Mon, 17 Dec 2018 05:18:11 GMT

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.