SQLCODE -679: Drop pending on the object

What it means

When you drop an object, Db2 doesn't finish the job straight away. The drop stays pending until the unit of work commits, and the name stays in use until then. If the same unit of work tries to create a new object of the same type with the same name, Db2 rejects the CREATE with -679. The message names the object.

Indexes have an extra twist. An index lives in an index space, and Db2 generates the index space name from the index name. If a new index would get the same index space name as an index dropped without a commit, the CREATE fails too, even when the index names differ.

The fix is nearly always to commit after the DROP. In CICS that means a SYNCPOINT, and in IMS a checkpoint.

Common causes

• A SPUFI member drops and re-creates an object. SPUFI's autocommit commits once, after all the statements in the input have run, not after each one.
• A DSNTEP2 or DSNTEP4 batch step runs DROP and CREATE with no COMMIT between them.
• A program drops and re-creates a work table in one unit of work.
• A client tool or JDBC program runs DDL with autocommit turned off.
• A new index's generated index space name matches an index dropped in the same unit of work.

How to find the problem

1. Look for a DROP of the same name earlier in the same script, program or job step.
2. Find where the commits happen. If none comes between the DROP and the CREATE, that is the cause.
3. For an index, check whether the same unit of work dropped another index with a similar name. Db2 generates the index space name from the index name, so the clash can be on the index space rather than the index name. The INDEXSPACE column of SYSIBM.SYSINDEXES shows the name Db2 generated for an existing index.

How to fix it

• Put a COMMIT between the DROP and the CREATE. In SPUFI or DSNTEP2 input, add a COMMIT statement after the DROP.
• In a CICS program issue EXEC CICS SYNCPOINT, and in IMS take a checkpoint, between the two statements.
• For temporary work data, consider a declared global temporary table, or TRUNCATE the table instead of dropping and re-creating it.
• Rerun the CREATE after the commit.

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.