Common SQLCODEs and SQLSTATEs
Plain-English IBM i learning notes with examples, comparisons, and official references.
How to read the result: SQLCODE 0 is success; +100 / SQLSTATE 02000 is the expected no-data boundary; negative SQLCODEs are failures. SQLSTATE is a five-character condition code and may be the better application contract.
- Check the result immediately after each SQL statement.
- For a failure, preserve SQLCODE, SQLSTATE, message text, and row-count diagnostics before recovery.
- Use IBM's message listing and DSPMSGD for release-specific cause and recovery text.
0 · SQLSTATE 00000 — Statement completed successfully (Success)
Typical trigger: The database completed the statement without an error or exceptional condition.
Response: Continue. For INSERT, UPDATE, or DELETE, also verify the affected-row count matches the business expectation.
if SQLSTATE = '00000'; // normal success path endif;
+100 · SQLSTATE 02000 — No row found or end of data (Warning / no data)
Typical trigger: A singleton SELECT finds no row, or a FETCH reaches the end of a cursor result.
Response: Treat it as an expected branch when the use case allows it. Clear or initialise output fields so a previous row is not reused.
if SQLCODE = 100; // not found or cursor end endif;
+802 / -802 · SQLSTATE 01519, 01547, 01565, 22001, 22003, 22012, 22023, 22504 — Data conversion or data mapping problem (Warning / failure)
Typical trigger: A value overflows, has an invalid numeric/date format, divides by zero, or cannot be mapped to the target type. The sign determines warning versus error for the returned condition.
Response: Read the job log and diagnostic tokens, identify the failing value and target column or host variable, then correct the data or type conversion.
-204 · SQLSTATE 01532, 42704 — Referenced object or name is not found (Failure)
Typical trigger: A table, view, alias, routine, function, or other SQL object cannot be resolved in the build or run environment.
Response: Check the qualified library/schema, object type, spelling, library list, and authority. Use the SQL0204 message details rather than guessing.
DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG)
-205 · SQLSTATE 42703 — Column is not in the table or view (Failure)
Typical trigger: The statement names a column that does not exist, was renamed, or is ambiguous after a schema change.
Response: Verify the column in the catalog, qualify columns when joining tables, and re-precompile or rebuild the program after a schema change.
-303 · SQLSTATE 22001, 42806 — Host variable is incompatible or too small (Failure)
Typical trigger: A FETCH, SELECT, CALL, or assignment cannot map the SQL value to the RPG or host variable type or length.
Response: Compare SQL column and host-variable types, lengths, precision, and date formats. Correct the definition before retrying.
-305 · SQLSTATE 22002, 22004 — Indicator variable is required (Failure)
Typical trigger: A nullable result is assigned to a host variable without an indicator or nullable host structure.
Response: Add an indicator and branch on its negative value, or use an explicit COALESCE only when replacing NULL has the intended business meaning.
exec sql
select SHIPPED_DATE into :shipDate :shipDateNull
from MYLIB.ORDERS where ORDER_ID = :orderId;-407 · SQLSTATE 23502 — NULL cannot be stored in a NOT NULL column (Failure)
Typical trigger: An INSERT or UPDATE supplies NULL, or omits a required column without a default.
Response: Supply a valid value or deliberate default, or change the nullable design. Do not silently convert an unknown value to blank or zero.
-530 · SQLSTATE 23503 — Foreign-key value has no matching parent (Failure)
Typical trigger: A child row is inserted or updated before its referenced parent row exists.
Response: Check the parent key and constraint definition, and insert or correct the parent in the same controlled transaction when appropriate.
-532 · SQLSTATE 23001, 23504 — Parent row cannot be deleted because dependents exist (Failure)
Typical trigger: A DELETE or key change violates a RESTRICT or NO ACTION referential rule.
Response: Find dependent rows and follow the documented business delete order. Never remove a constraint just to make the statement pass.
-551 · SQLSTATE 42501 — User is not authorised for the object or operation (Failure)
Typical trigger: The job profile lacks the required authority on a table, view, routine, package, or underlying file.
Response: Identify the exact object and required privilege, then request the narrow authority from the owner or security administrator.
-803 · SQLSTATE 23505 — Duplicate key or unique constraint violation (Failure)
Typical trigger: An INSERT or UPDATE would create a duplicate value in a primary key, unique constraint, or unique index.
Response: Decide whether the conflict is an expected business response or a defect. Inspect the key and transaction before retrying.
-811 · SQLSTATE 21000 — A singleton SELECT returned more than one row (Failure)
Typical trigger: SELECT INTO or a scalar subquery is used where the data matches multiple rows.
Response: Fix the predicate or data rule if one row is required; otherwise use a cursor, multi-row fetch, or an intentional aggregate.
-913 · SQLSTATE 57033 — Row or object is in use after a deadlock or timeout (Failure)
Typical trigger: The requested row or object remains locked beyond the configured wait time or a deadlock is detected.
Response: Read the preceding messages, inspect locks, keep transactions short, and retry only when the operation is idempotent and the retry policy is bounded.
// Review DSPRCDLCK or WRKOBJLCK evidence before changing retry logic
-952 · SQLSTATE 57014 — SQL processing ended before normal completion (Failure / cancelled)
Typical trigger: A client cancellation, abnormal termination, activation-group end, or another interruption stops SQL processing.
Response: Read the preceding job-log messages and reason code. Roll back or clean up as required; do not treat cancellation as a no-data result.