SMALLINT in DB2

Range and storage

A SMALLINT column stores a binary integer with a precision of 15 bits. The representable range is −32768 through +32767. Storage is 2 bytes per value.

1
2
3
4
5
6
7
8
9
CREATE TABLE APP.ITEM_STATUS (
  STATUS_CODE SMALLINT NOT NULL,
  STATUS_NAME VARCHAR(30) NOT NULL,
  PRIMARY KEY (STATUS_CODE)
);
 
INSERT INTO APP.ITEM_STATUS VALUES (1, 'NEW');
INSERT INTO APP.ITEM_STATUS VALUES (32767, 'MAX_OK');
-- INSERT INTO APP.ITEM_STATUS VALUES (32768, 'TOO_BIG');  -- overflow

Like other data types, SMALLINT may be null unless you specify NOT NULL. Primary keys and many code tables declare NOT NULL. The SMALLINT scalar function converts compatible values to SMALLINT (and can overflow if the source is too large).

1
2
3
VALUES SMALLINT(100);
VALUES SMALLINT(DECIMAL(20,0));  -- ok
-- VALUES SMALLINT(40000);       -- overflow

When to use SMALLINT

Choose SMALLINT when the domain is naturally small and stable:

• Status / reason codes with a few dozen values
• Flags and rankings (1–10 scales, priority levels)
• Small quantities (items per pack when packs stay tiny)
• Array dimensions or limits known to fit halfword range

Prefer INTEGER or BIGINT when:

• Counts can grow (order lines, hits, sequence-like values)
• You integrate with systems that already use 32-bit or 64-bit integers
• Saving two bytes is irrelevant next to row overhead and clarity

Do not use SMALLINT for money. Even “dollars without cents” eventually exceed 32767 for real businesses. Use DECIMAL for currency.

Space savings are real but modest: millions of rows × 2 bytes versus 4 bytes can matter on huge tables and indexes, yet wrong range assumptions cost more than the bytes you saved. When in doubt, INTEGER is a safer default for new designs.

Overflow and promotion

Overflow

Any attempt to place a value outside −32768…32767 into a SMALLINT target fails with a numeric overflow or assignment error. Sources include:

• Literals that are too large
• CAST / SMALLINT(function) on a large source
• Arithmetic whose result type is SMALLINT and whose magnitude does not fit
• Host variables that contain out-of-range values on INSERT/UPDATE

1
2
3
UPDATE APP.ITEM_STATUS
   SET STATUS_CODE = STATUS_CODE + 1
 WHERE STATUS_CODE = 32767;  -- next value would overflow

Application code should validate halfword ranges before writing, especially when values originate as INTEGER in COBOL or Java and are narrowed into SMALLINT columns.

Promotion

On the numeric promotion precedence list, SMALLINT can promote toward INTEGER, BIGINT, decimal, real, double, and DECFLOAT. That helps function resolution when a routine expects a wider numeric parameter. Mixed expressions often yield INTEGER or DECIMAL results rather than staying SMALLINT—check the SQL Reference operation tables when the result type matters for downstream casts.

1
2
3
-- SMALLINT column compared/combined with larger types promotes as needed
SELECT STATUS_CODE * 1000  -- result type widens per arithmetic rules
  FROM APP.ITEM_STATUS;

COBOL / host-variable mapping notes

Embedded SQL needs host variables that match SMALLINT’s binary halfword nature. Typical COBOL declarations (confirm with your shop):

1
2
3
       01  HV-STATUS-CODE      PIC S9(4) COMP.
      *    or PIC S9(4) COMP-5 / BINARY per standards
       01  HV-STATUS-CODE-IND  PIC S9(4) COMP.

Practical notes:

• DCLGEN — prefer generated declarations over hand-invented ones
• COMP vs COMP-5 — COMP-5 (native binary) is often recommended on z/OS to avoid truncated big-endian surprises; follow local standards
• Indicators — nullable SMALLINT columns need indicator variables; check them after FETCH
• Display fields — PIC S9(4) DISPLAY is not the same as SMALLINT storage; convert carefully
• Java / JDBC — map to short / Types.SMALLINT; watch autoboxing and widening to int
• C — typically short or int16_t aligned with SQL TYPE IS SMALLINT patterns in your SQC headers

1
2
3
4
5
6
7
8
9
10
11
           EXEC SQL
                SELECT STATUS_CODE
                  INTO :HV-STATUS-CODE:HV-STATUS-CODE-IND
                  FROM APP.ITEM_STATUS
                 WHERE STATUS_NAME = 'NEW'
           END-EXEC
           IF HV-STATUS-CODE-IND < 0
              DISPLAY 'STATUS WAS NULL'
           ELSE
              DISPLAY 'CODE=' HV-STATUS-CODE
           END-IF

If your program uses INTEGER host variables against SMALLINT columns, Db2 converts on the way in and out—but out-of-range program values still overflow on INSERT/UPDATE. Matching types reduces conversion cost and surprise.

SMALLINT versus neighboring types

• vs INTEGER — INTEGER is 4 bytes (±2.1 billion). Default choice for most whole numbers
• vs BIGINT — BIGINT is 8 bytes for huge counters and modern surrogate keys
• vs DECIMAL(5,0) — decimal scale 0 can hold up to 99999 for precision 5, but packed storage and semantics differ; SMALLINT is binary halfword exact to 32767
• vs CHAR codes — sometimes codes are better as CHAR(2) when they are truly identifiers ('A1'), not quantities

Explain It Like I'm Five

SMALLINT is a tiny number box that only holds counts from about negative thirty-two thousand to positive thirty-two thousand. It is perfect for “status 1, 2, 3” stickers. If you try to put a huge score into the tiny box, Db2 says no (overflow). In COBOL, you give the program a matching tiny box (a halfword COMP field) so Db2 can hand the number back and forth without squeezing it into the wrong size toy.

Exercises

1. State whether each value fits SMALLINT: −1, 0, 32767, 32768, −32768, −32769.
2. Design a SMALLINT NOT NULL status code table with three sample rows.
3. Explain one case where INTEGER is safer than SMALLINT even if today’s values are small.
4. Sketch COBOL host variables (data + indicator) for a nullable SMALLINT column.
5. Name two types SMALLINT can promote toward during function resolution.