Data Transformation Engine Database Interface Designer Reference Guide

Chapter 10 - Debugging and Viewing Results DBMS Trace Utilities and SQL Command Tools
Database Interface Designer Reference Guide
166
DBMS Trace Utilities and SQL Command Tools
The utilities and tools that are part of your relational database management
system (RDBMS) will also be helpful during the troubleshooting process. For
example, a map fails at runtime because a table name was invalid in an input that
is a database source. Try to execute that same query using the tools included with
your database to determine whether the query will run natively.
Trace Utilities
For example, when using ODBC data sources on a Windows platform, enable ODBC
tracing from the ODBC Data Source Administrator window. This creates a log of
the calls to ODBC drivers as you use the Database Interface Designer to define
databases and queries, and when you use an Event Server to run maps that have
database sources or targets. Similar tracing tools are available for most of the
other database systems. For information about the tracing capabilities available for
your database system, refer to the documentation for your RDBMS.
SQL Command Tools
Each database management system includes some form of an SQL command tool.
For example, ODBC data sources can be accessed using tools such as Microsoft
Query to test queries and view information about your tables, views, and stored
procedures. Oracle databases can be accessed using SQL Plus; Microsoft SQL
Server data sources can be accessed with ISQL.
These tools provide the ability to determine the problem by testing your queries
against the database using the same drivers being accessed by the database
adapters.
For example, if you run a map that executes a query for a data source and it fails
because one or more of the column names was invalid, you could copy the query
text from the Database Interface Designer and test it using the SQL command tool
for your database. Then, you could modify the query as necessary to get it to work
correctly. When you achieve the expected results, copy the SQL statement back to
the query defined in the Database Interface Designer.
Similarly, if a database insert or update operation fails, you could try entering the
corresponding INSERT or UPDATE statement into your database system’s SQL
command tool to help determine the cause of the failure.