Db2 INTEGER Data Type

Range and storage

An INTEGER value is a binary integer with 31 bits of magnitude plus a sign. Db2 stores each INTEGER column value in 4 bytes. Unlike DECIMAL, there is no separate precision/scale parameter—the type itself fixes the range.

1
2
3
4
5
6
CREATE TABLE ORDER_LINE (
  ORDER_ID    INTEGER     NOT NULL,
  LINE_NO     INTEGER     NOT NULL,
  QTY         INTEGER     NOT NULL,
  PRIMARY KEY (ORDER_ID, LINE_NO)
);

Integer literals in SQL are written as numbers without quotes: 42, -1000. Quoted '42' is a character string that may convert implicitly in some contexts—but clear code uses true numeric literals for numeric columns.

When to use INTEGER

Choose INTEGER when:

• Values are whole numbers (no fractional money or rates)
• The domain fits comfortably in −2,147,483,648 … 2,147,483,647
• You want compact storage and efficient binary integer arithmetic
• You are modeling keys, counters, sequence-like attributes, or quantities measured in whole units

INTEGER vs SMALLINT vs BIGINT

• SMALLINT (2 bytes): prefer when values always fit in −32,768…32,767 (for example tiny codes). Saves space in wide tables and indexes.
• INTEGER (4 bytes): default “normal” whole-number type for most application attributes.
• BIGINT (8 bytes): use when values can exceed INTEGER, such as very large event counters or certain generated keys.

INTEGER vs DECIMAL

IBM documentation advises: for integer values, use SMALLINT, INTEGER, or BIGINT—not DECIMAL. DECIMAL is for fixed-point numbers with a fractional scale (money, percentages with fixed digits). Using DECIMAL(9,0) as a fake integer wastes clarity and can surprise you with packed-decimal behavior and host mappings.

Overflow and promotion

Overflow

If an expression or assignment produces a value outside the INTEGER range, Db2 signals numeric overflow. Examples include multiplying two large INTEGER values into a temporary that still expects INTEGER, or casting a BIGINT that is too large down to INTEGER.

1
2
3
4
5
6
7
-- Risky if QTY and UNIT_FACTOR are both large INTEGER values
SELECT QTY * UNIT_FACTOR AS EXTENDED
  FROM ORDER_LINE;
 
-- Safer when the product may exceed INTEGER: promote explicitly
SELECT BIGINT(QTY) * BIGINT(UNIT_FACTOR) AS EXTENDED
  FROM ORDER_LINE;

Overflow is a run-time data error, not a syntax error. Test boundary values when columns feed calculations.

Promotion and mixed arithmetic

When SQL mixes numeric types, Db2 applies promotion and conversion rules so operands share a result type. Mixing INTEGER with DECIMAL or floating-point can change the result type and rounding behavior. Mixing INTEGER with BIGINT often promotes toward BIGINT. Learn the exact precedence tables later; as a beginner rule: do not assume the result of mixed arithmetic stays INTEGER.

1
2
3
4
5
6
7
8
9
-- INTEGER column compared to a typed literal
SELECT *
  FROM ORDER_LINE
 WHERE QTY > 100;
 
-- Cast when moving between integer sizes deliberately
SELECT CAST(LINE_NO AS SMALLINT) AS LINE_SMALL
  FROM ORDER_LINE
 WHERE LINE_NO <= 32767;

COBOL / host-variable mapping notes

In embedded SQL, host variables carry INTEGER values between COBOL and Db2. A typical fullword mapping looks like:

1
2
3
4
5
6
7
8
9
10
       01  WS-ORDER-ID         PIC S9(9)  USAGE COMP.
       01  WS-QTY              PIC S9(9)  USAGE COMP-5.
 
       EXEC SQL
            SELECT QTY
              INTO :WS-QTY
              FROM ORDER_LINE
             WHERE ORDER_ID = :WS-ORDER-ID
               AND LINE_NO  = 1
       END-EXEC.

• COMP / BINARY / COMP-4: traditional binary; watch truncation and sign rules for PIC precision vs machine size.
• COMP-5: often preferred for portable binary integers that match native binary representation—follow your compiler and Db2 precompiler guidance.
• PIC S9(9): common picture for INTEGER-range values; confirm with shop standards (some use S9(8) COMP or explicit SYNCHRONIZED layouts).
• Always check SQLCODE after FETCH/SELECT INTO; a null INTEGER needs an indicator variable.

Mismatching a Db2 INTEGER column with a halfword SMALLINT host variable (or vice versa) can cause truncation or unexpected SQLCODEs. Keep the host declaration aligned with the column type.

Practical design tips

• Document the business maximum for every INTEGER attribute
• Leave headroom for counters that grow for years
• Prefer IDENTITY/SEQUENCE designs that match INTEGER vs BIGINT deliberately
• Do not store money in INTEGER cents unless that is an explicit, documented rule
• Index-friendly: INTEGER keys are compact compared with long character codes

Explain It Like I'm Five

INTEGER is a special box that only holds whole toys—no half toys. The box is medium-sized: it can hold a huge pile, but not an endless pile. If you try to stuff in more toys than the box allows, it overflows and complains. Smaller boxes (SMALLINT) hold fewer toys; bigger boxes (BIGINT) hold more. Money with coins usually needs a different kind of box (DECIMAL) that understands fractional parts.

Exercises

1. State the minimum and maximum INTEGER values and the storage size in bytes.
2. Pick INTEGER, SMALLINT, or BIGINT for: (a) number of children in a family, (b) a national population counter that might exceed two billion, (c) a typical order line quantity under one million.
3. Write CREATE TABLE SQL with an INTEGER primary key and an INTEGER quantity column.
4. Explain one way INTEGER arithmetic can overflow even when each input column looks “reasonable.”
5. Draft a COBOL host variable declaration your shop might use for an INTEGER column and note which USAGE you chose and why.