|
Problem IC63100
|
Status: Closed |
DBMS_SQL.Parse does not execute TRUNCATE sql statement |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
If the SQL command is a DDL command, it is immediately executed
and does not require running the EXECUTE procedure.
This does not work for TRUNCATE sql statement.
This APAR fixes this issue.
For example, we have following statements in a stored procedure:
-----------------------------------------------
curid := DBMS_SQL.OPEN_CURSOR;
statement := 'TRUNCATE TABLE tab_name';
DBMS_SQL.Parse(curid, statement, DBMS_SQL.native);
DBMS_SQL.CLOSE_CURSOR(curid);
-----------------------------------------------
After executing these statement, we will find that the table did
not get truncated. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All. *
****************************************************************
* PROBLEM DESCRIPTION: *
* If the SQL command is a DDL command, it is immediately *
* executed *
* and does not require running the EXECUTE procedure. *
* *
* This does not work for TRUNCATE sql statement. *
* *
* This APAR fixes this issue. *
* *
* *
* *
* For example, we have following statements in a stored *
* procedure: *
* ----------------------------------------------- *
* *
* curid := DBMS_SQL.OPEN_CURSOR; *
* *
* statement := 'TRUNCATE TABLE tab_name'; *
* *
* DBMS_SQL.Parse(curid, statement, DBMS_SQL.native); *
* *
* DBMS_SQL.CLOSE_CURSOR(curid); *
* *
* ----------------------------------------------- *
* *
* *
* *
* After executing these statement, we will find that the table *
* did *
* not get truncated. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to version 9.7 fixpack 1 or later. *
**************************************************************** |
| Local Fix: |
As a workaround you can include the DBMS_SQL.Execute(curid) as
follows, so that the table can really get truncated.
--------------------------------------------
curid := DBMS_SQL.OPEN_CURSOR;
statement := 'TRUNCATE TABLE tab_name';
DBMS_SQL.Parse ( curid, statement, DBMS_SQL.native);
v_status := DBMS_SQL.Execute (curid);
DBMS_SQL.CLOSE_CURSOR(curid);
-------------------------------------------- |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
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 9a 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 10 for Linux, UNIX, and Windows
|
| Solution |
This problem is first fixed in version 9.7 fixpack 1. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 10.09.2009
22.12.2009
22.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
