|
Problem IC66886
|
Status: Closed |
SELECT INTO IN COMPOUND STATEMENT DISALLOWED WHEN ISSUED BY CLI OR JDBC |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 |
| Problem description: |
If your application uses CLI or JDBC to issue a dynamic
SELECT INTO or VALUES INTO statement contained inside a
compound statement, DB2 will report SQLCODE -109 when the
statement is prepared. The error occurs for SQL PL compiled
compound statements, as well as PL/SQL anonymous blocks.
For example, you will obtain SQLCODE -109 if you attempt to
prepare the following compound statement via JDBC:
begin
declare test varchar(10);
select 'abc' into test from sysibm.sysdummy1;
set ? = test;
end |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* If your application uses CLI or JDBC to issue a dynamic *
* *
* SELECT INTO or VALUES INTO statement contained inside a *
* *
* compound statement, DB2 will report SQLCODE -109 when the *
* *
* statement is prepared. The error occurs for SQL PL compiled *
* *
* compound statements, as well as PL/SQL anonymous blocks. *
* *
* *
* *
* *
* *
* For example, you will obtain SQLCODE -109 if you attempt to *
* *
* prepare the following compound statement via JDBC: *
* *
* *
* *
* begin *
* *
* declare test varchar(10); *
* *
* select 'abc' into test from sysibm.sysdummy1; *
* *
* *
* *
* set ? = test; *
* *
* end *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.8 Fix Pack 3. *
**************************************************************** |
| Local Fix: |
As a workaround, convert the compound statement to an SQL
procedure, and then call the procedure from CLI or JDBC. For
example:
create or replace procedure workaround(out result varchar(10))
begin
select 'abc' into result from sysibm.sysdummy1;
end |
| available fix packs: |
DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux
|
| Solution |
This problem is fixed in DB2 Version 9.8 Fix Pack 3. |
| Workaround |
See LOCAL FIX. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.03.2010
20.12.2010
20.12.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.8.0.3
|
 |
