|
Problem IC65000
|
Status: Closed |
NO ROWS ARE RETURNED TO VSE CLIENTS FROM AN SQL STORED PROCEDURE WITH A CURSOR THAT IS DECLARED AS WITH RETURN TO CLIENT |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Users affected:
Programs that run on VSE and connect to a DB2 LUW
(DB2 for Linux, UNIX and Windows) database and call a stored
procedure.
Problem Description:
No rows are returned to a client program on VSE
when it calls a stored procedure in a DB2 LUW server
that opens a cursor that is declared using the
WITH RETURN TO CLIENT clause.
After the stored procedure returns no rows, SQLSTATE is set to
02000, and SQLCODE is set to SQL0100W or SQL0231W.
Here is an example of such a stored procedure.
CREATE PROCEDURE PROCEDURE1( )
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT cast(PROCSCHEMA as char(8)) FROM
SYSCAT.PROCEDURES;
OPEN cursor1;
END P1
Problem Summary:
This problem only happens when you use a version of DB2 LUW that
contains the change described by APAR LI72598.
The APAR LI72598 change was introduced in DB2 LUW 9.5 fixpak 4
to improve performance for DB2 version 7 on zOS clients.
This APAR fix keeps the performance improvement for DB2
version 7 on zOS clients that was effected by the APAR LI72598
change. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Programs that run on VSE and connect to a DB2 LUW (DB2 for *
* Linux, UNIX and Windows) database and call a stored *
* procedure. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* . *
**************************************************************** |
| Local Fix: |
The stored procedure returns the correct result if the cursor is
declared as WITH RETURN TO CALLER instead of
WITH RETURN TO CLIENT. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 2 |
| Workaround |
The stored procedure returns the correct result if the cursor is
declared as WITH RETURN TO CALLER instead of WITH RETURN TO
CLIENT. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 07.12.2009
25.01.2011
25.01.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
