HP Neoview Transporter User Guide HP Part Number: 514030-001 Published: April 2009 Edition: Release 2.
© Copyright 2009 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Table of Contents About This Document.........................................................................................................9 Intended Audience.................................................................................................................................9 New and Changed Information in This Edition.....................................................................................9 Related Documentation.....................................................................
5 Control File Organization and Syntax.......................................................................25 Creating a Control File..........................................................................................................................25 Control File Limits................................................................................................................................25 Control File Organization and Syntax..................................................................
7 Load and Extract Operations ....................................................................................61 Steps for Performing Load and Extract Operations.............................................................................61 Starting the Transporter Client ............................................................................................................61 Starting the Client on UNIX-type Systems....................................................................................
Restrictions.....................................................................................................................................107 Packaging.......................................................................................................................................107 Using the Control File Editor.........................................................................................................107 Creating a New Control File..................................................
List of Tables 1-1 2-1 2-2 4-1 4-2 4-3 5-1 5-2 5-3 7-1 7-2 7-3 8-1 9-1 C-1 D-1 E-1 Transporter Client Software Components.....................................................................................16 GUI Environment Transporter Client Installation........................................................................18 Console Environment Transporter Client Installation..................................................................19 Encryption Files............................................
List of Examples 5-1 9-1 9-2 9-3 9-4 9-5 9-6 9-7 9-8 9-9 A-1 A-2 A-3 A-4 A-5 A-6 A-7 A-8 A-9 A-10 A-11 A-12 A-13 A-14 A-15 A-16 8 Supported Scalar Expressions ......................................................................................................42 Examples of Custom Log File Locations.......................................................................................74 Sample HP_TRANSPORTER_BASE_JOB_STATS File..................................................................
About This Document This manual describes how to install and manage HP Neoview Transporter. This product provides processes and commands for loading data into your Neoview platform or extracting data from it. You can install and execute the Transporter client from a system running Microsoft® Windows, Red Hat Enterprise Linux 5, AIX® 5L version 5.3, SunOS 5.10 ( Solaris 10), or HP-UX® 64 for the Itanium platform.
Neoview Customer Library The manuals in the Neoview customer library are listed here for your convenience.
Neoview Workload Management Information about using Neoview Workload Management Services (WMS) to Services Guide manage workload and resources on a Neoview data warehousing platform.
Italic Letters Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name Computer Type Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: myfile.sh Bold Text Bold text in an example indicates user input typed at the terminal. For example: ENTER RUN CODE ?123 CODE RECEIVED: 123.
ATTRIBUTE[S] attribute [, attribute]... {, sql-expression}... An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: expression-n… Punctuation Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. For example: DAY (datetime-expression) @script-file Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown.
1 Overview The HP Neoview Transporter client provides a high-speed load and extract facility to move data to (load) and from (extract) the HP Neoview platform.
• “Control File Generator” (page 109) • “Control File Editor ” (page 107) For detailed information see: “Control File Organization and Syntax” (page 25) “Control File Options” (page 49) Client Components Table 1-1 Transporter Client Software Components Transporter Component Type Definition Transporter Java Client A Java application that has been validated on these platforms: Linux, Windows, HP-UX for Itanium, Solaris 10, and AIX.
2 Installation and Configuration This chapter addresses the following topics: • • “Installation Requirements” (page 17) “Installing Transporter” (page 17) Installation Requirements Before Transporter is installed, be sure that the following are valid and present in the environment. • A Java platform certified compliant with Java Platform Specification, Standard Edition 5 (Update 12) or later. (HP recommends Version 6.0 Update 6 or later): — HPUX Java version "1.5.0.
• • • export JAVA_HOME=/usr/java/jdk1.5.0_09 export PATH=/usr/java/jdk1.5.0_09/bin:$PATH export JREHOME=/usr/java/jdk1.5.0_09/jre Installing the Client Using the GUI You install the Transporter Java client using a GUI that installs and configures the Transporter environment. • For Windows, double-click the nvt_java_client_v.v.v.jar package on your desktop. • From a Linux or HP-UX shell, use this command: java -jar nvt_java_client_v.v.v.jar v.v.
Installing the Client Using CONSOLE Mode If you do not have a GUI environment, or if you prefer a console-like installation, you can install Transporter in console mode. • From a Linux or HP-UX shell, use this command: java -jar nvt_java_client_v.v.v.jar -console v.v.v indicates the major version, minor version, and update number. For example, 2.4.0 corresponds to the initial release of version 2.4.
These subdirectories are created in the NVTHOME directory: Directory Contents / License file, Transporter executable scripts, and Readme /lib Libraries, static and dynamic and Java class library /inc Header files /conf Transporter Control File Generator template and framework configuration (properties) files /mesg Message catalogs or locale specific properties files /log Application message log directory /extensions Client extensions to Transporter — operation extension /samples Sample Tran
3 Functions and Features In addition to standard load and extract, Transporter provides support for these additional features and functions: • “Staging Tables” (page 21) • “Parallel Streams” (page 21) • “Parallel Query Extract” (page 21) • “Prehashing” (page 22) • “JMS Trickle Feed For Data Loads” (page 22) • “Transporter Job Statistics on the Neoview Platform ” (page 22) • “Reflexive Update” (page 22) • “Identity Columns” (page 22) Staging Tables A staging table is a Neoview SQL table used as an intermedi
Prehashing Transporter streamlines the process for load operations with hash-partitioned tables by performing a pre-hashing operation on the client. For more information about hash-partitioned tables, see the HP Neoview SQL Reference Manual JMS Trickle Feed For Data Loads Trickle feed is a technique that allows for continuous updates of the database as the data in the source system changes. Trickle Feed differs in this way from the use of flat files and named pipes, which are considered "batch" techniques.
4 Security Because load and extract jobs connect to databases and access password-protected resources, it is necessary for Transporter to keep track of user IDs and passwords. This chapter addresses the following topics: • “Password Encryption” (page 23) • “Encryption Files ” (page 23) • “The Encryption Utility — nvtencrsrv” (page 24) Password Encryption Because Transporter accesses other systems, it transmits User IDs and passwords. Transporter uses PKCS #5V1.
%NVTHOME%\utils\nvtencsrv -o install The Encryption Utility — nvtencrsrv nvtencrsrv is a utility that installs the security directory and related files, adds encrypted words to the password encryption file, and deletes encrypted words when they are no longer needed. Only the administrator of the client platform can use this utility. Invoke nvtencrsrv with the suffix corresponding to the platform you are using. For example, if you used the jtrans.
5 Control File Organization and Syntax The control file is a text file that tells Transporter how you want your data moved from source to target for either loading or extracting purposes.
Table 5-1 Control File Organization Control File Section Options “Version” (page 27) N.A. “Options” (page 28) Global options for all Transporter jobs controlled by this file.
Table 5-1 Control File Organization (continued) Control File Section Options “Sources” (page 43) Location of sources and targets for data. These global options can be overridden in this section of the control file.
versionnumber is the major version, followed by optional minor and patch versions. All version number components are separated by a period (.). Only the major version is required. The components of the version number correspond to the major, minor, and patch versions of the Transporter product, respectively. If Transporter sees an incompatible version number in the control file, it rejects the control file and reports an error. There cannot be more than 99 minor versions to a major version.
• • • • • • • • • • • • • • • • • • “noaudit” (page 56) “nullstring” (page 56) “operation” (page 57) “parallel” (page 57) “parallelstreams” (page 57) “password” (page 57) “retries” (page 58) “rowsetsize” (page 58) “sorted” (page 58) “startseq” (page 58) “system” (page 59) “taskrowsetsize” (page 59) “teamsize” (page 59) “tenacity” (page 59) “timeout” (page 59) “truncate” (page 60) “url” (page 60) “user” (page 60) Typeformats The typeformats section defines various formats in which to represent data types.
formatstring Defines the pattern for this field. For a load operation, formatstring tells Transporter how to interpret source file values. For an extract operation, formatstring tells Transporter how to write target file values. • Character data types Specify “default”. It is the only format supported. • Integral data types — decimal — values are interpreted as decimal values with an optional leading sign character — octal — values are interpreted as octal values with an optional leading sign character.
• Date/Time data types Use a combination of the following patterns to a specify date or time format: — %b The abbreviation for the month name in the current locale (for example, “Feb”, in the POSIX locale). — %C The first two digits of the year (19 or 20). — %d The two-digit day of the month as a number (01 to 31). — %e The day of the month in a two-digit, right-justified, blank-filled field (1 to 31). — %H The hour in the 24-hour clock representation (00 to 23).
— backslash (\\) Period (.) is the only supported separator between integral and fractional seconds.
The following are defaults and examples: — Date values ◦ Default: %C%y-%m-%d ◦ Example: The input characters “2007–10–05” can be processed without a format definition and is interpreted as October 5, 2007. — Time values ◦ Default: %H:%M:%s ◦ Example: The input characters “13:32:00” can be processed without a format definition and is interpreted as 1:32 p.m. — Timestamp values ◦ Default: %C%y-%m-%d %H:%M:%S ◦ Example: The input characters “2007–10–05 13:32:00.
• Interval data types Interval data types require a formatstring. There is no default for interval fields. These field specifiers are allowed: — For year-month ranges: ◦ %Y — year ◦ %M — month — For day-time ranges: ◦ %D — day ◦ %H — hour ◦ %M — minutes ◦ %S — seconds (can include fractional seconds) The values for specifiers must be positive integers. Specifiers must appear in order from day to seconds. For example, %M (minutes) cannot precede %H (hours).
Table 5-2 Typeformats: format_definition Date/Time Format String Examples (continued) Description Example "%m/%d/%y %H:%M" Specify a formatstring for a timestamp field with mixed delimiters, in the format of "10/1/2007 12:45" Two typeformats of datatype date, with formats of "20070131" and "2007-01-31", respectively. typeformats { formatname date "%C%y%m%d", formatname date1 "%C%y-%m-%d" } Dataformats The dataformats section describes the data format of sources and targets.
optionally qualified by {' | "} Specifies a quoting character for each data field. For information, see “Quote Processing For Load Operations” (page 71) field-definitions See Dataformats: Field Definitions for Source and Target (page 37). Dataformats: Fixed Length Format In a fixed-length record file, the width of each field in the record is consistent across all records. Records are separated by one or more system EOL characters.
Dataformats: Neoview SQL Table Format formatname sql fields fields-definition formatname The name you create for this delimited dataformat. The name must be unique among existing dataformats. Dataformats: Field Definitions for Source and Target field-definitions: ( field-definition [, field-definition ]... ) field-definition: fieldname datatype [format formatname] [options field-options] field-options ( option_name = option_value [, option_name = option_value ]...
a matching name is found. Subsequent columns with the matching name are not accessible within this dataformat. There are two workarounds for this problem: — Derived columns of the query, for example, the results of Neoview SQL expressions and aggregate functions, by default are identified by the name “(EXPR)”. If multiple derived columns are produced by a single query, use an AS clause in the query text to associate a name with each derived column.
Maps The maps section describes how one data format is mapped to another when data is moved from source to target. maps { map-definition [, map-definition ]... } map-definition: mapname from sourceformat to targetformat field-mappings: ( field-mapping-definition [, field-mapping-definition ) ]...
fieldname_target, fieldname_source fieldname_target and fieldname_source must refer to names defined in the source dataformat and target dataformat, respectively. For more information about dataformats, refer to “Dataformats” (page 35). For a simple mapping, where the source field and target field are the same and there are no specific map functions required (for example, expressions and identity columns), field-source-ref can be omitted.
Table 5-3 Datatype Mappings (continued) Source Field of Type... Can be Mapped to a Target Field of Type... timestamp any character type date time timestamp interval interval For interval mappings, both fieldname_source and fieldname_target must have an associated typeformat. Both typeformats must either be year-month, or day-time ranges. [add | subtract] are used for reflexive update and apply only to update and upsert operations.
Example: EMP_ID = identity loader Asks the loader to generate a key for target field EMP_ID. scalar-expression A simple expression composed of a fieldname_source, constant, and SQL functions connected by simple arithmetic operators. This expression must be syntactically valid for fitting into a corresponding SQL insert, update, or upsert statement. The maximum length of a scalar-expression is 256 characters. This option is not supported for extract operations and is not allowed on primary key columns.
The condition and update options are ignored for insert operations. The condition and update options cannot both be “true” for a field mapping unless it is a reflexive update field mapping.
sourcename pipe pipename [options source-options] Specifies a named pipe as the data source/target. pipename is the name of the pipe. sourcename { jdbc } { sql sqltext | table tablename } [options source-options] Specifies an SQL table or query accessed through a JDBC data connection to the Neoview system. jdbc, sql, and table are keywords. The Transporter client does not support ODBC sources. Neoview SQL views and synonyms are not supported for JDBC table extracts.
table1 jdbc table "SQL"."MyTable" • Specify a source names “query1” to access an SQL join query: query1 jdbc sql "select * from sch.dept where number_of_employees > 10" sourcename jms jmsname [options source-options] This specifies a Java Messaging Service (JMS) source. jmsname is a JMS name. source-options one or more occurrences of source-options, separated by a comma (,). The last source-options should not use the comma separator.
Jobs The jobs section specifies a list of Transporter jobs. A job is a collection of job entries. Jobs are processed serially, one at a time. This section starts with the keyword jobs followed by a list of job definitions surrounded by curly braces. The Java client accepts control files containing multiple jobs. jobs { job-definition [{job-definition ] ...
• • • • “operation” (page 57) “parallel” (page 57) “rowsetsize” (page 58) “truncate” (page 60) job-entry-list one or more occurrences of job-entry, separated by a comma (,). The last job-entry should not use the comma separator. This specifies a list of job entries for the job. Each entry specifies its own source, target, and a mapping between them. Each job entry within a job must specify a unique (sourcename, targetname) pair.
/* Your comment line 1 Your Comment line 2 ... */ Include You can include one or more control file fragments from other control files. A control file fragment is a control file without a version number. You can define common sections in a control file fragment and include them from other control files. This avoids having duplicate definitions and makes it easier to manage your control files.
6 Control File Options autotran autotran = "true" | "false" The default is “false”. For a load operation: when this option is set to “false”, Transporter executes each statement in a global transaction. For a load operation: when this option is set to “true”, Transporter uses an independent transaction for each insert. NOTE: If a job fails with autotran set to true, it cannot be recovered. If before-triggers exist on the target table, “enabletriggers” (page 51) must be set to false (the default).
Applies only to date values in source records for a load operation. This option is ignored for extract operations. When a date value is to be interpreted according to a data format specified in the control file and the data format uses a two-digit year instead of a four-digit year, the crossoveryear specifies the year the century changes from 20 back to 19. number must be an integer in the range from zero to 99.
NOTE: These values are advisory only. While this number can be considered a trigger for shutting down a job, Transporter continues to process data already in progress to completion. For this reason, there may be additional discard records beyond the number specified. • For a job where the “parallel” (page 57) option is “true”: When the number of records written to the baddatafile file for the job reaches number, Transporter stops processing the job.
TIP: The Transporter NVTHOME/utils directory contains these programs that you can compile and execute to display character set encodings: • CharsetsSupported.java, which will display all the character set encodings supported by your Java installation. • DefaultEncoding.java, which will display the default encoding used by Java on your client system.
◦ ◦ ◦ ◦ ◦ ◦ ◦ ◦ ◦ ◦ ◦ ◦ ◦ ◦ ◦ ◦ — — ' (apostrophe) ( (open parenthesis) ) (close parenthesis) * (asterisk) + (plus sign) , (comma) - (hyphen) . (period) / (slash mark) The numbers 0 – 9 : (colon) ; (semi-colon) < (left angle-bracket) > (right angle-bracket) = (equal sign) ? (question mark) This property does not apply when loading to or extracting from UCS2 columns.
NOTE: These values are advisory only. While this number can be considered a trigger for shutting down a job, Transporter continues to process data already in progress to completion. For this reason, there may be additional error records beyond the number specified. • For a job where the “parallel” (page 57) option is “true”: When the number of records written to the “faileddatafile” (page 54) file for the job reaches number, Transporter stops processing the job.
• • • • Each target table has its own staging table. The names of staging tables are generated by Transporter and written to the Transporter log. Source rows are first written to staging tables. When all source rows for a given target table have been written to the staging table, Transporter populates the target table. Jobs using staging tables cannot be recovered if the failure occurs during the staging table to target table row transfer. multipart multipart = "true" | "false" The default is “false”.
noaudit noaudit = "true" | "false" The default is false. For a load operation, this option specifies whether Transporter should use non-transactional database operations for improved performance. If the “operation” (page 57) option is not “insert”, the noaudit setting is ignored and noaudit is implicitly set to false. If parallel = true for a job, all targets in that job must have the same setting for noaudit. This option is ignored for extract operations.
operation operation = "insert" | "update" | "upsert" | "delete" The default is “insert”.
password specifies the password to use when connecting to a JDBC, ODBC, or JMS data source. • • • password specifies a plain-text password. ${referencename} refers to a plain-text password in the plain property file. Transporter automatically substitutes the corresponding password when it processes the control file. $E{referencename} refers to an encrypted password in the encrypted property file.
For a load operation when a pipe is read, Transporter discards any records read until the startseq record is seen. Transporter stops reading records once the endseq record has been seen. For an extract operation, Transporter writes the startseq record into the pipe before the first data record. After all records have been written, the endseq record is written. The endseq record is written even if errors are encountered. system system = "unqualified_Neo_systemname" This option is required.
number_of_seconds can be zero or a positive integer. truncate truncate = "true" | "false" The default is “false” (do not delete data from the target). This option specifies whether Transporter should truncate target tables (for a load operation) or target data files (for an extract operation) before job processing begins. true means Transporter will delete all data from the target table or file before job processing begins.
7 Load and Extract Operations The Transporter Client performs load and extract operations based on the content of a control file.
resubmitting it, and you want Transporter to perform recovery on the job. For more information about -recover, refer to “ The -recover Command Line Option” (page 74). Starting the Client on UNIX-type Systems Use this shell command to start the Transporter Client. You specify the control file depending on where the file resides. If the control file is not found, Transporter exits with an error. • The control file is in NVTHOME/conf: jtrans.sh {-run | -recover} MyControlFile.
The Transporter Job The units that make up a control file are called jobs. A job is a load or extract operation consisting of several job entries.
• • Set “parallelstreams” (page 57) = n Set “multipart” (page 55) = true CAUTION: If multipart = false, and parallelstreams is enabled, Transporter still moves data off the Neoview platform in multiple streams. However, all the streams merge into one file or named pipe. Ordering is not maintained if the files are merged for an ORDER BY query. See “multipart” (page 55) for details.
Requirements These are the requirements to use the Trickle Feed feature: • • ActiveMQ (JMS provider), which is included with the Java client package A separate (and dedicated) instance of the Transporter Java client A JMS trickle feed load is similar to loads from flat files or named pipes, except for the following differences: • Workflow When the Java client parses the control file and determines that the source is JMS, a trickle feed process known as a JMS Consumer is launched to consume data from the
• • • • The set of targets (that you have set in the “Sources” (page 43) section) must be the same for all sources in the job. If multiple job entries specify the same target, either all mapnames specified by the job entries must be identical, or they must use the same targetformat, the same fieldmappings, and a sourceformat with identical field names and field order. See “Maps” (page 39) for details about mappings.
• • • • Updates to primary key fields are not supported. The condition map entry option defaults to true for all primary key columns. The update map entry option defaults to true for all non-primary key columns. The “enabletriggers” (page 51) option is not supported for upsert operations.
8 Data Processing This chapter addresses these topics: “Data For Load Operations” (page 69) “Processing Data for Load Operations” (page 69) “Delimited Data” (page 70) “Considerations for Character Data” (page 72) Data For Load Operations This section addresses how Transporter processes data for a load operation, and how it treats delimited and fixed-width data.
Fixed—Width Data for Load Operations Transporter addresses and processes a fixed—width input field as follows, in this order: 1. If “nullstring” (page 56) is specified, determine whether the input field matches the nullstring value, ignoring leading and trailing white space. If it matches, the input is considered a NULL value. 2. If the target data type is not a character type, trim leading and trailing white space characters. 3.
— — — For Windows platforms: CR/LF (carriage return/line-feed) For Unix-like systems: LF (line feed) For the Macintosh family: CR (carriage return) If multiple record separators are specified, then each one will match exactly one newline or carriage return (not a system End Of Line, EOL). Only if the record separator is a single system EOL will Transporter match either newline Unix style, carriage-return newline (DOS style) or carriage return (Macintosh style).
— To qualify by double-quotes (""): optionally qualified by " — To qualify by single-quotes (""): optionally qualified by ' Considerations for Character Data • For Load Operations: — Character fields can include any characters valid for the character set of the target data type. — If an input field has fewer characters than the target column for target data type CHAR, values written to the target table are padded with trailing spaces. This does not apply to VARCHAR.
9 Troubleshooting This section covers job recovery and solutions to problems you might encounter. • “Logs” (page 73) • “Job Recovery” (page 74) • “Job Statistics on the Neoview Platform ” (page 75) Logs If errors occur during a data movement operation, Transporter logs appropriate messages to log files on the client, describing the errors encountered. Important messages are also logged to the console and EMS log on the Neoview platform.
Example 9-1 Examples of Custom Log File Locations Use this command to send log output here —DNVT.instance-log-dir=test /test/log/java/DBTransporter.log -DNVT.instance-log-dir=/home/test /home/test/log/java/DBTransporter.log Note that the Unix®-type platform shell translates log/java/DBTransporter.log to ./log/java/DBTransporter.log. Logging to the Console INFO messages are sent to standard out. WARN, ERROR and FATAL messages are sent to standard error.
CAUTION: • You must use the original control file to recover load jobs. If the control file has changed, Transporter reports an error and exits. • The original and unmodified data files must be available to Transporter during recovery. • Do not attempt to recover a job that is still running. • Do not attempt to recover a job that is already in the recovery process. Job Statistics on the Neoview Platform Job statistics on the Neoview platform are used by Transporter to facilitate job recovery.
— — Time for next retry operation if applicable End time of the job entry Control File Metadata Tables You can retrieve control file related data from these metadata tables on the Neoview platform: • HP_TRANSPORTER.CONTROL_FILES — list of control files including the host name, absolute path, and modification time. Use this list to locate a particular control file.
Example 9-5 Identify All Job Entries that Successfully Completed Within the Last Seven Days SELECT B.FILE_HOST, B.FILE_ABS_PATH, B.FILE_MODTIME, A.JOB_NAME, A.SOURCE_NAME, A.TARGET_NAME, A.START_TIME, A.END_TIME FROM HP_TRANSPORTER.BASE_JOB_STATS A, HP_TRANSPORTER.CONTROL_FILES B WHERE A.FILE_ID = B.FILE_ID AND A.END_TIME IS NOT NULL AND DATEDIFF(DAY, A.END_TIME, CURRENT_TIMESTAMP) < 7 ORDER BY B.FILE_HOST, B.FILE_ABS_PATH, B.FILE_MODTIME, A.JOB_NAME, A.SOURCE_NAME, A.TARGET_NAME, A.
Example 9-7 Identify All Jobs Executed From Host abc Using the Latest Version of Control File /home/control files/ControlFile.txt SELECT DISTINCT A.JOB_NAME FROM HP_TRANSPORTER.BASE_JOB_STATS A, HP_TRANSPORTER.CONTROL_FILES B WHERE A.FILE_ID = B.FILE_ID AND B.FILE_HOST = 'abc' AND B.FILE_ABS_PATH = '/home/control_files/ControlFile.txt' AND B.FILE_VERSION = (SELECT MAX(FILE_VERSION) FROM HP_TRANSPORTER.CONTROL_FILES WHERE FILE_HOST = B.FILE_HOST AND FILE_ABS_PATH = B.
A Control File Examples Control File Examples Following are control file examples for: • Data types Example A-1 (page 80) • Extract from a Neoview SQL source Example A-2 (page 83) • Extract from a table source to a named pipe Example A-3 (page 85) • Load fixed-width data Example A-4 (page 87) • Include file for defining data formats Example A-5 (page 89) • Load from a file with an Include file Example A-6 (page 90) • Load and extract Example A-7 (page 91) • Multiple data formats and maps Example A-8 (page 9
Example A-1 Control File: Datatypes /* All Data Types */ version 1; options { errors = 1000, discards = 500, rowsetsize = 1000 } typeformats { # comment: show examples date_frmt time_frmt timestamp_frmt intv_frmt_00 intv_frmt_01 intv_frmt_02 intv_frmt_03 intv_frmt_04 intv_frmt_05 intv_frmt_06 intv_frmt_07 intv_frmt_08 intv_frmt_09 intv_frmt_10 intv_frmt_11 intv_frmt_12 of multiple date, time and interval formats date '%C%y-%m-%d' time '%H:%M:%s' datetime '%C%y-%m-%d %H:%M:%s' interval '%Y' interval '%Y-%M
intv10 interval format intv11 interval format intv12 interval format flot1 float, flot2 float intv_frmt_10, intv_frmt_11, intv_frmt_12, ) # comment: data format for the target SQL table on the Neoview platform */ dataformat2 sql fields ( smin1 smallint, smin2 smallint, inte1 integer, inte2 integer, lint1 largeint, nume1 numeric, nume2 numeric, deci1 decimal, char1 char, vchr1 varchar, date1 date, time1 time, time2 time, tims1 timestamp, tims2 timestamp, intv00 interval, intv02 interval, intv03 interval,
tims1 tims2 intv00 intv02 intv03 intv05 intv07 intv08 intv09 intv10 intv12 intv13 intv14 intv16 intv17 flot1 flot2 = = = = = = = = = = = = = = = = = tims1, tims2, intv00, intv02, intv03, intv05, intv07, intv08, intv09, intv10, intv12, intv13, intv14, intv16, intv17, flot1, flot2 ) } jobs { load01 load options ( parallel = "false" ) ( source src1 target tgt01 map map01 ) } 82 Control File Examples
Example A-2 Control File: Extract From Neoview SQL Source /*----------------------------------------*/ /* Simple Extract From Neoview SQL Source To File */ /*----------------------------------------*/ version 1.
LAST_NAME ADDRESS ZIP PHONE INFO1 INFO2 = = = = = = LAST_NAME, ADDRESS, ZIP, PHONE, INFO1, INFO2 ) } jobs { extract1 extract options ( parallel = "true" ) ( source src1 target tgt1 map map1 ) } 84 Control File Examples
Example A-3 Control File: Extract From Table Source to Named Pipe /*------------------------------------------------*/ /* Simple Extract From Table Source To Named Pipe */ /*------------------------------------------------*/ version 1; options { errors = 1000, rowsetsize = 5000, discards = 100 } dataformats { dataformat1 delimited records separated by fields delimited by <,> optionally qualified by <"> ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2
{ extract1 extract options ( parallel = "false" ) ( source src1 target tgt1 map map1 ) } 86 Control File Examples
Example A-4 Control File: Load Fixed Width Data /* Load Fixed Width Data */ version 1; options { errors = 1000, discards = 100, rowsetsize = 5000 } dataformats { # fixed width data source file dataformat1 fixed record length 443 fields positions ( 1, 13, 37, 165, 169, 179, 187, 315 ) ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char ) dataformat2 sql ( FIRST_NAME LAST_NAME ADDRESS ZIP PHONE SSN INFO1 INFO2 ) fields char, char, char, int, char, large
LAST_NAME ADDRESS ZIP PHONE SSN INFO1 INFO2 = = = = = = = LAST_NAME, ADDRESS, ZIP, PHONE, SSN, INFO1, INFO2 ) } jobs { load1 load options ( parallel = "false" ) ( source src1 target tgt1 map map1 ) } 88 Control File Examples
Example A-5 Control File: Include Defining Dataformats You can use an Include file such as this to define data formats for many control files. See Example A-6, in which this file, ./CF/include_part_2.cf has been included to define the data formats.
Example A-6 Control File: Include — Load From File to Table /* Simple Load From File To Table Using INCLUDE To Include Control File Fragment (File 1/2) */ version 1.0; # comment: control file fragment include_part_2.cf defines the data formats %include "./CF/include_part_2.cf" options { errors discards rowsetsize } = = = 1000, 100, 5000 sources { src1 file "./data_files/sn_nvt.dat" tgt1 jdbc table ( system user password datasource url ) NEO.nvt.
Example A-7 Control File: Load and Extract /* Simple Load From File To Table & Extract From Neoview SQL Source To File */ version 1.
PHONE SSN INFO1 INFO2 = = = = PHONE, SSN, INFO1, INFO2 ) map2 from dataformat2 to dataformat3 ( FIRST_NAME = FIRST_NAME, LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP, PHONE = PHONE, SSN = SSN, INFO1 = INFO1, INFO2 = INFO2 ) } # comment: define 2 separate jobs - one for load, one for extract # comment: note that load or extract is a job attribute, and cannot be combined into a single job jobs { load1 load options ( parallel = "false" ) ( source src1 target tgt1 map map1 ) extract1 extract options (
Example A-8 Control File: Multiple Dataformats and Maps /*-----------------------------*/ /* Multiple Dataformats & Maps */ /*-----------------------------*/ version 1.
( system user password url datasource = = = = = "asl0101", "user", "transport", "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/", "Admin_Load_DataSource" ) src2 file "./data_files/sn_nvt_sml.dat" tgt2 jdbc table NEO.nvt.sn_nvt_sml options ( system = "asl0101", user = "user", password = "transport", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.
Example A-9 Control File: Reflexive Update /*-------------------------*/ /* Simple Reflexive Update */ /*-------------------------*/ version 1.0; options { errors discards rowsetsize } = 1000, = 100, = 100 dataformats { dataformat1 delimited records separated by fields delimited by <,> optionally qualified by <"> ( C1 int, C2 int, C3 char ) dataformat2 sql fields ( C1 int, C2 int, C3 char ) } sources { src1 file "./data_files/sn_nvt_refu.dat" tgt1 jdbc table NEO.nvt.
Example A-10 Control File: Update with Constant, NULL, or Expression /*--------------------------------*/ /* Constants, NULLs & Expressions */ /*--------------------------------*/ version 1; options { errors = 1000, discards = 100, rowsetsize = 5000 } dataformats { dataformat1 delimited records separated by fields delimited by <,> optionally qualified by <"> ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char ) dataformat2 sql ( FIRST_NAME LAST_NAM
load1 load options ( parallel= "false" ) ( source src1 target tgt1 map map1 ) } 97
Example A-11 Control File: Identity column This example of defines an IDENTITY column. CUSTOMER_NUMBER can be any numeric data type (SMALLINT, INTEGER, LARGEINT) column.
Example A-12 Control File: Usage of NULLSTRING /*--------------------------------*/ /* Usage of NULLSTRING */ /*--------------------------------*/ version 1.
Example A-13 Control File: NOAUDIT mode /*---------------------------------------------------------------------------------------*/ /* A Single Load Job Using NOAUDIT Mode, A Single Source File, And A Single Target Table */ /*---------------------------------------------------------------------------------------*/ version 1; options { errors discards rowsetsize sorted noaudit } = = = = = 1000, 100, 5000, "true", "true" dataformats { dataformat1 delimited records separated by fields delimited by <,>
Example A-14 Control File: Forcestaging Load with Multiple Job Entries in a Single Job /*-------------------------------------------------------------------------------------*/ /* A Single Load Job Using Staging Tables And Multiple Job Entries Running In Parallel */ /*-------------------------------------------------------------------------------------*/ version 1; options { errors discards rowsetsize forcestaging sorted } = = = = = 1000, 100, 5000, "true", "true" dataformats { dataformat1 delimited reco
source src2 target tgt1 map map1 source src3 target tgt1 map map1 ) } 102 Control File Examples
Example A-15 Control File: Load From File to Table Using Subset of Source Columns *------------------------------------------------------------------------*/ /* Simple Load From File To Table With Only Subset Of Source Columns Used */ /*------------------------------------------------------------------------*/ /* DDL to be used to create target table CREATE TABLE NVT.
ADR_tgt ZP_tgt PH_tgt SOCIAL_tgt IN1_tgt = = = = = ADDRESS_src, ZIP_src, PHONE_src, SSN_src, INFO1_src ) } jobs { load1 load options ( parallel = "false" ) ( source src1 target tgt1 map map1 ) } 104 Control File Examples
Example A-16 Control File: Load File to Table Without 1 to 1 Mapping /*-----------------------------------------------------*/ /* Simple Load From File To Table With Non 1-1 Mapping */ /*-----------------------------------------------------*/ /* DDL to be used to create target table: CREATE TABLE NVT.
password datasource url = = = "dbt", "Admin_Load_DataSource", "jdbc:hpt4jdbc://
B Control File Editor and Control File Generator This chapter addresses the following topics: • The “Control File Editor ” (page 107) • The “Control File Generator” (page 109) Control File Editor The Control File Editor is a Java-based GUI editor for creating, editing, and managing the Transporter control file. This tool can be used either on the loader platform that hosts the Transporter client component, or on your local workstation or PC to create, edit, and syntactically validate the control file.
If a control file is not syntactically correct, it opens in text mode only. Fix all syntax errors before viewing the file in GUI mode. See “Checking the Syntax of a Control File” (page 108). Adding Content to a Control File Click the appropriate tab to select the section of the control file you want to create or change. An Input Pane and Entered Input Table appear for each section in the file. Input Pane: the left pane where you create the control file.
Switching Views in the Control File Editor You can work in two views in the Control File Editor: • GUI view (default view) The GUI view assists you in creating, editing, and viewing your control file. Select one of eight tabs to create the eight components of a control file. • Text view Text view displays plain editable text where you can create, edit, and view your control file. Including Another Control File To include another control file: Click Insert —> Include or click the Ctrl+I.
—table specifies the table name or names to be used in generating a control file. If specified without either –schema or –cat, fully-qualify the table name or names. Delimited table names must start and end with a double-quote (“). Names containing inner double quotes must be escaped . If the name contains backslash before double-quote the backslash must be escaped. Therefore: • Table name “np*”jap”” becomes “np*\”jap\””. • Table name “tabl\e”\”name” becomes “tabl\e”\\\”name”.
Restrictions for Delimited Table Names • • • For a list of tables, a delimited table name cannot end with a comma (,). The schema part of a fully-qualified table name cannot contain a comma. The Control File Generator does not differentiate between lowercase names with and without double quotes. For example, the CFG treats “tablename” and tablename identically. Error Message and Behavior The control file generator can encounter connection, SQL, and file I/O type errors.
C Global Options Table Global Transporter options that are specified in the Options section of the control file can be overridden at other levels or sections of the file. This table shows the level at which the global setting can be overridden.
Table C-1 Global Options Table (continued) This Option 114 Can be overridden at these levels “url” (page 60) X X “user” (page 60) X X Global Options Table
D Reserved Keywords Transporter Reserved Keywords All control file options and section identifiers are reserved in a control file. All keywords are case-insensitive. While some of the keywords are not explicitly disallowed, use of the keywords listed is discouraged because their use can hamper upgrades to future releases of the Transporter product.
Table D-1 Reserved Keywords List (continued) Reserved Keywords 116 expression parallelstreams url extract password usenullstring faileddatafile pipe username fields positions version file postlaunch Reserved Keywords
E Transporter Exit Codes Table E-1 Transporter Exit Codes Code Number Error 0 Success 1 General failure. 2 NVTHOME environment variable is not defined or is invalid. 3 JAVA_HOME environment variable is not set for the JNI Bridge. 4 The control file passed doesn't exist or can't be accessed. 5 Invalid parameter specified on command line. 12 The Transporter Release directory layout is invalid or missing objects. 20 Error during control file parsing.
Glossary control file Input to Transporter. The control file specifies how you want to move data from source to target. Control File Generator A standalone tool that helps you create a control file(s) for a single table or all tables in a schema. Control File GUI A standalone GUI editor tool that creates, edits, and syntactically validates a Control File. data extractor The feature that allows data movement from the Neoview platform to a file, named pipe, or other data source.
Index A autotran, description of, 49 B baddatafile, description of, 49 byte integral data type, 48 byteint integral data type, 48 C char[acter] character data type, 48 client on Linux, 62 on windows, 62 starting, 61 stopping, 62 client software, 16 column IDENTITY, 22 update with a constant, 22 update with an expression, 22 update with NULL, 22 comments control file section, 47 commitsize, description of, 49 control file comments section, 47 creating, 25 dataformats section, 35 jobs section, 46 maps secti
delimited format, 35 fixed field definitions , 36 fixed length format, 36 SQL table format, 37 dataformats control file section, 35 datasource, description of, 50 date date/time data type, 48 datetime date/time data type, 48 decimal integral data type, 48 deleteonerror, description of, 50 delimited data, 69, 70, 71 discards, description of, 50 Documents, related information, 9 double floating point data type, 48 E editor, control file, 107 enabletriggers, description of, 51 encoding, description of, 51 enc
S saving control file packaging, 108 security, 23 of passwords, 23 smallint integral data type, 48 sorted, description of, 58 sources control file section, 43 staging table, 21 forcestaging option, 21 startseq, description of, 58 streams parallel, 21 supported data types, 48 system, description of, 59 T table staging, 21 taskrowsetsize, description of, 59 teamsize, description of, 59 tenacity, description of, 59 time date/time data type, 48 timeout, description of, 59 timestamp date/time data type, 48 tran
