HP Neoview Release 2.3 Software User Manual

HP Neoview Transporter User Guide

HP Part Number: 545787-001

Published: April 2008

Edition: Release 2.3

© Copyright 2008 Hewlett-Packard Development Company, L.P.

Legal Notice

Confidentialcomputersoftware.ValidlicensefromHPrequiredforpossession,useorcopying.ConsistentwithFAR12.211and12.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.

Theinformationcontainedhereinissubjecttochangewithoutnotice.TheonlywarrantiesforHPproductsandservicesaresetforthintheexpress warrantystatementsaccompanyingsuchproductsandservices. Nothinghereinshouldbeconstruedasconstitutinganadditionalwarranty. HP shall not be liable for technical or editorial errors or omissions contained herein.

Export of the information contained in this publication may require authorization from the U.S. Department of Commerce.

Microsoft, Windows, and Windows NT are U.S. registered trademarks of Microsoft Corporation.

Intel, Pentium, and Celeron are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries.

Java is a U.S. trademark of Sun Microsystems, Inc.

Motif, OSF/1, UNIX, X/Open, and the "X" device are registered trademarks, and IT DialTone and The Open Group are trademarks of The Open Group in the U.S. and other countries.

Open Software Foundation, OSF, the OSF logo, OSF/1, OSF/Motif, and Motif are trademarks of the Open Software Foundation, Inc.

OSF MAKES NO WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

OSF shall not be liable for errors contained herein or for incidental consequential damages in connection with the furnishing, performance, or use of this material.

©1990, 1991, 1992, 1993 Open Software Foundation, Inc. The OSF documentation and the OSF software to which it relates are derived in part from materials supplied by the following:

©1987, 1988, 1989 Carnegie-Mellon University. © 1989, 1990, 1991 Digital Equipment Corporation. © 1985, 1988, 1989, 1990 Encore Computer Corporation. © 1988 Free Software Foundation, Inc. © 1987, 1988, 1989, 1990, 1991 Hewlett-Packard Company. © 1985, 1987, 1988, 1989, 1990, 1991, 1992 International Business Machines Corporation. © 1988, 1989 Massachusetts Institute of Technology. © 1988, 1989, 1990 Mentat Inc. © 1988 Microsoft Corporation. © 1987, 1988, 1989, 1990, 1991, 1992 SecureWare, Inc. © 1990, 1991 Siemens Nixdorf Informationssysteme AG. © 1986, 1989, 1996, 1997 Sun Microsystems, Inc. © 1989, 1990, 1991 Transarc Corporation.

OSFsoftwareanddocumentationarebasedinpartontheFourthBerkeleySoftwareDistributionunderlicensefromTheRegentsoftheUniversity of California. OSF acknowledges the following individuals and institutions for their role in its development: Kenneth C.R.C. Arnold, Gregory S. Couch, Conrad C. Huang, Ed James, Symmetric Computer Systems, Robert Elz. © 1980, 1981, 1982, 1983, 1985, 1986, 1987, 1988, 1989 Regents of the University of California.

Table of Contents
About This Document.......................................................................................................	11
Intended Audience................................................................................................................................	11
Related Documentation........................................................................................................................	11
Neoview Customer Library.............................................................................................................	11
HP Encourages Your Comments..........................................................................................................	12
General Syntax Notation.......................................................................................................................	12
Publishing History................................................................................................................................	14
1 Overview.......................................................................................................................	15
Network Changes for the Neoview Platform ......................................................................................	15
Load and Extract Operations................................................................................................................	15
The Transporter Control File................................................................................................................	15
Client Components...............................................................................................................................	16
2 Installation and Configuration....................................................................................	17
Installation Requirements.....................................................................................................................	17
Installing Transporter............................................................................................................................	17
Client Installation............................................................................................................................	17
Required Environment Settings For Client Installation.............................................................	17
The Client Installation GUI...................................................................................................	17
Client Directory Structure.....................................................................................................	18
Security Parameters Saved....................................................................................................	19
Utilities..................................................................................................................................	19
3 Functions and Features................................................................................................	21
Staging Tables.......................................................................................................................................	21
Parallel Streams.....................................................................................................................................	21
Parallel Query Extract...........................................................................................................................	21
Prehashing............................................................................................................................................	22
JMS Trickle Feed For Data Loads..........................................................................................................	22
Transporter Job Statistics on the Neoview Platform ............................................................................	22
Reflexive Update...................................................................................................................................	22
Reflexive Update Considerations....................................................................................................	22
Updates Using Constant, NULL, or Expression...................................................................................	22
Identity Columns..................................................................................................................................	22
4 Security..........................................................................................................................	23
Password Encryption............................................................................................................................	23
Encryption Files....................................................................................................................................	23
The Encryption Utility — nvtencrsrv...................................................................................................	23
Sample Commands for the nvtencrsrv Utility................................................................................	24
5 Control File Organization and Syntax.......................................................................	25
Creating a Control File..........................................................................................................................	25
Control File Limits................................................................................................................................	25
Control File Organization and Syntax..................................................................................................	25
Version.............................................................................................................................................	27

Table of Contents

3

Options............................................................................................................................................	28
Typeformats.....................................................................................................................................	29
Dataformats.....................................................................................................................................	32
Maps................................................................................................................................................	36
Sources.............................................................................................................................................	40
Jobs...................................................................................................................................................	42
Comments........................................................................................................................................	44
Include.............................................................................................................................................	45
Supported Data Types..........................................................................................................................	45
6 Control File Options.....................................................................................................	47
baddatafile............................................................................................................................................	47
commitsize............................................................................................................................................	47
crossoveryear........................................................................................................................................	47
datasource.............................................................................................................................................	47
deleteonerror.........................................................................................................................................	48
discards.................................................................................................................................................	48
enabletriggers........................................................................................................................................	48
endseq...................................................................................................................................................	48
errors.....................................................................................................................................................	49
faileddatafile.........................................................................................................................................	49
forcestaging...........................................................................................................................................	49
multipart...............................................................................................................................................	50
noaudit..................................................................................................................................................	51
nullstring...............................................................................................................................................	51
operation...............................................................................................................................................	52
parallel..................................................................................................................................................	52
parallelstreams......................................................................................................................................	52
password...............................................................................................................................................	52
retries.....................................................................................................................................................	53
rowsetsize..............................................................................................................................................	53
sorted.....................................................................................................................................................	53
startseq..................................................................................................................................................	53
system...................................................................................................................................................	54
tenacity..................................................................................................................................................	54
timeout..................................................................................................................................................	54
truncate.................................................................................................................................................	54
url..........................................................................................................................................................	54
user........................................................................................................................................................	55
7 Load and Extract Operations ....................................................................................	57
Steps for Performing Load and Extract Operations.............................................................................	57
Starting the Transporter Client ............................................................................................................	57
Starting the Client on Linux Systems..............................................................................................	58
Starting the Client on Windows Systems........................................................................................	58
Stopping the Client...............................................................................................................................	58
The Transporter Job...............................................................................................................................	58
Sources and Targets..............................................................................................................................	59
Parallel Streams.....................................................................................................................................	59
Parallel Streams for Load Jobs.........................................................................................................	59
Parallel Streams for Extract Jobs......................................................................................................	59
Transaction Modes for Load Jobs.........................................................................................................	60
Loading Data With JMS Trickle Feed....................................................................................................	60

4Table of Contents

Requirements...................................................................................................................................	61
Parallel Processing for Load Operations..............................................................................................	61
Considerations for Extract Jobs.............................................................................................................	62
8 Data Processing............................................................................................................	63
Data For Load Operations....................................................................................................................	63
Processing Data for Load Operations...................................................................................................	63
Delimited Data for Load Operations...............................................................................................	63
Fixed—Width Data for Load Operations........................................................................................	64
Delimited Data......................................................................................................................................	64
Field Delimiter Character................................................................................................................	64
Record Separator Character ............................................................................................................	64
Quote Processing For Load Operations..........................................................................................	65
Quote Processing For Extract Operations.......................................................................................	65
Considerations for Character Data.......................................................................................................	66
9 Troubleshooting............................................................................................................	67
Logs.......................................................................................................................................................	67
Log Files on the Client.....................................................................................................................	67
Logging to the Console....................................................................................................................	67
The EMS Log....................................................................................................................................	67
Job Recovery..........................................................................................................................................	67
Considerations for Job Recovery.....................................................................................................	68
The -recover Command Line Option..........................................................................................	68
Job Statistics on the Neoview Platform ................................................................................................	68
Control File Metadata Tables...........................................................................................................	69
Using Transporter Metadata Tables................................................................................................	69
A Control File Examples..................................................................................................	73
B Control File Editor and Control File Generator.........................................................	97
Control File Editor ...............................................................................................................................	97
Features............................................................................................................................................	97
Restrictions......................................................................................................................................	97
Packaging.........................................................................................................................................	97
Using the Control File Editor..........................................................................................................	97
Creating a New Control File......................................................................................................	97
Opening an Existing Control File...............................................................................................	97
Adding Content to a Control File...............................................................................................	98
Saving a Control File to a Local System.....................................................................................	98
Checking the Syntax of a Control File........................................................................................	98
Validating a Control File............................................................................................................	98
Setting the Default Folder..........................................................................................................	98
Switching Views in the Control File Editor................................................................................	98
Including Another Control File..................................................................................................	98
Control File Generator..........................................................................................................................	99
Required Arguments.......................................................................................................................	99
Optional Arguments........................................................................................................................	99
Error Message and Behavior..........................................................................................................	100
Packaging.......................................................................................................................................	100

Table of Contents

5

C Global Options Table...............................................................................................	101
D Reserved Keywords...................................................................................................	103
Transporter Reserved Keywords........................................................................................................	103
glossary..........................................................................................................................	105
Index...............................................................................................................................	107

6Table of Contents

List of Tables

1-1	Transporter Client Software Components.....................................................................................	16
2-1	Transporter Client Installation......................................................................................................	18
4-1	Encryption Files.............................................................................................................................	23
4-2	Options Supported by the nvtencrsrv Utility...........................................................................	24
4-3	Sample Commands for nvtencrsrv...........................................................................................	24
5-1	Control File Organization..............................................................................................................	26
5-2	Typeformats: format_definition Date/Time Format String Examples..........................................	32
5-3	Datatype Mappings.......................................................................................................................	37
7-1	Steps for Load and Extract Operations.........................................................................................	57
7-2	Sources and Targets for Loading and Extracting Data..................................................................	59
7-3	Transaction Modes for Load Jobs..................................................................................................	60
8-1	Delimited and Fixed-width Data Descriptions.............................................................................	63
C-1	Global Options Table...................................................................................................................	101
D-1	Reserved Keywords List..............................................................................................................	103

7

8

List of Examples

9-1	Sample HP_TRANSPORTER_BASE_JOB_STATS File..................................................................	68
9-2	Sample HP_TRANSPORTER_CONTROL_FILES File..................................................................	69
9-3	Sample HP_TRANSPORTER_CONTROL_FILE_TEXT File.........................................................	69
9-4	Identify All Job Entries that Successfully Completed Within the Last Seven Days......................	70
9-5	Identify All Job Entries That Were Started in the Last 24 Hours But Have Not Completed —
	Either Failed or In Progress...........................................................................................................	70
9-6	Identify All Jobs Executed From Host abc Using the Latest Version of Control File	71
	/home/control files/ControlFile.txt..........................................................................
9-7	Delete All Job Statistics For Jobs Started Three Or More Months Ago.........................................	71
9-8	DeleteAllVersionsofControlFile /home/control files/ControlFile.txt ThatHave
	Been Used On Host abc................................................................................................................	71
A-1	Control File: Datatypes..................................................................................................................	74
A-2	Control File: Extract From Neoview SQL Source..........................................................................	77
A-3	Control File: Extract From Table Source to Named Pipe..............................................................	79
A-4	Control File: Load Fixed Width Data............................................................................................	81
A-5	Control File: Include Defining Dataformats .................................................................................	83
A-6	Control File: Include — Load From File to Table..........................................................................	84
A-7	Control File: Load and Extract......................................................................................................	85
A-8	Control File: Multiple Dataformats and Maps..............................................................................	87
A-9	Control File: Reflexive Update......................................................................................................	89
A-10	Control File: Update with Constant, NULL, or Expression .........................................................	90
A-11	Control File: Identity column........................................................................................................	92
A-12	Control File: Usage of NULLSTRING...........................................................................................	93
A-13	Control File: NOAUDIT mode......................................................................................................	94
A-14	Control File: Forcestaging Load with Multiple Job Entries in a Single Job..................................	95

9

10

About This Document

ThismanualdescribeshowtoinstallandmanageHPNeoviewTransporter.Thisproductprovides processes and commands for loading data into your Neoview platform or extracting data from it.YoucaninstallandexecutetheTransporterclientfromasystemrunningMicrosoft®Windows, Red Hat Enterprise Linux 5 or HP-UX® 64 for the Itanium platform.

Intended Audience

This document is intended for Neoview system administrators responsible for loading and extracting data to and from HP Neoview platforms. Administrators are expected to have knowledge of the client operating system. It is also helpful to have knowledge of database and connectivity concepts.

Related Documentation

This manual is part of the HP Neoview customer library.

Neoview Customer Library

• Administration

Neoview Character Sets	Information for database administrators and end users of the Neoview
Administrator's Guide	Character Sets product, including rules for defining and managing character
	data using SQL language elements, capabilities and limitations of Neoview
	clientapplications,troubleshootingcharacterset-relatedproblems,andenabling
	Pass-Through mode in the ISO88591 configuration.
Neoview Command Interface	Information about using the HP Neoview Command Interface to run SQL
(NCI) Guide	statements interactively or from script files.
Neoview Command Interface	Command-line help that describes the commands supported in the current
(NCI) Online Help	operating mode of Neoview Command Interface.
Neoview Database	Information about how to load and manage the Neoview database by using
Administrator’s Guide	the Neoview DB Admin and other tools.
Neoview DB Admin Online	Context-sensitive help topics that describe how to use the HP Neoview DB
Help	Admin management interface.
Neoview Guide to Stored	InformationabouthowtousestoredproceduresthatarewritteninJavawithin
Procedures in Java	a Neoview database.
NeoviewManagementDashboard	InformationonusingtheDashboardClient,includinghowtoinstalltheClient,
Client Guide for Database	start and configure the Client Server Gateway (CSG), use the Client windows
Administrators	and property sheets, interpret entity screen information, and use Command
	and Control to manage queries from the Client.
Neoview Management	Context-sensitive help topics that describe how to use the Neoview
Dashboard Online Help	Management Dashboard Client.
Neoview Repository User Guide	InformationaboutusingtheRepository,includingdescriptionsofRepository
	views and guidelines for writing Neoview SQL queries against the views.
Neoview Owner’s Manual	Site-planning information and basic hardware information.
Neoview Performance	Context-sensitive help topics that describe how to use the Neoview
Analyzer Online Help	Performance Analyzer to analyze and troubleshoot query-related issues on
	the Neoview data warehousing platform.
Neoview Query Guide	Information about reviewing query execution plans and investigating query
	performance of Neoview databases.
NeoviewReportsOnlineHelp	Help topics that describe how to use the HP Neoview Reports Tool.

Intended Audience

11

	Neoview Transporter User Guide	Information about processes and commands for loading data into your
		Neoview platform or extracting data from it.
	Neoview Workload Management	InformationaboutusingNeoviewWorkloadManagementServices(WMS)to
	Services Guide	manage workload and resources on a Neoview data warehousing platform.
	README files for	— README for the HP Neoview Management Dashboard Client
	Administration products	— README for HP Neoview Command Interface
		— README for HP Neoview Reports Client
		— README for the Neoview Performance Analyzer
•	Reference
	Neoview SQL Reference Manual	Reference information about the syntax of SQL statements, functions, and
		other SQL language elements supported by the Neoview database software.
	Mapping Tables for Neoview	Provides links to the mapping tables used by the Neoview Character Sets
	Character Sets	product.
	Neoview Messages Manual	Cause, effect, and recovery information for error messages.
•	Connectivity
	NeoviewJDBCType4DriverAPI	Reference information about the HP Neoview JDBC Type 4 Driver API.
	Reference
	Neoview JDBC Type 4 Driver	InformationaboutusingtheHPNeoviewJDBCType4driver,whichprovides
	Programmer’s Reference	Java applications on client workstations access to a Neoview database.
	NeoviewODBCDriversManual	Information about using HP Neoview ODBC drivers on a client workstation
		to access a Neoview database.
	ODBC Client Administrator	Context-sensitivehelptopicsthatdescribehowtousetheODBCclientinterface.
	Online Help
	READMEfilesforConnectivity	— README for the HP Neoview JDBC Type 4 Driver
	products	— README for the HP Neoview ODBC Driver for Windows
		— README for the HP Neoview UNIX Drivers

HP Encourages Your Comments

HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to:

[e-mail]

Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.

General Syntax Notation

This list summarizes the notation conventions for syntax presentation in this manual.

UPPERCASE LETTERS

Uppercaselettersindicatekeywordsandreservedwords.Typetheseitemsexactlyasshown. Items not enclosed in brackets are required. For example:

SELECT

12

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

Computertypeletterswithintextindicatecase-sensitivekeywordsandreservedwords.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.00

The user must press the Return key after typing the input.

[ ] Brackets

Brackets enclose optional syntax items. For example:

DATETIME [start-field TO] end-field

A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example:

DROP SCHEMA schema [CASCADE] [RESTRICT]

DROP SCHEMA schema [ CASCADE | RESTRICT ]

{ } Braces

Braces enclose required syntax items. For example:

FROM { grantee[, grantee]...}

Agroupofitemsenclosedinbracesisalistfromwhichyouarerequiredtochooseoneitem.

Theitemsinthelistcanbearrangedeithervertically,withalignedbracesoneachsideofthe list,orhorizontally,enclosedinapairofbracesandseparatedbyverticallines. Forexample:

INTERVAL { start-field TO end-field } { single-field }

INTERVAL { start-field TO end-field | single-field }

| Vertical Line Averticallineseparatesalternativesinahorizontallistthatisenclosedinbracketsorbraces. For example:

{expression | NULL}

… Ellipsis

An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example:

General Syntax Notation

13

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,andothersymbolsnotpreviouslydescribedmustbetyped as shown. For example:

DAY (datetime-expression)

@script-file

Quotationmarksaroundasymbolsuchasabracketorbraceindicatethesymbolisarequired character that you must type as shown. For example:

"{" module-name [, module-name]... "}"

Item Spacing

Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example:

DAY (datetime-expression)

DAY(datetime-expression)

If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items:

myfile.sh

Line Spacing Ifthesyntaxofacommandistoolongtofitonasingleline,eachcontinuationlineisindented three spaces and is separated from the preceding line by a blank line. This spacing distinguishesitemsinacontinuationlinefromitemsinaverticallistofselections.Forexample:

match-value [NOT] LIKE pattern

[ESCAPE esc-char-expression]

Publishing History

Part Number	Product Version	Publication Date
545787–001	HP Transporter R2.3	April 2008

14

1 Overview

TheHPNeoviewTransporterclientprovidesahigh-speedloadandextractfacilitytomovedata to (load) and from (extract) the HP Neoview platform. Transporter provides these features:

•A Java-based data loader and extractor

•High speed parallel bulk load and extract

•Ability to move data between HP Neoview and other platforms

This chapter addresses the following topics:

•“Network Changes for the Neoview Platform ” (page 15)

•“Load and Extract Operations” (page 15)

•“The Transporter Control File” (page 15)

•“Client Components” (page 16)

Network Changes for the Neoview Platform

For R2.3 and later releases, the internal network for the Neoview platform provides greater flexibilityandhigherbandwidthwhenconnectingtoacustomernetwork. Theinternalnetwork is fully fault-tolerant and uses four HP ProCurve 3500 switches in a ring configuration. Each 3500 switch provides Gigabit-Ethernet (GbE) connectivity ports for connection between the customer-provided local data loaders and the Neoview platform. In addition, these optional components are available to expand the base switch features:

•10 Gb GBICs that enable you to connect directly to customer-provided remote data loaders or the customer network backbone

•ProCurve 620 Redundant and External Power Supply that provides a redundant, external power supply for the 3500 switches. Each power supply will power two 3500 switches

For more information about the changes to the Neoview platform network and setting up and configuring the network prior to deployment of the Transporter product, refer to the Neoview Hardware Installation and Support Guide.

Load and Extract Operations

The Neoview Transporter Client can load data (import to the Neoview platform) to and extract data (export from the Neoview platform) using:

•Flat Files

•Named Pipes

You create and manage pipes with whatever application you want to use. For more information about creating named pipes, see http://www.docs.hp.com/en/B2355-60127/ mkfifo.1.html

•Neoview SQL queries

•Java Messaging Service (JMS) Trickle Feed

For information about JMS, see http://java.sun.com/products/jms/index.jsp.

The Transporter Control File

The Transporter Control File specifies how you want data moved from sources to targets, and includes configuration and tuning parameters for Transporter. This manual explains the syntax and meaning of control file elements so that you can create and customize control files for your data movement tasks.

You can create a control file with any text editor or use these provided tools:

Network Changes for the Neoview Platform 15

•“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
	creatingaTransporterControlFile.Formoreinformation,
	see:
	“Control File Organization and Syntax” (page 25)
	“Control File Editor ” (page 97)
Control File Generator	A Transporter command-line tool for quickly creating
	controlfilesusingatemplateorbasedontablesthatexist
	in a Neoview database schema. For more information,
	see:
	“Control File Generator” (page 99)
Transporter encryption tool	A Transporter command-line tool for encrypting and
	storing passwords and userIDs.
	For more information, see: “Password Encryption”
	(page 23)
Transporter Installer	A GUI-based tool that installs and configures the
	Transporter environment. More information: “Client
	Installation” (page 17)

16 Overview

2 Installation and Configuration

This chapter addresses the following topics:

•“Installation Requirements” (page 17)

•“Installing Transporter” (page 17)

Installation Requirements

BeforeTransporterisinstalled,besurethatthefollowingarevalidandpresentintheenvironment.

•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.0 (required for Trickle Feed support)

•A heap size of 1GB or greater

Installing Transporter

This section discusses installation processes.

Client Installation

This section addresses client installation requirements, the installation Graphic User Interface (GUI), and client directories.

Required Environment Settings For Client Installation

These settings are required for using the installer.

For example, if Java is installed in /usr/java/jdk1.5.0_09:

•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

The Client Installation GUI

You install the Transporter Java client using a GUI that installs and configures the Transporter environment.

•ForWindows,double-clickonthenvt_java_client_291.jar packageonyourdesktop.

•From a Linux or HP-UX shell, use this command: java -jar nvt_java_client_291.jar

CAUTION: The installer does not update a Transporter installation. If you have an existing installation and you reinstall Transporter, you lose the existing installation.

NOTE: TheinstallerrequiresaGUIenvironment. IfnoGUIenvironmentisfound,theinstaller

returns an error.

Use these standard buttons to navigate the installation GUI:

•Previous — return to the last screen displayed.

•Next — proceed to the following screen and continue installation.

•Quit — stop installing Transporter.

Installation Requirements

17

Table 2-1	Transporter Client Installation
Screen	Action
1	Click Next to continue installation.
Welcome
2	Read and accept the Transporter license agreement.
License
Agreement
3	Enter the path to the installation folder in which you want Transporter files to be installed, or

Installation path click Browse to select from a folder list.

4Using the check boxes, select the software packs you want installed.

Software packs Packs listed without check boxes are automatically included with those you select.

5	Confirm that the installation path and software packs displayed are correct.
Confirmation	If not, you can go back to the previous screens and make corrections.
6	When the installer displays Finish, click Next to continue.
Installation
progress
7	MakeaselectiononthisscreentoreviewtheReadmefileand/orthestartupscriptfilewhenthe
Select files to	installation is complete.
review
8 (optional)	The Readme file contains any last-minute release specific instructions for:
Readme file	•	The Spring framework
	•	Transporter
9 (optional)	The startup script create environment variables for the Transporter environment.
Startup script
10	Confirmation messages.
Finish	Click Done to exit the installation program.

Client Directory Structure

InstallationcreatestheenvironmentvariableNVTHOMEforthedefaultlocationoftheTransporter base directory. The directory specification follows the conventions of your loader platform.

For example,

•Windows — c:\Program Files\NVTransporter

•Unix®-type platform— /usr/nvtransporter or /opt/nvtransporter

These subdirectories are created In the $NVTHOME directory:

Directory	Contents
/bin	All binary executables
/lib	Libraries, static and dynamic and java class library
/conf	License files, system control files, framework configuration files
/mesg	Message catalogs or locale specific properties files
/log	Application message log directory
/extensions	Client extensions to Transporter — operation extension
/utils	Utilities

18 Installation and Configuration

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).

Utilities

These utilities are installed in $NVTHOME/utils:

•The password encryption utility nvtencrsrv (“The Encryption Utility — nvtencrsrv” (page 23))

•The “Control File Generator” (page 99)

•The “Control File Editor ” (page 97) (if installed)

Installing Transporter

19

20

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

AstagingtableisaNeoviewSQLtableusedasanintermediateplacefordatabeforeitisprocessed and moved to the target table on a Neoview platform. Transporter creates the staging table in the same schema as the target table, names it, uses it during the load operation, and deletes it when the load operation is complete.

You cannot use staging tables for extract operations.

Use the “forcestaging” (page 49) option in the control file to control the use of staging tables.

Staging tables increase performance for load operations. However, if the operation fails:

•You cannot perform recovery if a job failed during transfer from the staging table to the target table row, you must restart the entire operation.

•If recovery is not performed, the staging table is not deleted. Check the daily log file in $NVTHOME/log/java tofindthefilenameanddroptheleftoverstagingtable. Thestaging table is deleted automatically if recovery is performed. For information about recovery, see “Job Recovery” (page 67).

Parallel Streams

Transporter supports the use of parallel streams for data movement. Whenever possible, Transporter uses the parallel stream feature so that data movement is fast and efficient.

You determine the number of parallel streams with the “parallelstreams” (page 52) option in thecontrolfile.Foranextractoperation,youalsosupplyafilenameprefixforthetargetfilenames, and Transporter creates the necessary target files.

Using parallel streams can increase performance for load and extract jobs. However, because a larger number of parallel streams increases overhead on the client system, you must determine the best number of streams to use for maximum performance.

TIP: Begin by using the default number of parallel streams and then tune your number of parallel streams based on the performance you observe.

Parallel Query Extract

Transporter enables you to use a single source query to generate multiple parallel data streams from the Neoview platform. For more information, see the “parallelstreams” (page 52) control file option.

Staging Tables

21

Prehashing

Transporterstreamlinestheprocessforloadoperationswithhash-partitionedtablesbyperforming 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 sourcesystemchanges.TrickleFeeddiffersinthiswayfromtheuseofflatfilesandnamedpipes, which are considered "batch" techniques.

Transporter supports the use of JMS Trickle Feed as a data source for load jobs.

Transporter Job Statistics on the Neoview Platform

TransportermetadatatablesontheNeoviewplatformmaintaininformationandstatisticsabout jobs and control files. For more information, see “Job Statistics on the Neoview Platform ” (page 68)

Reflexive Update

A reflexive update is an update in which an input value provided can be a numerical value that isaddedtoorsubtractedfromthecurrentcolumnvalue.Usethesourceandtargetfieldmappings in the “Maps” (page 36) section of the control file to perform a reflexive update. See “Control File: Reflexive Update” (page 89).

Reflexive Update Considerations

•Reflexive updates apply only to columns with numerical values.

•The input value provided for a reflexive update must match the range of values that are legal for the target column.

•You cannot use reflexive update on primary key columns.

Updates Using Constant, NULL, or Expression

Inadditiontoreflexiveupdates,TransporterprovidesforupdatingNeoviewSQLcolumnswith a constant, a NULL, or an expression. Use the source and target field mappings in the “Maps” (page 36) section of the control file to update columns with these values. Refer to this control file example for details: “Control File: Update with Constant, NULL, or Expression ” (page 90).

Identity Columns

In Neoview SQL, you can use IDENTITY columns to automatically generate values that are unique across all partitions of the table for that column. IDENTITY columns can be used as surrogate keys and to uniquely identify records with the same key. For more information, refer to the HP Neoview SQL Reference Manual.

Transporter supports the creation of an IDENTITY column in target tables, and automatically generates values for the identity column as part of the load task. The resulting column can be a surrogate key in the table. Use the source and target field mappings in the “Maps” (page 36) sectionofthecontrolfiletohaveTransportergeneratedataforidentitycolumns.SeeExampleA-11 (page 92).

NOTE: The maximum value for the identity value is the same as a Java long data type (java.lang.Long.MAX_VALUE) when the target column is a LARGEINT data type. That is, 9223372036854775807.

22 Functions and Features

4 Security

Because load and extract jobs connect to databases and access password-protected resources, it isnecessaryforTransportertokeeptrackofuserIDsandpasswords. Thischapteraddressesthe 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.5 based password security to protect resource passwords. In the interest of protecting user IDs and passwords, you use an encryption utility called nvtencrsrv, which storesencryptedwordsinasecurityfile.OnlytheadministratorfortheTransporterclientplatform can run this utility.

NOTE: Transporter always transmits passwords in encrypted format. When control files are savedontheNeoviewplatform,allplaintextpasswordsareencrypted.Youcanchoosetoencrypt user IDs or not. If you do not encrypt them, user IDs are stored in the ctlprops.txt file as plain text.

Encryption Files

Thesefilesareusedbytheencryptionutilityandarestoredinthe $NVTHOME/conf/security directory.

Encrypted passwords and user IDs are stored in the format:

Name = {DES} encrypted value

Name is a name you assign and that can be referenced by the Transporter Control File. Unencrypted passwords and user IDs are stored in the format:

Name = plain text

Name is a name you assign that can be referenced by the Transporter Control File.

Table 4-1 Encryption Files

Filename	File Content	Example
encctlprops.txt	Encrypted words	neo1pass = {DES}5y9BTnIKJuw=
ctlprops.txt	Unencryptedwords(plain	neo1user = root
	text)

CAUTION: TheencryptionfilesmustbeproperlysecuredbytheadministratoroftheTransporter client platform to ensure that no unauthorized users can access these files.

The Encryption Utility — nvtencrsrv

nvtencrsrv is a utility that adds encrypted words to the password encryption file and deletes encryptedwordswhentheyarenolongerneeded. Onlytheadministratoroftheclientplatform can use this utility.

Password Encryption

23

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	Plaintextwordtobeencrypted—validonlyfortheaddoption.
-l	Log file directory
Default value: $NVTHOME/conf/security
-f	Log file name
Default value: NVTLOGyyyy mm dd
-h	Display the help text

Sample Commands for the nvtencrsrv Utility

Table 4-3 Sample Commands for nvtencrsrv

Action	Form of the Command
Add an encrypted word to the	./nvtencrsrv o add r refname
file	p password Neo
	Example:
	./nvtencrsrv o add r p1 p Neo

Result

1.p1 is encrypted.

2.p1isenteredinthefileencctlprop.txt, in the form:

p1 = {DES}6rOGfryxsWk=

Remove a reference name ./nvtencrsrv o del r refname

Example:

./nvtencrsrv -o del -r p1

1.p1 is removed from the file encctlprop.txt.

2.A success message is displayed.

Display the help message	./nvtencrsrv or	The list of options for nvtencrsrv is
	./nvtencrsrv –h	displayed.

24 Security

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. This chapter discusses the following topics:

•“Creating a Control File” (page 25)

•“Control File Limits” (page 25)

•“Control File Organization and Syntax” (page 25)

•“Supported Data Types” (page 45)

Creating a Control File

You can use any text editor to create the control file and you can create the file on any system and move it to your loader platform. You can also use the Control File Editor (CFE) or Control File Generator to create a control file.

Control File Limits

These limits apply to the control file:

•The name you create for a control file element (for example, “Typeformats” (page 29) or “Maps” (page 36)) is limited to 64 bytes, must begin with a letter or an underscore, and can contain only letters, underscores, and digits.

•For a load operation, the maximum length of an input record is 32768 characters, including the record separator.

•The maximum number of job entries allowed for a single job is 1024.

•The maximum number of jobs allowed in a single control file is 1024.

Control File Organization and Syntax

The control file describes source, target, and settings for data movement.

A control file begins with a version attribute, followed by any number of options, dataformats, typeformats, maps, sources, and jobs sections, in any order.

The following control file elements are required:

•a version attribute

•dataformats

•a sources section

•a maps section

•a jobs section

If you specify a control file section or option list, you must have at least one entry. If a given control file option is defined multiple times as a global option or defined multiple times in the same option list, only the final occurrence is recognized by Transporter.

All control file keywords and option names are case-insensitive.

Theoptions sectionsetsglobaloptionsforalljobscontrolledbyacontrolfile.However,certain options in other sections of the control file can override the global setting or are unique to that section. For more information, refer to “Global Options Table” (page 101).

The control file consists of these sections:

Creating a Control File

25

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.
	•	“baddatafile” (page 47)
	•	“commitsize” (page 47)
	•	“crossoveryear” (page 47)
	•	“datasource” (page 47)
	•	“deleteonerror” (page 48)
	•	“discards” (page 48)
	•	“enabletriggers” (page 48)
	•	“endseq” (page 48)
	•	“errors” (page 49)
	•	“faileddatafile” (page 49)
	•	“forcestaging” (page 49)
	•	“multipart” (page 50)
	•	“noaudit” (page 51)
	•	“nullstring” (page 51)
	•	“operation” (page 52)
	•	“parallel” (page 52)
	•	“parallelstreams” (page 52)
	•	“password” (page 52)
	•	“retries” (page 53)
	•	“rowsetsize” (page 53)
	•	“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” (page 29)	Formats in which data types can be represented.
	This control file section has no unique options.
“Dataformats” (page 32)	Data formats for sources and targets.
	Job options and unique options for this section:
	•	“crossoveryear” (page 47)
		(fixed-width and Neoview SQL table format)
	•	delimited by
		(delimited format)
	•	optionally qualified by
	•	record length
		(fixed-width format)
	•	records separated by
“Maps” (page 36)	Maps data to another format when moved from source to target.
	Unique options for this section:
	•	condition
	•	update

26 Control File Organization and Syntax

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.
	•	“datasource” (page 47)
	•	“endseq” (page 48)
	•	“multipart” (page 50)
	•	“parallelstreams” (page 52)
	•	“password” (page 52)
	•	“retries” (page 53)
	•	“sorted” (page 53)
	•	“startseq” (page 53)
	•	“system” (page 54)
	•	“tenacity” (page 54)
	•	“rowsetsize” (page 53)
	•	“url” (page 54)
	•	“user” (page 55)
“Jobs” (page 42)	A list of Transporter jobs and assigned options.
	These job level options can override those global options defined in the
	options section for the corresponding job:
	•	“baddatafile” (page 47)
	•	“commitsize” (page 47)
	•	“deleteonerror” (page 48)
	•	“discards” (page 48)
	•	“errors” (page 49)
	•	“faileddatafile” (page 49)
	•	“operation” (page 52)
	•	“parallel” (page 52)
	•	“rowsetsize” (page 53)
	•	“truncate” (page 54)
“Comments” (page 44)	Comments you want to include in the file.
	This control file section has no unique options.
“Include” (page 45)	Includes a control file segment in this file.
	This control file section has no unique options.

Version

version versionnumber;

A control file always begins with the version attribute. No other sections can precede the version attribute.

If a control file begins with the %include statement, the first included file that does not begin with another %include statement must begin with the version attribute. In other words, the first non-%include statement that Transporter sees must be the version attribute.

versionnumber

is the major version, followed by optional minor and patch versions. All version number componentsareseparatedbyaperiod(.).Onlythemajorversionisrequired.Thecomponents oftheversionnumbercorrespondtothemajor,minor,andpatchversionsoftheTransporter product,respectively. IfTransporterseesanincompatibleversionnumberinthecontrolfile,

Control File Organization and Syntax

27

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,itispossibletohaveincompatibilitybetweenmajorversionsandifitoccurs,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. All of the following are allowed:

version 1; version 1.0; version 1.0.0;

A control file is based on Transporter version 1.0.3:

version 1.0.3;

Options

The options section defines global options for all Transporter jobs. Most global options can be overridden by format, source, job, or job entry settings.

See Appendix C (page 101), which lists all Transporter options and indicates the levels at which each option can be overridden.

options

{

option-name = option_value [, option-name = option_value ]...

}

option-name

Oneormoreoccurrencesofoption-name,separatedbyacomma(,).Thelastoption-name should not use the comma separator.

option-name is case-insensitive.

These are the supported global option-names:

•“baddatafile” (page 47)

•“commitsize” (page 47)

•“crossoveryear” (page 47)

•“datasource” (page 47)

•“deleteonerror” (page 48)

•“discards” (page 48)

•“endseq” (page 48)

•“errors” (page 49)

•“faileddatafile” (page 49)

•“forcestaging” (page 49)

•“multipart” (page 50)

•“noaudit” (page 51)

•“nullstring” (page 51)

•“operation” (page 52)

•“parallel” (page 52)

•“parallelstreams” (page 52)

•“password” (page 52)

•“retries” (page 53)

•“rowsetsize” (page 53)

28 Control File Organization and Syntax

•“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 last format-definition should not use the comma separator.

formatname

The name you choose for this typeformat. It must be unique among existing typeformats. formatname is case-insensitive and cannot exceed 64 bytes.

datatype

A supported data type. See “Supported Data Types” (page 45) for a list of supported data types.

formatstring

Definesthepatternforthisfield.Foraloadoperation,formatstring tellsTransporterhow 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—valuesareinterpretedasoctalvalueswithanoptionalleadingsigncharacter.

For numeric and decimal data types, a period can be used to separate the integral portion of the value from the fractional portion. For example, the value 10.5 can be represented in octal as “12.4”.

—hex — values are interpreted as hexadecimal values with an optional leading sign character.

For numeric and decimal data types, a period can be used to separate the integral portionofthevaluefromthefractionalportion. Forexample,thevalue -10.5canbe represented in hexadecimal as “-A.8”.

Control File Organization and Syntax

29

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).

—%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).

—%I

The hour in the 12-hour clock representation (01 to 12).

—%M

The minute (00 to 59).

—%m

The month number (01 to 12).

—%p

The equivalent of AM or PM in the current locale.

—%s

The second (00 to 59).

—%y

The two-digit year (offset from %C).

—%S

Fractional seconds.

Transporter returns an error if any of the following are true:

—A given specifier appears more than once in formatstring.

—formatstring contains both %s and %S.

—formatstring contains both %H and %p.

Field delimiters are optional. The supported field delimiters are:

—hyphen (-)

—colon (:)

—period (.)

—underscore (_)

—percent (%)

30 Control File Organization and Syntax