Specifying TRUNCATE and STRIP options for output data

You can unload certain types of data into output fields that are shorter than the length of the output data. This data truncation occurs only when you explicitly specify the TRUNCATE option. Any CCSID conversion is applied first, and then truncation is applied to encoded data for output.

For bit strings, truncation occurs at a byte boundary. For character type data, truncation occurs at a character boundary (a multi-byte character is not split). If a mixed-character type data is truncated in an output field of fixed size, the truncated string can be shorter than the specified field size. In this case, blanks in the output CCSID are padded to the right. If the output data is in EBCDIC for a mixed-character type field, truncation preserves the SO (shift-out) and the SI (shift-in) characters around a DBCS substring.

The TRUNCATE option of the UNLOAD utility truncates string data, and it has a different purpose than the SQL TRUNCATE scalar function.

For VARCHAR and VARGRAPHIC, and VARBINARY output fields, in addition to the TRUNCATE option, the STRIP option is provided to remove the specified characters, or the leading blanks, the trailing blanks, or both. The strip operation is applied on the encoded data for output. If both the TRUNCATE and STRIP options are specified, the truncation operation is applied first, and then strip is applied. For example, the output for an UNLOAD job in which you specify both the TRUNCATE and STRIP options for a VARCHAR(5) output field is shown in the following table. In this table, an underscore represents a character that is to be stripped. In all cases, the source string is first truncated to '_ABC_' (a five-character string to fit in the VARCHAR(5) field), and then the strip operation is applied.

Table 1. Results of specifying both the TRUNCATE and STRIP options for UNLOAD
Specified STRIP option Source string Truncated string Output string Specified length
STRIP BOTH 
'_ABC_DEF'
 
'_ABC_'
 
'ABC'
 3
STRIP LEADING 
'_ABC_DEF'
 
'_ABC_'
 
'ABC_'
 4
STRIP TRAILING 
'_ABC_DEF'
 
'_ABC_'
 
'_ABC'
 4

The following control statement shows an example of using the STRIP option.

In the example, STRIP TRAILING '_' is included in the field specification for the TEXT column. The TEXT column contains variable character data with a maximum length of 8 characters, as specified by VARCHAR(8). When the UNLOAD utility unloads the table, all occurrences of the '_' (underscore) character at the end of the data from the TEXT column are stripped from the data.

    UNLOAD TABLESPACE DB.TS
           PUNCHDDN SYSPUNCH
           UNLDDN UNLDD2
           FROM TABLE TB
             (EMPNO    POSITION(*)  CHAR(6),
              TEXT     POSITION(*)  VARCHAR(8) STRIP TRAILING '_',
              DEPTNO   POSITION(*)  CLOB(4),
              ROWID    POSITION(*)  ROWID,
              LAST_UPDATE   POSITION(*)  TIME EXTERNAL)

The following table further illustrates the STRIP option.

Table 2. Example of the results of specifying the STRIP option for UNLOAD
Original data STRIP specification Data after stripping Final length
'_ABC_' STRIP LEADING '_' 'ABC_' 4
'_ABC_' STRIP TRAILING '_' '_ABC' 4
'_ABC_' STRIP BOTH '_' 'ABC' 3