SQL Code -302 SQLState 22001: Data Truncation Fix for DB2 Systems

SQLCODE=-302 with SQLSTATE=22001 stopped my DB2 application dead in its tracks last week. ⚡ This error isn't just annoying—it's a clear sign your data is trying to squeeze into a space it wasn't built for, and the database is refusing to let it.

The good news? It's one of the most straightforward errors to diagnose once you know where to look.

The root cause is almost always data truncation—whether you're inserting a 20-character string into a 10-character field or feeding a decimal with too many digits into a numeric column. DB2's strict data type enforcement makes this error pop up immediately, saving you from silent corruption later.

I've seen it trip up everything from simple INSERT statements to complex stored procedures where developers assumed data formats would match between systems.

You'll fix it by validating your data types against table definitions, adjusting your application logic to handle proper formatting, and—if needed—expanding column sizes in your schema.

The process takes less than 30 minutes once you identify the mismatched data, and it's a skill that transfers directly to Oracle, SQL Server, and other databases that use similar error codes for the same problem. Here's exactly how I tracked it down and resolved it.

Fair warning: this error often hides in transaction logs where you'd least expect it. The most common culprits are user inputs, automated data imports, and legacy systems feeding modern databases with outdated formats.

Once you've fixed the immediate truncation, I'll show you how to add validation checks that catch these issues before they reach the database.

Why it happens

When you encounter a SQLCODE=-302 error paired with SQLSTATE=22001 in DB2, you’re staring at a classic data truncation issue—your database is rejecting an input because it’s too big for the target field.

This isn’t just a random glitch; it’s a precise clash between what your application tries to shove into a column and what the database’s schema allows. Let’s break down the why behind this error with crystal clarity.

🔍 1. Mismatched Data Types or Lengths

DB2 enforces strict rules on data types and field sizes. If your application attempts to insert or update a value that exceeds the column’s defined length—or tries to stuff a VARCHAR(10) into a CHAR(5) field—DB2 throws this error. For example:

• String too long: Trying to insert "IBM_DB2_2024" (13 chars) into a VARCHAR(10) column.
• Numeric overflow: Storing a 12-digit number in a SMALLINT (which maxes out at 32,767).
• Date/time misalignment: Passing a timestamp with microseconds into a DATE column (which only handles years, months, and days).

Why it happens: DB2’s data integrity checks kick in during preparation (before execution), rejecting the operation entirely. The error isn’t about syntax—it’s about physical capacity.

📏 2. Implicit or Explicit Type Conversion Failures

Sometimes, the issue isn’t the raw data but how DB2 tries to interpret it. If your application passes a string like "2024-05-15" to a numeric column (e.g., INTEGER), DB2 will fail to auto-convert it, triggering truncation.

Even worse, if you rely on implicit casts—like storing a DECIMAL(10,2) value in a FLOAT column—precision loss can occur, and DB2 may reject the operation.

Why it happens: DB2’s type affinity rules are strict. Unlike some databases that silently truncate or round, DB2 explicitly rejects conversions that could lose data or exceed limits. This is by design to prevent silent corruption.

🔄 3. Dynamic SQL or Parameterized Queries Gone Wrong

Dynamic SQL (e.g., EXECUTE IMMEDIATE) or poorly parameterized queries are breeding grounds for truncation errors. If your code builds a SQL string dynamically—like concatenating user input into a VALUES clause—you risk:

• Accidentally omitting quotes around strings, causing DB2 to treat part of the value as SQL syntax.
• Including hidden characters (e.g., Unicode BOM markers or trailing spaces) that inflate the string length beyond the column’s limit.
• Using host variables or bind parameters with mismatched data types in the query definition.

Why it happens: Dynamic SQL bypasses some of DB2’s static validation, so runtime errors like truncation slip through until execution. This is why static SQL (prepared statements) is often safer.

🔧 4. Schema or Table Structure Changes Without Updates

If your application was written for a VARCHAR(50) column but the DBA later alters the table to VARCHAR(20), existing code will suddenly fail. Similarly, if you clone a table or restore a backup with different constraints, legacy queries may hit truncation errors without warning.

Why it happens: DB2 doesn’t retroactively update application logic. The schema change is physical, but the application’s logical assumptions (e.g., "this field can hold 50 chars") remain hardcoded.

⚠️ 5. Hidden Characters or Encoding Issues

Not all characters are created equal. A string might appear to be 10 characters long in your application but actually be 15 when encoded in UTF-8 (due to multi-byte characters like é or 日本語).

If your column is defined as CHAR(10) CCSID 1208 (EBCDIC) but your data is UTF-16, DB2 will truncate it silently—or reject it outright.

Why it happens: DB2 uses code page units (not bytes or characters) to measure length. A misconfigured CCSID (code set identifier) can make your "short" string appear longer than expected.

💡 Pro Tip: How to Diagnose Fast

To pinpoint the exact cause, use these DB2 commands:

• DESCRIBE TABLE schema.table – Verify column lengths and data types.
• SELECT LENGTH(yourcolumn), DATALENGTH(yourcolumn) FROM your_table – Check actual vs. stored length.
• DB2EXPLN – Analyze the failed statement’s access plan for type conversion hints.

For dynamic SQL, enable CURRENT SQLCA logging to capture the exact failing statement and bind values.

How to solve it

Encountering SQLCODE=-302 with SQLSTATE=22001 can feel like hitting a roadblock, but the fixes are often straightforward once you pinpoint the root cause. Below are practical solutions mapped to common triggers, along with prevention tips to keep your DB2 environment running smoothly. Let’s roll up our sleeves and get this resolved!

###

🔥 Cause 1: Data Exceeds Column Size Limits

When you try to insert or update data that’s longer than the defined column length, DB2 throws this error. For example, inserting a 50-character string into a VARCHAR(30) field will trigger SQLCODE=-302.

🍳 Fix: Adjust Column Size or Trim Data

• Option 1: Expand the column Use ALTER TABLE to resize the column if the data is valid but the field is too small:

  ALTER TABLE yourtable ALTER COLUMN yourcolumn SET DATA TYPE VARCHAR(100);

• Option 2: Trim or validate data before insertion Use functions like SUBSTR() or TRIM() to ensure data fits:

  INSERT INTO yourtable (yourcolumn) VALUES (TRIM(SUBSTR(yourlongstring, 1, 30)));

• Option 3: Use CAST or CONVERT for implicit truncation If you’re okay with cutting off excess data, force a cast:

  INSERT INTO yourtable (yourcolumn) VALUES (CAST(yourlongstring AS VARCHAR(30)));

💡 Prevention Tip:

Always define column sizes based on actual requirements, not just "maximum possible." Use CHECK constraints to enforce limits programmatically:

ALTER TABLE yourtable ADD CONSTRAINT chkcolumnsize CHECK (LENGTH(yourcolumn) <= 30);

###

👨‍🍳 Cause 2: Incorrect Data Type Mismatch

This error can also occur when you try to store a value of one data type (e.g., a string) in a column expecting another (e.g., numeric). For instance, inserting '123ABC' into a DECIMAL(5,2) field.

🔪 Fix: Convert Data Types Properly

• Option 1: Explicitly cast the value Convert the data to the correct type before insertion:

  INSERT INTO yourtable (numericcolumn) VALUES (CAST('123.45' AS DECIMAL(5,2)));

• Option 2: Use TRYCAST (DB2 11.1+) Handle potential errors gracefully:

  INSERT INTO yourtable (numericcolumn)
          SELECT TRYCAST('123ABC' AS DECIMAL(5,2)) FROM SYSIBM.SYSDUMMY1;

• Option 3: Validate data in application logic Add checks in your code (e.g., Python, Java) before sending queries to DB2.

🌡️ Prevention Tip:

Use CREATE TABLE with explicit data types and document expected formats. For example:

CREATE TABLE orders (
    orderid INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL, -- Clearly specifies numeric + precision
    notes VARCHAR(255) -- Explicitly allows text
);

• Option 1: Check LOB size constraints Ensure your data fits within DB2’s limits (e.g., max CLOB size is 2GB). Use LENGTH() or DATALENGTH() to verify:

  SELECT DATALENGTH(yourclobcolumn) FROM yourtable WHERE DATALENGTH(yourclobcolumn) > 1000000;

• Option 2: Use SET for LOB updates Avoid direct assignments; use SET with LOCATOR for large objects:

  UPDATE yourtable SET yourclobcolumn = LOCATOR(yourlargestring);

• Option 3: Specify encoding explicitly If encoding mismatches occur, cast with the correct collation:

  INSERT INTO yourtable (textcolumn)
          VALUES (CAST('éñçödîng' AS VARCHAR(100) CHARACTER SET UTF8));

• Option 1: Set proper bind variable sizes In JDBC, ensure PreparedStatement parameters match column sizes:

  PreparedStatement stmt = conn.prepareStatement("INSERT INTO yourtable (textcol) VALUES (?)");
          stmt.setString(1, longString); // Driver may truncate silently or throw -302

  Use setMaxFieldSize() to enforce limits:

  stmt.setMaxFieldSize(30); // Explicitly cap input

• Option 2: Enable truncation warnings Configure your DB2 client to log warnings (not just errors) for debugging:

  db2set DB2WARNLEVEL=3
          db2set DB2WARNTYPE=2

• Option 3: Use stored procedures with validation Offload truncation checks to the database layer:

  CREATE PROCEDURE safeinsert(IN ptext VARCHAR(100))
          LANGUAGE SQL
          BEGIN
              IF LENGTH(ptext) > 100 THEN
                  SIGNAL SQLSTATE '75000' SET MESSAGETEXT = 'Input too long';
              ELSE
                  INSERT INTO yourtable (textcol) VALUES (ptext);
              END IF;
          END;

💡 Prevention Tip:

Implement input validation layers in your application. For example, in a REST API, reject oversized payloads with a 400 Bad Request before they hit DB2.

Frequently asked questions about SQLCODE=-302 and SQLSTATE=22001