Tracing database errors only

Depending upon the number of database sources and targets, the volume of database functions that are executed, and the database trace settings that are used, the database trace file can contain a large amount of data. Even when no database errors occur, the database trace file can become very large.

Use the Trace Error adapter command (-TRACEERR) to minimize the amount of information contained in the database trace file ( map_name .dbl). This file contains only the database errors occurring during the map execution.

When -TRACEERR is specified, the database trace file is produced using the full name of the MDQ with a .dbl file name extension instead of the usual trace (.mtr) extension. It is created in the directory in which the map is located. Use the -TRACEERR filename syntax as described in the Resource Adapters documentation to specify the name for the database trace file.

A full database trace is most beneficial during map development; however, it is preferable to use -TRACEERR in a production environment because only the database errors are reported.

For example, if you generate the full database trace for a map called DBUpdate that has three database input cards, it would produce the following 88-line MultiDB's.dbl file.

Database type is MS SQL Server
Status returned to engine: (0) Success
No existing connection was found.
Error in: dbopen
Error   : 5701
State   : 2
Message : Changed database context to 'master'.
Error in: dbuse
Error   : 5701
State   : 1
Message : Changed database context to 'pubs'.
A transaction was started.
Connection to SQL Server has been established.
Interface library version 6.0(140)
Data being retrieved for input card 1.
Database adapter version 6.0(140)
Starting a database unload...
Server\\Database: HP_NT\\pubs
Userid          : sa
Password        : 
Query           : SELECT * FROM Authors
Query size      : 21
Output is to a buffer.
Statement execution succeeded.
The columns are of the following types:
   Column 1 (au_id) type is varchar(11).
   Column 2 (au_lname) type is varchar(40).
   Column 3 (au_fname) type is varchar(20).
   Column 4 (phone) type is char(12).
   Column 5 (address) type is varchar(40).
   Column 6 (city) type is varchar(20).
   Column 7 (state) type is varchar(2).
   Column 8 (zip) type is varchar(5).
   Column 9 (contract) type is bit.
Number of buffers in fetch array = 1
Writing results to a buffer.
Retrieved 23 records (1810 bytes).
Status returned to engine: (0) Success
Database type is MS SQL Server
Status returned to engine: (0) Success
Interface library version 6.0(140)
Data being retrieved for input card 2.
Database adapter version 6.0(140)
Starting a database unload...
Server\\Database: HP_NT\\pubs
Userid          : sa
Password        : 
Query           : SELECT * FROM Publishers
Query size      : 24
Output is to a buffer.
Statement execution succeeded.
The columns are of the following types:
   Column 1 (pub_id) type is char(4).
   Column 2 (pub_name) type is varchar(40).
   Column 3 (city) type is varchar(20).
   Column 4 (state) type is varchar(2).
   Column 5 (country) type is varchar(30).
Number of buffers in fetch array = 1
Writing results to a buffer.
Retrieved 8 records (305 bytes).
Status returned to engine: (0) Success
Database type is MS SQL Server
Status returned to engine: (0) Success
Interface library version 6.0(140)
Data being retrieved for input card 3.
Database adapter version 6.0(140)
Starting a database unload...
Server\\Database: HP_NT\\pubs
Userid          : sa
Password        : 
Query           : SELECT * FROM Title
Query size      : 19
Output is to a buffer.
Error in: dbsqlexec

            Error   : 208

            State   : 1

            Message : Invalid object name 'Title'.

            Error in: dbsqlexec

            Error   : 10007

            Message : General SQL Server error: Check messages from the SQL Server.

            Failed to execute statement.

            Invalid object name 'Title'.
Retrieved 0 records (0 bytes).
Error returned to engine: (-9) Failed to execute the SQL statement
Cleaning up and closing the transaction...
The transaction was successfully committed.
A transaction was started.
Status returned to engine: (0) Success

After reviewing this file, notice that the query for input card number 3 (SELECT * FROM Title) failed because it references an object name (the table name Title) that does not exist. Alternatively, you could use the Trace Error adapter command (-TRACEERR) for each of the three input cards and produce the following database trace file:

Invalid object name 'Title'.

This file can be used along with the following execution audit log information:

  <SourceReport card="1" adapter="DB" bytes="1810" adapterreturn="0">
      <Message>Success</Message>
      <TimeStamp>01:02:00 January 9, 2004</TimeStamp>
   </SourceReport>

   <SourceReport card="2" adapter="DB" bytes="305" adapterreturn="0">
      <Message>Success</Message>
      <TimeStamp>01:02:00 January 9, 2004</TimeStamp>
   </SourceReport>

   <SourceReport card="3" adapter="DB"  bytes="0"  adapterreturn="-9">
      <Message>Failed to execute the SQL statement</Message>
      <TimeStamp>01:02:00 January 9, 2004</TimeStamp>
   </SourceReport>

Use this information to determine whether the query defined for input card 3 references a table or view that does not exist.