Data Transformation Engine Database Interface Designer Reference Guide

Chapter 10 - Debugging and Viewing Results Database Trace Files
Database Interface Designer Reference Guide
158
Transaction rollback was successful.
Status returned to engine: (0) Success
Commit was successful.
Database disconnect succeeded.
If you forget to specify the usage of the update setting (using -UPDATE either in
the PUT > Target > Command setting in the Map Designer or Integration Flow
Designer or in the command line), you may receive a database error resulting from
the attempt to insert a row with a duplicate index.
The first entry in this example highlighted in bold type indicates that update mode
is off. Because update mode is off, the database adapter attempts to insert a row
into the database table for each row produced by the map. The Message entry
contains the message returned by the database driver to the database adapter
describing the cause of the error. In this example, the row in error would violate
the UNIQUE_MEMBER_ID constraint defined for the table. The next lines show
the column values for the row in which the error occurred and the final result of
the database operation.
There are several possible methods for resolving this problem. Depending upon the
desired behavior, you might:
♦ Enable update mode when executing the map by using the
-UPDATE ON or
-UPDATE ONLY adapter command.
♦ Use the Delete adapter command (
-DELETE) to remove all rows from the
output database table before inserting the rows resulting from map execution.
♦ Build logic into your map to ensure that there is no existing row in the table
prior to generating an output row to be inserted. This might be accomplished by
either defining a query for the table being used as an input against which a
LOOKUP or SEARCHUP function is performed or by using the DBLOOKUP function
to check for an existing row.
♦ Use
-BADDATA so that all rows with a unique MEMBERID are inserted. Those
rows that would result in duplicate rows are then saved to a specified file.
Database Trace for a Target - DBLOOKUP/DBQUERY Functions
The following is a selection from a database trace file (map_name.dbl) for an
output card using the
GET function to execute a DELETE statement based upon a
value in the input.
.
.
Starting a database unload...
Host string:
Userid : eventmgt
Password : ********
Query : DELETE FROM REGISTRATION WHERE REGISTRATIONSTATUS = 'Overflow'
Query size : 71