SQLCODE -138: SUBSTR argument out of range

What it means

SUBSTR(string, start, length) returns part of a string. The start position counts from 1. The length says how many characters to take. If start is less than 1 or past the end of the string, or the length is negative or reaches beyond the end, there is no valid piece to return and Db2 fails the statement with -138.

For SUBSTR in Db2 for z/OS, the end of the string is measured by its length attribute, the declared length of the column or expression, not by how much data is in it. A short value in a VARCHAR(50) column is treated as padded with blanks up to 50 for this purpose. Check the SUBSTR and SUBSTRING descriptions in the SQL Reference for your release before relying on that for SUBSTRING, which uses string units.

When start and length are constants, the mistake is easy to see. When they come from host variables or from functions like LOCATE, POSSTR or LENGTH, they change with the data. That's why -138 often appears only for some rows, after the query has run for a while.

Common causes

• A length computed from LOCATE or POSSTR, such as SUBSTR(NAME, 1, LOCATE(',', NAME) - 1). When the value has no comma, LOCATE returns 0 and the length becomes -1.
• A start position of 0, often from a host variable that was never set.
• A start or length computed from LENGTH(col) minus something, which goes negative for short values.
• A start plus length that runs past the declared length of the string, for example after a column was shortened.
• A host variable for start or length that holds a value from a previous row.

How to find the problem

1. Look at the SUBSTR or SUBSTRING calls in the failing statement. Constants can be checked by eye against the column's length in SYSIBM.SYSCOLUMNS.
2. If start or length come from host variables, display them in the program just before the statement.
3. If they come from LOCATE, POSSTR or LENGTH, find rows where those return 0 or small values, for example SELECT NAME FROM CUSTOMER WHERE LOCATE(',', NAME) = 0.
4. Note which row failed. In a cursor, the last key fetched usually tells you which value was the problem.

How to fix it

• Guard computed arguments with CASE, as in CASE WHEN LOCATE(',', NAME) > 0 THEN SUBSTR(NAME, 1, LOCATE(',', NAME) - 1) ELSE NAME END.
• Validate host variables before the statement runs. Start must be at least 1 and length at least 0.
• Keep start plus length minus 1 within the declared length of the string.
• After a column is shortened, review every SUBSTR that uses fixed positions on it.

What it looks like

DSNT408I SQLCODE = -138, ERROR:  THE SECOND OR THIRD ARGUMENT OF THE SUBSTR OR SUBSTRING
         FUNCTION IS OUT OF RANGE
DSNT418I SQLSTATE   = 22011 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.