SQL Code -302 SQLState 22001: Data Truncation Fix in Seconds

by

Troubleshooting

SQL Code -302 SQLState 22001: Data Truncation Fix in Seconds

SQLCODE=-302, SQLSTATE=22001 isn't just an error—it's a clear signal your data is fighting for space in a column that's too small. ⚡ I've seen this derail transactions in production systems, and the fix is almost always staring you right in the face once you know what to look for.

The root cause? You're trying to stuff data wider than the column's data type allows, and Db2 won't let you break its rules.

The error appears when inserting or updating values that exceed the defined field length—whether it's a VARCHAR(10) getting hit with a 15-character string or a DECIMAL(5,2) receiving a number with too many digits. On Linux or Windows, the symptoms are identical: failed INSERT/UPDATE statements with this exact code pair.

The good news? Db2 gives you the exact column name in the error message if you're paying attention to the diagnostic text.

You'll resolve it in seconds by either expanding the column's size with ALTER TABLE or trimming your input data to fit. I've fixed this on systems handling everything from customer names to financial records—once you verify the data type constraints, the solution becomes obvious.

The key is checking both your schema definitions and the actual data you're trying to insert.

This works across Db2 versions and platforms, though the exact ALTER syntax differs slightly between LUW and z/OS. I'll walk through both approaches next, plus how to automate constraint validation in your application code to catch these before they hit production.

Why it happens

When you encounter SQL code -302 (SQLSTATE 22001), it’s almost always pointing to one core issue: your data doesn’t fit where you’re trying to store it.

But why does this happen? Let’s break down the real-world causes behind these errors—so you can fix them faster and prevent them in the future.

🔍 1. Mismatched Data Types

SQL databases enforce strict rules about data types. If you try to insert a string that’s too long into a column defined as VARCHAR(10), or a numeric value exceeding the precision of a DECIMAL(5,2) field, the database will reject it with this error. For example:

  • String truncation: Inserting "HelloWorld" into a VARCHAR(5) column.
  • Numeric overflow: Storing 9999999999 into a SMALLINT (which maxes out at 32,767).
  • Date/time misalignment: Trying to store "2025-12-32" (invalid date) or a timestamp outside the column’s range.

Pro Tip: 💡 Always check your CREATE TABLE or ALTER TABLE statements to verify column constraints. Use tools like DESCRIBE tablename (MySQL) or SELECT * FROM informationschema.columns (PostgreSQL) to audit field sizes.

📏 2. Character Encoding Mismatches

Not all characters take up the same space! Multibyte characters (like é, ñ, or 中) can consume 2-4 bytes per character, while ASCII characters use just 1 byte. If your database expects UTF-8 but your application sends raw bytes (or vice versa), the length calculation becomes skewed.

Example scenarios:

  • Storing "Café" (3 bytes in UTF-8) into a CHAR(2) column (which assumes 2 ASCII characters).
  • Using NCHAR (Unicode) vs. CHAR (ASCII) without proper conversion.
  • Database collation settings (e.g., SQLLatin1General_CP1_CIAS) that don’t account for multibyte characters.

Fix It: 🌡️ Explicitly declare your connection’s character set (e.g., CHARSET=utf8mb4 in MySQL) and validate string lengths before insertion using functions like LENGTH() or OCTETLENGTH().

🔄 3. Automatic Data Conversion Failures

SQL databases sometimes try to convert incompatible data types automatically—but they fail silently when the result exceeds limits. For instance:

  • Converting a FLOAT to an INT might truncate decimal places, but if the original value was 999999.999 and the INT column can only hold 999999, you’ll get this error.
  • Concatenating strings that exceed the target column’s length (e.g., CONCAT(column1, column2) where the result is 200 characters but the destination is VARCHAR(100)).
  • Using CAST or CONVERT functions without validating the output size.

Prevent It: ✨ Use TRYCAST (SQL Server) or SAFECAST (PostgreSQL) to catch conversion errors before they hit the database. For concatenation, pre-calculate lengths with DATALENGTH() (SQL Server) or LENGTH() (MySQL/PostgreSQL).

⏳ 4. Legacy Systems or Hardcoded Limits

Older databases or applications often have hardcoded field sizes that don’t account for modern data needs. Common culprits:

  • Legacy CHAR fields set to 255 (pre-UTF-8 era) now failing on longer strings.
  • Stored procedures or triggers that assume fixed-length inputs but don’t validate them.
  • Third-party APIs or ETL pipelines sending data formatted for older systems (e.g., VARCHAR(30) for phone numbers with country codes).

Action Step: 🔥 Audit your schema for CHAR vs. VARCHAR usage and migrate to NVARCHAR or TEXT where needed. Use ALTER TABLE to expand columns safely:

ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

How to solve it

Encountering SQLCode=-302 and SQLState=22001 means your database is rejecting data because it’s too large for the target column. The good news? These fixes are straightforward once you pinpoint the root cause. Below are actionable solutions mapped to common triggers, along with prevention tips to keep your queries running smoothly.

###

🔥 Cause 1: Inserting Data Longer Than Column Size

Your query is trying to stuff a 50-character string into a 20-character column—classic truncation! This is the most common culprit.

How to Fix It:

  • 🔍 Check the column definition: Run DESCRIBE tablename; (or SELECT FROM syscolumns WHERE id = OBJECTID('tablename'); in SQL Server) to confirm the column’s length.
  • 📏 Adjust the data: Trim or shorten the input data to fit. For example: 
    INSERT INTO customers (name) VALUES (LEFT('Alexander Johnson', 20));
  • 🔄 Expand the column (if needed): Alter the table to accommodate longer data: 
    ALTER TABLE tablename ALTER COLUMN name VARCHAR(50);

Prevention Tip:

💡 Validate input lengths before inserting. Use functions like LEN() (SQL Server) or CHARLENGTH() (MySQL/PostgreSQL) to check data size dynamically:

IF LEN(@inputdata) > 20 BEGIN
    RAISERROR('Data exceeds column limit!', 16, 1);
    RETURN;
END

###

🍳 Cause 2: Automatic Truncation in Implicit Conversions

When SQL tries to convert data types automatically (e.g., string to numeric), it might silently truncate values, leading to errors later.

How to Fix It:

  • 🛠️ Explicitly cast data: Force the conversion with CAST() or CONVERT() to handle truncation intentionally: 
    INSERT INTO products (price) VALUES (CAST('99.99' AS DECIMAL(10,2)));
  • 🚨 Enable strict mode (if supported): In MySQL, set sqlmode = 'STRICTTRANSTABLES' to reject implicit conversions outright.
  • 🔎 Debug with explicit checks: Test conversions in a SELECT first to catch issues: 
    SELECT CAST('1234567890' AS INT); -- Will truncate; verify behavior

Prevention Tip:

🌡️ Use parameterized queries to avoid implicit conversions. Libraries like PDO (PHP) or PreparedStatement (Java) handle type safety automatically.

###

👨‍🍳 Cause 3: Bulk Operations or Batch Inserts

Loading data from files (CSV, Excel) or batch scripts often triggers truncation when row sizes exceed expectations.

How to Fix It:

  • 📑 Pre-process your data: Use tools like sed (Linux), Excel’s "Text to Columns," or Python (pandas) to trim fields before importing.
  • 🔄 Split large inserts: Break bulk inserts into smaller batches or use LOAD DATA INFILE with FIELDS TERMINATED BY to control parsing.
  • 🔍 Log problematic rows: Redirect truncation errors to a log table for review: 
    CREATE TABLE truncationlog (errormessage VARCHAR(255), rowdata VARCHAR(MAX));
    -- Then use TRY-CATCH (SQL Server) or HANDLER (MySQL) to log errors.

Prevention Tip:

✨ Profile your data first! Run a SELECT MAX(LEN(columnname)) on your source data to identify outliers before writing inserts.

###

🥘 Cause 4: Time/Date Formatting Mismatches

Inserting a string like '2023-12-31' into a DATE column might work, but 'December 31, 2023' will truncate or fail.

How to Fix It:

  • 🗓️ Standardize formats: Use ISO formats (YYYY-MM-DD) or database-specific functions like TODATE() (Oracle) or STR_TO_DATE() (MySQL): 
    INSERT INTO events (date) VALUES (STR_TO_DATE('31/12/2023', '%d/%m/%Y'));
  • 🔄 Convert to timestamp first: Parse strings into timestamps before inserting: 
    INSERT INTO logs (timestamp) VALUES (UNIXTIMESTAMP('2023-12-31 23:59:59'));

Prevention Tip:

🎯 Use database-specific date functions in your application code to avoid manual formatting. For example, Java’s java.sql.Timestamp or Python’s datetime module.

###

🔪 Cause 5: Default Constraints or Triggers

Triggers or default values might be silently truncating data during INSERT or UPDATE operations.

How to Fix It:

  • 🔍 Inspect triggers: Run SELECT FROM informationschema.triggers (or equivalent) to find triggers modifying the table.
  • 📝 Review default constraints: Check for defaults like DEFAULT SUBSTRING(column, 1, 10) that shorten data.
  • 🛑 Temporarily disable triggers: Use DISABLE TRIGGER (SQL Server) or ALTER TABLE DISABLE TRIGGER (Oracle) to isolate the issue.

Prevention Tip:

💡 Test triggers in isolation by inserting known-length data and checking outputs. Log trigger execution paths for debugging.

###

⏰ General Troubleshooting Checklist

Stuck? Run through this quick checklist:

  1. 🔍 Verify the exact error line: Add PRINT @variable (SQL Server) or RAISE NOTICE (PostgreSQL) to debug dynamic SQL.
  2. 📊 Compare data lengths: Use DATALENGTH() (SQL Server) or OCTET_LENGTH() (MySQL) to check byte sizes.
  3. 🔄 Test with minimal data: Insert a single, known-good row to rule out environment issues.
  4. 📋 Check

Frequently asked questions

1

Why do I keep seeing SQLCODE=-302 with SQLSTATE=22001 during data imports?

This error occurs when your import process tries to insert data that exceeds the column's defined length or precision. Common triggers include CSV files with wider fields than your database schema expects, or batch operations where string concatenation creates oversized values. Always validate field lengths before importing using functions like LEN() or OCTETLENGTH() to catch these issues early.

2

How can I safely expand a column that's causing truncation errors?

Use ALTER TABLE with the ALTER COLUMN clause to resize the field. For example: 

ALTER TABLE customers ALTER COLUMN fullname VARCHAR(100);
Always back up your table first, and consider doing this during low-traffic periods. For production systems, test the change in a staging environment first to verify no downstream processes depend on the old size.
3

Will this error appear in stored procedures even if the data fits now?

Yes, if your procedure contains dynamic SQL or uses implicit conversions that might truncate data. Always include length validation in your procedures. For example, add this check before any insert: 

IF LEN(@inputstring) > 50 BEGIN
    RAISERROR('String exceeds maximum length of 50 characters', 16, 1);
    RETURN;
  END
This catches issues before they reach the database layer.
4

Can character encoding differences cause SQLCODE=-302 errors?

Multibyte characters (like UTF-8) consume more space than single-byte ASCII characters. If your column is defined as VARCHAR(20) but you're storing strings with special characters, the actual byte count might exceed the limit. Always specify your connection's character set (e.g., CHARSET=utf8mb4) and use OCTETLENGTH() instead of CHARLENGTH() for accurate byte counting.

5

What's the fastest way to identify which column is causing the truncation?

Enable detailed error logging in your database client or connection pool. Most systems include the exact column name in the error message when detailed diagnostics are enabled. For Db2 specifically, check the SQLERRM function which often contains the full error context including the problematic column name. You can also temporarily add PRINT ERRORMESSAGE() (SQL Server) or RAISE NOTICE (PostgreSQL) to your transaction.

★★★★★4.9(7 reviews)
Categories Troubleshooting