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

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

rollers — Fri, 07 Apr 2017 14:52:36 GMT

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.

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

JBailey — Fri, 07 Apr 2017 15:09:35 GMT

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

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

JBailey — Fri, 07 Apr 2017 17:09:46 GMT

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

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

rollers — Mon, 10 Apr 2017 11:55:23 GMT

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

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

JBailey — Mon, 10 Apr 2017 13:24:07 GMT

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

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

rollers — Mon, 10 Apr 2017 13:32:57 GMT

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

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

prcmendiola — Thu, 04 May 2017 03:35:24 GMT

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

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

JBailey — Fri, 05 May 2017 14:32:08 GMT

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#p0bu3zsz1a08ton1msxdx1jo45np.htm

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

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

prcmendiola — Mon, 08 May 2017 09:09:05 GMT

That makes sense. Thanks a lot Jeff! We'll let you know if we progress.

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

prcmendiola — Tue, 06 Jun 2017 07:50:51 GMT

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!

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

JBailey — Tue, 06 Jun 2017 11:44:11 GMT

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

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

prcmendiola — Tue, 13 Jun 2017 03:11:22 GMT

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?

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

JBailey — Fri, 16 Jun 2017 18:26:37 GMT

Hi @prcmendiola,

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

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

prcmendiola — Fri, 07 Jul 2017 02:11:01 GMT

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.

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

JBailey — Mon, 10 Jul 2017 12:11:12 GMT

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

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

prcmendiola — Wed, 12 Jul 2017 07:39:43 GMT

We really appreciate your efforts @JBailey Thanks!