SQLCode=-302 SQLState=22001: Data Truncation Fix for Stuck Queries

SQLCode=-302, SQLState=22001 stopped my query dead in its tracks last week—turns out my 200-character string was hitting a 100-character column limit. ⚡ The error message is clear, but the fix isn’t always obvious, especially when you’re staring at a production database with no time to waste.

This error happens when data you’re inserting or updating is too big for its target column—whether it’s a VARCHAR, CHAR, or even a numeric field with precision limits.

DB2 and IBM i throw this same code, but the solutions are nearly identical: check your data length, verify table constraints, and adjust either the data or the schema. I’ve fixed this on tables with implicit lengths, LOBs, and even JSON fields where the parser choked.

You’ll get your queries running again in minutes by validating input lengths first, then either trimming data or expanding columns. The key is catching it before it hits production—my team now adds length checks to all ETL jobs as a safeguard.

For DB2, the LENGTH() function is your best friend; IBM i users should check the SQLCA for details.

Works across DB2 LUW, IBM i, and even some Oracle environments where similar truncation errors occur. Let’s walk through the exact steps that saved my team’s weekend—and how to prevent it from happening again.

Why it happens

When you encounter SQLCODE=-302 with SQLSTATE=22001, you’re dealing with a classic database error: data truncation. This happens when a value you’re trying to insert or update is too large for the target column’s defined size.

Let’s break down the root causes in detail so you can diagnose and fix them effectively.

🔍 1. Column Size Mismatch

This is the most common cause. Your database column has a fixed length (e.g., VARCHAR(10)), but the data you’re inserting or updating exceeds that limit. For example:

• Attempting to store "ThisIsWayTooLong" in a VARCHAR(10) column truncates the string to "ThisIsWayT" and triggers the error.
• Numeric or date values may also fail if they exceed the column’s precision/scale (e.g., inserting 99999999999999999999 into a BIGINT(10) column).

Why it happens: Databases enforce constraints to ensure data integrity. When a value violates these constraints, SQL rejects it with SQLCODE=-302.

📊 2. Data Type Incompatibility

Sometimes, the issue isn’t size but the type of data you’re trying to store. For instance:

• Inserting a TIMESTAMP into a DATE column (or vice versa) can cause truncation of time components.
• Storing a FLOAT in a DECIMAL(5,2) column may fail if the decimal precision exceeds the column’s limits.
• Using CHAR instead of VARCHAR (or padding with spaces) can lead to unexpected truncation if the input doesn’t match the fixed length.

Why it happens: The database engine attempts to cast or convert the data, but the target column’s type or precision cannot accommodate the source data without loss.

🔄 3. Automatic Truncation in Implicit Conversions

Some databases (like IBM Db2) automatically truncate data during implicit conversions, such as:

• Converting a VARCHAR to a CHAR without explicit length handling.
• Using functions like CAST() or CONVERT() without specifying sufficient precision.
• Joining tables where a column in one table is wider than the corresponding column in another.

Why it happens: The database silently truncates data to fit the smaller column, but if the application or query expects the full value, it fails with SQLCODE=-302.

⚠️ 4. Hidden Characters or Encoding Issues

Non-printable characters, multi-byte encodings (e.g., UTF-8), or hidden formatting (like BOM markers in CSV imports) can inflate the actual byte length of your data beyond the column’s limit. For example:

• A string with N' prefix (Unicode) may occupy twice the space of a standard VARCHAR.
• Special characters (e.g., emojis, non-Latin scripts) can consume multiple bytes per character.
• Trailing whitespace or control characters (e.g., \n, \t) may not be visible but still count toward the limit.

Why it happens: Databases measure storage in bytes, not characters. If your column is defined as VARCHAR(50) but your UTF-8 string uses 100 bytes, truncation occurs.

🔧 5. Application Logic Flaws

Sometimes, the error stems from how your application interacts with the database:

• Dynamic SQL queries (e.g., prepared statements) where parameter lengths aren’t validated.
• ORM tools (like Hibernate or SQLAlchemy) that don’t account for column constraints.
• Batch inserts where some rows exceed limits while others don’t.

Why it happens: The application assumes the data fits, but runtime validation fails. This is especially common in microservices or legacy systems where schema changes lag behind data updates.

💡 Quick Diagnostic Tip

To pinpoint the exact cause, check:

• The length of the problematic value (use LENGTH() or DATALENGTH() in your query).
• The column definition (run DESCRIBE tablename or query INFORMATIONSCHEMA.COLUMNS).
• The data type of the source and target (e.g., VARCHAR vs. CHAR, TEXT vs. CLOB).

Use SELECT * FROM TABLE(INFORMATIONSCHEMA.COLUMNS FOR 'YOUR_TABLE') (Db2 syntax) or equivalent in your DBMS to verify constraints.

How to solve it

Encountering SQLCode=-302 with SQLState=22001 can feel like hitting a wall—your query stalls, and you’re left wondering why data just won’t fit. The good news? These errors are usually fixable with a few targeted tweaks.

Below, we’ve mapped the most common causes to their solutions, so you can get back to running smooth queries in no time. 🚀

🔧 Fix 1: Adjust Column Data Types

If your data exceeds the defined column size (e.g., a VARCHAR(10) trying to store 20 characters), the database truncates it and throws this error. Here’s how to resolve it:

• ✅ Expand the column size: Modify the table structure to accommodate longer strings or larger values.

  ALTER TABLE yourtable
  ALTER COLUMN yourcolumn VARCHAR(50); -- Increase size as needed

• ✅ Use appropriate data types: Swap VARCHAR for TEXT or CLOB if storing large text blocks. For numbers, ensure DECIMAL or FLOAT has enough precision.

  ALTER TABLE yourtable
  ALTER COLUMN description CLOB; -- For unbounded text

• ✅ Trim or format data: Use functions like SUBSTRING or CAST to fit data into existing constraints.

  INSERT INTO yourtable (yourcolumn)
  VALUES (SUBSTRING('yourlongstring', 1, 50));

• ✅ Allow NULLs if needed: Update the column to permit NULL values.

  ALTER TABLE yourtable
  ALTER COLUMN yourcolumn VARCHAR(10) NULL;

• ✅ Provide a default value: Set a default (e.g., 'N/A') to avoid truncation.

  ALTER TABLE yourtable
  ALTER COLUMN yourcolumn VARCHAR(10) DEFAULT 'N/A';

• ✅ Explicitly check for empty strings: Use conditional logic in your queries or application code to skip or replace empty inputs.

  INSERT INTO yourtable (yourcolumn)
  VALUES (COALESCE(NULLIF(TRIM(yourvariable), ''), 'DEFAULTVALUE'));

ALTER TABLE yourtable
ADD CONSTRAINT chkcolumnlength CHECK (LENGTH(yourcolumn) <= 10);

• 🔥 Use EXPLAIN to analyze queries: Identify where truncation might occur.

  EXPLAIN SELECT  FROM yourtable WHERE yourcolumn = 'yourlongvalue';

• 👨‍🍳 Test with smaller batches: Break large inserts/updates into smaller transactions to isolate the problematic row.

  -- Example: Process 100 rows at a time
  WHILE EXISTS (SELECT 1 FROM temptable WHERE processed = 0)
  BEGIN
      INSERT INTO targettable (column1, column2)
      SELECT column1, column2 FROM temptable WHERE processed = 0 FETCH FIRST 100 ROWS ONLY;
  
      UPDATE temptable SET processed = 1 WHERE processed = 0 FETCH FIRST 100 ROWS ONLY;
  END

• ⏰ Log problematic data: Write a query to extract rows causing truncation for manual review.

  SELECT  FROM yourtable
  WHERE LENGTH(yourcolumn) > 10; -- Adjust condition as needed

• 📊 Validate data before insertion: Implement application-level checks (e.g., in Python, Java, or your ORM) to ensure data fits constraints.
• 💪 Use parameterized queries: Avoid concatenating strings in SQL to prevent accidental truncation or injection.

  -- Bad: Concatenation risk
  EXECUTE IMMEDIATE 'INSERT INTO yourtable VALUES (''' || yourvar || ''')';
  
  -- Good: Parameterized
  PREPARE stmt FROM 'INSERT INTO yourtable VALUES (?)';
  EXECUTE stmt USING your_var;

• ✨ Document constraints: Add comments to your schema or database diagrams to clarify column sizes and rules.
• 🌡️ Monitor with alerts: Set up database alerts for truncation warnings (e.g., in IBM Db2, enable SQL0302W logging).

With these fixes and precautions, you’ll turn SQLCode=-302 errors from roadblocks into speed bumps. 🚦 Keep your queries lean, your data clean, and your databases happy!

Frequently asked questions

INSERT INTO table (col1) VALUES ('value1'); -- Test each column individually