SQL -204 Error On DB2 Z/OS After Attempt To Violate A Unique Index

(Doc ID 2073326.1)

Last updated on SEPTEMBER 22, 2017

Applies to:

Siebel CRM - Version 8.1.1.11.9 [IP2013] and later
Information in this document applies to any platform.

Symptoms

On : 8.1.1.11.9 [IP2013] and above, on z/OS databases only

When attempting to do an insert that violates a unique index user key (_U* index)

ERROR
-----------------------

ObjMgrBusCompLog Error 1 00000002542b1ac4:0 2014-10-01 08:15:47 (clicon.cpp (3613)) SBL-DBC-00111: An error has occurred writing to a record.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1.Find a Contact, then go to Orders tab
2.Create a new Order and save it:
3. Siebel logfile then will show that the database has rejected the INSERT as it is trying to violate a unique index:
ObjMgrBusCompLog Error 1 00000002542b1ac4:0 2014-10-01 08:15:47 (clicon.cpp (3613)) SBL-DBC-00111: An error has occurred writing to a record.
This is entirely normal and expected.
But the database also sees when this happens, the following SQL which is NOT logged in the Siebel log:

SELECT INDNAME
FROM SYSCAT.INDEXES
WHERE IID = 0 AND TABNAME = '' AND TABSCHEMA = '0024070F18' FOR FETCH
ONLY

This SQL is invalid on this database platform and generates an error on DB2 z/OS.

DSNT408I SQLCODE = -204, ERROR: SYSCAT.INDEXES IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -500 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000'
  X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
  INFORMATION


BUSINESS IMPACT
-----------------------
The issue has the following business impact:

Benign error messages fill up the database logs

Changes

 applying 8.1.1.11.9 [IP2013] or above

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms