Db2 BIGINT Data Type

Range and storage

BIGINT is a binary integer with 63 bits of magnitude plus a sign. Each value occupies 8 bytes in the row. The range is enormous for business counting, but it is still finite—applications that generate keys without a plan can eventually hit limits in theory, though INTEGER usually hits first in real systems.

1
2
3
4
5
6
CREATE TABLE EVENT_LOG (
  EVENT_ID      BIGINT       NOT NULL,
  EVENT_TS      TIMESTAMP    NOT NULL,
  PAYLOAD_LEN   INTEGER,
  PRIMARY KEY (EVENT_ID)
);

When to use BIGINT

• Surrogate keys or identity columns that may exceed INTEGER
• High-volume message, click, or audit sequence numbers
• Interfacing with languages/APIs that use 64-bit integers by default
• Intermediate results of calculations that overflow INTEGER

When not to default to BIGINT

BIGINT doubles storage versus INTEGER for every row and index entry that includes the column. If the business maximum will never approach two billion, INTEGER (or SMALLINT) is clearer and leaner. Do not use BIGINT for money—use DECIMAL with an appropriate scale.

1
2
3
4
5
6
Quick chooser (whole numbers only):
 
  Fits in −32768…32767?     -> SMALLINT
  Fits in ±2.1e9?           -> INTEGER
  Needs up to ±9.22e18?     -> BIGINT
  Needs fractional digits?  -> DECIMAL / DECFLOAT / float types

Overflow and promotion

Overflow

BIGINT can still overflow—multiplying two huge BIGINT values may exceed ±9.22×10¹⁸. Narrowing casts are a more common beginner trap: assigning a BIGINT host value to an INTEGER column, or CAST(big AS INTEGER), fails when the value does not fit.

1
2
3
4
5
6
7
-- Promote INTEGER operands before multiplying if product may exceed INTEGER
SELECT BIGINT(a) * BIGINT(b) AS PRODUCT
  FROM METRICS;
 
-- Narrowing cast: fails if EVENT_ID > 2147483647
SELECT CAST(EVENT_ID AS INTEGER) AS EVENT_INT
  FROM EVENT_LOG;

Promotion

In mixed expressions, Db2 promotes numeric types according to documented precedence. INTEGER and BIGINT together typically yield BIGINT-scale results for the wider calculation path. Mixing BIGINT with DECIMAL or DECFLOAT shifts you into decimal/floating rules—including scale and rounding. Prefer explicit CAST when the result type matters for a column assignment or host variable.

The scalar function BIGINT(expression) converts a value to BIGINT when the conversion is legal. Use it to document intent in views and SELECT lists.

COBOL / host-variable mapping notes

BIGINT needs an 8-byte binary host field. A common pattern (verify against your shop standard) is:

1
2
3
4
5
6
7
8
9
       01  WS-EVENT-ID         PIC S9(18) USAGE COMP-5.
 
       EXEC SQL
            SELECT EVENT_ID
              INTO :WS-EVENT-ID
              FROM EVENT_LOG
             WHERE EVENT_TS >= :WS-FROM-TS
             FETCH FIRST 1 ROW ONLY
       END-EXEC.

• PIC S9(18) COMP-5: frequent choice for BIGINT-range binary integers
• Do not map BIGINT into PIC S9(9) COMP—that is INTEGER-sized and will not hold the range
• Display edits (PIC −9(18)) are for printing; keep SQL INTO targets binary unless your standards say otherwise
• Nullability still requires an indicator variable for nullable BIGINT columns
• Java long, C long long, and similar 64-bit types align conceptually with BIGINT across DDF applications

Design and operations notes

• Index keys that are BIGINT are wider than INTEGER—factor into page density
• Application logs should print BIGINT with full precision (no silent truncation)
• When migrating INTEGER identity columns to BIGINT, plan application, CAST, and unload impacts
• Generators and sequences should declare BIGINT if the sequence object and column are BIGINT

Explain It Like I'm Five

INTEGER is a big toy chest. BIGINT is an even bigger toy chest—almost a whole closet. Both only hold whole toys, not broken half-toys. If you try to pour the closet into the smaller chest, toys spill everywhere (overflow). If your counting game might go past what the chest can hold, start with the closet (BIGINT) instead of upgrading in a panic later.

Exercises

1. Write the BIGINT minimum, maximum, and byte length.
2. Decide INTEGER or BIGINT for: (a) seats in a theater, (b) a global click counter for a busy website over decades, (c) days since a known epoch under 100,000.
3. Write SQL that multiplies two INTEGER columns into a BIGINT result using BIGINT() or CAST.
4. Explain why CAST(BIGINT_COL AS INTEGER) can fail at run time.
5. Propose a COBOL host declaration for a BIGINT primary key and list one mistake to avoid.