Problem Note 64163: SAS/ACCESS® Interface to DB2 produces the error "…DEFINITION OF ROWSET ACCESS FOR CURSOR …INCONSISTENT WITH THE FETCH ORIENTATION CLAUSE SPECIFIED"
 |  |  |  |  |
In certain circumstances, after you query a DB2 table on z/OS, you see an error that is similar to the following:
DB2 ERROR:
RESULT OF SQL STATEMENT:
DSN00249E DEFINITION OF ROWSET ACCESS FOR CURSOR DBCUR065 IS INCONSISTENT WITH THE FETCH ORIENTATION CLAUSE SPECIFIED
A DUMP OF THE SQLCA FOR THE GET DIAGNOSTICS REQUEST FOLLOWS:
ROW NUMBER: 0 ERROR CONDITION: 1 REASON CODE: 0
SQLCODE: -249 SQLSTATE: 24523 SQLERRP: DSNXERT
DBMS ????????,; ,A?H????? TABLE_NAME????????????????????DATA ??????????????
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements
RESULT OF SQL STATEMENT:
DSN00249E DEFINITION OF ROWSET ACCESS FOR CURSOR DBCUR065 IS INCONSISTENT WITH THE FETCH ORIENTATION CLAUSE SPECIFIED
A DUMP OF THE SQLCA FOR THE GET DIAGNOSTICS REQUEST FOLLOWS:
ROW NUMBER: 0 ERROR CONDITION: 1 REASON CODE: 0
SQLCODE: -249 SQLSTATE: 24523 SQLERRP: DSNXERT
DBMS ????????,; ,A?H????? TABLE_NAME????????????????????DATA ??????????????
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements
The error can appear when the following conditions occur:
- You join a SAS table to a DB2 table on z/OS with the SQL procedure.
- The READBUFF= data set option has a value greater than 1.
- The characteristics of the tables vary (for example, the joining column's value is not found in both tables).
The workaround is to use READBUFF=1. However, that option value increases the elapsed time for PROC SQL processing.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.4_M5 | 9.4 TS1M5 | ||
| z/OS 64-bit | 9.4_M5 | 9.4 TS1M5 | ||||
A fix for this issue for SAS/ACCESS Interface to DB2 9.43 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/F4B.html#64163A fix for this issue for SAS/ACCESS Interface to DB2 9.42 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/B7W.html#64163
If the READBUFF= data set option has a value greater than 1, the error might occur.
| Type: | Problem Note |
| Priority: | high |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> DB2 Common Programming Tasks ==> Improving Performance SAS Reference ==> LIBNAME Engines |
| Date Modified: | 2019-05-17 11:35:17 |
| Date Created: | 2019-05-07 11:34:21 |