SQLCODE -420: String argument not acceptable to function

What it means

Some functions read a string and turn it into something else. DECIMAL, INTEGER and CAST to a numeric type need a string that follows the rules for an SQL number. When a string breaks those rules, Db2 fails the statement at run time with -420. The token in the message is the function name. For a cast it is the target data type.

Since Db2 10 for z/OS, this code appears in places where it didn't before. When a character string is compared with a number, Db2 now casts the string to DECFLOAT(34), and the message then names DECFLOAT. Db2 10 in new-function mode also casts string input host variables to numeric targets. An empty or blank string sent from a client for a numeric column fails with -420 where older releases returned -301.

Because the cast happens row by row, -420 depends on the data. A query can run cleanly for months and fail the day a bad value appears. A change of access path can also make it come and go, since Db2 may then evaluate the cast on rows it used to skip.

Common causes

• A character column holds values that aren't numbers, and the SQL compares it with a number, as in WHERE ACCT_CODE = 1234. One bad row in the rows Db2 examines is enough.
• Blank or empty strings are converted to a number, either by an explicit CAST or by a client passing a blank for a numeric parameter.
• Strings with thousands separators, currency signs or other formatting, such as '1,234.50', are passed to DECIMAL or a numeric cast.
• The system uses DECIMAL=COMMA in DSNHDECP, so a comma is the decimal separator and strings written with a period don't convert as expected. IBM fixed one Db2 10 problem in this area under APAR PM70908.
• Generated SQL quotes numbers in a form Db2 won't parse, for example with spaces in the wrong place. APAR PH31035 fixed a case of this in the Db2 Administration Tool.

How to find the problem

1. Take the function or type name from the message. DECFLOAT usually means an implicit cast from a comparison between a string and a number.
2. Find the string expressions in the statement that feed that function or comparison.
3. Look for bad values, for example SELECT ACCT_CODE FROM ... WHERE TRANSLATE(ACCT_CODE, ' ', '0123456789') <> ' ' OR ACCT_CODE = ' ', which lists values with characters other than digits, and blank values.
4. For client programs, trace the parameter values sent. IBM points to IFCID 0366 trace records to find the inputs affected by the Db2 10 implicit cast change.

How to fix it

• Compare like with like. If ACCT_CODE is a CHAR column, write WHERE ACCT_CODE = '1234' so no cast is needed.
• Clean the bad values at the source, and validate numeric input before it reaches Db2.
• Guard the conversion with a CASE expression that only casts values made of digits.
• Fix the client so it sends a null or a real number instead of a blank string.
• Apply the PTFs for known APARs if the failing SQL comes from IBM code. The DDF_COMPATIBILITY setting DISABLE_IMPCAST_NJV can turn off implicit casting for older non-Java drivers, but IBM marks it deprecated, so treat it as a short-term workaround.

What it looks like

DSNT408I SQLCODE = -420, ERROR:  THE VALUE OF A STRING ARGUMENT WAS NOT ACCEPTABLE TO THE
         DECIMAL FUNCTION
DSNT418I SQLSTATE   = 22018 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXRDEC SQL PROCEDURE DETECTING ERROR

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.