Hp Neoview Release 2.4 Software Manual do Utilizador

HP Neoview Transporter User GuideHP Part Number: 514030-001Published: April 2009Edition: Release 2.4

Neoview Customer LibraryThe manuals in the Neoview customer library are listed here for your convenience.• AdministrationInformation for database admi

Example A-13 Control File: NOAUDIT mode/*---------------------------------------------------------------------------------------*//* A Single Load Job

Example A-14 Control File: Forcestaging Load with Multiple Job Entries in a Single Job/*--------------------------------------------------------------

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*--------------------------------------------------------------------

ADR_tgt = ADDRESS_src, ZP_tgt = ZIP_src, PH_tgt = PHONE_src, SOCIAL_tgt

Example A-16 Control File: Load File to Table Without 1 to 1 Mapping/*-----------------------------------------------------*//* Simple Load From File

password = "dbt", datasource = "Admin_Load_DataSource", url

B Control File Editor and Control File GeneratorThis chapter addresses the following topics:• The “Control File Editor ” (page 107)• The “Control File

If a control file is not syntactically correct, it opens in text mode only. Fix all syntax errors beforeviewing the file in GUI mode. See “Checking th

Switching Views in the Control File EditorYou can work in two views in the Control File Editor:• GUI view (default view)The GUI view assists you in cr

Information about using Neoview Workload Management Services (WMS) tomanage workload and resources on a Neoview data warehousing platform.Neoview Work

—tablespecifies the table name or names to be used in generating a control file. If specified withouteither –schema or –cat, fully-qualify the table n

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-qualifie

112

C Global Options TableGlobal Transporter options that are specified in the Options section of the control file can beoverridden at other levels or sec

Table C-1 Global Options Table (continued)Can be overridden at these levelsThis OptionXX“url” (page 60)XX“user” (page 60)114 Global Options Table

D Reserved KeywordsTransporter Reserved KeywordsAll control file options and section identifiers are reserved in a control file.All keywords are case-

Table D-1 Reserved Keywords List (continued)Reserved Keywordsurlparallelstreamsexpressionusenullstringpasswordextractusernamepipefaileddatafileversion

E Transporter Exit CodesTable E-1 Transporter Exit CodesErrorCode NumberSuccess0General failure.1NVTHOME environment variable is not defined or is inv

118

Glossarycontrol file Input to Transporter. The control file specifies how you want to move data from source totarget.Control FileGeneratorA standalone

Italic LettersItalic letters, regardless of font, indicate variable items that you supply. Items not enclosedin brackets are required. For example:fil

120

IndexAautotran, description of, 49Bbaddatafile, description of, 49byteintegral data type, 48byteintintegral data type, 48Cchar[acter]character data ty

delimited format, 35fixed field definitions , 36fixed length format, 36SQL table format, 37dataformats control file section, 35datasource, description

Ssaving control filepackaging, 108security, 23of passwords, 23smallintintegral data type, 48sorted, description of, 58sources control file section, 43

ATTRIBUTE[S] attribute [, attribute]...{, sql-expression}...An ellipsis immediately following a single syntax item indicates that you can repeat thats

14

1 OverviewThe HP Neoview Transporter client provides a high-speed load and extract facility to move datato (load) and from (extract) the HP Neoview pl

• “Control File Generator” (page 109)• “Control File Editor ” (page 107)For detailed information see:“Control File Organization and Syntax” (page 25)“

2 Installation and ConfigurationThis chapter addresses the following topics:• “Installation Requirements” (page 17)• “Installing Transporter” (page 17

• 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/jreInstalling the Client U

Installing the Client Using CONSOLE ModeIf you do not have a GUI environment, or if you prefer a console-like installation, you can installTransporter

© Copyright 2009 Hewlett-Packard Development Company, L.P.Legal NoticeConfidential computer software. Valid license from HP required for possession, u

These subdirectories are created in the NVTHOME directory:ContentsDirectoryLicense file, Transporter executable scripts, and Readme/Libraries, static

3 Functions and FeaturesIn addition to standard load and extract, Transporter provides support for these additionalfeatures and functions:• “Staging T

PrehashingTransporter streamlines the process for load operations with hash-partitioned tables by performinga pre-hashing operation on the client. For

4 SecurityBecause load and extract jobs connect to databases and access password-protected resources, itis necessary for Transporter to keep track of

%NVTHOME%\utils\nvtencsrv -o installThe Encryption Utility — nvtencrsrvnvtencrsrv is a utility that installs the security directory and related files,

5 Control File Organization and SyntaxThe control file is a text file that tells Transporter how you want your data moved from sourceto target for eit

Table 5-1 Control File OrganizationOptionsControl File SectionN.A.“Version” (page 27)Global options for all Transporter jobs controlled by this file.•

Table 5-1 Control File Organization (continued)OptionsControl File SectionLocation of sources and targets for data.These global options can be overrid

versionnumberis the major version, followed by optional minor and patch versions. All version numbercomponents are separated by a period (.). Only the

• “noaudit” (page 56)• “nullstring” (page 56)• “operation” (page 57)• “parallel” (page 57)• “parallelstreams” (page 57)• “password” (page 57)• “retrie

Table of ContentsAbout This Document...9Intended

formatstringDefines the pattern for this field. For a load operation, formatstring tells Transporter howto interpret source file values. For an extrac

• Date/Time data typesUse a combination of the following patterns to a specify date or time format:— %bThe abbreviation for the month name in the curr

— backslash (\\)Period (.) is the only supported separator between integral and fractional seconds.32 Control File Organization and Syntax

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 form

• Interval data typesInterval data types require a formatstring.There is no default for interval fields.These field specifiers are allowed:— For year-

Table 5-2 Typeformats: format_definition Date/Time Format String Examples (continued)ExampleDescription"%m/%d/%y %H:%M"Specify a formatstrin

optionally qualified by {' | "}Specifies a quoting character for each data field.For information, see “Quote Processing For Load Operations”

Dataformats: Neoview SQL Table Formatformatname sql fieldsfields-definitionformatnameThe name you create for this delimited dataformat. The name must

a matching name is found. Subsequent columns with the matching name are notaccessible within this dataformat.There are two workarounds for this proble

MapsThe maps section describes how one data format is mapped to another when data is moved fromsource to target.maps{map-definition [, map-definition

5 Control File Organization and Syntax...25Creating a Control File...

fieldname_target, fieldname_sourcefieldname_target and fieldname_source must refer to names defined in the sourcedataformat and target dataformat, res

Table 5-3 Datatype Mappings (continued)Can be Mapped to a Target Field of Type...Source Field of Type...any character typedatetimetimestamptimestampin

Example:EMP_ID = identity loaderAsks the loader to generate a key for target field EMP_ID.scalar-expressionA simple expression composed of a fieldname

The condition and update options are ignored for insert operations.The condition and update options cannot both be “true” for a field mapping unless i

sourcename pipe pipename [options source-options]Specifies a named pipe as the data source/target. pipename is the name of the pipe.sourcename { jdbc

table1 jdbc table "SQL"."MyTable"• Specify a source names “query1” to access an SQL join query:query1 jdbc sql "select * fro

JobsThe jobs section specifies a list of Transporter jobs. A job is a collection of job entries. Jobs areprocessed serially, one at a time. This secti

• “operation” (page 57)• “parallel” (page 57)• “rowsetsize” (page 58)• “truncate” (page 60)job-entry-listone or more occurrences of job-entry, separat

/*Your comment line 1Your Comment line 2...*/IncludeYou can include one or more control file fragments from other control files. A control file fragme

6 Control File Optionsautotranautotran = "true" | "false"The default is “false”.For a load operation: when this option is set to “

7 Load and Extract Operations ...61Steps for Performing Load and Extr

Applies only to date values in source records for a load operation. This option is ignored forextract operations.When a date value is to be interprete

NOTE: These values are advisory only. While this number can be considered a trigger forshutting down a job, Transporter continues to process data alre

TIP: The Transporter NVTHOME/utils directory contains these programs that you can compileand execute to display character set encodings:• CharsetsSupp

◦ ' (apostrophe)◦ ( (open parenthesis)◦ ) (close parenthesis)◦ * (asterisk)◦ + (plus sign)◦ , (comma)◦ - (hyphen)◦ . (period)◦ / (slash mark)◦ Th

NOTE: These values are advisory only. While this number can be considered a trigger forshutting down a job, Transporter continues to process data alre

• Each target table has its own staging table.• The names of staging tables are generated by Transporter and written to the Transporterlog.• Source ro

noauditnoaudit = "true" | "false"The default is false.For a load operation, this option specifies whether Transporter should use n

operationoperation = "insert" | "update" | "upsert" | "delete"The default is “insert”.For a load operation, th

password specifies the password to use when connecting to a JDBC, ODBC, or JMS data source.• password specifies a plain-text password.• ${referencenam

For a load operation when a pipe is read, Transporter discards any records read until thestartseq record is seen. Transporter stops reading records on

Restrictions...107Pa

number_of_seconds can be zero or a positive integer.truncatetruncate = "true" | "false"The default is “false” (do not delete data

7 Load and Extract OperationsThe Transporter Client performs load and extract operations based on the content of a controlfile. This chapter addresses

resubmitting it, and you want Transporter to perform recovery on the job. For more informationabout -recover, refer to “ The -recover Command Line Opt

The Transporter JobThe units that make up a control file are called jobs. A job is a load or extract operation consistingof several job entries.A job

• Set “parallelstreams” (page 57) = n• Set “multipart” (page 55) = trueCAUTION: If multipart = false, and parallelstreams is enabled, Transporter st

RequirementsThese are the requirements to use the Trickle Feed feature:• ActiveMQ (JMS provider), which is included with the Java client package• A se

• The set of targets (that you have set in the “Sources” (page 43) section) must be the samefor all sources in the job.• If multiple job entries speci

• 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

68

8 Data ProcessingThis chapter addresses these topics:“Data For Load Operations” (page 69)“Processing Data for Load Operations” (page 69)“Delimited Dat

List of Tables1-1 Transporter Client Software Components...162-1 GUI

Fixed—Width Data for Load OperationsTransporter addresses and processes a fixed—width input field as follows, in this order:1. If “nullstring” (page 5

For Windows platforms: CR/LF (carriage return/line-feed)—— For Unix-like systems: LF (line feed)— For the Macintosh family: CR (carriage return)If mul

— To qualify by double-quotes (""):optionally qualified by "— To qualify by single-quotes (""):optionally qualified by '

9 TroubleshootingThis section covers job recovery and solutions to problems you might encounter.• “Logs” (page 73)• “Job Recovery” (page 74)• “Job Sta

Example 9-1 Examples of Custom Log File Locationsto send log output hereUse this command/test/log/java/DBTransporter.log—DNVT.instance-log-dir=test/ho

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 or

— Time for next retry operation if applicable— End time of the job entryControl File Metadata TablesYou can retrieve control file related data from th

Example 9-5 Identify All Job Entries that Successfully Completed Within the Last Seven DaysSELECT B.FILE_HOST, B.FILE_ABS_PATH, B.FILE_MOD

Example 9-7 Identify All Jobs Executed From Host abc Using the Latest Version of Control File/home/control files/ControlFile.txtSELECT DISTINCT A.JOB_

A Control File ExamplesControl File ExamplesFollowing are control file examples for:• Data types Example A-1 (page 80)• Extract from a Neoview SQL sou

List of Examples5-1 Supported Scalar Expressions ...

Example A-1 Control File: Datatypes/* All Data Types */version 1;options { errors = 1000, discards = 500, rowsetsize = 1000}typeformats {# c

intv10 interval format intv_frmt_10, intv11 interval format intv_frmt_11, intv12 interval format intv_frmt_12, flot1 fl

tims1 = tims1, tims2 = tims2, intv00 = intv00, intv02 = intv02, intv03 = intv03, intv05 = intv05,

Example A-2 Control File: Extract From Neoview SQL Source/*----------------------------------------*//* Simple Extract From Neoview SQL Source To File

LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP, PHONE = PHONE, INFO1 = INFO1, INFO2

Example A-3 Control File: Extract From Table Source to Named Pipe/*------------------------------------------------*//* Simple Extract From Table Sour

{ 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

LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP, PHONE = PHONE, SSN = SSN, INFO1

Example A-5 Control File: Include Defining DataformatsYou can use an Include file such as this to define data formats for many control files. SeeExamp

About This DocumentThis manual describes how to install and manage HP Neoview Transporter. This product providesprocesses and commands for loading dat

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)

Example A-7 Control File: Load and Extract/* Simple Load From File To Table & Extract From Neoview SQL Source To File */version 1.0;options{ er

PHONE = PHONE, SSN = SSN, INFO1 = INFO1, INFO2 = INFO2 )map2 from dataformat2 to dataformat3 (

Example A-8 Control File: Multiple Dataformats and Maps/*-----------------------------*//* Multiple Dataformats & Maps *//*-----------------------

( system = "asl0101", user = "user", password = "transport", url

Example A-9 Control File: Reflexive Update/*-------------------------*//* Simple Reflexive Update *//*-------------------------*/version 1.0;options {

Example A-10 Control File: Update with Constant, NULL, or Expression/*--------------------------------*//* Constants, NULLs & Expressions *//*----

load1 load options ( parallel= "false" ) ( source src1 target tgt1 map map1 )}97

Example A-11 Control File: Identity columnThis example of defines an IDENTITY column. CUSTOMER_NUMBER can be any numeric datatype (SMALLINT, INTEGER,

Example A-12 Control File: Usage of NULLSTRING/*--------------------------------*//* Usage of NULLSTRING */ /*-----------------------------