|
Problem IC73963
|
Status: Closed |
USING DBMS_SQL AND CALLING CURSOR TWICE IN SAME PROCEDURE GIVES SQL0443N WITH CLI -502 AND SQLSTATE=38553. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Result set cursor clean up in functions such as
SYSIBM.SQLCOLUMNS is not performed, in the presence of DBMS_SQL
usage, resulting in error SQL0443N with CLI -502 and
SQLSTATE=38553.
SQL0443N with CLI -502 and SQLSTATE=38553 can be experienced
under the following conditions when executing a custom stored
procedure:
1. Calls to DBMS_SQL module procedures are made in your custom
procedure (Oracle compatibility mode is necessary to access
DBMS_SQL procedures).
2. A trusted function that returns a result set using a cursor
(such as SYSIBM.SYSCOLUMNS) is invoked more than once in the
procedure body.
Below is a sample script, and its output, showing this issue:
SET SQLCOMPAT PLSQL
create or replace PROCEDURE proc1
AS
cid int;
BEGIN
cid := DBMS_SQL.open_cursor;
dbms_output.put_line (1);
sysibm.sqlcolumns ('', '', 'C_C', '', ''); --First call works
dbms_output.put_line (2);
sysibm.sqlcolumns ('', '', 'C_C', '', ''); --Second call fails
dbms_output.put_line (3);
END;
call proc1
Output: SQL0443N Routine "SYSIBM.SQLCOLUMNS" (specific name
"COLUMNS") has returned
an error SQLSTATE with diagnostic text "SYSIBM:CLI:-502".
SQLSTATE=38553 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users of procedures and functions satisfying conditions *
* given in APAR description. *
****************************************************************
* PROBLEM DESCRIPTION: *
* Result set cursor clean up in functions such as *
* SYSIBM.SQLCOLUMNS is not performed, in the presence of *
* DBMS_SQL usage, resulting in error SQL0443N with CLI -502 *
* and SQLSTATE=38553. *
****************************************************************
* RECOMMENDATION: *
* Install fixpack containing code fix. *
**************************************************************** |
| Local Fix: |
Apply fixpack containing fix. |
| available fix packs: |
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 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Properly clean up cursors so no error is returned; fixed in
97FP5. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 19.01.2011
04.01.2012
04.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
