DB2 Other Common Negative SQLCODEs (-601, -602, -603, -4742)

At a glance

-601 Object name already exists

IBM: THE NAME (VERSION OR VOLUME SERIAL NUMBER) OF THE OBJECT TO BE DEFINED OR THE TARGET OF A RENAME STATEMENT IS IDENTICAL TO THE EXISTING NAME … object-name OF THE OBJECT TYPE object-type. SQLSTATE 42710 or 46002.

You tried to CREATE (or rename to, or add a version/volume/constraint name for) something whose name is already taken for that object type. The object-type token tells you what collided: TABLE, INDEX, VIEW, ALIAS, PROCEDURE, FUNCTION, TRIGGER, SEQUENCE, ROLE, CONSTRAINT, DISTINCT TYPE, MASK, PERMISSION, VARIABLE, JAR, TRUSTED CONTEXT, VERSION, VOLUME, XSR SCHEMA, and more.

Typical situations

• CREATE — table, index, procedure, etc. already defined
• ALTER add clone / version / volume — pending object, routine version id, or STOGROUP volume serial already present
• CONSTRAINT names — FK, CHECK, PRIMARY KEY, UNIQUE names on a table must be unique among themselves
• RENAME / pending objects — target name exists in the catalog or in SYSIBM.SYSPENDINGOBJECTS
• CREATE ALIAS — alias name matches an existing table or view name (even if that table is missing—name space conflict)

Programmer response: DROP or remove the existing object, finish or clear pending definitions, or choose another name. For data set name clashes on CREATE, verify the STOGROUP VCATNAME and consider IDCAMS DELETE of an orphan VSAM data set before retrying.

1
2
3
4
5
6
7
8
9
CREATE TABLE HR.EMP (...);  -- first time OK
CREATE TABLE HR.EMP (...);  -- -601 object-type TABLE
 
CREATE INDEX HR.EMP_X1 ON HR.EMP (EMPNO);
CREATE INDEX HR.EMP_X1 ON HR.EMP (LASTNAME);  -- -601 INDEX
 
-- Constraint names must be unique on the table
ALTER TABLE HR.EMP ADD CONSTRAINT CK1 CHECK (SALARY >= 0);
ALTER TABLE HR.EMP ADD CONSTRAINT CK1 CHECK (BONUS >= 0);  -- -601 CONSTRAINT

-602 Too many columns or key-expressions in an index

IBM: TOO MANY COLUMNS, PERIODS, OR KEY-EXPRESSIONS SPECIFIED IN A CREATE INDEX OR ALTER INDEX STATEMENT. SQLSTATE 54008. The statement cannot be processed.

Db2 limits index keys so that either:

• columns + (2 × number of identified periods) exceeds 64, or
• the number of key-expressions exceeds 64

Programmer response: reduce the index definition—fewer columns, fewer periods in the key, or fewer expression keys. Wide indexes are rarely optimal anyway; prefer a selective subset that supports the access path you need.

1
2
3
4
5
6
7
-- Conceptual: more than 64 key pieces → -602
CREATE INDEX BIG.IX ON BIG.T (
  C1, C2, C3 /* ... dozens of columns ... */
);
 
-- Fix: index only what queries filter/join on
CREATE INDEX BIG.IX ON BIG.T (C1, C2, C3);

-603 Unique index refused because duplicates already exist

IBM: A UNIQUE INDEX CANNOT BE CREATED BECAUSE THE TABLE CONTAINS ROWS WHICH ARE DUPLICATES WITH RESPECT TO THE VALUES OF THE IDENTIFIED COLUMNS AND PERIODS. SQLSTATE 23515. Statement not processed.

Unlike -803 (runtime insert/update against an existing unique index),-603 happens at CREATE INDEX … UNIQUE (or equivalent) when the table’s current data already violates uniqueness. Periods participate in the uniqueness test for temporal keys.

Programmer response: find duplicate groups with GROUP BY / HAVING COUNT(*) > 1 on the key columns, clean or merge rows, then recreate the unique index—or create a non-unique index if duplicates are valid.

1
2
3
4
5
6
7
8
9
10
11
-- Find duplicates before CREATE UNIQUE INDEX
SELECT EMPNO, COUNT(*) AS CNT
FROM HR.EMP
GROUP BY EMPNO
HAVING COUNT(*) > 1;
 
-- After cleanup:
CREATE UNIQUE INDEX HR.EMP_PKX ON HR.EMP (EMPNO);
 
-- If duplicates are legitimate business data:
CREATE INDEX HR.EMP_LAST_X ON HR.EMP (LASTNAME);  -- non-unique

-601 vs -603 vs -803

• -601 — the name of an object already exists
• -603 — CREATE UNIQUE INDEX blocked by existing duplicate rows
• -803 — INSERT/UPDATE would create a duplicate against an index that already exists

-4742 Cannot execute in Db2 or in the accelerator

IBM: THE STATEMENT CANNOT BE EXECUTED BY DB2 OR IN THE ACCELERATOR (REASON reason-code). SQLSTATE 560D5. This is the workhorse code for IBM Db2 Analytics Accelerator eligibility failures when Db2 cannot (or must not) run the statement locally either.

When Db2 itself cannot run it

• CURRENT GET_ACCEL_ARCHIVE = YES and data exists only on the accelerator
• CURRENT QUERY ACCELERATION = ALL (must run on accelerator)
• Function exists only on the accelerator
• Statement references an accelerator-only table

Common reason-code themes (accelerator side)

IBM documents many numeric reason codes. Memorize the themes; look up the exact number in Codes when you hit production:

• 1 — no active accelerator / table not enabled for acceleration
• 2 — CURRENT QUERY ACCELERATION is NONE
• 3 — short-running query or no performance advantage
• 4 — query is not read-only
• 6 — scrollable or rowset-positioned cursor
• 7 — multiple encoding schemes
• Higher codes — unsupported functions, correlated subqueries, SELECT INTO, two-phase commit paths, WAITFORDATA conflicts, version mismatches, and more

Tip from IBM: issue EXPLAIN and inspect DSN_QUERYINFO_TABLE for why acceleration failed. Programmer responses depend on the reason: start/enable the accelerator, change QUERY ACCELERATION / QUERYACCELERATION bind options, rewrite SQL, commit before WAITFORDATA work, or stop referencing accelerator-only objects when you need local Db2 execution.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SET CURRENT QUERY ACCELERATION = ALL;
 
-- May return -4742 reason-code if not eligible / no accelerator
SELECT COUNT(*), SUM(AMOUNT)
FROM SALES.FACT_DAILY
WHERE SALE_DATE BETWEEN '2024-01-01' AND '2024-12-31';
 
-- Diagnose
EXPLAIN ALL SET QUERYNO = 1001 FOR
  SELECT COUNT(*), SUM(AMOUNT)
  FROM SALES.FACT_DAILY
  WHERE SALE_DATE BETWEEN '2024-01-01' AND '2024-12-31';
 
SELECT REASON_CODE, QI_DATA
FROM DSN_QUERYINFO_TABLE
WHERE QUERYNO = 1001
ORDER BY EXPLAIN_TIME DESC;

1
2
3
4
5
6
-- Allow local Db2 when acceleration is optional
SET CURRENT QUERY ACCELERATION = ENABLE;
-- or ENABLEWITHFAILBACK via special register / bind option
 
-- Force local only while debugging
SET CURRENT QUERY ACCELERATION = NONE;

System action and logging tips

For -601/-602/-603 the DDL does not create the object. For -4742 the statement does not return a result set from the intended engine. Capture SQLCODE, SQLSTATE, and for -4742 the reason-code and EXPLAIN QUERYNO. Accelerator failures are often environment issues (started task down, table not enabled) rather than bad COBOL—check operations before rewriting application SQL.

Explain It Like I'm Five

-601 is trying to put a second lunchbox with the same name tag in the cubby. -602 is stuffing more than sixty-four stickers on one index binder spine. -603 is asking for a “no two alike” sticker book when the desk already has twin stickers. -4742 is being told “this homework must be done on the turbo computer,” but the turbo computer is off or the homework is the wrong kind—so nobody does it.

Exercises

1. Produce -601 with CREATE TABLE twice. Produce -601 with two CHECK constraints that share a name.
2. Load duplicate EMPNO values, then CREATE UNIQUE INDEX to get -603. Write the SELECT that finds the duplicates.
3. Explain in one sentence why -603 is not the same as -803.
4. With QUERY ACCELERATION ALL and no accelerator, run a SELECT and record the -4742 reason-code. Look it up in IBM Docs.
5. Run EXPLAIN for that query and list columns you would read in DSN_QUERYINFO_TABLE.