HP NonStop Data Transformation Engine NonStop SQL/MP Adapter Reference Guide Abstract This manual provides information about using the HP NonStop™ Data Transformation Engine HP NonStop SQL/MP adapter. Product Version NonStop Data Transformation Engine 6.7.
Document History Part Number Product Version Published 528260-001 NonStop Data Transformation Engine 6.7.1 June 2004 528260-002 NonStop Data Transformation Engine 6.7.
Contents About This Document Related References........................................................................................... 4 Chapter 1 - NonStop SQL/MP 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 Mercator NonStop SQL/MP 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. Related References The Mercator Online Library contains information about all products.
Chapter 1 - NonStop SQL/MP Adapter This chapter introduces the NonStop SQL/MP adapter. You can use the adapter with the Command Server or Mercator Tuxedo Service. Overview Use the NonStop SQL/MP adapter for HP NonStop ZLE (Zero Latency Enterprise) to access and manipulate data contained in databases that are NonStop SQL/MP data sources. The NonStop SQL/MP adapter is available as part of the Mercator product on the HP NonStop ZLE operating system.
Chapter 2 - Command Alias Specify the NonStop SQL/MP 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 NonStop SQL/MPcompliant Relational Database Management System (RDBMS). Item types will be created in a type tree that represents the data types of the columns of a query, table, or view.
Date and Time Formats Chapter 3 - Database Columns and Types NonStop SQL/MP Data Type Interpret as Item Subclass, Presentation Length SMALLINT Character Number, Integer * INTEGER Character Number, Integer * LARGEINT Character Number, Integer * FLOAT (1 to 22 bits) Binary Number 4 FLOAT (23 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 s
Date and Time Formats Chapter 3 - Database Columns and Types Default NonStop SQL/MP Format When using columns defined as dates and times in NonStop SQL/MP databases, the default format for both input and output is: datetime: ccyy-mm-dd:hh:mm:ss.ffffff where cc yy mm dd hh mm ss ffffff = = = = = = = = 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 fractional seconds An example is 2001-08-27:00:00:00.
Date and Time Formats Chapter 3 - Database Columns and Types Available NonStop SQL/MP Formats The NonStop SQL/MP database environment provides many more data types for DATETIME than ODBC provides (as indicated in the following table). To take advantage of this functionality, you may need to either use mtsmaker to generate your type trees (instead of the Database Interface Designer) or you can manually edit these values in the Type Designer.
Date and Time Formats Chapter 3 - Database Columns and Types NonStop SQL/MP DATETIME Data Type Results in an mtsmaker-generated Type Results in an ODBC-generated Type DATETIME MONTH TO FRACTION(d) {MM-DD}:{HH24:MM[:SS [.d-d]]} {CCYY-MM-DD} {HH24:MM [:SS[.6-6]]} DATETIME DAY {DD} {CCYY-MM-DD} DATETIME DAY TO HOUR {DD}:{HH24} {CCYY-MM-DD} {HH24:MM [:SS[.6-6]]} SECOND DATETIME DAY TO MINUTE {DD}:{HH24:MM} {CCYY-MM-DD} {HH24:MM [:SS[.
Chapter 4 - Database Interface Designer Settings When you define an NonStop SQL/MP database in the Database Interface Designer, in addition to the common settings available for all the database-specific adapters in the Database Definition dialog box, you need to enter the information specific to NonStop SQL/MP. Database Definition Dialog Box The NonStop SQL/MP 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 dialog box with NonStop SQL/MP-specific settings.
Chapter 5 - Adapter Commands This chapter describes the functions and usage of the NonStop SQL/MP adapter commands and their options. Note For information about syntax notation, refer to Getting Started. Adapter Command Summary This chapter presents 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 an .
Adapter Commands for a Source Chapter 5 - Adapter Commands Option Description SQLMP The database-specific adapter type is NonStop SQL/MP. 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 -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 one double quotation mark followed by one single quotation mark and end with one single quotation mark followed by one double quotation mark.
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 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 SQLMP -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-specific 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-specific adapters for NonStop SQL/MP. 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 using adapter commands, 6 . F .log file, 21 .
SQL/MP Adapter Reference Guide 23
