DB001043 String truncation

Products

• IDMS

Issue/Introduction

This article describes the logic behind the processing when numerics are the subject of a CAST() operation and some of the values may be too large - resulting in DB001043 String truncation messages.

Environment

Release: All supported releases.
Components: SQL Option.

Cause

The CAST() function converts data items from one type to another. As there are many different data types and the function needs to be able to handle all kinds of values, it needs to be flexible and as such, some of the results may be open to interpretation. In particular, when using CAST() to convert numeric values to character strings, care must be taken to ensure that the size of the target character string should be large enough to hold all potential values which the function may encounter, otherwise, DB001043 String truncation messages and unexpected results may be encountered.

For example, consider this sample data:

SELECT DEPT_ID_0410,
DEPT_HEAD_ID_0410
FROM EMPNET.DEPARTMENT;
*+
*+ DEPT_ID_0410  DEPT_HEAD_ID_0410
*+ ------------  -----------------
*+         5300                321
*+         5100                 15
*+         2000                 11
*+         1000                 13
*+         3100                  3
*+         3200                  4
*+         4000                  7
*+          100                 30
*+         5200                349
*+                                
*+ 9 rows processed

The task is to CAST() the DEPT_HEAD_ID_0410 to a character column. CHAR(3) can be used because it is known that all the values are less than 1000 and will fit in three characters:

SELECT DEPT_ID_0410,
CAST(DEPT_HEAD_ID_0410 AS CHAR(3))
FROM EMPNET.DEPARTMENT;           
*+
*+ DEPT_ID_0410  (EXPR)
*+ ------------  ------
*+         5300  321
*+         5100  15
*+         2000  11
*+         1000  13
*+         3100  3
*+         3200  4
*+         4000  7
*+          100  30
*+         5200  349
*+
*+ 9 rows processed

However, if the operand of the CAST() function is involved in a calculation that makes it too large for a CHAR(3), DB001043 is the result and what looks like unprintable characters are returned to the output field. Note that columns for which the value still fits are returned as before:

SELECT DEPT_ID_0410,
CAST(DEPT_HEAD_ID_0410 + 700 AS CHAR(3))
FROM EMPNET.DEPARTMENT;
*+ DB001043 T122 C1M322: String truncation
*+ DB001043 T122 C1M322: String truncation
*+
*+ DEPT_ID_0410  (EXPR)
*+ ------------  ------
*+         5300  ***
*+         5100  715
*+         2000  711
*+         1000  713
*+         3100  703
*+         3200  704
*+         4000  707
*+          100  730
*+         5200  **æ
*+
*+ 9 rows processed

For the two columns on which DB001043 is issued, what's being returned is the internal representation of the number. This can be seen by using the HEX() function.

SELECT DEPT_ID_0410,
HEX(CAST(DEPT_HEAD_ID_0410+700 AS CHAR(3)))
FROM EMPNET.DEPARTMENT;
*+ DB001043 T123 C1M322: String truncation
*+ DB001043 T123 C1M322: String truncation
*+
*+ DEPT_ID_0410  HEX(FUNCTION)
*+ ------------  -------------
*+         5300  01021C
*+         5100  F7F1F5
*+         2000  F7F1F1
*+         1000  F7F1F3
*+         3100  F7F0F3
*+         3200  F7F0F4
*+         4000  F7F0F7
*+          100  F7F3F0
*+         5200  01049C
*+
*+ 9 rows processed

Resolution

In this case, the product is working as designed. To avoid the DB001043 messages, use a CHAR() designation that you know will be large enough (in this example, 4):

SELECT DEPT_ID_0410,
CAST(DEPT_HEAD_ID_0410+700 AS CHAR(4))
FROM EMPNET.DEPARTMENT;
*+
*+ DEPT_ID_0410  (EXPR)
*+ ------------  ------
*+         5300  1021
*+         5100  715
*+         2000  711
*+         1000  713
*+         3100  703
*+         3200  704
*+         4000  707
*+          100  730
*+         5200  1049
*+
*+ 9 rows processed

Additional Information

CAST-function
DB001043