HP NonStop Data Transformation Engine NonStop SQL/MX Adapter Reference Guide Abstract This manual provides information about using the HP NonStop™ Data Transformation Engine HP NonStop SQL/MX adapter. Product Version NonStop Data Transformation Engine 6.7.
Document History Part Number Product Version Published 528259-002 NonStop Data Transformation Engine 6.7.
Contents About This Document Related References........................................................................................... 4 Chapter 1 - NonStop SQL/MX Adapter Overview ....................................................................................................... 5 Chapter 2 - Command Alias Chapter 3 - Database Columns and Types Item Type Properties......................................................................................... 7 Date and Time Formats.....................
About This Document This document contains information about the NonStop SQL/MXadapter. 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.
Chapter 1 - NonStop SQL/MX Adapter This chapter introduces the NonStop SQL/MX adapter. You can use the adapter with the Command Server or Mercator Tuxedo Service. Overview Use the NonStop SQL/MX adapter for HP NonStop™ ZLE (Zero Latency Enterprise) to access and manipulate data contained in databases that are NonStop SQL/MX data sources. The NonStop SQL/MX adapter is available as part of the Mercator product on the HP NonStop ZLE operating system.
Chapter 2 - Command Alias Specify the NonStop SQL/MX adapter commands on the command line using an execution command string or using the Platform API. You can also create a command file, shell script, or batch file that contains the desired adapter commands for map execution. For information about all of the options you can use with the execution commands for adapters or how to create a command file, refer to the Execution Commands Reference Guide.
Chapter 3 - Database Columns and Types The Database Interface Designer and mtsmaker with the Type Tree Maker generate type trees for queries, tables, and views in an SQL/MX-compliant RDBMS (Relational Database Management System). Item types will be created in a type tree that represent the data types of the columns of a query, table, or view. The Database Interface Designer and mtsmaker get information about columns by calling NonStop SQL/MX to describe the columns associated with a query, table, or view.
Item Type Properties Chapter 3 - Database Columns and Types NonStop SQL/MX Data Type Interpret as Item Subclass, Presentation INTEGER Character Number, Integer * LARGEINT Character Number, Integer * FLOAT (1 to 23 bits) Binary Number 4 FLOAT (24 to 54 bits) Binary Number 8 REAL Binary Number 4 DOUBLE PRECISION Binary Number 8 DECIMAL Character Number, Decimal * PIC 9(1-s)V9(s) COMP Character Number, Decimal or Integer (if scale = 0) * PIC 9(1-s)V9(s) Character Number,
Date and Time Formats Chapter 3 - Database Columns and Types Date and Time Formats There are two different categories of date and time formats for the NonStop SQL/MX adapter: ♦ default ♦ available Note If any of your tables contain a datetime column, or the columns you are selecting for a type tree are datetime, you need to use mtsmaker to generate your type trees. (The available section identifies the various SQL/MX-specific types.
Date and Time Formats Chapter 3 - Database Columns and Types The Represent date/time columns as text items check box is modal. After it has been disabled, it will remain disabled for all subsequent type tree generations, regardless of source. Therefore, you must be careful in determining this setting. Available NonStop SQL/MX Formats The NonStop SQL/MX database environment provides many more data types for DATETIME than ODBC provides (as indicated in the following table).
Date and Time Formats Chapter 3 - Database Columns and Types NonStop SQL/MX DATETIME Data Type Results in an mtsmaker-generated Type Results in an ODBC-generated Type DATETIME MONTH TO MINUTE {MM-DD}:{HH24:MM} {CCYY-MM-DD} {HH24:MM [:SS[.6-6]]} DATETIME MONTH TO SECOND {MM-DD}:{HH24:MM:SS} {CCYY-MM-DD} {HH24:MM [:SS[.6-6]]} DATETIME MONTH TO FRACTION(d) {MM-DD}:{HH24:MM[:SS [.d-d]]} {CCYY-MM-DD} {HH24:MM [:SS[.
Chapter 4 - Database Interface Designer Settings When you define a NonStop SQL/MX database in the Database Interface Designer, in addition to the common settings available for all the database adapters in the Database Definition dialog box, you need to enter the information specific to SQL/MX. Database Definition Dialog Box The NonStop SQL/MX adapter-specific settings are described in the following table.
Database Definition Dialog Box Chapter 4 - Database Interface Designer Settings Setting Description Password This is the authorization password used to connect to the database. The following is an example of the Database Definition window with SQL/MXspecific settings.
Chapter 5 - Adapter Commands This chapter describes the functions and usage of the NonStop SQL/MX adapter commands and their options. Note For information about 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 (.
Adapter Commands for a Source Chapter 5 - Adapter Commands 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 .
Adapter Commands for a Source Chapter 5 - Adapter Commands The Source in the Compiled Map is a Database With Database/Query File Without Database/Query File "'[-MDQ mdq_file -DBNAME database_name] "'-DBTYPE SQLMX [-QUERY query_name|-STMT SQL_stmt] -STMT SQL_statement [-FILE [directory]] [-FILE [directory]] [-VAR name=value…] [-VAR name=value…] [-CCARD|-CSTMT [number]] [-CCARD|-CSTMT [number]] [-AUDIT[G][+] [full_path]] [-AUDIT[G][+] [full_path]] [{-TRACE|-TRACEERR}[+] [full_path]]'" [{-TRACE|-TRACEERR}[
Adapter Commands for a Target Chapter 5 - Adapter Commands GET Function The GET function returns the data from the source adapter.
Adapter Commands for a Target Chapter 5 - Adapter Commands 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.
Adapter Commands for a Target Chapter 5 - Adapter Commands The Target in the Compiled Map is Not a Database With Database/Query File Without Database/Query File "'-MDQ mdq_file -DBNAME database_name -TABLE table_name [-CCARD|-CSTMT [number]] [-DELETE] [-UPDATE [OFF|ONLY]] [-BADDATA[+] full_path] [-AUDIT[G][+] [full_path]] [{-TRACE|-TRACEERR}[+] [full_path]]'" "'-DBTYPE SQLMX -TABLE table_name [-CCARD|-CSTMT [number]] [-DELETE] [-UPDATE [OFF|ONLY]] [-BADDATA[+] full_path] [-AUDIT[G][+] [full_path]] [{-T
Chapter 6 - 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 are some restrictions and limitations of certain functions when using the database adapters for SQL/MX. These functions are: ♦ Stored Procedures Using stored procedures to access adapter commands and return values from stored functions is not supported.
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 default, 9 functions DBLOOKUP/DBQUERY, 16 GET, 17 PUT, 19 . .log file, 21 .
