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.
Specified STRIP option | Source string | Truncated string | Output string | Specified length |
---|---|---|---|---|
STRIP BOTH |
|
|
|
3 |
STRIP LEADING |
|
|
|
4 |
STRIP TRAILING |
|
|
|
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.
Original data | STRIP specification | Data after stripping | Final length |
---|---|---|---|
'_ABC_' |
STRIP LEADING '_' |
'ABC_' |
4 |
'_ABC_' |
STRIP TRAILING '_' |
'_ABC' |
4 |
'_ABC_' |
STRIP BOTH '_' |
'ABC' |
3 |