DB2 FOR LUW / DB2FORLUW / 970 - DB2

In the Command Line Processor (CLP), 
if the Remove New Line Character Option (-n) is ON, then 
whenever a literal string in a statement spans more than one 
line, and the last line begins with spaces and ends with the 
statement termination character, 
then the spaces at the beginning of the last line are stripped 
out. 
 
 
For example, suppose that you have a file named repro.db2 
which contains the following sequence of statements 
(with spaces at the beginning of the last line of the 
INSERT statement and of the UPDATE statement): 
 
drop table tab1; 
 
CREATE TABLE tab1 
( 
   col1 char(100) 
); 
 
UPDATE COMMAND OPTIONS USING N ON; 
 
INSERT INTO tab1 (col1) VALUES ('a 
  b 
  c 
  d'); 
 
UPDATE tab1 SET col1 = 'e 
  f 
  g 
  h'; 
 
 
Suppose that you run the above script in CLP like this: 
 db2 -tvf repro.db2 
so that the statement termination character defaults to being 
the semi-colon (";"); 
 
It outputs the following lines, which show that the spaces at 
the beginning of the last line of the string literals have been 
stripped out: 
 
 
drop table tab1 
DB20000I  The SQL command completed successfully. 
 
CREATE TABLE tab1 ( col1 char(100) ) 
DB20000I  The SQL command completed successfully. 
 
UPDATE COMMAND OPTIONS USING N ON 
DB20000I  The UPDATE COMMAND OPTIONS command completed 
successfully. 
 
INSERT INTO tab1 (col1) VALUES ('a  b  cd') 
DB20000I  The SQL command completed successfully. 
 
UPDATE tab1 SET col1 = 'e  f  gh' 
DB20000I  The SQL command completed successfully. 
 
 
The spaces before the "d" in the INSERT statement have been 
stripped out. 
The spaces before the "h" in the UPDATE statement have been 
stripped out.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users using db2 version prior to where the fix is        * 
* applied                                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When command option N ON, then last line of a string literal * 
* had its spaces stripped                                      * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to db2 version v9.7.1                                * 
****************************************************************

To work around the problem, you can put the 
statement termination character on a new line. For example: 
 
INSERT INTO tab1 (col1) VALUES ('a 
  b 
  c 
  d') 
; 
 
UPDATE tab1 SET col1 = 'e 
  f 
  g 
  h' 
; 
 
When you run the above statements in a script, it outputs the 
following, which shows that the literal strings were processed 
correctly: 
 
INSERT INTO tab1 (col1) VALUES ('a  b  c  d') 
DB20000I  The SQL command completed successfully. 
 
UPDATE tab1 SET col1 = 'e  f  g  h' 
DB20000I  The SQL command completed successfully. 
 
 
Alternatively, you can do either of the following: 
 
- Leave the value of the Remove New Line Character Option for 
  Command Line Processor as OFF. 
 
- In Command Line Processor, do not have literal strings 
  spanning more than one line.

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
 

Product db2_v91fp8 contains the fix as parent defect and AMDed 
to future releases

not known / see Local fix

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

20.08.2009
17.02.2010
17.02.2010

9.7.1