SQLCODE -117: Number of values doesn't match columns

What it means

Every value in an assignment needs exactly one target column. In an INSERT, the values in the VALUES clause, or the columns of the SELECT in an INSERT ... SELECT, are matched one for one with the column list. If there is no column list, Db2 uses every column of the table, in the order they were defined. In an UPDATE that sets several columns at once, such as SET (A, B) = (X, Y), the two sides must also be the same length.

When the counts differ, Db2 returns -117. For static SQL, Db2 usually finds it at bind time, and the bind fails. For dynamic SQL it comes back on PREPARE or EXECUTE IMMEDIATE.

The most common story is an INSERT with no column list that worked for years. Then someone adds a column to the table. The statement now implies one more column than it supplies, and the next bind or rebind of the package fails with -117.

Common causes

• An INSERT without a column list, written before a column was added to the table.
• A value left out of a long VALUES list, or one value too many, often after a copy and paste.
• An INSERT ... SELECT where the SELECT returns a different number of columns from the target list, for example SELECT * from a table that has changed.
• A multi-column SET in an UPDATE or a SET statement with unequal lists on the two sides.
• The insert part of a MERGE with a column list and values list of different lengths.

How to find the problem

1. Count the target columns and the values in the failing statement. The bind output names the statement number in the DBRM, and the precompile listing maps that number to a line.
2. If the INSERT has no column list, count the columns the table really has with SELECT COUNT(*) FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'PAYPROD' AND TBNAME = 'EMP_HIST'.
3. Compare the DCLGEN copybook the program uses with the current table. A stale DCLGEN is a strong clue that the table changed.
4. For INSERT ... SELECT, list the SELECT's columns explicitly and count them against the target.

How to fix it

• Always name the columns in an INSERT. It stops a table change from breaking the program and makes the statement easier to read.
• Add the missing value, or remove the extra one, so each column gets exactly one value.
• Use the DEFAULT keyword in the VALUES list for a column that should take its default value.
• Regenerate the DCLGEN after a table change, update the program, then precompile and bind again.
• Replace SELECT * in INSERT ... SELECT with a named column list.

What it looks like

DSNT408I SQLCODE = -117, ERROR:  THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER
         OF SPECIFIED OR IMPLIED COLUMNS
DSNT418I SQLSTATE   = 42802 SQLSTATE RETURN CODE

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.