HP NonStop Data Transformation Engine Database Interface Designer Reference Guide Abstract This manual provides information about using the HP NonStop™ Data Transformation Engine (NonStop DTE) Database Interface Designer Reference Guide. Product Version NonStop Data Transformation Engine 6.7.
Document History Part Number Product Version Published 528257-001 NonStop Data Transformation Engine 6.7.1 June 2004 528257-002 NonStop Data Transformation Engine 6.7.
Contents About This Document Related References........................................................................................... 6 Chapter 1 - Introduction Overview ....................................................................................................... 8 Installation..................................................................................................... 9 Basic Steps for Using Database Data ......................................................................
Contents Components and Format of the Row Type ......................................................... 76 Defining the Column Type(s) ......................................................................... 79 Stored Procedure Type Tree Structure .................................................................. 89 Oracle8 AQ Message Type Tree Structure ............................................................... 90 Chapter 5 - Database Sources and Targets Using a Database as a Source................
Contents Issues for Both Row- and Table-Based Triggering................................................136 Issues for Row-Based Triggering Only ..............................................................136 Defining a Trigger Using a Database Source ......................................................136 Using a Database as a Map Trigger ..................................................................... 137 Defining a Trigger for a Query..............................................................
About This Document This document contains information about the Mercator Database Interface Designer. This document assumes that the reader knows the Windows environment, has used the Mercator Design Studio, and understands words such as mapping, type trees, and other basic Mercator terminology. Related References The Mercator Online Library contains information about all products. Refer to the following references for more information about topics frequently discussed in this document.
About This Document Related References Related Reference Description Map Designer Reference Guide Describing the Map Designer user interface and providing instruction on specifying mapping rules, configuring map sources and targets, defining map-level settings, and executing a compiled map. Type Designer Reference Guide Using the Type Designer to create and edit type trees that describe your data.
Chapter 1 - Introduction This document provides information about the Database Interface Designer. It explains the functions and dialog boxes used to utilize your database-specific adapters when accessing your database as map sources or targets. Note For information about using specific adapters, refer to the respective reference guides. Overview The Database Interface Designer is used to import metadata about queries, tables, and stored procedures for data stored in relational databases.
Chapter 1 - Introduction Installation Note Non-Windows adapters do not require the Database Interface Designer if you plan to only use mtsmaker without a database/query file (.mdq) to generate each type tree. For information about using mtsmaker, refer to the Resource Adapters Reference Guide. Installation This product is installed as one of the components of the Mercator installation program. Refer to the Getting Started book for details about installing or removing this product from your system.
Chapter 2 - Basics This chapter introduces the Mercator Database Interface Designer window and provides information about working with the graphical user interface. Starting the Database Interface Designer During installation, an entry for the Database Interface Designer is added to the Mercator program folder. From the Windows Start menu, choose Programs > Mercator > Design Studio > Database Interface Designer. When the Database Interface Designer runs, the Startup dialog box appears.
Chapter 2 - Basics Mercator Database Interface Designer Window Mercator Database Interface Designer Window The Mercator Database Interface Designer window provides a graphical user interface in which to create and maintain .mdq files. These files contain database definitions that include information such as database name, connection information, queries, stored procedures, and so on.
Chapter 2 - Basics Mercator Database Interface Designer Window The Navigator The Navigator graphically presents all of your opened .mdq files and the databases that they contain. It also provides a graphical representation of the queries, stored procedures, message queues, and tables or views that have type trees generated. Also displayed are tables and views with update keys defined, as well as variables that are defined in each .mdq file.
Chapter 2 - Basics Mercator Database Interface Designer Window To float the Navigator in the main window 1 Starting with the Navigator being docked, right-click on the top border of the Navigator. The context menu appears. 2 Click Float In Main Window. The Navigator is now a separate window, floating in the main window. To dock the Navigator Note This procedure assumes that the Navigator is floating in the main window.
Chapter 2 - Basics Mercator Database Interface Designer Window Navigator Icons In addition to the hierarchical structure indicating the contents of each .mdq file, information about entries in the Navigator is visually conveyed using different icon representations defining the object and its state. For example, when a type tree has been generated for a query, the query icon changes to represent the existence of a generated type tree.
Chapter 2 - Basics Mercator Database Interface Designer Window ♦ update keys have been defined for the Categories table ♦ a variable named #01-01-1999# The following table lists and briefly describes the icons that appear in the Navigator. Icon Description .mdq file .mdq file that has been checked out of third-party source control project .
Chapter 2 - Basics Help Help Comprehensive Help is available for the Design Studio that includes the Database Interface Designer. The Help provides easy access to descriptions and procedures for all application features and for functions including various menus and tools. Access Help by selecting Contents from the Help menu. Menu Commands and Tools Actions can be performed in the Database Interface Designer window using menu commands, tools, and shortcut keys.
Chapter 2 - Basics Menu Commands and Tools Toolbar Database Interface Designer-specific tools Common Windows tools The toolbar is a part of the Mercator Database Interface Designer window, providing quick access to various tools that invoke Database Interface Designer actions while working with database/query files. The left side of the toolbar provides the tools generally available in Windows applications. The remaining tools are specific to the Database Interface Designer.
Chapter 2 - Basics Command Menu Commands and Tools Tool Shortcut Key Description Source Control > Open Project none Alt+F, L, O Displays the Project window in which you can begin the process of selecting an existing project in which to work using a third-party source control dialog box. Source Control > Get Latest Version none Alt+F, L, G Displays the Get Latest Version dialog box in which you can select related files that you can get.
Chapter 2 - Basics Command Menu Commands and Tools Tool Shortcut Key Description Source Control > Show History none Alt+F, L, H Displays a third-party, source control application-specific dialog box that allows you to define what you want to see in a history report generated by that third-party application.
Chapter 2 - Basics Menu Commands and Tools Edit menu (Alt+E) The Edit menu provides the editing commands generally available in Windows applications. It also contains the Find command that can be very useful when viewing trace files in a trace window.
Chapter 2 - Basics Menu Commands and Tools Command Tool Navigator none Trace File Shortcut Key Description Alt+V, N Shows or hides the Navigator Alt+V, F Displays the Database Interface Designer trace file for the selected .mdq file in a separate trace window Database menu (Alt+D) The Database menu provides commands to initiate actions for a selected database.
Chapter 2 - Basics Command Generate Tree From > Table Menu Commands and Tools Tool Shortcut Key Alt+D, G, T Description Displays the Generate Type Tree from Tables dialog box that allows you to generate a type tree from a table or view in the selected database Note You can also double-click the Tables object in the Navigator to perform the same function as the tool.
Chapter 2 - Basics Menu Commands and Tools Query menu (Alt+Q) The Query menu provides commands to initiate actions for a selected query. Command New Tool Shortcut Key Ctrl+Q or Insert Description Displays the New Query dialog box in which to add a new query Note You must first select the Queries object in the Navigator to be able to do this.
Chapter 2 - Basics Menu Commands and Tools Tools menu (Alt+L) The Tools menu provides options to customize your Database Interface Designer environment.
Chapter 2 - Basics Menu Commands and Tools Help menu (Alt+H) The Help menu offers choices to display information about the Database Interface Designer.
Chapter 2 - Basics Configuring the Environment Configuring the Environment Much of the Database Interface Designer environment can be configured to accommodate your preferred work environment. For example, you can: ♦ specify various user interface options (font, line appearance, dialog box display) ♦ select the tools to display ♦ change the look of the tools on the toolbar ♦ assign shortcut keys Tools > Options From the Tools menu, select Options. The Options dialog box appears.
Chapter 2 - Basics Configuring the Environment In the list of options, select General to specify values concerning the backing up and saving of your .mdq files. The fields in this dialog box are as follows: Field Description Auto-save files every n minutes (where n represents a number) This spin button indicates the time interval at which to automatically save opened .mdq files. The default value is 0. Backup on save This check box specifies whether to create a backup copy of each .
Chapter 2 - Basics Configuring the Environment Field/Button Description Lines This group box displays the options that control the appearance of lines in the Navigator. Select one of the following options. None No lines are displayed. Solid Solid lines are displayed. Dotted Dotted lines are displayed. This is the default setting.
Chapter 2 - Basics Configuring the Environment Tables/Views Option In the options list, select Tables/Views to determine the objects to be displayed that are associated with the database. The fields in this dialog box are as follows: Field Description List tables This check box determines whether tables are displayed in the database list. The default value is enabled. List views This check box determines whether views are displayed in the database list. The default value is enabled.
Chapter 2 - Basics Configuring the Environment Confirmations Options In the list of options, select Confirmations to specify the actions for which you want a confirmation dialog box displayed before completion of those actions. The fields in this dialog box are described as follows: Field/Button Description Database operations This group box displays the options that control whether confirmation dialog boxes are displayed with regard to database operations. Select any or all of these options.
Chapter 2 - Basics Configuring the Environment Field/Button Description Table operations This group box displays the options that control whether confirmation dialog boxes are displayed with regard to table operations. Select any or all of these options. Deleting table(s) This check box determines whether a confirmation dialog box appears when deleting a table. The default value is enabled. Copying table(s) This check box determines whether a confirmation dialog box appears when copying a table.
Chapter 2 - Basics Configuring the Environment Shortcut Keys You can assign your own shortcut keys for any existing or new menu items. Using the Shortcut Keys dialog box (as shown below), you can: ♦ assign shortcut keys ♦ remove shortcut key assignments ♦ restore the shortcut key assignments present at installation These commands are available for shortcut keys. Assigned shortcut keys appear here. Select a command. Click to create a shortcut.
Chapter 3 - Database/query Files This chapter discusses how to use the Database Interface Designer when working with .mdq files to perform the following types of tasks: ♦ Create .mdq files. ♦ Define the various objects included in an .mdq file. (For more information about such objects as queries and variables, refer to Defining a Query and Defining Variables in SQL Statements.) ♦ Generate type trees. (Refer to Generating Type Trees.) ♦ Print reports and enable trace functionality.
Chapter 3 - Database/query Files Creating Database/Query Files When an .mdq file is created, it appears in the Navigator next to the appropriate icon ( ). The Database_QueryFile file name is automatically assigned, along with a sequential number. To save an .mdq file or rename it Note Saving a new .mdq file uses the same procedure as saving an existing one. 1 From the File menu, select Save As. The Save As dialog box appears. 2 Enter the new file name and select the path. 3 Click OK.
Chapter 3 - Database/query Files Defining a Database Defining a Database When an .mdq file appears in the Navigator, you can add new database definitions to it or you can modify the name of an existing database. This is done using the Database Definition window. Each Database Definition window contains some settings that are common across all platforms and others that are platform-specific.
Chapter 3 - Database/query Files Setting Defining a Database Description Adapter Type This is a list of adapters that can host the database you are defining. Select one from the list. The default value is ODBC. Note This selection will affect the list of supported platforms displayed in the Platform list. Platform Access user tables/procedures only This is a list of platforms upon which the adapter (that you selected in the Type list) is supported.
Chapter 3 - Database/query Files Setting Defining a Database Description Data Source Database Interface Designer Note Both this and the Runtime settings are platform-specific setting. On other platforms, you may have different setting. Again, refer to the platform-specific adapter reference guide or the contextsensitive help for more setting-specific information. This is the name of the data source used to access database information for design-time (pre-production or testing) purposes.
Chapter 3 - Database/query Files Defining a Query Defining a Query After you have opened an .mdq file in the Mercator Database Interface Designer window and a database has been defined, you can specify queries. To use a query as a source, define the query by assigning it a name and entering either the SQL SELECT statement or the stored procedure invocation statement. To define a query 1 In the Navigator, select Queries under the database icon ( want to add a query. 2 From the Query menu, select New.
Chapter 3 - Database/query Files Editing or Deleting a Database or Query Editing or Deleting a Database or Query To edit a database or query 1 In the Navigator, select the name of the database or query to be edited. 2 From the Database menu (or Query menu as appropriate), select Edit. or In the Navigator, right-click the database or query icon and select Edit. The Edit/View Query dialog box appears. 3 Make any necessary changes and click OK.
Chapter 3 - Database/query Files Comparing Database/query Files Criteria For Analyzing Differences The criteria for analyzing differences for the different entities is as follows: ♦ .mdq file ! Any of the database definitions are different. ! Any of the variables are different. ! Any of the objects exist in one, but not the other, .mdq file. ♦ database definition ! Any of the database definition settings are different.
Chapter 3 - Database/query Files 3 Comparing Database/query Files Click Open. The Select Second File dialog box appears. 4 Navigate your file system and select the second .mdq source file to be compared. 5 Click Open. The progress of the comparison is shown briefly in the Database Source File Differences Analysis dialog box. When the analysis has completed, the Database Differences window appears. Viewing Database/query File Differences Note The Database Differences window should already appear.
Chapter 3 - Database/query Files Comparing Database/query Files Database/query File Difference Results When database/query source files are compared, they are considered different if any of the database definition settings are different, any of the variables are different, or any of the objects exist in one .mdq file and not the other. Text color is used to provide a visual distinction.
Chapter 3 - Database/query Files Comparing Database/query Files Query Differences If there are differences between the two compared .mdq files with regard to query syntax or column overrides, the name of the query with differences appears in red letters in the Database Differences window. If the query in each .mdq file is uniquely named, each query name appears in blue letters. If each .
Chapter 3 - Database/query Files Comparing Database/query Files Stored Procedure Differences If the stored procedures of the two compared .mdq files have the same name, but different values, the stored procedure name will appear in red letters in the upper pane and both stored procedures will appear in the bottom panes with the Column Overrides tab letters also in red letters. In the bottom panes, the unique value(s) will appear in red letters. If the stored procedure names of either of the two compared .
Chapter 3 - Database/query Files Defining Variables in SQL Statements Defining Variables in SQL Statements Elements of SQL statements can be executed as map sources that are determined at runtime. Use the Database Interface Designer to define a statement variable with a pseudo value in an SQL statement and then pass the actual value on the command line at runtime.
Chapter 3 - Database/query Files Defining Variables in SQL Statements To specify values in the Define Variables dialog box 1 In the Navigator, select the variable to which you want to add one or more values. 2 From the Query menu, select Define Variables. The Define Variables dialog box appears, listing all the variables in the .mdq file. 3 In the Value field, enter a value for each variable and click OK. Note The pseudo values are stored in the .
Chapter 3 - Database/query Files Generating Type Trees (-ID). For more information about how to specify values for these variables at run time, refer to the Resource Adapters Reference Guide. Generating Type Trees Use the Database Interface Designer to generate a type tree from a table, query, stored procedure, or message queue associated with a particular database.
Chapter 3 - Database/query Files Generating Type Trees To define the type tree to generate from a table or view, use the Generate Type Tree from Tables dialog box or the Generate Type Tree from Views dialog box. An example of the Generate Type Tree from Tables dialog box follows, along with a table describing its fields and their descriptions. Field Description Database Name This is the name of the database from which you want to generate a type tree.
Chapter 3 - Database/query Files Generating Type Trees Field Description File name This field displays a system-generated file name based upon the database selection in the Navigator. The default value is the database name with a type tree extension (.mtt) and a default path. To change any of this information, click (browse). Overwrite file This check box determines whether you want to replace the existing type tree file (.mtt) upon saving it. The default value is disabled.
Chapter 3 - Database/query Files Generating Type Trees Field Description Delimiter This is the value for the delimiter to be used when generating this type tree. For a list of available symbols, click the browse button. The Symbols dialog box appears, allowing you to select the desired value. The default value is a pipe character (|). Terminator This is the value for the terminator to be used when generating this type tree. For a list of available symbols, click the browse button.
Chapter 3 - Database/query Files Generating Type Trees Field Description Represent date/time columns as text items This check box determines whether to automatically format this information in its database-specific date and time format (enabled) or as a text string (disabled).
Chapter 3 - Database/query Files Generating Type Trees Note If the table or view you want is not displayed in this list, add it by rightclicking in the list, selecting Insert, and typing the desired name. 4 To create a new type tree, in the File name field, specify the path and name (with an .mtt extension). or Click (browse) to select a type tree from the Save As dialog box. Highlight it and click OK. 5 Specify the remaining options as desired.
Chapter 3 - Database/query Files Generating Type Trees Field Description Database Name (Display only) This is the name of the database from which you want to generate a type tree. (This name is automatically inserted based upon your selection in the Navigator.
Chapter 3 - Database/query Files Generating Type Trees Field Description Stored Procedures This list displays all of the stored procedures that have been defined in this database. Select one or more from this list. Note The list of stored procedures includes stand-alone procedures by default. (Procedures that are parts of a package are not listed.
Chapter 3 - Database/query Files Generating Type Trees Field Description Row group format This list displays options to determine the row format in the generated type tree. The default value is Delimited. Delimited Select this option to create rows delimited with a pipe character (|) or some other user-defined selection. This is the preferred option if you are using large column widths where the values may be smaller than the width of the columns. The pipe character is the default selection.
Chapter 3 - Database/query Files Generating Type Trees Field Description Override Column Definitions (For ODBC only) This check box indicates whether the database driver (with the check box disabled) or a command line override (with the check box enabled) is used to interpret columns. The default value is disabled. For more information about this field, refer to the adapter-specific documentation in each adapter reference guide. Generate type trees in Mercator 1.4.
Chapter 3 - Database/query Files Generating Type Trees To generate a type tree from a stored procedure 1 In the Navigator, highlight the icon of the database containing one or more stored procedures from which you want to generate a type tree. 2 From the Database menu, click Generate Tree From > Procedure. or In the Navigator, right-click the database and select Generate Tree From > Procedure. The Generate Type Tree from Stored Procedures dialog box appears, including a list of stored procedures.
Chapter 3 - Database/query Files Generating Type Trees The generated type tree is represented in the Navigator with the stored procedure icon ( ) next to the name. From a Message Queue (Oracle8 AQ) Use the Database Interface Designer to generate type trees for a message on an Oracle8 AQ message queue. An example of the Generate Type Tree from Queues dialog box follows, along with a table describing its fields and their descriptions.
Chapter 3 - Database/query Files Generating Type Trees Field Description Tables/Views This list displays all of the queues that have been defined in this database. Select one or more from this list. Note If the queue you want is not displayed in this list, add it by right-clicking in the list, selecting Insert, and typing the desired name. File name This field displays a system-generated file name based upon the database selection in the Navigator.
Chapter 3 - Database/query Files Field Generating Type Trees Description Fixed Select this option to create rows of fixed size in the generated type tree. There are certain situations in which you would want to select Fixed. ♦ The query result contains a large number of rows where each row consists of small, fixed-length fields. In this situation, delimiters are unnecessary and they also consume memory. ♦ You need to append fields in the output.
Chapter 3 - Database/query Files Generating Type Trees Field Description Represent date/time columns as text items This check box determines whether to automatically format this information in its database-specific date and time format (enabled) or as a text string (disabled).
Chapter 3 - Database/query Files 4 Generating Type Trees To create a new type tree, in the File name field, specify the path and name (with an .mtt extension). or Click the browse button to select a type tree from the Save As dialog box. Highlight it and click OK. 5 Specify the remaining options as desired. Refer to context-sensitive help for field-specific information. 6 Click Generate.
Chapter 3 - Database/query Files Generating Type Trees Field Description File name This field displays a system-generated file name based upon the database selection in the Navigator. The default value is the database name with a type tree extension (.mtt) and a default path. To change any of this information, click (browse). Overwrite file This check box determines whether you want to replace the existing type tree file (.mtt) upon saving it. The default value is disabled.
Chapter 3 - Database/query Files Generating Type Trees Field Description Row group format This list displays options to determine the row format in the generated type tree. The default value is Delimited. Delimited Select this option to create rows delimited with a pipe character (|) or some other user-defined selection. This is the preferred option if you are using large column widths where the values may be smaller than the width of the columns. The pipe character is the default selection.
Chapter 3 - Database/query Files Generating Type Trees Field Description Override Column Definitions (For ODBC only) This check box indicates whether the database driver (with the check box disabled) or a command line override (with the check box enabled) is used to interpret columns. The default value is disabled. For more information about this field, refer to the adapter-specific documentation in each adapter reference guide. Generate type trees in Mercator 1.4.
Chapter 3 - Database/query Files Printing Reports To generate a type tree from a query 1 In the Navigator, highlight the icon of the query from which you want to generate a type tree. 2 From the Query menu, select Generate Tree. or In the Navigator, right-click the query and select Generate Tree. The Generate Type Tree from Query dialog box appears, displaying the name of the selected type tree. 3 To create a new type tree, in the File name field, specify the path and name (with an .mtt extension).
Chapter 3 - Database/query Files Printing Reports To print a report 1 Highlight the database within an .mdq file for which you want to print the information. 2 From the File menu, select Print. or In the Navigator, right-click the icon of the database and select Print. The Print dialog box appears. 3 To print all of the entities displayed, skip to the next step. or To select certain entities to not be printed, disable the appropriate check box next to the entity name.
Chapter 3 - Database/query Files 7 Database Interface Designer Trace Files Make any changes as desired and click OK. The report is printed. Database Interface Designer Trace Files The Database Interface Designer trace is a facility that produces information about accessing your database from the Database Interface Designer and then logs this information to a file.
Chapter 3 - Database/query Files Database Interface Designer Trace Files Viewing Database Interface Designer Trace Files After trace has been enabled and a Database Interface Designer trace file has been generated, the contents of this file can be viewed in a trace window in the Database Interface Designer. To view trace files 1 In the Navigator, highlight the .mdq file for which you want to view the results of a trace. 2 From the View menu, select Trace File.
Chapter 3 - Database/query Files Database Interface Designer Trace Files Field Description Find what Enter the text upon which to search. The default value is blank. Match whole word only This check box determines whether to use the value entered in the Find what field to match only whole words against in the trace file. (For example, if this was enabled and text was your find value, texts would not be a match.). The default value is disabled.
Chapter 3 - Database/query Files 4 Make other selections as desired. 5 Click Find Next. Database Interface Designer Trace Files If a match is found, the text is highlighted in the file in the trace window. Otherwise, there may be a message asking if you want to continue searching. 6 Repeat Step 5 until you have finished your search. 7 Click Cancel.
Chapter 4 - Database Type Trees Use the Database Interface Designer or mtsmaker to generate a type tree to be used in a map. You can also define your own type tree using the Type Designer. However, for the database adapters to correctly process your database data, your type tree must conform to the format described in this chapter. For information about using mtsmaker to generate type trees, refer to the Resource Adapters Reference Guide.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure As shown in these examples, the type trees generated from tables, views, queries, and stored procedures are very similar, while the type tree generated for message queues differs slightly. In these example type trees: ♦ The type tree named ActvProj.mtt was created for a query that referenced a Microsoft Access table. ♦ The type tree named Stores.mtt was created for a table in a Microsoft SQL Server database.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure Each type tree generated by the Database Interface Designer or mtsmaker for a database table or query (or a query that calls a stored procedure) will have the following standard characteristics: ♦ The root of the generated type tree is named Data. ♦ The generated type tree contains a category whose name corresponds to any one of the following: ! the name of the table in the database ! the name of the query in the .
Chapter 4 - Database Type Trees Table and Query Type Tree Structure Invalid Character(s) Conversion to Type Name punctuation (other than ~ # % \ ‘ ? _) example: ^^Total^Amount replaced with pound sign (#) becomes: ##Total#Amount digit (as first character of column name) example: 1st-Game-of-2 prefixed with pound sign (#) becomes: #1st_Game_of_2 Characteristics of the DBSelect or DBTable Type In each type tree generated by the Database Interface Designer or mtsmaker, an implied group type represents
Chapter 4 - Database Type Trees Table and Query Type Tree Structure ♦ DBTable The type tree defines a table used as a data target for a map. The DBSelect or DBTable type is an implied group with a series of row objects as its only component. Components and Format of the Row Type As its name implies, the Row group represents a row (or tuple). The Row type is a group with an explicit format.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure Delimited Row Group Format If you select Delimited as the value in the Row group format list in either the Generate Type Tree from Tables window or the Generate Type Tree from Query window, the Properties window in the generated type tree indicates that the Group Subclass > Format is an explicit group defined as having a delimited syntax.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure Fixed Row Group Format If you select Fixed as the value in the Row group format list in either the Generate Type Tree from Tables dialog box or the Generate Type Tree from Query dialog box, the Properties window in the generated type tree indicates that the Row type is an explicit group defined as having a fixed syntax. As a result of this selection, you can specify the Terminator. (The default value is None.) An example follows.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure Defining the Column Type(s) Each named column in the database has a corresponding item type under the Column type in the type tree with the same name. For example, if the DEMO.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure Note The minimum size of item types for columns in a delimited Row is 0 unless otherwise specified by the database driver. The minimum size of item types for columns in a fixed Row equals the maximum size. Binary Column Types If a database column corresponds to a binary type, your type tree contains additional types so that the binary data can be correctly interpreted.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure A category called SizedGroup is added to the tree (as indicated in the example that follows). This category has an item subtype called Sizeof and group subtypes with the same names as the item types for each binary column.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure ♦ The formats of those group types defined as subtypes of the SizedGroup category are defined in the same way as the format for the Row group type. If the type tree has a delimited Row group format, the group types for the binary columns are also defined as delimited groups with the pipe character (|) as an infix delimiter. In this example, the Sizeof component is defined as being required in the component list.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure An Example Using Binary Data The following data represents the contents of a database table named Parts.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure The following example is the type tree for the Parts table as generated by the Database Interface Designer using a delimited Row group format.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure The Unit_Price group item appears in the following example: The Picture column is defined as a group item consisting of: ♦ a Sizeof item that specifies the size of the second component ♦ an item representing the contents of the Picture column that is defined as a binary byte stream of unlimited length Database Interface Designer Reference Guide 85
Chapter 4 - Database Type Trees Table and Query Type Tree Structure An example of this definition follows. The following example illustrates the appearance of the data before insertion into the table. Note In this example, the floating-point values are italicized because their actual values would result in unprintable characters. Gasket|103401|4|1.99|1718|<1718 bytes of binary data> Widget|103402|4|3.49|0 0|1569|<1569 bytes of binary data> |103403|4| Gadget Pro|103404|4|9.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure ♦ In the third record (which has no Part_Name), the value in the Unit_Price column is zero. The Sizeof value for the Unit_Price column remains at four and the Unit_Price column contains the value zero. ♦ In the last record (DooDad), there is no value in the Unit_Price column nor in the Picture column. Therefore, the Sizeof value for both columns is zero. Delimiters (|) are included as placeholders for the Unit_Price column.
Chapter 4 - Database Type Trees Table and Query Type Tree Structure Specifying Column Aliases When you want to control the name of the generated item to represent a particular column or expression in a query, use the AS keyword to specify an alias for that column. For example, you could specify the query as: select min(salary) as min_salary, max(salary) as max_salary from employee which would result in a type tree containing a Row with two columns representing the result of the SQL MIN and MAX functions.
Chapter 4 - Database Type Trees Stored Procedure Type Tree Structure Stored Procedure Type Tree Structure The type trees generated by the Database Interface Designer for stored procedures used for outputs from a map are slightly different from the ones generated for tables, queries, and queries calling stored procedures (that are used as the source of data for a map).
Chapter 4 - Database Type Trees Oracle8 AQ Message Type Tree Structure The ProcedureCall group is defined in the same way as a Row type for a table or a query in the type tree. Its group format is determined by the value selected in the Row group format list in the Generate Type Tree from Tables dialog box or the Generate Type Tree from Query dialog box, respectively. The selected group format determines the terminator and release characters.
Chapter 5 - Database Sources and Targets This chapter explains how to use the Map Designer to configure a map having a database source or target. For detailed information about using the Map Designer and defining sources and targets in input and output cards, refer to the Map Designer Reference Guide. Using a Database as a Source After you have used the Database Interface Designer to define a query for a database, you can use that query as an input source.
Chapter 5 - Database Sources and Targets Using a Database as a Source To define a database as a data source Note For more information about the map settings in this procedure, refer to the Map Designer Reference Guide. This procedure will specifically address databasespecific parameters and settings. 1 In the Map Designer, when defining the settings for SourceRule, select Database as the GET > Source setting.
Chapter 5 - Database Sources and Targets Using a Database as a Source 3 For the CardName setting, enter a name for the card that describes the data object represented by this card. 4 For the TypeTree setting, select the type tree containing the group type that defines the desired query. 5 For the TypeName setting, select the group type from the type tree that defines the desired query (for example, DBSelect).
Chapter 5 - Database Sources and Targets Using a Database as a Target Using a Database as a Target After using the Database Interface Designer to define tables, views, message queues, or stored procedures for a database, use the database to specify the database adapter as the data target. To use a database table, view, message queue, or stored procedure as a target 1 Define the database using the Database Interface Designer.
Chapter 5 - Database Sources and Targets Using a Database as a Target 3 For the CardName setting, enter a name for the card. 4 For the TypeTree setting, select the type tree file containing the group type that defines the content of the desired output. 5 For the TypeName setting, select the group type from the type tree that defines the desired output (for example, DBSelect). 6 Specify all Backup settings as desired.
Chapter 5 - Database Sources and Targets 7 Database Connections and Transactions For the PUT > Target setting, select Database from the list. The settings displayed in the Output Card dialog box change to display the database adapter settings for a target. Note For database adapter-specific information about target settings, refer to the adapter-specific reference guide. 8 Specify the values as desired in the adapter settings and click OK.
Chapter 5 - Database Sources and Targets Database Connections and Transactions Transactional Control Scope is one of the settings that may be specified for a source (GET > Source > Transaction > Scope) or target (PUT > Target > Transaction > Scope) in a map. For information about Scope settings, refer to the Map Designer Reference Guide. Note There is one restriction for Scope. If the value of the SourceRule > FetchAs setting in an input card is set to Burst, the Scope setting is always Map.
Chapter 5 - Database Sources and Targets Database Connections and Transactions When connection sharing occurs between cards and maps within a map, the connection is described as active as long as there are one or more cards or rules that access a database and that have yet to be committed or rolled back. (For example, an active transaction exists.) When there are no such cards or rules, the connection is inactive, yet still alive.
Chapter 5 - Database Sources and Targets Database Connections and Transactions Connection Rules Existing connections are reused whenever possible while adhering to certain connection rules. The rules are as follows: ♦ An inactive connection is reused if the database type, connection string, and user ID of the new card or rule match those of the previous card or rule that had established the connection.
Chapter 5 - Database Sources and Targets Database Connections and Transactions Connection Example A map has four database input cards. Assume the connections specified (datasource, userID, and so on) are the same for each: In this example, ♦ A connection will be made and a transaction started for Card 1. ♦ Because Card 2 has Transaction > Scope set to Card, this initial connection cannot be shared. Therefore, another connection will be made for this card.
Chapter 6 - Updating Database Tables This chapter discusses how you can designate specific columns in your database to be updated with data produced by a mapping operation. Using Key and Update Columns The data produced by a mapping operation can be inserted as new rows in a database table or can update only specific columns in a table as designated.
Chapter 6 - Updating Database Tables Defining Key and Update Columns Defining Key and Update Columns To update a table from a map, use the Database Interface Designer to specify the columns in a table to be used as key columns and to specifically specify the columns that will be updated. To designate columns as key columns or columns to update 1 In the Navigator, select the database containing the table for which you want to set update keys. 2 From the Database menu, choose Set Update Keys.
Chapter 6 - Updating Database Tables 5 Specifying Update Mode To designate a column as a column to update, select it from the Columns list button associated with the Columns to update list. and click the The selected column moves to the Columns to update list. 6 To specify all non-key columns as Columns to update, click the associated with the Columns to update list. button All of the columns in the Columns list move to the Columns to update list.
Chapter 6 - Updating Database Tables Specifying Update Mode Using an Adapter Command at Execution Time The update mode for a table can be specified at execution time by using the command line to pass the -UPDATE database adapter command. Specify OFF or ONLY to control the updating operation. For more information about using database-specific adapter commands, refer to the Resource Adapters Reference Guide.
Chapter 6 - Updating Database Tables Example Using Update Key Columns Example Using Update Key Columns When you specify a column as a key column, the value in that column is used to determine whether a row produced by a mapping operation should be inserted as a new row into the table or should be used to update existing row(s) in a table.
Chapter 6 - Updating Database Tables Example Using Update Key Columns A map is then created to update the Parts table. When the map runs, the following two rows are produced: PART_NUMBER Name Price 2 5” bolt 0.84 4 Flange 1.
Chapter 6 - Updating Database Tables Example Using Update Columns After the table is updated, it looks like this: Please note the following: ♦ If more than one column is designated as a key column, the values generated by a map must match the values in each designated key column for the row to be updated.
Chapter 6 - Updating Database Tables Example Using Update Columns In the following example, a table (PersonalInfo) has the following key columns and update columns defined in the Database Interface Designer: This table contains the following data: When the map runs (Update is enabled for this output card), the following data is produced: ID FirstName LastName PhoneNumber SSN 10 Karl March (847) 555-1234 999-88-7766 14 Janice Armstrong (203) 555-9898 Database Interface Designer Reference Gu
Chapter 6 - Updating Database Tables Example Using Update Columns The contents of the table after successful map execution will be: When this map runs, because -UPDATE is enabled for the output, the database adapter will first go through the results of the map and update all rows in the table matching the key columns in the output produced.
Chapter 7 - Database Functions This chapter discusses two functions (DBLOOKUP and DBQUERY) that are designed exclusively for use with databases. These functions can be used in component rules in the Type Designer and map rules in the Map Designer when creating a map to be used with a database.
Chapter 7 - Database Functions Using DBLOOKUP and DBQUERY DBLOOKUP and DBQUERY execute an SQL statement within a rule against the database. The SQL statement can be any statement permitted by your database management system or database-specific driver. There are two syntax methods that can be used to specify the arguments for these two functions: Syntax1 and Syntax2. Syntax1 - Using a Static .
Chapter 7 - Database Functions Using DBLOOKUP and DBQUERY The following table describes these arguments in more detail. Argument Description SQL_statement SQL statement as a text string. It can be any valid SQL statement permitted by your database management system and supported by your database-specific driver. In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.
Chapter 7 - Database Functions Using DBLOOKUP and DBQUERY Using this syntax and the Syntax2 formatting issues, DBLOOKUP and DBQUERY use the following arguments. Argument Explanation SQL_statement single_text_expression adapter_commands Either -MDQ mdq_file –DBNAME db_name or -DBTYPE database_type [database_adapter_commands] The following table describes these arguments in more detail. Argument Description SQL_statement SQL statement as a text string.
Chapter 7 - Database Functions Argument -DBTYPE Using DBLOOKUP and DBQUERY Description This adapter command is followed by the type of database (for example, ORACLE or ODBC) followed, optionally, by database-specific adapter commands. Note This syntax does not use an .mdq file because the database-specific adapter commands provide the information required to connect to the database.
Chapter 7 - Database Functions Using DBLOOKUP and DBQUERY Also assume that this database has been defined in a file named mytest.mdq using the Database Interface Designer. The name of the database, as specified in the .mdq file, is PartsDB. Notice the difference between the returned values from the execution of the following two Syntax1-formatted functions. Function Returns PART_NAME DBLOOKUP ("SELECT PART_NAME from PARTS ¼” x 3” Bolt where PART_NUMBER =1", "mytest.
Chapter 7 - Database Functions Using DBLOOKUP and DBQUERY Also assume that this database has been defined in a file named mytest.mdq using the Database Interface Designer. The name of the database, as specified in the .mdq file, is PartsDB. Using the Syntax1 format, the following DBQUERY function: DBQUERY ("SELECT * from PARTS", "mytest.mdq", "PartsDB") returns: 1|¼" x 3" Bolt2|¼" x 4" Bolt where is a carriage return followed by a line feed.
Chapter 7 - Database Functions Using DBLOOKUP and DBQUERY Example 4 - Using WORD to Parse Multi-Column Output from DBQUERY In certain situations, you may want to use one of the database functions, rather than a database source, due to the size of a cross-reference table. However, you need the function to return the data from several different columns. For example, assume you need to create a map that processes inventory requests, one order at a time, using a messaging system.
Chapter 7 - Database Functions Using Bind Values in Database Functions of the DBLOOKUP function was called ItemData, the rule using the vendor ID column would be: =WORD ( ItemData , "|" , 2 ) The values of the other columns can be retrieved in a similar manner.
Chapter 7 - Database Functions Using Bind Values in Database Functions the DBMS considers the statement to be new and does not take advantage of caching. For example, the two following statements are distinct to a DBMS: SELECT * FROM MyTable WHERE CorrelationID=123 SELECT * FROM MyTable WHERE CorrelationID=124 Use the bind facility for DBLOOKUP and DBQUERY functions to submit such statements to the DBMS so that the statements are syntactically identical.
Chapter 7 - Database Functions Using Bind Values in Database Functions Note There is no performance benefit unless all values that change from one invocation of the statement to the next are bound. For example, if the ID value is bound and not the CorrelationID value, the statement will vary because the CorrelationID value varies.
Chapter 8 - Using Stored Procedures This chapter describes the various mechanisms for using stored procedures to access all types of parameters and return values from stored functions. You can call stored procedures when using DBQUERY and DBLOOKUP functions and when defining a query in the Database Interface Designer for a map data source. When defining a database as the target for an output card, you can also output to a stored procedure.
Chapter 8 - Using Stored Procedures Examples Using Stored Procedures The Database-independent Syntax for Calls The database-independent syntax for calling stored procedures is: CALL [?=] procedure_name [(argument_list)] Refer to the following table for a description of the arguments.
Chapter 8 - Using Stored Procedures Examples Using Stored Procedures Using the DBLOOKUP function, an example of the syntax used to call DoSalaryIncrease would be: DBLOOKUP ("call DoSalaryIncrease(" + EmpID:.:PayrollFile + ",?,?/" + Salary:.:PayrollFile + ",?)", "AdminDb.
Chapter 8 - Using Stored Procedures Using a Stored Procedure as an Input Using a Stored Procedure as an Input Stored procedures can be used in input cards by specifying the call in the Query field in the New Query dialog box of the Database Interface Designer, an example of which follows.
Chapter 8 - Using Stored Procedures Using a Stored Procedure as an Input Generated type trees for stored procedures adhere to the same format as type trees for queries and will contain a Row group, the components of which correspond to the ? placeholders in the CALL statement. Return values from stored functions will typically have the field name RETURN_VALUE unless the database returns a specific name.
Chapter 8 - Using Stored Procedures Using a Stored Procedure as an Input Next, provide the value on the command line for parm1 at execution time as follows: mercnt MyMap.
Chapter 8 - Using Stored Procedures Using a Stored Procedure as an Output To define a query using a stored procedure When you use a stored procedure to define a query, follow the same steps as when you use a SELECT or other statement: 1 Define the query in the New Query or Edit/View Query dialog box of the Database Interface Designer. 2 Generate a type tree from the query.
Chapter 8 - Using Stored Procedures Stored Procedures with Object Type Parameters There are two ways to call stored procedures in output cards: ♦ In the output card dialog box in the Map Designer, specify -PROC procedure_name in the PUT > Target > Command setting. For more information, refer to Using a Database as a Target. or ♦ When overriding an output card using database adapter commands on the command line, use the -PROC adapter command and specify the stored procedure name.
Chapter 8 - Using Stored Procedures Stored Procedures with Object Type Parameters Refer to the following example. The type 'outer' is defined by the following 'create type' statements: create type inner as object ( a_char varchar(10), b_int number(10,0)); create type outer as object ( x_inner inner, y_date date); Assume that there is a table named 'object_holder' and a stored function 'insert_object' defined to insert an object of type 'outer' and to return the number of objects in the table.
Chapter 9 - Database Triggers Data sources using database adapters can serve as input event triggers that are defined in the Database Interface Designer, enabled in the Integration Flow Designer, and executed by an Event Server. Note For information about the availability and usage of triggering for your specific database and platform, refer to the database-specific adapter reference guide.
Chapter 9 - Database Triggers Database Triggers Overview The Database Interface Designer allows two different triggering actions during event-based map execution: ♦ Table-based Triggering ♦ Row-based Triggering Installation Requirements Before you can take advantage of the database triggering functionality, either one of two database-specific installation scripts must be run by a system administrator: Note This is a one-time only operation except for a possible unexpected Event Server shutdown.
Chapter 9 - Database Triggers Database Triggers Overview Tables Created for Triggering Refer to the following table for information about the four tables that are created by executing the SQL script: Table Description Trigger_Server Used at startup, this table tracks the Event Servers that are accessing this database. The Event Servers must have unique machine name and TCP/IP address combinations. (In other words, triggering is only supported for one Event Server per machine.
Chapter 9 - Database Triggers Database Triggers Overview The following is a diagram illustrating the functionality and relationship of these tables. Maintaining Triggering Tables The current implementation of the triggering functionality should be selfmaintaining.
Chapter 9 - Database Triggers Database Triggers Overview Otherwise, the database triggers will continually remain in operation, taking up processing and space resources on the target DBMS. If the triggering installation script cannot be run, a temporary solution would be to run the following SQL statements on the target DBMS: 1 DELETE FROM Trigger_Events; 2 DELETE FROM Trigger_Registry; 3 COMMIT; Handling Truncated Tables Note For Oracle8 only.
Chapter 9 - Database Triggers Database Triggers Overview Table-based Triggering A typical usage for table-based triggering would be to set up a trigger that will execute a map to run after another map has completed processing and has inserted rows into a table. For example: Trigger Map 1 Database Map 2 SELECT Name FROM My_Table My_Table In this example, Map 2 is triggered as soon as the rows inserted into My_Table become known to another process.
Chapter 9 - Database Triggers Database Triggers Overview Issues for Both Row- and Table-Based Triggering The following list represents what is and is not supported in row- or table-based triggering: ♦ The appropriate database must be used (Oracle 8 client or above and Oracle 8 server or above [for Windows and UNIX] or Microsoft SQL Server 7.0 and above [for Windows only]). ♦ The respective SQL file (refer back to Installation Requirements) must be installed and executed by your system administrator.
Chapter 9 - Database Triggers 4 Using a Database as a Map Trigger Use the Integration Flow Designer to create a system with the map component containing the trigger specification. Enable the Input(s) > GET > Source setting in the Event Server Settings dialog box. For information about how to do this, refer to Using the Integration Flow Designer to Enable Triggers. Note The Integration Flow Designer does not perform any validation of a database source used as a trigger.
Chapter 9 - Database Triggers 3 Using a Database as a Map Trigger Determine whether you can define row-based triggering for this query as determined by the availability of the Row-based triggering check box. Note If this is a new query and the table supports it, this check box will be enabled as the default value. (For old queries, this check box will be cleared as the default value, which means that you could use the functionality by enabling it.
Chapter 9 - Database Triggers Using a Database as a Map Trigger Note Delete from cannot be used with row-based triggering. 5 To move the table name, in the text box area corresponding to the selected . table name (either Insert into, Delete from, or Update of), click The table name moves into the corresponding list on the right. 6 Select as many names as necessary to define the event(s) that must occur so that conditions can be met for the trigger specification.
Chapter 9 - Database Triggers Specifying a Combination of Different Event Classes Defining Events Use the Trigger Specification dialog box to define a trigger specification for an individual query that will be stored with the query in the .mdq file. As you add and remove the table names, the lists on the right display the tables for which an Insert into, Delete from, or Update of event comprises the condition(s) that must be met for the trigger specification.
Chapter 9 - Database Triggers Specifying a Combination of Different Event Classes Specifying AND or OR Within the Insert into and Delete from event classes, you can define multiple insert and delete events by specifying multiple tables. Similarly, you can define multiple Update of events by specifying multiple table names. Note Using the Delete from event class disables row-based triggering. However, you can still define multiple Insert Into and Update of events using row-based triggering.
Chapter 9 - Database Triggers Specifying a Combination of Different Event Classes Format of the When Expression There are two basic formats that can be used for the When expression as specified in the Trigger Specification dialog box: ♦ clauses referencing table columns ♦ clauses using the SELECT 1 FROM format to establish conditions of execution Table Column Format The When expression in the Trigger Specification dialog box can contain any SQL expressions that are valid for the database.
Chapter 9 - Database Triggers Specifying Triggers on the Command Line Specifying Triggers on the Command Line If a trigger specification for a query has not been defined in the Database Interface Designer or if you want to override conditions that are already defined in a trigger specification associated with a query, use the command line. For more information about using the command line and specifying the Trigger adapter command (-TR), refer to the Resource Adapters Reference Guide.
Chapter 10 - Debugging and Viewing Results This chapter explains various troubleshooting tools available when you encounter problems using database objects as data sources or targets for a map or when using the Database Interface Designer to define databases and queries. Also presented are various methods for viewing data extracted from a database or loaded into a database.
Chapter 10 - Debugging and Viewing Results Database Trace Files ♦ The database trace file produced at map execution time records detailed information about the database adapter activity such as records retrieved, data source and target activity, and so on. Format of Database Trace Files Information displayed in the database trace file varies depending upon the database generating the trace file.
Chapter 10 - Debugging and Viewing Results Database Trace Files Producing the Database Trace in the Database Interface Designer Click the Trace tool or select Trace from the File menu to enable a database trace file (.dbl). If trace is enabled, a database trace file is automatically created when you generate a type tree. This file is placed in the same directory as the currently open .mdq file. The newly created database trace file is named using the full name of the .mdq file plus a .dbl extension.
Chapter 10 - Debugging and Viewing Results <1776-940>: <1776-940>: <1776-940>: <1776-940>: <1776-940>: <1776-940>: <1776-940>: <1776-940>: <1776-940>: <1776-940>: <1776-940>: Column Column Column Column Column Column Column Column Column Column Column Database Trace Files 1 (CustomerID) type is nchar(5) [DBTYPE_WSTR]. 2 (CompanyName) type is nvarchar(40) [DBTYPE_WSTR]. 3 (ContactName) type is nvarchar(30) [DBTYPE_WSTR]. 4 (ContactTitle) type is nvarchar(30) [DBTYPE_WSTR].
Chapter 10 - Debugging and Viewing Results Database Trace Files Producing the Database Trace During Map Execution There are several different ways to enable the database trace for reporting database-related information during map execution as discussed in the following sections: ♦ Using the Trace adapter command (-TRACE) ♦ Source usage: ! for a valid source ! for a source with errors ♦ Target usage: ! for a valid target ! for a target with a missing required value ! for a target using the Bad Dat
Chapter 10 - Debugging and Viewing Results Database Trace Files Database Trace for a Valid Source To produce the following database trace file example, we want to produce a database trace for input card 2 (which is a database). To do this, you must include the Trace adapter command (-TRACE) in the GET > Source > Command setting. Upon execution, database trace information is generated for input card 2; the example follows. Note Line numbers are for reference purposes only.
Chapter 10 - Debugging and Viewing Results Database Trace Files This sample database trace file (map_name.dbl) reveals important information, examples of which are described below. ♦ Lines 1-5 show information about the connection made, identifying the database type as Oracle as well as the version number of the interface library. In this example, there was no existing database connection and a new connection was successfully established.
Chapter 10 - Debugging and Viewing Results Database Trace Files Message : ORA-00942: table or view does not exist Retrieved 0 records (0 bytes). Error returned to engine: (-17) Failed to parse SQL statement Cleaning up and closing the transaction... The transaction was successfully committed. Status returned to engine: (0) Success Commit was successful. Database disconnect succeeded.
Chapter 10 - Debugging and Viewing Results Database Trace Files 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.
Chapter 10 - Debugging and Viewing Results Database Trace Files ♦ 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.
Chapter 10 - Debugging and Viewing Results Database Trace Files 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 FIRSTNAME=:a01,LASTNAME=:a02,TITLE=:a03, COMPANYNAME=:a04, ADDRESS=:a05,CITY=:a06,STATEORPROVINCE=:a07, POSTALCODE=:a08,COUNTRY=:a09, PHONENUMBER=:a10,FAXNUMBER=:a11,EMAILADDR=:a12 WHERE (MEMBERID=:a00) Error in: oexec Message : ORA-01400: cannot insert NULL into ("EVENTMGT".
Chapter 10 - Debugging and Viewing Results Database Trace Files The remaining lines in the database trace file display information about the disposition of the entire database card transaction. The transaction failed after one row was inserted. The database adapter returns an error code of -9 to the Event Server with a corresponding error message of Failed to execute the SQL statement.
Chapter 10 - Debugging and Viewing Results Database Trace Files Column 4 TITLE : Vice President-New Products Column 5 COMPANYNAME : Northwind Traders Column 6 ADDRESS : 722 Moss Bay Blvd. Column 7 CITY : Kirkland Column 8 STATEORPROVINCE : WA Column 9 POSTALCODE : 98033 Column 10 COUNTRY : USA Column 11 PHONENUMBER : (206) 555-3412 Column 12 FAXNUMBER : (206) 555-3413 Column 13 EMAILADDR : jleverling@northwind.com Column 14 MEMBERSINCE : 1999-07-09 22:03:03 Failed to insert a row (rc = -9).
Chapter 10 - Debugging and Viewing Results Database Trace Files Database Trace for a Target with -UPDATE Off The following example illustrates the database trace that can result from a common error encountered when developing maps have database targets. Database type is ODBC Status returned to engine: (0) Success No existing connection was found. Connection to datasource OracleProd has been established. Interface library version 6.0(140) Loading data for output card 1. Database adapter version 6.
Chapter 10 - Debugging and Viewing Results Database Trace Files 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.
Chapter 10 - Debugging and Viewing Results Database Trace Files Output is to a buffer. Statement execution succeeded. Retrieved 0 records (0 bytes). Size of DBLOOKUP data is 0. Warning returned to engine: (2) No data found Cleaning up and closing the transaction... . .
Chapter 10 - Debugging and Viewing Results Database Trace Files Query : SELECT FIRSTNAME, LASTNAME , EMAILADDR FROM EVENTMGT.MEMBERSHIP WHERE MEMBERID = 'D191-0002' Query size : 108 Output is to a buffer. Statement execution succeeded. The columns are of the following types: Column 1 (FIRSTNAME) type is VARCHAR(30). Column 2 (LASTNAME) type is VARCHAR(50). Column 3 (EMAILADDR) type is VARCHAR(50). Number of buffers in fetch array = 492 Writing results to a buffer. Retrieved 1 records (42 bytes).
Chapter 10 - Debugging and Viewing Results Database Trace Files Note 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.
Chapter 10 - Debugging and Viewing Results Database Trace Files 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).
Chapter 10 - Debugging and Viewing Results Viewing Database Source and Target Data 01:02:00 July 9, 2001 Failed to execute the SQL statement 01:02:00 July 9, 2001 Use this information to determine whether the query defined for input card 3 references a table or view that does not exist.
Chapter 10 - Debugging and Viewing Results Database Audit Files ♦ on the command line or in a command file. (Refer to the following example.) The rejected record(s) is/are saved in the file specified with the -BADDATA adapter command (badstuff.txt in this example).
Chapter 10 - Debugging and Viewing Results Database Audit Files specify a name or the full path for the file. For more information, refer to the Resource Adapters Reference Guide.
Chapter 10 - Debugging and Viewing Results DBMS Trace Utilities and SQL Command Tools 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.
Index factors, 98 management, 96, 97 rules, 99 sharing, 97 customization options, 16 . .dbl file, 68 creating, 146 logging errors only, 160 .log file, 144 .mdq file using with DBLOOKUP/DBQUERY, 111 .
Index E DBSelect and DBTable types, 75 debugging using native tools, 166 delimited Row type example, 84 rules, 77 I input cards calling stored procedures, 124 creating for a database, 91 K key columns defining, 102 example, 105 general rules, 107 E Edit menu, 20 commands, 20 events classes, 140 conditions to be met, 141 defining for triggers, 138, 140 modifying or deleting for triggers, 139 specifying AND or OR condition, 141 different classes, 140 tables, 137 When expression, 141 examples native call
Index P type tree structure, 89 using as input, 124 using as output, 127 syntax bind value in SQL statement, 119 variables in the Database Interface Designer, 45 system definition diagram tools, 17 P Print dialog box, 67 ProcedureCall type, 89 Q query defining triggers, 137 defining with variables, 45 generating a type tree, 66 text using a stored procedure, 126 type tree structure, 73 Query menu, 23 commands, 23 queues generating a type tree, 59, 61 T table generating a type tree, 51 type tree structur
Index U columns to update, 107 definition of, 101 examples columns to update, 108 key columns, 105 key column general rules, 107 specifying update mode, 103 update mode specifying in the Map Designer, 103 specifying on the command line, 104 using native tools, 166 type tree file, 47 type trees comparison of structures, 89 for stored procedures, 124, 127 generating from a query, 66 from a queue, 59, 61 from a stored procedure, 57 from a table, 51 from a view, 48, 52 using Database Interface Designer, 47 qu
