HP NonStop Data Transformation Engine ODBC Adapter Reference Guide Abstract This manual provides information about using the HP NonStop™ Data Transformation Engine (NonStop DTE) ODBC adapter. Product Version NonStop Data Transformation Engine 6.7.
Document History Part Number Product Version Published 528267-001 NonStop Data Transformation Engine 6.7.1 June 2004 528267-002 NonStop Data Transformation Engine 6.7.
Contents About This Document Related References........................................................................................... 4 Chapter 1 - ODBC Adapter Overview ....................................................................................................... 6 System Requirements ........................................................................................ 6 Chapter 2 - Database Columns and Types Item Type Properties...........................................................
About This Document This document contains detailed information about using the ODBC Adapter. For general information regarding using any of the Mercator Resource adapters, see the Resource Adapters Reference Guide. 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.
About This Document Related References Related Reference Description Integration Flow Designer Reference Guide Using the Integration Flow Designer as a Design Studio companion and graphical facility to manage collections of related maps. Also graphically organizing these maps, based upon your requirements, into logical collections called systems.
Chapter 1 - ODBC Adapter This chapter introduces the Open DataBase Connectivity (ODBC) adapter. You can use the adapter with a Command Server, an Event Server, the Platform API, or with a map in a map rule. Overview Use the database adapters for ODBC to access and manipulate data contained in databases that are ODBC data sources. You can also install database adapters on additional systems for remote database connectivity.
Chapter 2 - Database Columns and Types The Database Interface Designer and mtsmaker with the Type Tree Maker generate type trees for queries, tables, views, and stored procedures in an ODBCcompliant RDBMS (Relational Database Management System). Item types are created in a type tree that represents the data types of the columns of a query, table, view, or stored procedure.
Chapter 2 - Database Columns and Types Date and Time Formats ODBC Data Type Interpret as Item Subclass, Presentation SQL_DOUBLE Binary Number, Float 8 SQL_LONGVARCHAR Character Text * SQL_BIT Character Number, Integer * SQL_TINYINT Character Number, Integer 4 SQL_BIGINT Character Number, Integer 20 SQL_BINARY Binary Text * SQL_VARBINARY Binary Text * SQL_LONGVARBINARY Binary Text * SQL_DATE Character Date & Time * SQL_TIME Character Date & Time * SQL_TIMESTAMP C
Chapter 2 - Database Columns and Types Date and Time Formats date: ccyy-mm-dd time: hh:mm:ss timestamp: ccyy-mm-dd hh:mm:ss[.fff…] Note If the group format is fixed, the field is padded to 26 with trailing spaces. where cc yy mm dd hh mm ss .fff… = = = = = = = = a two-digit century a two-digit year a two-digit month a two-digit day a two-digit hour a two-digit minute a two-digit second optional fractional seconds An example is 2001-08-27 00:00:00 which specifies August 27, 2001.
Chapter 3 - Database Interface Designer Settings When you define an ODBC database in the Database Interface Designer, in addition to the common settings available for all of the database adapters in the Database Definition dialog box, you need to enter the information specific to ODBC.
Chapter 3 - Database Interface Designer Settings Stored Procedures Native Call Syntax The following is an example of the Database Definition dialog box with the ODBC-specific settings. Stored Procedures Native Call Syntax A stored procedure can be accessed from within a map by specifying the native call syntax in the following: ♦ a query that is specified in an input card ♦ the first argument in a DBQUERY or DBLOOKUP function This argument does not need to be a literal.
Chapter 4 - ODBC Adapter Commands This chapter describes the functions and usage of the ODBC adapter commands and their options. Note For information about command syntax notation, refer to Getting Started. Adapter Command Summary The following is a summary of the adapter commands that can be used to specify data sources and targets. The applicability of many of the commands depends upon whether you are specifying a source or target, whether a database/query file (.
Chapter 4 - ODBC Adapter Commands Adapter Command Summary Database Adapter Type (-DBTYPE) Use the Database Adapter Type adapter command (-DBTYPE) to specify the database adapter type. -DBTYPE ODBC Option Description ODBC The database adapter type is ODBC. Note This command must be specified if the original card is not a database and no database is specified using the Database/Query adapter command (-MDQ) and the Database Name adapter command (-DBNAME).
Chapter 4 - ODBC Adapter Commands Adapter Commands for a Source Data Source (-SOURCE) Use the Data Source adapter command (-SOURCE) to specify the ODBC data source. -SOURCE datasource Option Description datasource Specify the ODBC data source. Adapter Commands for a Source This summary shows the syntax of the adapter commands that can be used when defining a data source using an .mdq file or without using an .
Chapter 4 - ODBC Adapter Commands Adapter Commands for a Source -ID Execution Command Use the Input Source Override - Database execution command (-ID) to designate a database as the source or you can override one or more of the adapter command settings or database definitions in a RUN function or on the command line. Note The following adapter commands are shown using a single quotation mark, which is the Windows syntax.
Chapter 4 - ODBC Adapter Commands Adapter Commands for a Target DBLOOKUP or DBQUERY Functions The DBLOOKUP and DBQUERY functions can be used in component rules in the Type Designer and map rules in the Map Designer when creating a map that can be used with a database.
Chapter 4 - ODBC Adapter Commands Adapter Commands for a Target PUT > Target > Command Setting Use the Map Designer or the Integration Flow Designer to specify Database as the value for the PUT > Target setting and enter the adapter commands as desired for the Command setting.
Chapter 4 - ODBC Adapter Commands With Database/Query File Adapter Commands for a Target Without Database/Query File [-AUDIT[G][+] [full_path]] [{-TRACE|-TRACEERR}[+] [full_path]]' [-BADDATA[+] full_path] [-AUDIT[G][+] [full_path]] [{-TRACE|-TRACEERR}[+] [full_path]]' The Target in the Compiled Map is Not a Database With Database/Query File Without Database/Query File '-MDQ mdq_file -DBNAME database_name -PROC procedure_name|-TABLE table_name [-USER username] [-PASSWORD password] [-CCARD|-CSTMT [num
Chapter 5 - Overriding Column Attributes When using the Database Interface Designer to generate a type tree for a query, table, view, or stored procedure, you may want to specify how columns are to be interpreted, rather than relying upon the interpretation of a particular database driver. These column overrides are saved in the .mdq file.
Chapter 5 - Overriding Column Attributes 3 Defining Column Attribute Overrides From the Database menu, select either Generate Tree From > Table or Generate Tree From > Procedure, depending upon the one you are generating. Either the Generate Type Tree from Tables or Generate Type Tree from Procedures dialog box appears, depending upon your selection. An example of the Generate Type Tree from Tables dialog box follows.
Chapter 5 - Overriding Column Attributes Defining Column Attribute Overrides 8 Accept the default values for the Group options fields (Delimiter, Terminator, and Release) or change as desired. 9 To specify how columns are to be interpreted, enable the Override column definitions check box as opposed to relying on the interpretation of the database driver. 10 Click Generate. The Column Datatype Specification dialog box appears, an example of which follows.
Chapter 5 - Overriding Column Attributes Defining Column Attribute Overrides 15 When finished, click OK. To define column definition overrides for a query 1 In the Database Interface Designer, open the .mdq file. 2 In the Navigator, select the query for which you want to generate a type tree. 3 From the Queries menu, choose Generate Tree. The Generate Type Tree from Query dialog box appears, an example of which follows.
Chapter 5 - Overriding Column Attributes Defining Column Attribute Overrides 7 Accept the default values for the Group options fields (Delimiter, Terminator, and Release) or change as desired. 8 To specify how columns are to be interpreted, enable the Override column definitions check box as opposed to relying on the interpretation of the database driver. 9 Click OK. The Column Datatype Specification dialog box appears, an example of which follows.
Chapter 6 - Binding Values in DBLOOKUP/DBQUERY When using a DBLOOKUP or DBQUERY function, use the Bind facility to submit similarly constructed SQL statements to the database server so that the statements are syntactically identical. By binding a value to a placeholder in the SQL statement, the actual syntax of the statement can be made static, which may improve performance. For more information about using bind values in database functions, refer to the Database Interface Designer Reference Guide.
Chapter 6 - Binding Values in DBLOOKUP/DBQUERY Specifying the Data Type The following table lists the data type indicators and the relationship between each data type indicator and the ODBC data types. Data Type Indicator ODBC data type T or TEXT SQL_CHAR N or NUM or NUMBER SQL_NUMERIC D or DATE SQL_DATETIME Note For information about item formats and interpretation, refer to Item Type Properties.
Chapter 7 - Configuring DSN for Event Server Startup Note This information also applies to establishing a Windows-based data source name for the Informix adapter. For information about how to configure a DSN for Unix, refer to your Unix documentation.
Chapter 7 - Configuring DSN for Event Server Startup Selecting the Startup Method However, if your ODBC data sources are defined as user data sources and are listed in the User Data Sources list on the User DSN tab of the ODBC Data Source Administrator applet, you must change the Startup options for the Event Server so that the logon is specified for the user account.
Chapter 7 - Configuring DSN for Event Server Startup 3 Selecting the Startup Method From the Services applet, select Mercator Event Server and click the Startup button. The Service dialog box appears, an example of which follows. 4 Enable This Account and click which the DSN was created. (Browse) to select the user account under 5 Enter values for the selected user account in the Password and Confirm Password fields.
Chapter 7 - Configuring DSN for Event Server Startup 5 Selecting the Startup Method Click the Log On tab. The Log On tabbed page appears, an example of which follows.
Chapter 7 - Configuring DSN for Event Server Startup Selecting the Startup Method 6 Enable This account and click which the DSN was created. to select the user account under 7 Enter values for the selected user account in the Password and Confirm password fields. The Event Server will use these values to log on to the system and access the user data sources. 8 Click OK.
Chapter 8 - Restrictions and Limitations The Database Interface Designer and database adapters offer options and functions for accessing and manipulating data contained within a database. However, there is one restriction for this adapter: database triggers are not supported. You cannot use a data source as an input event trigger for the Event Server.
Appendix A - Return Codes and Error Messages Return codes and messages are returned when the particular activity completes. Return codes and messages may also be recorded as specified in the audit logs, trace files, execution summary files, etc. For information about error codes and messages returned by database-specific adapters, see Appendix A - Return Codes and Error Messages > Database-specific Adapter Messages in the Resource Adapters Reference Guide.
Index . F .log file, 32 .
Index S defining attribute overrides for, 19 troubleshooting, 32 type trees generating from a view, 20, 21 S -SOURCE adapter command, 14 stored procedures using native call, 11 syntax for binding, 24 system requirements, 6 V views generating a type tree, 20 T table ODBC Adapter Reference Guide 34
