Trailing blanks in Db2 catalog columns

Certain Db2 catalog table columns were converted from fixed to varying length data types in earlier Db2 releases. If your applications uses these columns in comparisons such as LIKE predicates, or when comparing the lengths of values, you might need to adjust the applications to get the desired results.

Unlike fixed-length columns, VARCHAR column values generally do not contain any trailing blanks, unless the blanks were explicitly part of the data value when it was inserted. However, when the data type of a column is altered from CHAR to VARCHAR, the existing data is not changed in any way, so the column data is padded to the original CHAR length and remains that way. The conversion does not remove trailing blanks from the original fixed length value. The same rule applies in any table, including the Db2 catalog tables.

For more information about the result of converting a fixed-length column to a varying length, see "Altering the data type, length, precision, or scale of a column" in ALTER TABLE statement.

If your application needs the varying length character strings without trailing blanks, you can use the STRIP function to remove the trailing blanks in the converted columns. See STRIP scalar function.

You can also change how LIKE predicates compare values with trailing blanks by enabling the LIKE_BLANK_INSIGNIFICANT subsystem parameter. For more information, see LIKE predicate.

Example

For example, the data type of the CREATOR column in the SYSTABLES catalog table was converted from CHAR(8) to VARCHAR(128) in DB2 version 8.

Assume that you issued the following CREATE TABLE statement in DB2 version 7:

CREATE TABLE WELK.T1 (C1 CHAR(5));

If you issued the following query in DB2 version 7, the returned value was 8 because CREATOR was a fixed-length CHAR(8) column.

SELECT LENGTH(CREATOR) FROM SYSIBM.SYSTABLES 
    WHERE CREATOR = 'WELK' AND NAME = 'T1';

However, assume that you issue the following CREATE TABLE statement in Db2 13:

CREATE TABLE WELK.T2 (C1 CHAR(5));

Compare the results of these queries in Db2 13:

The following query returns the value 8 because T1 was created in DB2 version 7, and the padded characters from the fixed-length value in the CREATOR column were not removed by the conversion at migration to DB2 version 8:
```
SELECT LENGTH(CREATOR) FROM SYSIBM.SYSTABLES 
    WHERE CREATOR = 'WELK' AND NAME = 'T1';
```
The following query returns the value 4 because T2 was created in Db2 13, when CREATOR was a VARCHAR column.
```
SELECT LENGTH(CREATOR) FROM SYSIBM.SYSTABLES 
    WHERE CREATOR = 'WELK' AND NAME = 'T2';
```
The following query also returns the value 4 because the STRIP function removes any trailing blanks from the CREATOR column before the length is determined.
```
SELECT LENGTH(STRIP(CREATOR)) FROM SYSIBM.SYSTABLES 
    WHERE CREATOR = 'WELK' AND NAME = 'T1';
```