sinistral.sourcery

The DB2 command-line processor (CLP; invoked from a shell with the command db2) truncates output greater than 8K. One way to retrieve the full text of a column value that exceeds that size (e.g. CLOB data) is to use the export command. For example, having connected a database and set the current schema, one could use a statement of the following form from within the db2 CLP (note that the "db2 =>" is the CLP prompt):

   db2 => export to result.del of del modified by chardel'' coldel; decpt, select <column> from <table>

The "of del" indicates that the output file should contain delimited data. The delimiters used, and how they are applied, is defined by the modified by clause and its qualifiers "chardel" and "coldel";

• chardel: defines the character that will be used to delimit character data; in the above example a single quote (') is used.
• coldel: defines the character that will be used to delimit columns; in the above example a semi-colon (;) is used.

The file produced by this statement would look something like:

   1;'foo';256;'KB'
   2;'bar';512;'KB'
   3;'baz';1;'MB'

Note, however, that if the column value that is being exported is longer than the default character column that can be exported, export will truncate the data and issue the warning:

SQL3132W The character data in column "DATA" will be truncated to size "32700"

To work around this, the lobfile parameter may be used to specify a base prefix for the files that will be used to store LOB data without truncation. The form of the command then becomes:

   export to result.del of del lobfile result_lob_ modified by chardel'' coldel; decpt, select data from debug_data

For more information please refer the IBM Infocenter pages that describe the EXPORT command, and its file-type modifiers.