DB2 error: '23502', 'DB2 SQL Error, 'com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException'

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Error Message :
U00003590 UCUDB - DB error: '23502', 'DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=202, COLNO=0, DRIVER=4.15.82', '-407', 'com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException'

A new install of Automation Engine is installed on Linux with a DB2 database.  The JWP is started.  

The JWP log shows this error:

U00003432 Termination of Server 'AUTOMICD#WP002' initiated.
U00003590 UCUDB - DB error: '23502', 'DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=205, COLNO=4, DRIVER=4.15.82', '-407', 'com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException'
SQL Statement which caused this DB error:

U02012075 Set bind parameter '1' of type 'Integer' to value '1091001'
U02012075 Set bind parameter '2' of type 'String' to value '20170515/163516.626 - U00003400 Server 'AUTOMICD#WP' version '12.0.1+hf.1.build.2961' (changelist '1481273491') started.

The JWP trace (TCP/IP=2, DB=4) file shows this:

INSERT INTO AH AH_HostDst,AH_Idnr,AH_Info,AH_Name,AH_OH_Idnr,AH_OType,AH_ProcessId,AH_Rest,AH_TimeStamp1) VALUES (?,?,?,?,?,?,?,?,?)

It is able to write into the AH table, but then it errors out when attempting to write into the RH table:

INSERT INTO RH (RH_AH_Idnr,RH_BlkSize,RH_FileInDB,RH_TimeStamp1,RH_Type) VALUES (?,?,?,?,?)

UCUDB32 EXEC RET 0001 HSTMT: 10.8.41.130.36130.170515203517 VALUE: 0000000000000000 ALL:  0.00900 DB:  0.00000 ODBC:  0.00000 UDB:  0.00000
UCUDB32 CLST RET 0000 HSTMT: 10.8.41.130.36130.170515203517 VALUE: 0000000000000000
U00003590 UCUDB - DB error: '23502', 'DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=202, COLNO=0, DRIVER=4.15.82', '-407', 'com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException'
 

 

Cause

Cause type:
Configuration
Root Cause: The USER/SCHEMA name in DB2 is uppercase. It appears the JDBC driver is case sensitive for DB2. The JDBC function get METADATA for the table isn't returning all/NO fields (column) because of the 'lowercaseUID.RH' used.

Environment

OS Version: N/A

Resolution

In the AE's ucsrv.ini file, ensure the UID= parameter in the SQLDRIVERCONNECT= is all in uppercase.

Example:
 

SQLDRIVERCONNECT=ODBCVAR=NNJNIORD,DSN=XXXXXXXX;UID=xxxxxxx;PWD=??? 

to

SQLDRIVERCONNECT=ODBCVAR=NNJNIORD,DSN=XXXXXXXX;UID=XXXXXXX;PWD=??? 

Fix Status: No Fix

Fix Version(s):
N/A

Additional Information

Workaround :
N/A