DB2 SQLCODEs -180 to -330: data and datetime

How to read this range

Syntax codes (42xxx SQLSTATE) say “rewrite the statement.” Data exception codes (22xxx) say “the statement is legal, but this value cannot be stored or returned.” You still get a negative SQLCODE, still use DSNTIAR or GET DIAGNOSTICS, and still fix the application—not RETRY the same bad host data forever. SQLERRMC / MESSAGE_TEXT carry tokens: host variable position numbers, reason codes, column names, or the bad constant (truncated if long).

-180 invalid DATE, TIME, or TIMESTAMP value

IBM: THE DATE, TIME, OR TIMESTAMP VALUE value IS INVALID. The length or string representation does not conform to any valid format. The value token can be:

• For a host variable: the position number of the input host (blank if unknown)
• For a character string constant: the constant itself (length limited by SQLERRM)
• For a character column: the column name (view column without a base may show *N)
• Otherwise a string of *N

System action: the statement cannot be processed. Programmer response: correct the program so the value conforms to DATE, TIME, and TIMESTAMP syntax. SQLSTATE 22007.

Beginner causes: sending 2024-13-01 as a CHAR that you expect Db2 to accept as DATE; truncating a TIMESTAMP host to eight bytes; putting USA-style 12/31/2024 into a column that expects ISO; moving spaces or low-values from an uninitialized COBOL field. Prefer typed DATE/TIME/TIMESTAMP hosts or CAST with a format you control.

1
2
3
4
5
6
7
-- -180: not a valid date string for this context
INSERT INTO EMP (EMPNO, HIREDATE)
VALUES ('000010', '2024-99-99');
 
-- OK when the string matches a supported format
INSERT INTO EMP (EMPNO, HIREDATE)
VALUES ('000010', '2024-03-15');

-181 datetime string not a valid datetime value

IBM: THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE. The string is not in an acceptable range or correct format. Ranges include years 0001–9999; months 1–12; days by month (30 for Apr/Jun/Sep/Nov; 28/29 for February with leap years; 31 for the rest); hours 0–12 for USA format or 0–24 for other predefined formats (hour 24 requires zero minutes/seconds); minutes and seconds 0–59; fractional seconds 0–999999; time zone hours −12–14 and minutes 0–59.

System action: cannot process. Programmer response: check range and format, correct syntax, reissue. SQLSTATE 22007—same class as -180, different message. Think of -180 as “this does not look like a datetime at all” and -181 as “this looks like one but February 30th is still illegal.”

1
2
3
4
5
-- -181: February 30 never exists
SELECT * FROM EMP WHERE HIREDATE = '2023-02-30';
 
-- -181: hour 25 invalid outside documented formats
VALUES TIME('25:00:00');

-305 null without an indicator variable

IBM: THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE NO INDICATOR VARIABLE IS SPECIFIED. A FETCH, SELECT, VALUES INTO, or SET assignment retrieved a null into an output host (or SQLDA entry) that has no indicator. Aggregates on empty groups also return null—beginners forget indicators there too.

System action: statement fails; no data retrieved. Programmer response: examine the table definition and provide indicators for every host that can receive null. SQLSTATE 22002. Position-number identifies the host or parameter ordinal.

1
2
3
4
5
6
7
8
9
10
11
12
       01  HV-COMM                 PIC S9(7)V99 COMP-3.
       01  HV-COMM-IND             PIC S9(4) COMP.
      *
           EXEC SQL
                SELECT COMM
                  INTO :HV-COMM:HV-COMM-IND
                  FROM EMP
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.
           IF HV-COMM-IND < 0
              DISPLAY 'COMM IS NULL'
           END-IF.

Without :HV-COMM-IND, a null COMM yields -305. Checking only SQLCODE +100 does not cover “row found, column null.”

-311 input host string length bad

IBM: THE LENGTH OF INPUT HOST VARIABLE NUMBER position-number IS NEGATIVE OR GREATER THAN THE MAXIMUM. When evaluated, the length for an input host string (SQLDA entry) was negative or greater than the maximum allowed.

System action: cannot process. Programmer response: ensure lengths are not negative and not greater than the maximum. SQLSTATE 22501. Classic COBOL bug: forgetting to set the two-byte length on a VARCHAR before INSERT/UPDATE, or accidentally moving a binary value that looks like a huge length. Dynamic SQL with a wrong SQLDA SQLLEN shows the same code.

1
2
3
4
5
6
7
8
9
10
11
12
       01  HV-LNAME.
           49 HV-LNAME-LEN         PIC S9(4) COMP.
           49 HV-LNAME-TXT         PIC X(30).
      *
           MOVE 'SMITH' TO HV-LNAME-TXT
           MOVE 5 TO HV-LNAME-LEN
           EXEC SQL
                UPDATE EMP
                   SET LASTNAME = :HV-LNAME
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.
      * Negative or huge HV-LNAME-LEN → SQLCODE -311

-327 row outside last partition range

IBM: THE ROW CANNOT BE INSERTED BECAUSE IT IS OUTSIDE THE BOUND OF THE PARTITION RANGE FOR THE LAST PARTITION. On INSERT, the calculated partition key must fall within some partition’s bounds. If the key sorts past the last partition’s limit, Db2 returns -327.

System action: cannot process. Programmer response: specify a partition-key value within the bounds of the last partition (or redesign limit keys / add a partition). SQLSTATE 22525. This is not “duplicate key” (-803) and not “RI” (-530). Your data is fine for the table’s columns but not for the table space’s partitioning map.

1
2
3
4
-- Conceptual: last partition ends at 'M'
-- INSERT with partition key 'Z' → -327
INSERT INTO SALES_BY_REGION (REGION_CD, AMT)
VALUES ('Z', 100.00);

-330 string cannot be processed

IBM: A STRING CANNOT BE USED BECAUSE IT CANNOT BE PROCESSED. REASON reason-code, CHARACTER code-point, HOST VARIABLE position-number. An error occurred processing or converting a string. Reason codes include:

• 8 — length exception (for example expansion for PC MIXED exceeds maximum)
• 12 — invalid code point (code-point shows the bad point)
• 16 — form exception (invalid MIXED data)
• 20 — conversion procedure error (exit set an invalid length)
• SBCS found in a wchar_t / sqldbchar host, among other documented cases

If the string is an input host, position-number is its SQLDA ordinality; otherwise blank. System action: cannot process. Programmer response follows the reason: extend host length for expansion (8), fix the code point or source encoding (12), correct MIXED form (16), fix or replace the conversion exit (20), or remove SBCS from a graphic string (24). SQLSTATE 22021. Pair with CCSID and encoding tutorials—this is often an EBCDIC/Unicode or ASCII bridge problem, not a “typo in SQL.”

Debugging workflow

1. Read SQLSTATE and tokens

22007 → datetime. 22002 → null/indicator. 22501 → length. 22525 → partition. Then read SQLERRMC for position numbers and reason codes.

2. Dump the host at the failing statement

For -180/-181 print the CHAR you thought was a date. For -311 display the VARCHAR length in decimal. For -305 check which SELECT list item is nullable.

3. Do not confuse with nearby codes

-304 / -302 style value-range problems, -802 arithmetic overflow, and -420 invalid string for cast are cousins—different numbers, same “data is wrong” feeling. Look up the exact SQLCODE every time.

Explain It Like I'm Five

-180 and -181 are “the calendar sticker you wrote does not make sense” (wrong shape or impossible day). -305 is “Db2 tried to hand you an empty box and you had no place to put the empty-box flag.” -311 is “you said the word is −3 letters long.” -327 is “the toy does not fit in the last labeled bin.” -330 is “the translator broke while changing the letters from one alphabet to another.”

Exercises

1. Cause -181 with an illegal date literal, then fix it. Note the SQLSTATE.
2. SELECT a nullable column INTO a host without an indicator; capture -305; add the indicator and retest.
3. Set a VARCHAR length to −1 and show -311. What SQLSTATE do you get?
4. Explain in one sentence when you would expect -327 versus -803 on INSERT.
5. Look up -330 reason code 8 in IBM Docs and write the programmer response in your own words.