Troubleshooting
Problem
Using an IBM WebSphere Transformation Extender (WTX) map to insert data into Sybase that invalidates a foreign key constraint may result in tables being left in an inconsistent state.
Symptom
The trigger code uses the 'raiserror' function, which does NOT propagate to WTX, and this means the map does not fail, which will leave the tables in an inconsistent state because other DB transaction within the map will commit.
Cause
Map refuses to fail when inserting data that violates a foreign key relationship (implemented as code called by a trigger).
Environment
This issue can arise when using the ODBC adapter and the 3rd party (Data Direct) ODBC drivers supplied with WTX. The foreign constraint is implemented as a stored procedure that is called (via a trigger) upon table inserts.
Diagnosing The Problem
Add the trace (-T) command to the database adapter connection strings, execute the map, then review the WTX database adapter traces to see which one contains the key violation.
Resolving The Problem
As a workaround, the call to the ODBC adapter can be converted from a card to a DBLOOKUP or DBQUERY function which use a different insertion method. This method does recognize the 'raiserror' and fail the map if they are wrapped in a VALID/FAIL pair.
New Data Direct ODBC driver libraries are required to overcome this issue. Contact IBM Support for further details.
Was this topic helpful?
Document Information
More support for:
IBM Transformation Extender
Software version:
8.3, 8.4, 8.4.1
Operating system(s):
AIX, HP-UX, Linux, Solaris, Windows
Document number:
523019
Modified date:
29 September 2018
UID
swg21693639