Database trace for a valid target

Edit online

The information included in the database trace for an output (data target) is similar to that for a database source. The following example shows the PUT > Target settings for the first output card (which is a database) in a map called UpdateMembershipDB.

The Update adapter command (-UPDATE) specifies that the rows produced by the map should update existing rows or should insert new rows, based upon the update keys configured for the table in the Database Interface Designer. The Trace adapter command (-TRACE) generates the database trace information for this output card.

An example of the database trace information produced for this map follows.

Database type is Oracle
Status returned to engine: (0) Success
No existing connection was found.
Connection to Oracle has been established.
Interface library version 6.0(140)
Loading data for output card 1.
Database adapter version 6.0(140)
Starting database load...
Host string: 
Userid     : eventmgt
Password   : ********
Update mode is on.
The columns are of the following types:
   Column 1 (MEMBERID) type is VARCHAR(10).
   Column 2 (ATTENDEEFIRSTNAME) type is VARCHAR(30).
   Column 3 (ATTENDEELASTNAME) type is VARCHAR(50).
   Column 4 (TITLE) type is VARCHAR(50).
   Column 5 (COMPANYNAME) type is VARCHAR(50).
   Column 6 (ADDRESS) type is VARCHAR(255).
   Column 7 (CITY) type is VARCHAR(50).
   Column 8 (STATEORPROVINCE) type is VARCHAR(20).
   Column 9 (POSTALCODE) type is VARCHAR(20).
   Column 10 (COUNTRY) type is VARCHAR(50).
   Column 11 (PHONENUMBER) type is VARCHAR(30).
   Column 12 (FAXNUMBER) type is VARCHAR(30).
   Column 13 (EMAILADDR) type is VARCHAR(50).
   Column 14 (MEMBERSINCE) type is DATE.
The insert statement to be executed is:
INSERT INTO Membership  VALUES 
     (:a00,:a01,:a02,:a03,:a04,:a05,:a06,:a07,:a08,:a09,:a10,:a11,:a12,:a13)
The update statement to be executed is:
UPDATE Membership SET ATTENDEEFIRSTNAME=:a01,ATTENDEELASTNAME=:a02,TITLE=:a03,
     COMPANYNAME=:a04,ADDRESS=:a05,CITY=:a06,STATEORPROVINCE=:a07,
     POSTALCODE=:a08,COUNTRY=:a09,PHONENUMBER=:a10,FAXNUMBER=:a11,EMAILADDR=:a12
     WHERE (MEMBERID=:a00)5 rows inserted.

               2 rows updated.
Database load complete.
Status returned to engine: (0) Success
Cleaning up and closing the transaction...
The transaction was successfully committed.
Status returned to engine: (0) Success
Commit was successful.
Database disconnect succeeded.

This sample database trace file ( map_name .dbl) reveals important information that is highlighted in bold type above and some of which is described below.

  • After the logon information in the sample, a message indicates that Update mode is on. This will affect how the rows produced are handled. Because update mode is on (enabled by the -UPDATE adapter command), rows in the table are inserted or updated based upon the update keys defined in the Database Interface Designer.
  • After the connection is made, the database trace shows the column definitions for the output table.
  • Next, the database trace file displays the actual SQL statements to be executed using the data produced for the output card. Because update mode is enabled, the database trace file displays both an INSERT statement and an UPDATE statement. The UPDATE statement is executed for each row in the output for which a corresponding row is found in the database table, using the update key defined (which, in this example, is the MemberID column). The INSERT statement is executed for all rows in the output for which a corresponding row does not exist in the table.
  • After the SQL statements in the sample, the database trace file indicates the number of rows that were both inserted (5) and updated (2).
  • Finally, the database trace file indicates that database load was executed successfully (Database load complete) and that the database changes were committed (The transaction was successfully committed).