Fixes are available
APAR status
Closed as program error.
Error description
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.
Local fix
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.
Problem summary
Users affected: Users of DB2 on Linux, UNIX and Windows Problem Description: 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. Problem Summary: 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.
Problem conclusion
First fixed in DB2 9 Fix Pack 8
Temporary fix
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.
Comments
APAR Information
APAR number
JR32633
Reported component name
DB2 EDE WIN
Reported component ID
5724N7601
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-04-07
Closed date
2009-10-27
Last modified date
2009-10-27
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 EDE WIN
Fixed component ID
5724N7601
Applicable component levels
R910 PSN
UP
R950 PSN
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
12 October 2021