SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]8227

Reply
New Contributor
Posts: 3

ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]8227

[ Edited ]

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.

 

 

SAS Employee
Posts: 215

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

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

New Contributor
Posts: 3

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

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

SAS Employee
Posts: 215

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

[ Edited ]

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

 

New Contributor
Posts: 3

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

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

SAS Employee
Posts: 215

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

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

Occasional Contributor
Posts: 6

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

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

SAS Employee
Posts: 215

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

Posted in reply to prcmendiola

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

Occasional Contributor
Posts: 6

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

That makes sense. Thanks a lot Jeff! We'll let you know if we progress.
Occasional Contributor
Posts: 6

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

Posted in reply to prcmendiola
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!
SAS Employee
Posts: 215

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

Posted in reply to prcmendiola

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

Occasional Contributor
Posts: 6

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

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?

SAS Employee
Posts: 215

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

Posted in reply to prcmendiola

Hi @prcmendiola,

 

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

Occasional Contributor
Posts: 6

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

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.

SAS Employee
Posts: 215

Re: ERROR: CLI cursor fetch error: [DataDirect][ODBC 20101 driver][UDB DB2 for iSeries and AS/400]82

Posted in reply to prcmendiola

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

Ask a Question
Discussion stats
  • 15 replies
  • 506 views
  • 7 likes
  • 3 in conversation