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.
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
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
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
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
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
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
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:
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
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
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?
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.
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
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.