PH13579: BIGSQL ENFORCES THE LINE TERMINATION ON DELIMITED HADOOP TABLES TO BE NEWLINE

APAR status

• Closed as fixed if next.

Error description

• The BigSQL documentation states that when creating Hadoop
  tables that use the DELIMITED  text format, the only value
  allowed for the line terminator in the expression
  
  LINES TERMINATED BY
  
  is newline ( '\n' ).
  While this is what is documented, historically BigSQL has
  allowed other line terminator values up until version 5.0.3.
  
  Newer releases  instead only accept '\n' as line terminator.
  This can cause issues if you are going to upgrade Big SQL from
  version 5.0.3 and older to 5.0.4 and newer, because the data in
  tables that use non-standard line terminators won't be read
  properly.
  

Local fix

• Before upgrading to IBM Db2 Big SQL 5.0.4 or newer, you should
  modify the tables affected by changing the line terminator to
  be '\n'.
  
  A way to find such tables is to save the bigsql database schema
  definition with
  
  db2look -d bigsql -e > mybigsql.ddl
  
  then search in the file for tables that contain
  
  LINES TERMINATED BY
  
  and specify a value different than '\n'.
  
  For these tables you can create a table with the same schema
  and type but without specyfying the line terminator or
  specyfying '\n' then move the data from the orignal table to
  the new table and finally swap the name of the tables.
  
  Note that if the delimited files in the table using nonstandard
  line terminators are generated via an external  job rather than
  be inserted by BigSQL , the job definition need to also be
  amended to generated '\n' delimited lines.
  

Problem summary

• Please see the problem description.
  

Problem conclusion

Temporary fix

Comments

APAR Information

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

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

Fix information

Applicable component levels