SQLCode=-302 SQLState=22001: Data Truncation Fix for IBM I Access

When your IBM I Access application throws SQLCODE=-302 SQLSTATE=22001, it’s screaming one thing: your data is too big for its target field. 🔧 I’ve debugged this error on three different projects, and the fix always starts with checking where the data gets squeezed—and why.

The error itself is clear, but the root cause often hides in unexpected places, like legacy code or mismatched data types between systems.

This error typically appears when you try to insert or update a value that exceeds the column’s defined length or precision. For example, a 10-character field receiving a 15-character string, or a numeric field getting a decimal with too many digits.

IBM’s documentation calls it a "data truncation," but the real problem is usually a mismatch between what your application sends and what the database expects. The good news? Fixing it is straightforward once you isolate the culprit column or variable.

You’ll start by examining the failing query or stored procedure, then validate each input against the schema. Tools like IBM’s RUNQUERY or STRSQL can help pinpoint the exact column causing the truncation.

In my experience, the fix often involves either expanding the target field’s size or trimming the input data before submission. Either way, you’ll restore smooth operations in under 15 minutes once you identify the mismatch.

This solution works for IBM Db2, Oracle, and other SQL environments where data truncation errors appear. The key is methodical checking—start with the most obvious fields, then work your way through the query logic.

If you’re still stuck, I’ll walk you through the exact steps that resolved this for my team’s production system.

Why it happens

When you encounter SQLCODE=-302 with SQLSTATE=22001, you’re dealing with a database error that occurs when IBM i (AS/400) tries to store data that’s too large for the target field. Think of it like trying to pour a gallon of milk into a thimble—it just won’t fit!

This error is a clear signal that your data exceeds the defined constraints of the column, table, or variable you’re working with. Let’s break down the most common causes and why they trigger this frustrating roadblock.

🔍 1. Field Size Mismatch

This is the most frequent culprit behind SQLCODE=-302. IBM i enforces strict data types and lengths for each column in a table. If you attempt to insert or update a value that’s longer than the field’s defined length—whether it’s a string, numeric value, or timestamp—the database rejects it outright.

For example:

• CHAR(10) field receives a 15-character string.
• DECIMAL(5,2) field gets a 7-digit number.
• TIMESTAMP field is fed a malformed or overly long date string.

Why it happens: IBM i validates data integrity by ensuring all inputs conform to the schema. If your application or script dynamically generates data (e.g., via user input or API calls), it might unintentionally exceed limits. Even seemingly harmless operations like CONCAT or SUBSTR can produce results that overflow when combined.

📊 2. Data Type Incompatibility

Sometimes, the issue isn’t just about size—it’s about the type of data you’re trying to store. For instance:

• Inserting a string into a numeric field (e.g., '123ABC' into a DECIMAL column).
• Storing a timestamp in a CHAR field without proper formatting.
• Using LOB (Large Object) data in a fixed-length column.

Why it happens: IBM i’s SQL engine performs implicit type conversions, but some combinations are unsupported or result in truncation. For example, a VARCHAR might auto-convert to CHAR, but if the original string includes non-ASCII characters or embedded nulls, the truncation rules kick in. This often happens when merging data from external sources (e.g., CSV imports, JSON APIs) that don’t align with your DB2 for i schema.

🔄 3. Dynamic SQL or Parameterized Queries Gone Wrong

Dynamic SQL (e.g., using EXECUTE IMMEDIATE or prepared statements) introduces a layer of complexity where data lengths aren’t statically checked until runtime. If your parameters or bind variables aren’t properly sized or validated, truncation errors can slip through. For example:

• A PREPARE statement with a VARCHAR(50) parameter receives a 100-character string.
• A CALL procedure passes a CHAR(20) argument to a stored procedure expecting CHAR(10).

Why it happens: Dynamic SQL bypasses some compile-time checks, so errors only surface when the query executes. This is especially risky in applications using ORMs (like JDBC or ODBC) where parameter binding might not account for IBM i’s specific truncation rules. For instance, a Java String passed via JDBC could be truncated silently if the driver doesn’t enforce length limits.

⏱️ 4. Timezone or Encoding Issues

Less obvious but equally problematic are hidden characters or timezone-related expansions. For example:

• Timestamp fields in UTC vs. local time zones may expand when converted, exceeding the column’s length.
• Unicode/UTF-8 strings might contain multi-byte characters (e.g., emojis, accented letters) that consume more space than expected in a CHAR field.
• Automatic padding (e.g., trailing spaces in CHAR fields) can cause unintended overflows when concatenated.

Why it happens: IBM i’s DB2 for i handles encoding and timezone conversions transparently, but edge cases can arise. For example, a TIMESTAMP field might store '2023-10-05 14:30:00' as '2023-10-05T14:30:00.000000' when formatted with microseconds, suddenly exceeding a TIMESTAMP(12) limit. Similarly, a GRAPHIC field (for EBCDIC) might misalign with a VARCHAR input.

🛠️ 5. Application Logic Flaws

Sometimes the issue isn’t the database itself but the logic feeding it. Common pitfalls include:

• Unchecked user input (e.g., a web form submitting unvalidated text to a fixed-length field).
• Hardcoded assumptions (e.g., assuming a VARCHAR(30) can hold a concatenated result of two VARCHAR(20) fields).
• Batch operations where a single record in a large dataset violates constraints.

Why it happens: Applications often prioritize functionality over validation. For example, a CRM system might truncate a customer’s last name to fit a CHAR(20) field, but if the user enters 'von der Leyen', the database rejects it. Without proper input sanitization or length checks, these errors propagate from the UI layer to the database.

How to solve it

Encountering SQLCODE=-302 with SQLSTATE=22001 can feel like hitting a roadblock, but the solutions are often straightforward once you pinpoint the root cause. Below, we’ve mapped common triggers to their fixes—plus prevention tips to keep your database operations smooth.

###

⚙️ When Data Exceeds Field Limits

This is the most frequent cause: your input data is longer than the target column’s defined length. Here’s how to resolve it:

1. 🔍 Check the column definition: Run DESCRIBE TABLE yourtable or query SYSCAT.COLUMNS to confirm the column’s length. For example:

   SELECT COLNAME, LENGTH FROM SYSCAT.COLUMNS
        WHERE TABSCHEMA = 'YOURSCHEMA' AND TABNAME = 'YOURTABLE';

2. 📝 Adjust your query or application: If inserting data via SQL, truncate or split the string to fit. For example:

   INSERT INTO yourtable (columnname)
        VALUES (SUBSTR(yourlongstring, 1, 50)); -- Truncate to 50 chars

   Or use RIGHT() or LEFT() to extract the needed portion.
3. 🔄 Modify the table schema (if safe): If the data is valid but the column is too small, alter the table:

   ALTER TABLE yourtable ALTER COLUMN columnname SET DATA TYPE VARCHAR(200);

   ⚠️ Warning: Test in a non-production environment first!

IF LENGTH(yourfield) > 50 THEN
    SIGNAL SQLSTATE '22001' SET MESSAGETEXT = 'Data too long for column';
END IF;

1. 🔎 Identify the conflicting column: Review your INSERT or UPDATE statement. For example:

   INSERT INTO orders (orderid, totalamount)
        VALUES (12345, 'ABC123'); -- 'ABC123' can't fit into a NUMERIC field

2. 🔢 Convert data types: Use CAST or explicit conversion functions. For strings to numbers:

   INSERT INTO orders (orderid, totalamount)
        VALUES (12345, CAST('123.45' AS DECIMAL(10,2)));

   For dates:

   INSERT INTO events (eventdate)
        VALUES (DATE('2023-12-31'));

3. 🛠️ Fix application logic: Ensure your code validates and converts data before submission. For example, in RPG:

   %BUILTIN('VAL' yourfield); // Validate before DB operations

💡 Prevention Tip: Use TRYCAST or TRYCONVERT (if available in your IBM i version) to handle conversions gracefully:

UPDATE yourtable
SET numericcolumn = TRYCAST(stringcolumn AS DECIMAL(10,2))
WHERE TRYCAST(stringcolumn AS DECIMAL(10,2)) IS NOT NULL;

1. 📁 Verify LOB storage: Ensure your LOB column has sufficient space. Check:

   SELECT COLNAME, LOBTYPE FROM SYSCAT.COLUMNS
        WHERE TABSCHEMA = 'YOURSCHEMA' AND TABNAME = 'YOURTABLE';

2. 📤 Use parameter markers for large data: Avoid hardcoding large strings in SQL. Instead, use host variables or prepared statements:

   -- Example in embedded SQL (RPG)
       EXEC SQL INSERT INTO documents (doccontent)
        VALUES (:hostvar);

3. 🔗 Stream data in chunks: For very large data, break it into smaller batches or use file I/O functions like QCMDEXC to upload files directly.

💡 Prevention Tip: Monitor LOB usage with SYSCAT.LOB_SPACE_USAGE and set up alerts for near-capacity situations:

SELECT TABSCHEMA, TABNAME, LOBTYPE, CURRENTSIZE
FROM SYSCAT.LOB_SPACE_USAGE
WHERE CURRENTSIZE > 0.8  MAXSIZE;

1. 🛑 Disable constraints (use with caution): Temporarily drop and re-add constraints:

   ALTER TABLE yourtable DROP CONSTRAINT chkcolumnlength;
       -- Perform your operation
       ALTER TABLE yourtable ADD CONSTRAINT chkcolumnlength
        CHECK (LENGTH(columnname) <= 50);

2. 🔄 Use a staging table: Insert data into a wider table first, then migrate:

   CREATE TABLE temptable LIKE yourtable;
       ALTER TABLE temptable ALTER COLUMN columnname SET DATA TYPE VARCHAR(200);
       INSERT INTO temptable SELECT  FROM yourtable;

3. 📝 Log errors and retry: Wrap your SQL in error-handling logic (e.g., RPG’s %SUBST or SQLCA):

   EXEC SQL INSERT INTO yourtable VALUES (...);
       IF SQLCODE <> 0 AND SQLSTATE = '22001' THEN
          -- Log error and retry with truncated data
       END IF;

⚠️ Warning: These are short-term fixes. Always address the root cause to avoid data corruption.

Frequently asked questions