DB2 FOR LUW / DB2FORLUW / 970 - DB2

In SQL Stored Procedure when truncation happens in some cases 
SQLCODE and token for Truncation warning are not set. 
 
Following test case demonstrates the issue: 
 
connect to test % 
 
set serveroutput on % 
 
create or replace procedure test 
begin 
  declare sqlcode integer; 
  declare save_sqlcode integer; 
  declare message varchar(128) default ''; 
 
  declare CL clob(4); 
  declare S char(8) default 'abcdefgh'; 
  declare C char(1); 
 
  declare continue handler for sqlwarning, sqlexception 
  begin 
    get diagnostics exception 1 message = message_text; 
    set save_sqlcode = sqlcode; 
    call dbms_output.put_line('In handler: sqlcode=' || 
save_sqlcode 
      || '; message text=''' || message || ''''); 
  end; 
 
  -- Why do we get SQLCODE 445 and a message token here... 
  call dbms_output.put_line('CLOB test:'); 
  set CL = clob(S, 4); 
 
  -- ... but not here? 
  call dbms_output.put_line('SET test:'); 
  set C = 'foo'; 
end % 
 
call test % 
 
Output: 
 
call test 
 
  Return Status = 0 
 
CLOB test: 
In handler: sqlcode=445; message text='SQL0445W  Value 
"abcdefgh" has been truncated.  SQLSTATE=01004   ' 
SET test: 
In handler: sqlcode=0; message text='   ' 
 
We expect to see SQLCODE +445 and a valid token for both cases, 
however it is set for the first SET statement only. On the 
second SET statement we get SQLSTATE '01004' in the SQLCA but we 
do not get SQLCODE and valid token. 
This APAR will change this behavior. 
 
A side effect of not returning sqlcode is that when we get -443 
the token value is not populated.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* In SQL Stored Procedure when truncation happens in somecases * 
* SQLCODE and token for Truncation warning are not set.We get  * 
* SQLSTATE '01004' in the SQLCA but we do not getSQLCODE and   * 
* valid token.A side effect of not returning sqlcode is that   * 
* when we get-443 the token value is not populated.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 3.                       * 
****************************************************************

Check the sqlstate 01004 in case of truncation

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
 

Problem was first fixed in Version 9.7 FP3

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC68471 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

03.05.2010
07.10.2010
07.10.2010

9.7.FP3