I found this sentence in the DB2 documentation (IBM i 7.3 Database Db2 for i SQL Reference, chapter 2 "Expressions"):

Could someone clarify what is meant by "integers with zero scale"? Aren't all integers zero scale?
We can have the case of DECIMAL with zero scale, like DECIMAL (10,0), so you there are types that worth mentioning that are 0 scale for them to behave like integers, but that sentence includes them or not? Or refers to something completely different?

Thank you!

------------------------------
Agostino Sclauzero
------------------------------

Hi Agostino Sclauzero ,

In Db2, the term "scale" refers to the number of digits to the right of the decimal point in a numeric value. An "integer with zero scale" refers to a whole number with no decimal part. This includes:

• Standard integer types: SMALLINT, INTEGER, and BIGINT.
• Decimal types defined with a scale of zero, such as DECIMAL(10,0) or NUMERIC(15,0).

To answer your question, yes, DECIMAL types with zero scale are included in the definition of "integers with zero scale." Db2 applies specific optimizations to these types.

Why this distinction?

Db2 handles columns with zero scale differently from those with a non-zero scale:

1. Columns with zero scale (SMALLINT, INTEGER, BIGINT, or DECIMAL(x,0)):
   • Arithmetic operations are performed in binary, which is faster because there's no need to handle decimal precision.
2. Columns with non-zero scale (e.g., DECIMAL(10,2)):
   • Operations must handle precision and scale, requiring additional processing such as rounding and ensuring the scale is maintained.

Practical Example

Here's an SQL example to illustrate:

CREATE TABLE TEST_SCALE (
    INTEGER_COL INTEGER,
    BIGINT_COL BIGINT,
    DECIMAL_COL DECIMAL(10,0), -- Zero scale
    DECIMAL_SCALE DECIMAL(10,2) -- Non-zero scale
);

INSERT INTO TEST_SCALE (INTEGER_COL, BIGINT_COL, DECIMAL_COL, DECIMAL_SCALE)
VALUES 
    (123, 123456789, 9999999999, 12345.67);

SELECT 
    INTEGER_COL + BIGINT_COL AS SUM_BINARY_OPERATION,
    DECIMAL_COL * 2 AS MULTIPLICATION_DECIMAL_ZERO_SCALE,
    DECIMAL_SCALE * 2 AS MULTIPLICATION_DECIMAL_NON_ZERO_SCALE
FROM TEST_SCALE;

Results:

• INTEGER_COL and DECIMAL_COL (zero scale): The calculation is performed in binary, without rounding or adjustments.
• DECIMAL_SCALE (non-zero scale): The calculation maintains 2 digits after the decimal point, with precise control. For example, 12345.67 * 2 results in 24691.34.

For further details, here are some official IBM documents explaining these concepts:

https://www.ibm.com/docs/en/db2-for-zos/12?topic=types-numbers

https://srinimf.com/2015/08/17/db2-numeric-string-date-time-data-types/

Kind regards,

Sylvain AKTEPE

------------------------------
Sylvain AKTEPE
IBM i Trainer & RPG Developer | AI Solutions
Armonie
Montpellier
0651637753
------------------------------