HP Neoview Transporter User Guide HP Part Number: 545787-001 Published: April 2008 Edition: Release 2.
© Copyright 2008 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.......................................................................................................11 Intended Audience................................................................................................................................11 Related Documentation........................................................................................................................11 Neoview Customer Library.....................................................
Options............................................................................................................................................28 Typeformats.....................................................................................................................................29 Dataformats.....................................................................................................................................32 Maps................................................................
Requirements...................................................................................................................................61 Parallel Processing for Load Operations..............................................................................................61 Considerations for Extract Jobs.............................................................................................................62 8 Data Processing.....................................................................
C Global Options Table...............................................................................................101 D Reserved Keywords...................................................................................................103 Transporter Reserved Keywords........................................................................................................103 glossary..........................................................................................................................
List of Tables 1-1 2-1 4-1 4-2 4-3 5-1 5-2 5-3 7-1 7-2 7-3 8-1 C-1 D-1 Transporter Client Software Components.....................................................................................16 Transporter Client Installation......................................................................................................18 Encryption Files.............................................................................................................................
List of Examples 9-1 9-2 9-3 9-4 9-5 9-6 9-7 9-8 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 Sample HP_TRANSPORTER_BASE_JOB_STATS File..................................................................68 Sample HP_TRANSPORTER_CONTROL_FILES File..................................................................69 Sample HP_TRANSPORTER_CONTROL_FILE_TEXT 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 or HP-UX® 64 for the Itanium platform.
Neoview Transporter User Guide Information about processes and commands for loading data into your Neoview platform or extracting data from it. 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 99) • “Control File Editor ” (page 97) For detailed information see: “Control File Organization and Syntax” (page 25) “Control File Options” (page 47) Client Components Table 1-1 Transporter Client Software Components Transporter Component Type Definition Java Transporter Client A Java application that has been validated on these platforms: Linux, Windows, and HP-UX for Itanium Control File Editor (CFE) A Graphical User Interface (GUI) tool that assists you in creating
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 or later • JDBC Type 4 driver • Active MQ Java Messaging Services 4.1.
Table 2-1 Transporter Client Installation Screen Action 1 Welcome Click Next to continue installation. 2 License Agreement Read and accept the Transporter license agreement. 3 Installation path Enter the path to the installation folder in which you want Transporter files to be installed, or click Browse to select from a folder list. 4 Software packs Using the check boxes, select the software packs you want installed. Packs listed without check boxes are automatically included with those you select.
Security Parameters Saved As part of Transporter client installation, the security parameters are saved in $NVTHOME/conf/security/prop.file . For more information about Transporter security, see “Security” (page 23).
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 23) Password Encryption Because Transporter accesses other systems, it transmits User IDs and passwords. Transporter uses PKCS #5V1.
NOTE: If the environment variable NVTHOME does not exist, nvtencrsrv returns an error. Table 4-2 Options Supported by the nvtencrsrv Utility Option Description -o Action to perform on the file, either add or delete an entry. Valid arguments: • add • del -r Reference name for the add or delete action. -p Plain text word to be encrypted — valid only for the add option.
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 40) Location of sources and targets for data. These global options can be overridden in this section of the control file.
it rejects the control file and reports an error. There cannot be more than 99 minor versions to a major version. There will be no incompatible changes between minor version upgrades of Transporter. However, it is possible to have incompatibility between major versions and if it occurs, control files containing the old major version are rejected by the new Transporter application. The default value for minor and patch versions is zero. Examples: A control file is based on Transporter version 1.0.
• • • • • • • • “sorted” (page 53) “startseq” (page 53) “system” (page 54) “tenacity” (page 54) “timeout” (page 54) “truncate” (page 54) “url” (page 54) “user” (page 55) Typeformats The typeformats section defines various formats in which to represent data types. typeformats { format_definition [, format_definition ]... } format-definition: formatname datatype formatstring format-definition One or more occurrences of format-definition, separated by a comma (,).
The default is decimal. • Floating Point data types — general — equivalent to printf "%f" format (fixedpoint) — exponential — equivalent to printf "%e" format The default is general. • 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).
— — — — — — space tab comma (,) semicolon (;) slash (/) 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.
“%Y” — To specify a formatstring for a day-time duration of 30.5 seconds formatted as “30.
Dataformats: Delimited Format formatname delimited [options format-options] [fields delimited by character] [records separated by character] [optionally qualified by character] field-definitions format-options ( option_name = option_value [, option_name = option_value ]... ) formatname The name you choose for this dataformat. The name must be unique within the dataformats namespace. formatname is case-insensitive and cannot exceed 64 bytes.
options format-options “crossoveryear” (page 47) record-length number Specifies the length of a data record in unit of characters. For example, each record is 256 characters: record length 256 Dataformats: Fixed Field Definitions The fixed-field-definitions list defines the offset for each data field in order. It begins with keyword fields positions followed by a parenthesized list of offsets in units of characters. Offsets are 1–based, meaning the first character of a record is at position 1.
fieldname The name you select for the field of the format. If the format is SQL table, the fieldname must be one of the column names of the associated SQL table or query. Otherwise, the fieldname must be unique within this record format. When a Neoview SQL table format is used for an extract operation, the following rules and considerations apply: • For table sources, fieldname must be one of the column names of the associated table.
Only the simple data type name should be used. For example, if a table column named FIRSTNAME is of data type varchar(8), you should specify “FIRSTNAME varchar” instead of “FIRSTNAME varchar(8)”. format formatname one of the typeformats you defined in the typeformats section. This is optional and not applicable for SQL columns. options field-options Defines options for the field.
targetformat The target dataformat as defined in “Dataformats” (page 32). For a load operation, targetformat must be an SQL table dataformat. For an extract operation, targetformat can be delimited or fixed-length dataformat. field-mapping-definition One or more occurrences of field-mapping-definition, separated by a comma (,). The last field-mapping-definition should not use the comma separator.
Table 5-3 Datatype Mappings (continued) Source Field of Type... Can be Mapped to a Target Field of Type... date any character type date timestamp time any character type time timestamp 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.
discussion of the condition option below for more detail. This option is not supported for extract operations. • system — the Neoview system generates the key • loader — the loader generator is used 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.
EMP_ID = identity loader • Increments the source field VALUE_SOURCE by 10 and assigns the resulting value to the target field VALUE_TARGET: VALUE_TARGET = expression ":VALUE_SOURCE + 10" Sources The sources section describes the location of data source and target, and their provider options. sources { source-definition [, source-definition ]...
• • • Delimited identifiers are case-sensitive. Spaces within a delimited identifier are significant except for trailing spaces. You can use reserved words as delimited identifiers. See the HP Neoview SQL Reference Manual for details on SQL identifiers. For an extract operation, the sql option can be used to specify a SELECT query instead of a table name. The sql option cannot be used for a load operation.
Options for pipe sources: • “endseq” (page 48) • “forcestaging” (page 49) • “multipart” (page 50) • “retries” (page 53) • “sorted” (page 53) • “startseq” (page 53) • “tenacity” (page 54) • “timeout” (page 54) Options for odbc and jdbc sources: • “datasource” (page 47) • “noaudit” (page 51) • “parallelstreams” (page 52) • “password” (page 52) • “retries” (page 53) • “system” (page 54) • “tenacity” (page 54) • “url” (page 54) (for jdbc sources only) • “user” (page 55) Options for jms sources: • “password” (pa
The Java client accepts control files containing multiple jobs. jobs { job-definition [{job-definition ] ... } job-definition: jobname { load | extract }[options job-options] { job-entry-list } job-options: ( option_name = option_value [, option_name = option_value ) job-entry-list: job-entry [, job-entry ]... job-entry: source sourcename target { targetname | ( targetname-list )} map mapname [ options job-entry-options ] targetname-list: targetname [, targetname ]...
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.
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. Be aware that you cannot have duplicate definitions between included files and the main control file, otherwise the control file is rejected.
6 Control File Options baddatafile baddatafile = filename The default value is current-working-directory/badrecord.dat. For a load operation, this option specifies where to put source records that fail internal processing before being written to the database. For example, a record that only contains 6 fields while 8 fields are expected. Load records written baddatafile appear in the same format as the source file.
Specifies the data source name for ODBC and JDBC connections to the Neoview system. datasource_name must be a server-side data source defined on the Neoview system. Example: datasource = "LOAD01" Specifies a data source named LOAD01. deleteonerror deleteonerror = "true" | "false" The default is “false”. For an extract operation: when this option is set to “false”, Transporter does not delete target files or intermediate files when the operation terminates due to an error.
This is an optional feature to allow data synchronization between Transporter and your applications. The start and end sequence records are considered “substitute” records. Whenever one of these records is written to the pipe (either by Transporter during an extract operation, or by the application during a load operation), it is terminated by an appropriate record separator. sequence of characters should not contain a record separator.
This option can be specified at the global level or for individual file or pipe sources that are part of a load operation. When the “parallel” (page 52) option is set to "true" for a given job, all sources within the job must have the same forcestaging setting. For a load source where the “operation” (page 52) option for the job is “insert”, the forcestaging and “noaudit” (page 51) options cannot both be "true".
.NNNN Where NNNN is a zero-padded four digit integer. For example, if the specified prefix is “extractData” and the value of parallelstreams is 4, the following targets are used: extractData.0001 extractData.0002 extractData.0003 extractData.0004 This option is ignored for load operations. 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.
an empty string. For all other types, a field containing only white space is rejected and the record is written to the “baddatafile” (page 47) file. For an extract operation: • • • This option specifies the character string that should be written to targets to represent NULL values. For delimited data formats, if nullstring is not specified, Transporter does not write any data characters to the target field to represent a NULL value.
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.
• • JDBC connections to the Neoview system JMS sources There is no default value. The Transporter Java client uses this URL for its internal database connections. A JDBC connection URL must be of the form of:jdbc:hpt4jdbc://hostname:port/. See the Neoview JDBC Type 4 Driver Programmer’s Reference for complete details on JDBC connection URLs. Example: a JDBC connection URL for a Neoview system name neo0101.domain.com: jdbc:hpt4jdbc://neo0101.domain.
7 Load and Extract Operations The Transporter Client performs load and extract operations based on the content of a control file.
Starting the Client on Linux 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.txt • The control file is in the current directory: jtrans.sh {-run | -recover} ./MyControlFile.txt • The control file is in a directory you specify — in this case, my/trans/dir: jtrans.
• • • A source for the data, named in the “Sources” (page 40) section of the control file A target for the data, also named in the “Sources” (page 40) section of the control file The mapping information you provide in the “Maps” (page 36) section of the control file (for example, the table columns in which to put the data) NOTE: error. If the control file encounters a duplicate source/target pair, Transporter exits with an Several jobs make up a Transporter control file.
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 50) for details. You provide a base filename for the targets and Transporter creates the specified number of files or pipes with an incremental extension, (myfile.0001, myfile.0002, myfile.
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 40) 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 36) for details about mappings.
8 Data Processing This chapter addresses these topics: “Data For Load Operations” (page 63) “Processing Data for Load Operations” (page 63) “Delimited Data” (page 64) “Considerations for Character Data” (page 66) 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 51) 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) Quote Processing For Load Operations For a load operation involving a delimited dataformat: • There is no default quoting character. Quote processing is disabled if this option is not specified.
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 67) • “Job Recovery” (page 67) • “Job Statistics on the Neoview Platform ” (page 68) 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.
Considerations for Job Recovery Transporter jobs can be recovered, with these considerations: • • • Only load jobs with file sources can be recovered. Job recovery is not supported for named pipes or JMS Trickle Feed. Job recovery is not supported for extract operations. Only jobs started using the -recover command line option can be recovered. The -recover Command Line Option To recover a job, use the —recover option on the command line, and provide the name of the original control file for the load.
• Static — Extract source: Neoview SQL Text or Multi-Partition Set Table Extract — Single Stream or Parallel Extract Streams — Target information: name of file, named pipe or data source — Indication if recovery operation was initiated by user — Load target table name — Text of DML query for load • Dynamic — Run status: scheduled, in progress, complete or error — Exit status or error reason: Transporter exit error message — Internal recovery attempt triggered due to tenacity setting: for example, 2 of 3
Example 9-4 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-6 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 74) • Extract from a Neoview SQL source Example A-2 (page 77) • Extract from a table source to a named pipe Example A-3 (page 79) • Load fixed-width data Example A-4 (page 81) • Include file for defining data formats Example A-5 (page 83) • Load from a file with an Include file Example A-6 (page 84) • Load and extract Example A-7 (page 85) • Multiple data formats and maps Example A-8 (page 8
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 ) } 76 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 ) } 78 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 ) } 80 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 ) } 82 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 ) } 91
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 ) } 96 Control File Examples
B Control File Editor and Control File Generator This chapter addresses the following topics: • The “Control File Editor ” (page 97) • The “Control File Generator” (page 99) Control File Editor The Control File Editor is a Java-based GUI editor for creating, editing, and managing the Transporter control file.
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. Button Function Default Sets default values for a section. Clear Comment Clears all entered comments. Clear Clears all entered inputs. Enter Submits all entered inputs to the Entered Input Table.
Click Insert —> Include or click the Ctrl+I. Then select the control file that you want to insert into the current control file. Control File Generator The Java-based control file generator tool can be used to create sample control files quickly using either a standard template that you have or based on tables already created in the Neoview database schemas.
Error Message and Behavior The control file generator can encounter connection, SQL, and file I/O type errors. If a connection or a file I/O error is generated, the files may not be written. If a table is “skipped”, it is due to an SQL error only. Packaging The control file generator is packaged as part of the Transporter package. It is located in the Transporter client directory structure in $NVTHOME/utils.
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.
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 104 expression parallelstreams url extract password usenullstring faileddatafile pipe username fields positions version file postlaunch Reserved Keywords
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 B baddatafile, description of, 47 byte integral data type, 45 byteint integral data type, 45 C char[acter] character data type, 45 client on Linux, 58 on windows, 58 starting, 57 stopping, 58 client software, 16 column IDENTITY, 22 update with a constant, 22 update with an expression, 22 update with NULL, 22 comments control file section, 44 commitsize, description of, 47 control file comments section, 44 creating, 25 dataformats section, 32 jobs section, 42 maps section, 36 options section, 28 organ
date/time data type, 45 datetime date/time data type, 45 decimal integral data type, 45 deleteonerror, description of, 48 delimited data, 63, 64, 65 discards, description of, 48 Documents, related information, 11 double floating point data type, 45 integral data type, 45 load job, 58 source, 59 steps to perform, 57 target, 59 transaction modes, 60 with delimited data, 65 log files, 67 longint integral data type, 45 E maps control file section, 36 multipart, description of, 50 editor, control file, 97 en
startseq, description of, 53 streams parallel, 21 supported data types, 45 system, description of, 54 T table staging, 21 tenacity, description of, 54 time date/time data type, 45 timeout, description of, 54 timestamp date/time data type, 45 transaction modes for load jobs, 60 trickle feed, 22, 59, 60 control file specification, 61 requirements, 61 truncate, description of, 54 typeformats control file section, 29 U update column with a constant, 22, 38 with an expression, 22 with NULL, 22, 38 url, descrip
