BookmarkSubscribeRSS Feed
japsas100
Pyrite | Level 9

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.

9 REPLIES 9
sewa_bal_gmail_com
Quartz | Level 8

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
Quartz | Level 8
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
Super User

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
Opal | Level 21

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
Pyrite | Level 9

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
Opal | Level 21

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
Pyrite | Level 9

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
PROC Star

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

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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
  • 9 replies
  • 1163 views
  • 0 likes
  • 5 in conversation