Problem Note 45183: Using an SQL view while using a WHERE= data set option that results in a DB2 error might cause the step to continue executing
 |  |  |  |  |
If a DB2 -904 or -911 error occurs, SAS might continue to send requests for the data indefinitely rather than stopping when the following situation exists:
- PROC SQL view has been previously defined and includes a reference to a DB2 table
- In a DATA step program, the view is used in a SET statement that included the WHERE= data set option on that view
- A DB2 error, such as a -904 or -911 error, occurs that prevents DB2 from processing the query
SAS issues an error similar to the following:
DB2: DB2M_SQLCA->sqlcode: -911
DB2: Entering db2m_synch()
DB2: (1) Exiting db2m_synch() with return code 0
DB2 ERROR:
RESULT OF SQL STATEMENT:
DSN00911E THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEO
AND RESOURCE NAME DSN09186.CLASS .00000001
A DUMP OF THE SQLCA FOR THE GET DIAGNOSTICS REQUEST FOLLOWS:
ROW NUMBER: 0 ERROR CONDITION: 1 REASON CODE: 0
SQLCODE: -911 SQLSTATE: 40001 SQLERRP: DSNXRFF
DB2: (2) Exiting db2m_error() with return code 0
DB2: (3) Exiting db2m_fetch_error() with return code 0
ACCESS ENGINE: Exiting fetch with rc=0XFFFFFC71
ACCESS ENGINE: Entering fetch
DB2: Entering db2m_fetch_term()
DB2: Entering db2m_fetch_init()
DB2: Entering db2m_prep_init()
DB2: Entering routine db2m_stor_proc_parser()
DB2: (2) Exiting db2m_stor_proc_parser() with return code 0
DB2_3: Prepared: on connection 0
select * from sastest.class with rr
DB2: Entering db2m_prep()
DB2: (4) Exiting db2m_prep() with return code 0
DB2: (5) Exiting db2m_prep_init() with return code 0
DB2: Entering db2m_open()
DB2: (1) Exiting db2m_open() with return code 0
DB2_4: Executed: on connection 0
select * from sasmba.class with rr
DB2: Entering db2m_prep_fetch()
DB2: (1) Exiting db2m_prep_fetch()
DB2: (12) Exiting db2m_fetch_init() with return code 0
DB2: Entering db2m_fetch_error()
DB2: Entering db2m_error() with SQLCODE -911
DB2: DB2M_SQLCA->sqlcode: -911
DB2: Entering db2m_synch()
DB2: (1) Exiting db2m_synch() with return code 0
DB2 ERROR:
RESULT OF SQL STATEMENT:
DSN00911E THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEO
AND RESOURCE NAME DSN09186.CLASS .00000001
A DUMP OF THE SQLCA FOR THE GET DIAGNOSTICS REQUEST FOLLOWS:
ROW NUMBER: 0 ERROR CONDITION: 1 REASON CODE: 0
SQLCODE: -911 SQLSTATE: 40001 SQLERRP: DSNXRFF
However, rather than stop processing, which is what would happen without the WHERE= data set option, SAS continues to pass over a SELECT statement each time it receives the error from DB2. This continues until either the resource is freed and DB2 can process the query or until the DB2 spool becomes full.
Currently, there is no workaround for this issue.
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 | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to DB2 | z/OS | 9.2 TS2M3 | 9.3 TS1M2 |
A fix for this issue for SAS/ACCESS Interface to DB2 9.21_M2 is available at:
http://ftp.sas.com/techsup/download/hotfix/HF2/B94.html#45183| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2012-04-06 08:52:44 |
| Date Created: | 2011-12-19 16:26:01 |