MainframeKit

-905

SQLSTATE 57014NEGATIVE SQLCODE

The statement or stored procedure used more CPU than its resource limit allows, so Db2 stopped it. Usually a slow query hitting the governor.

MOST LIKELY

A query with a poor access path, such as a table space scan on a large table.

FIRST CHECK

Read the message: resource name, limit and source (table, system parameter or SYSROUTINES).

TRY IT ▸

MainframeKit assistant

What it means

Db2 can cap how much CPU a single dynamic SQL statement or a routine may use. The cap comes from a resource limit specification table (RLST), a system parameter, or the ASUTIME value of a routine in SYSIBM.SYSROUTINES. When the cap is reached, Db2 returns -905 with SQLSTATE 57014. The message names the resource (typically ASUTIME), the limit in CPU seconds and service units, and where the limit came from.

What happens next depends on the source. With a table or system parameter limit, only the statement ends; an open cursor keeps its position and can still be closed, and the program may carry on with other SQL. When a routine exceeds its own ASUTIME, the work rolls back to the last commit point and the thread must roll back before issuing more SQL.

Typical causes

  • A query with a poor access path, such as a table space scan on a large table.
  • A missing or unused index after data growth or a stale RUNSTATS.
  • A dynamic query from a tool or report that is broader than intended.
  • A stored procedure looping or doing far more work than its ASUTIME allows.
  • A resource limit set too low for a legitimate workload.

How to fix it

  1. Read the message: resource name, limit and source (table, system parameter or SYSROUTINES).
  2. EXPLAIN the statement and look for scans, sorts and missing index use.
  3. Narrow the predicates, add or fix indexes, or refresh statistics with RUNSTATS.
  4. For a stored procedure, look for loops and check its ASUTIME setting.
  5. If the work is legitimate, ask the team that owns the resource limit tables to review the limit.
  6. After a routine ASUTIME failure, make sure the program rolls back before it issues more SQL.

Example

Illustrative only; names and values are invented.

DSNT408I SQLCODE = -905, ERROR:  UNSUCCESSFUL EXECUTION DUE TO RESOURCE
         LIMIT BEING EXCEEDED, RESOURCE NAME = ASUTIME ...
DSNT418I SQLSTATE   = 57014 SQLSTATE RETURN CODE