SQL0802 on IBM i: Data Conversion or Data Mapping Errors and How to Investigate Them

Which SQL0802 this is. This article covers IBM i’s own message SQL0802 from the Db2 for i SQL message catalog. Db2 for Linux, UNIX, and Windows has a similarly numbered message with an “N” suffix (SQL0802N) with different wording and reason codes. That LUW message was not checked for this article (UNVERIFIED_SOURCE for anything about SQL0802N), and nothing below describes it. Use the IBM i error types listed here only for IBM i.

Who this is for: RPG and SQL developers, report writers, and IBM i operations staff who have hit SQLCODE -802 (or the warning form +802) or message ID SQL0802 in a job log, an SQLRPGLE program, or an ODBC/JDBC client. It explains what the error type number means, how to find the row and column that caused it, and how to handle it safely in code.

Prerequisites: basic Db2 for i SQL (SELECT, SELECT INTO, FETCH), a working idea of packed/zoned decimal precision and scale (see RPGLE Data Types, Lengths, and Precision), and enough SQLRPGLE to follow an embedded SQL statement with an indicator variable and an SQLCODE/SQLSTATE check.

What SQL0802 means

Confirmed against IBM’s own IBM i 7.5 Database — SQL messages and codes reference (see Sources Consulted), the SQL0802 entry reads:

• Message text: “Data conversion or data mapping error.”
• Cause text begins: “Error type &3 has occurred.” The &3 substitution value is a number that tells you which kind of conversion or mapping problem occurred.
• SQLCODEs: +802 and -802. The positive value is a warning; the negative value is an error. Both use message ID SQL0802.
• SQLSTATEs: 01004, 01519, 01547, 01564, 01565, 22001, 22003, 22012, 22023, 22504.

So SQL0802 is not one specific failure — it is a family. The error type number in the message is the first thing to read.

The documented error types

The IBM i 7.5 reference lists these error types for SQL0802 (quoted from the primary source):

Release differences (confirmed for 7.3, 7.5, and 7.6): the edition of the same reference that applies to IBM i 7.3 lists the same twelve types. The IBM i 7.6 edition adds a thirteenth: 13 — “Boolean value not valid.” The list for 7.4 was not checked separately, and older releases were not checked (UNVERIFIED_SOURCE for releases before 7.3). Check the message on your own system with DSPJOBLOG (or the message description, see below) rather than assuming the table above matches your release exactly.

The cause text also says, verbatim in substance:

• If the error happened while assigning a value to a host variable in a FETCH, embedded SELECT, SET, or VALUES INTO statement, the message substitutes the host variable name (&2) and its relative position in the INTO clause (&1).
• If the host variable name shows as *N, “the error occurred when attempting to resolve a search condition” — in other words, the bad value was hit while evaluating a WHERE/ON/HAVING-style predicate, not while returning data to your program.
• “If more than one data mapping error occurred, this is a description of the first error that occurred. For a description of any other data mapping errors, see the previously listed messages in the job log.”

The recovery text says the error “was caused by data that was not valid or that was too large,” and directs you to the previously listed job log messages (DSPJOBLOG, or F10 on the display) “to determine what row and columns were involved in the error,” then to correct the data and retry.

What the SQLSTATEs tell you

The same IBM reference’s SQLSTATE listing (confirmed) gives a quick way to tell the common SQL0802 cases apart programmatically:

Notice the pattern: the 01xxx warning states say “the null value has been assigned to a variable.” That is the warning path described in the next section.

Warning (+802) versus error (-802): indicator variables matter

IBM’s “Data retrieval errors” topic in the Db2 for i SQL programming documentation (confirmed, see Sources Consulted) explains the split:

• When a data mapping error happens in a SELECT-list expression and you supplied an indicator variable for that column, “SQL returns a -2 for the indicator variable corresponding to the expression in error,” returns all the valid data for the row, and returns a positive SQLCODE (+802 in this case).
• “If an indicator variable is not provided, SQL returns the corresponding negative SQLCODE” — that is, -802, and the statement fails.
• “For data mapping errors, the SQLCA reports only the last error detected.” (Note the wording difference from the SQL0802 message text, which describes “the first error that occurred” and points to earlier job log messages for the rest. Either way, the practical lesson is the same: the SQLCA alone does not give you every bad column — the job log does.)

This is why the same query can “work” in one program and fail with -802 in another: the first program declared an indicator variable for the column, so it received a null-with-indicator -2 plus +802; the second did not, so it received a hard -802.

Common causes, by error type

Arithmetic overflow (type 1) and decimal precision

The result of an expression does not fit the precision Db2 for i calculated for it, or the value does not fit the target it is being mapped to. Typical triggers:

• Multiplying two large DECIMAL columns, or summing a large table into an aggregate, so the integer part of the result exceeds the available digits.
• Casting to a narrower type — DECIMAL(expr, 7, 2) or INTEGER(expr) — where expr is larger than the target can hold.
• Returning a value into an RPG host variable that is smaller than the SQL result (for example a packed(7:2) receiving a value with eight integer digits). See also the related messages in the next section.

Decimal result precision is governed by DECRESULT. Confirmed against IBM’s SET OPTION documentation (7.5): the DECRESULT option “specifies the maximum precision, maximum scale, and minimum divide scale that should be used during decimal operations,” applies only to NUMERIC and DECIMAL, and takes three values:

• max-precision: 31 or 63 (default 31),
• max-scale: 0 to the maximum precision (default 31),
• min-divide-scale: default 0 (no minimum); IBM documents a range of 1 to 9, not greater than max-scale.

IBM’s “Decimal arithmetic in SQL” topic in the Db2 for i SQL reference (confirmed on the 7.5 edition, and identical in the 7.2 edition) gives the rules Db2 for i uses. With p,s and p',s' as the two operands’ precision and scale, mp/ms/mds as the DECRESULT values:

• Addition/subtraction: scale max(s,s'), precision min(mp, max(p-s, p'-s') + max(s,s') + 1).
• Multiplication: precision min(mp, p+p'), scale min(ms, s+s').
• Division: precision (p-s+s') + max(mds, min(ms, mp - (p-s+s'))), scale max(mds, min(ms, mp - (p-s+s'))); the scale must not be negative.
• mp is 63 automatically if either operand has precision greater than 31; otherwise 31 unless 63 is requested explicitly.

The same page states DECRESULT can be set on the CRTSQLxxx and RUNSQLSTM commands, with SET OPTION, and in ODBC, JDBC, OLE DB, and .NET connection properties. That last point matters: the same SQL statement can behave differently from an RPG program, from Run SQL Scripts, and from an ODBC client if their DECRESULT settings differ.

The IBM page does not itself describe what error is raised when a computed value exceeds these limits; connecting “intermediate precision exhausted” to SQL0802 type 1 specifically is a reasonable inference from the message’s own “Arithmetic overflow” wording, not a sentence IBM states on that page (UNVERIFIED_SOURCE for that direct link). The safest practice — also recommended in an IBM support Q&A on decimal division (see Sources Consulted) — is to explicitly CAST/DECIMAL() intermediate results to the precision and scale you actually need rather than relying on derived defaults.

Numeric data that is not valid (type 6)

This is the classic IBM i case: a packed or zoned decimal field whose bytes are not a valid decimal value. IBM’s own description of SQLSTATE 01565 lists “invalid data in a packed decimal field” as an example of the data exceptions in this family (confirmed). How the bad data got there is site-specific; commonly reported causes in the field (UNVERIFIED_SOURCE — practitioner experience, not IBM wording) include records written by older programs or file transfers that left blanks or hex zeros in numeric fields, data structures overlaid incorrectly, or files copied with FMTOPT(*NOCHK).

Native RPG record-level access can sometimes read such records without complaint until arithmetic is performed, while SQL validates the column when it maps it — which is why a file that “has always worked” in RPG can suddenly throw SQL0802 type 6 the first time someone queries it with SQL. Treat that as a common observation, not a guaranteed rule (UNVERIFIED_SOURCE).

Type 6 can also appear with *N as the host variable name if the bad column is referenced in a WHERE clause or join condition. A community forum thread titled “sql error 802 type 6 numeric handling for joins” appears to describe exactly this, but the thread itself could not be retrieved (UNVERIFIED_SOURCE for that report’s details).

Division by zero (type 8)

A divisor expression evaluated to zero. With an indicator variable on the SELECT-list column you get +802 / SQLSTATE 01564 and a -2 indicator; without one you get -802 / SQLSTATE 22012 (both SQLSTATE pairings confirmed in IBM’s listing). The usual fix is to make the zero case explicit — NULLIF(divisor, 0) or a CASE expression — so the result is a deliberate null instead of a mapping error.

Character and string cases (types 7 and 12)

Type 7 covers DBCS or UTF-8 data that is not valid; type 12 covers a concatenation on a varying-length field exceeding the result type’s maximum length. Both are documented in the 7.5 error type list. This article does not go deeper into CCSID conversion; if you see type 7, look first at the CCSID of the column and the job (UNVERIFIED_SOURCE as a diagnostic heuristic).

Related messages you may see instead

IBM documents two neighbouring messages that use an overlapping list of error types (both confirmed in the same 7.5 reference):

• SQL0304 — “Conversion error in assignment to variable &2.” Raised when returning a value to a variable on a FETCH, embedded SELECT, CALL, SET, SET DESCRIPTOR, or VALUES INTO. Its error types are 1–7 (overflow, floating point overflow/underflow/conversion, not an exact result, numeric data that is not valid, DBCS data that is not valid). Recovery: change the size and, if necessary, the type of the variable, or correct the data. SQLCODEs +304/-304.
• SQL0406 — “Conversion error on assignment to column &2.” Raised when assigning a value to a column with INSERT, UPDATE, ALTER TABLE, or REFRESH TABLE. Same error types 1–7. Recovery: change the statement so the value fits, or recreate the table with a larger type/length. SQLCODE -406.

The SQL0802 cause text also mentions host variables on FETCH, embedded SELECT, SET, and VALUES INTO, so the boundary between “this situation produces SQL0304” and “this situation produces SQL0802” is not spelled out precisely in the message text alone (UNVERIFIED_SOURCE for any exact rule about which one fires when). Practical advice: handle -304, -406, and -802 together in your error-handling code, and read the actual message ID and error type from the job log rather than assuming.

How to investigate SQL0802

1. Read the job log — including the messages before SQL0802

IBM’s recovery text is explicit: look at the previously listed messages in the job log to find which row and columns were involved. Use DSPJOBLOG (or WRKJOB OPTION(*JOBLOG); see IBM i Job Log and CPF Messages Reference), position to the SQL0802 entry, press F1 on it to see the second-level text with the substituted error type, host variable name, and position, and then look at the messages immediately above it. This is confirmed IBM guidance, not a heuristic.

For a client job (ODBC/JDBC), the SQL0802 lands in the job log of the database server job serving that connection rather than on the PC. IBM’s host server Q&A lists QZDASOINIT as the database server prestart job for TCP/IP connections, used for ODBC, the OLE DB and .NET providers, and the Toolbox/JTOpen JDBC driver, running in subsystem QUSRWRK by default (see Sources Consulted).

2. Look up the message description on your release

To see the exact SQL0802 text and error type list for your release, you can display the message description:

DSPMSGD RANGE(SQL0802) MSGF(QSYS/QSQLMSG)

IBM’s SQL messages and codes reference states that each SQLCODE recognized by Db2 for i has a corresponding message in message file QSQLMSG, and gives the example DSPMSGD RANGE(SQL0204) MSGF(QSYS/QSQLMSG) (the 7.6 edition writes it as MSGF(QSQLMSG) without the library). The job log’s own F1 detail is also authoritative for your system.

3. Identify the statement and the expression

If the failing program is SQLRPGLE, the job log normally shows the statement context near the SQL0802. Once you know the statement:

• Note the error type: 1 → look for a too-small target or a large product/sum; 6 → look for bad packed/zoned data; 8 → look for a divisor.
• If the host variable name is given, compare its RPG definition (digits and decimals) to the SQL expression’s result type.
• If the host variable is *N, look at predicates — WHERE, join ON clauses — that reference numeric columns or perform arithmetic.

4. Isolate the offending rows

For divide-by-zero and overflow, query the source data directly to find the rows that would break the expression, for example (illustrative):

-- Rows where the divisor is zero
SELECT ORDNO, LINENO, QTY
  FROM ORDLINE
 WHERE QTY = 0;

-- Rows whose product would not fit a DECIMAL(11,2) target
SELECT ORDNO, LINENO, QTY, UNITPRICE
  FROM ORDLINE
 WHERE ABS(QTY * UNITPRICE) >= 1000000000;

For type 6 (invalid packed data), a plain SELECT of the bad column will usually hit the same error, so you need to narrow the range by key (binary search by key range) until you find the record, or read the record with a tool that does not interpret the field. Exactly which tools are best for this is environment-specific and not covered by the IBM sources consulted (UNVERIFIED_SOURCE). Fix the data at the source — do not paper over it in every query.

A conservative SQLRPGLE example

STATIC CHECKED — this example has not been compiled or run on a live IBM i or PUB400 system. It is reviewed for syntactic plausibility against known SQLRPGLE/embedded-SQL conventions but is not confirmed to compile or execute correctly. Table and column names (ORDLINE, ORDNO, LINEAMT, QTY) are illustrative. Treat it as a starting point, not production-ready code, and test it before use.

The pattern does three things: it makes division by zero an explicit null instead of a mapping error, it casts the result to the precision the host variable can actually hold, and it uses an indicator variable so that a remaining mapping error comes back as +802 with indicator -2 (per IBM’s documented behavior) instead of failing the statement outright.

**FREE
ctl-opt dftactgrp(*no) actgrp(*caller);

dcl-s ordNo      packed(9:0)  inz(1001);
dcl-s avgPrice   packed(11:2);
dcl-s avgInd     int(5);
dcl-s sqlCodeN   int(10);
dcl-s sqlStateN  char(5);
dcl-s msgTxt     varchar(512);

exec sql
  SELECT DECIMAL(SUM(LINEAMT) / NULLIF(SUM(QTY), 0), 11, 2)
    INTO :avgPrice :avgInd
    FROM ORDLINE
   WHERE ORDNO = :ordNo;

sqlCodeN  = sqlcode;
sqlStateN = sqlstate;

select;
  when sqlCodeN = 0 and avgInd = 0;
    // Normal result in avgPrice.

  when sqlCodeN = 0 and avgInd = -1;
    // Deliberate null: no lines, or total quantity was zero
    // (NULLIF turned the zero divisor into a null). Not an error.
    avgPrice = 0;

  when sqlCodeN = 802 or avgInd = -2;
    // Warning form of SQL0802: a data mapping error occurred in the
    // SELECT list, but because an indicator variable was supplied,
    // Db2 for i set it to -2 and returned +802 instead of failing.
    // SQLSTATE 01519 = numeric value out of range, 01564 = division
    // by zero, 01565 = miscellaneous data exception (for example
    // invalid packed decimal data). avgPrice is NOT usable here.
    exec sql GET DIAGNOSTICS CONDITION 1 :msgTxt = MESSAGE_TEXT;
    // Log ordNo, sqlStateN, msgTxt for follow-up. Do not treat the
    // row as zero -- the source data or the precision is wrong.

  when sqlCodeN = -802 or sqlCodeN = -304 or sqlCodeN = -406;
    // Hard conversion/mapping error. -802 can also come from a
    // predicate (host variable *N), where an indicator cannot help.
    exec sql GET DIAGNOSTICS CONDITION 1 :msgTxt = MESSAGE_TEXT;
    // Log ordNo, sqlCodeN, sqlStateN, msgTxt. Check the job log for
    // the error type and for earlier messages naming the row/column.

  other;
    // Some other SQLCODE -- not a conversion/mapping problem.
    exec sql GET DIAGNOSTICS CONDITION 1 :msgTxt = MESSAGE_TEXT;
endsl;

*inlr = *on;
return;

Points to note about this example:

• Match the cast to the host variable. DECIMAL(..., 11, 2) matches packed(11:2). If the true value can exceed nine integer digits, the cast itself overflows — widen both together rather than shrinking one. Note that the inner division result precision follows the DECRESULT rules described above, so the explicit cast is what pins down the final shape.
• NULLIF(x, 0) is a deliberate design choice. It converts “divide by zero” into “no answer.” That is correct for an average with no quantity, but it is not correct everywhere — if a zero divisor indicates a data error in your application, you may prefer to let it fail and investigate.
• The -2 indicator means “bad,” not “null.” A common bug is to treat any negative indicator as null and carry on. IBM documents -2 specifically for the expression in error; the host variable value should not be trusted in that case.
• GET DIAGNOSTICS ... MESSAGE_TEXT is used to capture the substituted message text for logging. IBM’s 7.5 GET DIAGNOSTICS page confirms CONDITION 1 returns the condition that matches the previous statement’s SQLSTATE, and documents MESSAGE_TEXT as VARCHAR(32740), so the 512-character msgTxt here may truncate a long message. IBM also notes that MESSAGE_TEXT is an empty string when SQLCODE is 0. Availability on releases before 7.5 was not checked (UNVERIFIED_SOURCE for earlier releases). SQLERRMC from the SQLCA is an alternative.
• No automatic retry. Unlike lock-related SQLCODEs, SQL0802 is a data or design problem. Retrying the same statement against the same data will produce the same error.

Common mistakes and troubleshooting

• Ignoring the error type number. “SQL0802” alone tells you very little; types 1, 6, and 8 have completely different fixes. Always read the substituted &3 value from the job log’s second-level text.
• Undersized RPG host variables. Declaring a packed(7:2) to receive a SUM() over a large table works in test and overflows in production. Size host variables for the realistic maximum, and cast the SQL result to that same precision.
• Relying on implicit decimal precision. Multiplication and division results take their precision and scale from the DECRESULT rules, not from your intent. An explicit DECIMAL(expr, p, s) makes the result predictable and makes overflow show up where you expect it.
• Different DECRESULT in different environments. A statement tested in Run SQL Scripts or through ODBC may use different DECRESULT settings than the compiled SQLRPGLE program (IBM documents DECRESULT on CRTSQLxxx, RUNSQLSTM, SET OPTION, and client connection properties). Compare settings before concluding the data is different.
• Assuming the SQLCA lists every bad column. IBM documents that only one error is reported in the SQLCA for data mapping errors; additional ones are in earlier job log messages.
• Treating indicator -2 as an ordinary null. It signals a mapping error on that column; the host variable contents are not a valid result.
• “Fixing” invalid packed data in every query. Wrapping columns in conversions to dodge type 6 hides corrupted data. Find and repair the records, and fix the program or transfer that wrote them.
• Confusing SQL0802 with SQL0406 or SQL0304. An INSERT/UPDATE that does not fit a column is documented as SQL0406; a value that does not fit a variable is documented as SQL0304. Handle all three in your error routine and read the actual message ID.

Performance and security considerations

• Guard expressions are cheap; failed batch runs are not. NULLIF and CASE guards add negligible cost compared with a batch job that ends abnormally halfway through and must be rerun.
• Raising DECRESULT max-precision to 63 is not free of side effects. It changes the derived precision and scale of decimal results, which can change how values map into existing host variables or client-side types. Test before changing it globally (UNVERIFIED_SOURCE for any specific performance impact — none is documented on the pages consulted).
• Log the diagnostic detail, not just the code. Capture SQLSTATE, the message text (with the error type), and the key of the row being processed. Avoid logging full row contents if the table holds sensitive data.

A realistic practical use case

A month-end extract computes average unit price per order with SUM(LINEAMT) / SUM(QTY) and loads it into a packed(7:2) RPG field. It runs cleanly for years, then fails one night with SQL0802. The job log’s second-level text shows error type 8 for one order (a cancelled order whose lines all have QTY = 0) — and, on a rerun after excluding that order, error type 1 for a large wholesale order whose average no longer fits seven digits. The fix is three small changes: wrap the divisor in NULLIF(..., 0) and handle the resulting null as “no average,” widen the host variable and cast the expression to the same DECIMAL(11,2), and add an indicator variable so that any future mapping error on that column comes back as +802 with -2 and is logged for review instead of stopping the whole extract.

FAQ

What does SQL0802 actually mean?
“Data conversion or data mapping error.” Per IBM’s IBM i 7.5 SQL messages reference, the message carries an error type number (1–12 in that edition; the 7.6 edition adds 13, “Boolean value not valid”) that says whether the cause was arithmetic overflow, floating point problems, an inexact result, numeric data that is not valid, invalid DBCS/UTF-8 data, division by zero, a hash computation problem, a UDF mapping error, a bad varying-length value in an array result set, or a concatenation exceeding the maximum length.

Why do I sometimes get +802 and sometimes -802?
IBM documents that if the error occurs in a SELECT-list expression and you provided an indicator variable, the indicator is set to -2 and a positive SQLCODE is returned; without an indicator variable, the negative SQLCODE is returned and the statement fails.

What does the host variable name *N mean in the message?
Per IBM’s cause text, it means the error occurred while resolving a search condition — the bad value was encountered while evaluating a predicate, not while returning a value into your program.

Which SQLSTATE should I test for?
Prefer testing the SQLCODE (-802/+802) and logging the SQLSTATE. IBM lists ten SQLSTATEs for SQL0802; 22012 (division by zero), 22003 (numeric value out of range), and the warning states 01519, 01564, and 01565 are the most useful for distinguishing causes.

Does DECRESULT cause SQL0802?
DECRESULT controls the maximum precision, maximum scale, and minimum divide scale for decimal operations (confirmed on IBM’s SET OPTION page). It shapes the size of intermediate results, so it can determine whether an overflow happens. IBM’s arithmetic page does not explicitly say “this raises SQL0802 type 1,” so treat that link as an inference (UNVERIFIED_SOURCE). Explicit casts are the most reliable control.

Is it safe to retry automatically?
No. SQL0802 reflects the data or the statement’s design. The same data will fail the same way.

Sources Consulted

• WebFetch (PDF, extracted locally with pdftotext): https://www.ibm.com/docs/en/ssw_ibm_i_75/rzala/rzalapdf.pdf — IBM i 7.5 Database — SQL messages and codes (© IBM 1998, 2022). Confirms: SQL0802 message text, cause text, the 12 error types, the host-variable / *N / “first error” wording, recovery text, SQLCODEs +802/-802, and the SQLSTATE list; SQL0304 and SQL0406 message text, error types, and SQLCODEs; the SQLSTATE descriptions for 01004, 01519, 01547, 01564, 01565, 22001, 22003, 22012, 22023, 22504. The PDF’s text layout interleaves the field labels, so the label/value pairing was checked by reading neighbouring entries; the SQLSTATE values were cross-checked against the separate SQLSTATE listing in the same PDF.
• WebFetch (PDF, extracted locally with pdftotext): https://www.ibm.com/docs/en/ssw_ibm_i_76/pdf/rzalapdf.pdf — IBM i 7.6 SQL messages and codes. Confirms: the same SQL0802 text, SQLCODEs and SQLSTATEs as 7.5, plus error type 13, “Boolean value not valid”; that every Db2 for i SQLCODE has a message in QSQLMSG, with the example DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG).
• WebFetch (PDF, extracted locally with pdftotext): https://www.ibm.com/docs/en/ssw_ibm_i_74/rzala/rzalapdf.pdf — the file served at this path is the edition that “applies to IBM i 7.3”. Confirms: the same twelve SQL0802 error types as 7.5, and the example DSPMSGD RANGE(SQL0204) MSGF(QSYS/QSQLMSG).
• WebFetch: https://www.ibm.com/support/pages/q-host-server-and-other-jobs-related-ibm-i-access-functions (IBM Support, “Host Server and Other Jobs Related to IBM i Access Functions”). Confirms: QZDASOINIT is the database server (*DATABASE) job for TCP/IP, used for ODBC, data transfer, SQL APIs, the OLE DB and ADO.NET providers, and the Toolbox/JTOpen JDBC drivers; default subsystem QUSRWRK.
• WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=statements-get-diagnostics (IBM i 7.5 GET DIAGNOSTICS). Confirms: CONDITION 1 syntax and meaning; MESSAGE_TEXT is VARCHAR(32740); empty string when SQLCODE is 0.
• WebFetch: https://www.ibm.com/docs/en/ssw_ibm_i_75/db2/rbafzdecarithmetic.htm (IBM i 7.5, “Decimal arithmetic in SQL”). Confirms: the same addition/subtraction, multiplication and division formulas, the mp = 63 rule, and the places DECRESULT can be set, as the 7.2 page below. Does not say what error is raised on overflow.
• WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=codes-listing-sql-messages (and the 7.6 equivalent) — the HTML “Listing of SQL messages” page was too large to read fully; the portions retrieved covered SQ16000–SQ20467 and SQL0160–SQL0579 and did not include SQL0802. Confirmed SQL0406 message text and SQLSTATEs (22003, 22023, 22504) independently of the PDF.
• WebFetch: https://www.ibm.com/docs/en/ssw_ibm_i_76/sqlp/rbafyretrievalerrors.htm (IBM i 7.6, “Data retrieval errors”) — confirms: +802 listed as “Data conversion or data mapping error” among data mapping errors; indicator variable set to -2 with a positive SQLCODE when an indicator is provided; negative SQLCODE when not; “For data mapping errors, the SQLCA reports only the last error detected.”
• WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=statements-set-option (IBM i 7.5 SET OPTION) — confirms: DECRESULT definition, the 31/63 max-precision values, max-scale range and default, min-divide-scale default 0 and documented range.
• WebFetch: https://www.ibm.com/docs/en/ssw_ibm_i_72/db2/rbafzdecarithmetic.htm (IBM i 7.2, “Decimal arithmetic in SQL”) — confirms (for 7.2): the addition/subtraction, multiplication, and division precision/scale formulas, the rule that mp becomes 63 when an operand exceeds precision 31, and that DECRESULT can be set on CRTSQLxxx, RUNSQLSTM, SET OPTION, and ODBC/JDBC/OLE DB/.NET properties. Does not confirm: what error is raised on overflow.
• WebSearch: “IBM i 7.5 SQL reference decimal division result precision scale” — surfaced an IBM support Q&A, “Division considerations using decimals” (https://www.ibm.com/support/pages/division-considerations-using-decimals), summarized in search results as recommending explicit precision and scale for decimal expression results. The page itself was not fetched; the recommendation is cited as consistent advice, not as a quoted IBM statement.
• WebSearch: “SQL0802 Data conversion or data mapping error IBM i reason code” and “SQL0802 site:ibm.com” — surfaced third-party reports (an MC Press forum thread titled “sql error 802 type 6 numeric handling for joins” — fetch returned HTTP 403; a Power BI community thread tracing SQL0802 to a division; Alteryx and Oracle ODI/JD Edwards reports of SQL0802 through IBM i Access ODBC). Used only as directional evidence that types 6 and 8 are common in practice; no technical claim in this article rests on them alone.
• Not verified (marked UNVERIFIED_SOURCE in the text): Db2 LUW SQL0802N wording; the error type list on releases before 7.3 (and on 7.4 specifically); the exact boundary between SQL0304 and SQL0802 for host-variable assignment; a direct IBM statement linking DECRESULT overflow to SQL0802 type 1; GET DIAGNOSTICS MESSAGE_TEXT on releases before 7.5; field causes of invalid packed data and tools for finding it; the RPG-versus-SQL validation observation; the CCSID heuristic for type 7; any performance impact of DECRESULT 63.

Internal links

• Db2 for IBM i Explained
• SQL on IBM i for Beginners
• IBM i Job Log and CPF Messages Reference
• RPGLE Data Types, Lengths, and Precision
• RPGLE Numeric and Conversion BIFs Reference
• IBM i Error Handling and Message Control Explained