JR32633: IN COMMAND LINE PROCESSOR (CLP) WITH OPTION N ON, SPACES AT THE START OF A THE LAST LINE OF A STRING LITERAL ARE STRIPPED OUT

DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

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 is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  JR32642 IC62676

Fix information

• Fixed component name

  DB2 EDE WIN

• Fixed component ID

  5724N7601

Applicable component levels

• R910 PSN

     UP

• R950 PSN

     UP