BookmarkSubscribeRSS Feed
rollers
Fluorite | Level 6

Hi,

 

We have encountered the above error message whenever the scheduled SAS query runs to fetch data from the production AS/400.  Although it is running the same query every time, the same error points to a different table each time.  Could this be a possible object locking incident wheren in the table affected is not available because it is busy doing something else? What does "8227" code mean? Appreciate any feedback you might have on this one.

 

 

15 REPLIES 15
JBailey
Barite | Level 11

Hi @rollers,

 

Can you provide the entire error message? Do you see something matching the pattern SQLnnnnN (where n is a number and N is an N)?

 

 

Best wishes,
Jeff

rollers
Fluorite | Level 6

Hi Jeff,

 

Thanks for your reply.  Please find attached the trace logs gathered by the user and given to us.  We appreciate any feedback that you might have on this one.

 

 

Regards,

 

Rolly

JBailey
Barite | Level 11

Hi @rollers,

 

According to DataDirect (Progress) this is a DEADLOCK or TIMEOUT issue. I think your best bet is to contact SAS Tech Support (I think you already have a Tech Support track open - I am looking at it now) and share the information from my previous response with them. I will include the text at the end of this message. If this is your track, they don't know that this is a LOCKING/TIMEOUT issues. 

 

 

SAS Tech Support, and your DBA (something tells me you may be the DBA - is this true?), will want to know if the code ever runs successfully. In other words, does this happen periodically or every time the query is run. If this error can reproduced by running the SQL statement mentioned in the Trace Logs.txt file using a DB2 query tool you will have proven that it isn't a SAS problem. 

 

It may be helpful to try the READ_LOCK_TYPE=NOLOCK LIBNAME statement option. The READ_ISOLATION_LEVEL= option may be helpful too. Set it to RU. This allows dirty reads. If either of these options clears the error message then you know what the problem was. 

 

Here is the text from my previous message:

 

Hi @rollers

 

I ran this past a friend at DataDirect. Here is what he found:

 

ivdb227.po:8227 "FAILED EXECUTION DUE TO DEADLOCK/TIMEOUT.

 

Which leads us to this DataDirect knowledge base article:

 

DB2 ISERIES RETURNS ERROR "FAILED EXECUTION DUE TO DEADLOCK/TIMEOUT"

 

The READ_ISOLATION_LEVEL= LIBNAME statement option may help you here. 

 

You may want to contact SAS Tech Support for help with this. The DB2 DBA at your site may be able to help with it, too.

 

Best wishes,

Jeff

 

Best wishes,

Jeff

 

rollers
Fluorite | Level 6

Hi Jeff,

 

Thanks again for your reply.  The SAS query that we have is a scheduled job.  Previously it always run successfully, but there are other times that it doesn't run good and the user will have to run it manually.  Now when the user runs it manually, there are times that it doesn't run succeessfully as well.  When the local SAS support asked us to run the same query without using the SAS tool, we ran it successfully in the iSeries using DB2 Query Mgr and SQL Devkit (5770ST1).  Will try to let them know about your inputs regarding this.  We also have other options which is to fetch data from backup machine (instead of production) and see if it's still going to be the same.  Thanks again.  Appreciate it.

 

 

Regards,

 

Rolly

JBailey
Barite | Level 11

Hi @rollers

 

I ran this past a friend at DataDirect. Here is what he found:

 

ivdb227.po:8227 "FAILED EXECUTION DUE TO DEADLOCK/TIMEOUT.

 

Which leads us to this DataDirect knowledge base article:

 

DB2 ISERIES RETURNS ERROR "FAILED EXECUTION DUE TO DEADLOCK/TIMEOUT"

 

The READ_ISOLATION_LEVEL= LIBNAME statement option may help you here. 

 

You may want to contact SAS Tech Support for help with this. The DB2 DBA at your site may be able to help with it, too.

 

Best wishes,

Jeff

prcmendiola
Fluorite | Level 6

Hi @JBailey

 

 

Im a colleague of @rollers , who is on training at the moment. I hope to pick up where he left. We are looking to implement the solution you recommended, can you point us to where we can set the "READ_ISOLATION_LEVEL= LIBNAME statement option"?

 

Best Regards,

Patrick

JBailey
Barite | Level 11

Hi @prcmendiola

 

LIBNAME statements can be set in user-written SAS code, autoexec code, or SAS metadata. It depends entirely on how the system is configured and the job is setup. If this is a simple SAS program, the LIBNAME statement may be in program code.

 

Here is a link to the doc which describes these LIBNAME options:

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p0bu3zsz1a08ton1ms...

 

I think you will get a much faster answer to this question from SAS Tech Support. Here is the link:

https://support.sas.com/en/technical-support.html#contact-technical-support

 

Let me know if I can help you,
Jeff

prcmendiola
Fluorite | Level 6
That makes sense. Thanks a lot Jeff! We'll let you know if we progress.
prcmendiola
Fluorite | Level 6
Hi @JBailey! We would like to let you know that we have applied your recommendation and it seemed to have fixed the issue. Thank you for your guidance and support!
JBailey
Barite | Level 11

Hi @prcmendiola

 

This is great news. When you have a minute can you mark my response as a solution because it will help others find the help with this issue. There wasn't a lot to go on so this thread will help people. 

 

Best wishes,
Jeff

prcmendiola
Fluorite | Level 6

Hi @JBailey! Sorry but, we had to revert to the original setting, as the integrity of the output was not ideal. Do you have any other suggestion?

JBailey
Barite | Level 11

Hi @prcmendiola,

 

What does "integrity of the output was not ideal mean?"

prcmendiola
Fluorite | Level 6

Hi @JBailey

 

What i meant, was the output by SAS was not equal compared to output by showcase query. We have reverted to our initial settings for now.

JBailey
Barite | Level 11

Hi @prcmendiola

 

Thanks for the info. Read isolation inpacts what is seen, this is especially true if the the table being queried is updated/modified during the read process. It is understandable for a single query to return different result sets when run under varying read iscolation levels.

 

Best wishes,

Jeff

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 15 replies
  • 8234 views
  • 7 likes
  • 3 in conversation