Specifying the TRUNCATE and STRIP options

You can load certain fields that are longer than the length of target column by truncating the data. DB2® truncates the data only when you explicitly specify the TRUNCATE option.

You can specify TRUNCATE with the CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY, and VARBINARY data type options. LOAD first applies any CCSID conversion, and then truncates the data. The TRUNCATE option of the LOAD utility truncates string data, and it has a different purpose than the SQL TRUNCATE scalar function.

You can also remove a specified character from the beginning, end, or both ends of the data by specifying the STRIP option. This option is valid only with the CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY, and VARBINARY data type options. If you specify both the TRUNCATE and STRIP options, LOAD performs the strip operation first. For example, if you specify both TRUNCATE and STRIP for a field that is to be loaded into a VARCHAR(5) column, LOAD alters the character strings as shown in the following table. In this table, an underscore represents a character that is to be stripped.

Table 1. Results of specifying both TRUNCATE and STRIP for data that is to be loaded into a VARCHAR(5) column.
Specified STRIP option Input string String after strip operation String that is loaded
STRIP BOTH 
‘_ABCDEFG_'
 
‘ABCDEFG'
 
‘ABCDE'
STRIP LEADING 
‘_ABC_'
 
‘ABC_'
 
‘ABC_'
STRIP TRAILING 
‘_ABC_DEF_'
 
‘_ABC_DEF'
 
‘_ABC_'