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

Legal Notice

Confidential computersoftware. 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 informationcontained hereinis subject to change without notice. Theonly warranties forHP productsand services are set forth in the express

warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. 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.

from materials supplied by the following:

OSF software anddocumentation are based in parton theFourth Berkeley SoftwareDistribution under license from TheRegents ofthe University

of California. OSF acknowledges the following individuals and institutions for their role in its development: Kenneth C.R.C. Arnold, Gregory S.

the University of California.

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

4 Table 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

6 Table 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

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

/home/control files/ControlFile.txt..........................................................................71

9-7 Delete All Job Statistics For Jobs Started Three Or More Months Ago.........................................71

9-8 Delete All Versions of Control File /home/control files/ControlFile.txt That Have

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

About This Document

This manualdescribes 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.

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.

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:

pubs.comments@hp.com

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

Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example:

SELECT

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.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]...}

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

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

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

| Vertical Line

A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. 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, 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. 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

If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example:

match-value [NOT] LIKE pattern

[ESCAPE esc-char-expression]

Publishing History

Publication DateProduct VersionPart Number

April 2008HP Transporter R2.3545787–001

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. 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 flexibility and higher bandwidth when connecting to a customer network. The internal network 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

DefinitionTransporter Component Type

Java Transporter Client

Control File Editor (CFE)

Control File Generator

Transporter encryption tool

Transporter Installer

A Java application that has been validated on these platforms: Linux, Windows, and HP-UX for Itanium

A Graphical User Interface (GUI) tool that assists you in creating a Transporter Control File. For more information, see:

“Control File Organization and Syntax” (page 25) “Control File Editor ” (page 97)

A Transporter command-line tool for quickly creating control files using a template or based on tables that exist in a Neoview database schema. For more information, see:

“Control File Generator” (page 99)

A Transporter command-line tool for encrypting and storing passwords and userIDs. For more information, see: “Password Encryption”

(page 23)

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

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

• For Windows, double-click on the nvt_java_client_291.jar package on your desktop.

• 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: The installer requires a GUI environment. If no GUI environment is found, the installer

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

ActionScreen

Welcome

License Agreement

Installation path

Software packs

Confirmation

Installation progress

Select files to review

8 (optional)

Readme file

Startup script

Click Next to continue installation.

Read and accept the Transporter license agreement.2

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.

Using the check boxes, select the software packs you want installed. Packs listed without check boxes are automatically included with those you select.

Confirm that the installation path and software packs displayed are correct. If not, you can go back to the previous screens and make corrections.

When the installer displays Finish, click Next to continue.

Make a selection on this screen to review the Readme file and/or the startup script file when the installation is complete.

The Readme file contains any last-minute release specific instructions for:

• The Spring framework

• Transporter

The startup script create environment variables for the Transporter environment.9 (optional)

Finish

Confirmation messages. Click Done to exit the installation program.

Client Directory Structure

Installation createsthe environment variable NVTHOME for the default location of the Transporter 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:

ContentsDirectory

All binary executables/bin

Libraries, static and dynamic and java class library/lib

License files, system control files, framework configuration files/conf

Message catalogs or locale specific properties files/mesg

Application message log directory/log

Client extensions to Transporter — operation extension/extensions

Utilities/utils

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

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 intermediate place for data before it is processed 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 to find the filename and drop the leftover staging table. The staging 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 the control file. For an extract operation, you also supply a filename prefix for the target filenames, 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

Transporter streamlinesthe process for loadoperations with hash-partitioned tablesby 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.

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

Transporter Job Statistics on the Neoview Platform

Transporter metadata tables on the Neoview platform maintain information and statistics about 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 is added to or subtracted from the current column value. Use the source and target field mappings 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

In addition to reflexive updates, Transporter provides for updating Neoview SQL columns with 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) section of the control file to have Transporter generate data for identity columns. See Example A-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 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.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 stores encrypted words in a security file. Only the administrator for the Transporter client platform can run this utility.

NOTE: Transporter always transmits passwords in encrypted format. When control files are

saved on the Neoview platform, all plain text passwords are encrypted. You can choose to encrypt user IDs or not. If you do not encrypt them, user IDs are stored in the ctlprops.txt file as plain text.

Encryption Files

These files are used by the encryption utility and are stored in the $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

encctlprops.txt

ctlprops.txt

CAUTION: The encryptionfiles must be properly secured by the administrator of the Transporter

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

Encrypted words

Unencrypted words (plain text)

ExampleFile ContentFilename

neo1pass = {DES}5y9BTnIKJuw=

neo1user = root

The Encryption Utility — nvtencrsrv

nvtencrsrv is a utility that adds encrypted words to the password encryption file and deletes encrypted words when they are no longer needed. Only the administrator of the client platform can use this utility.

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

DescriptionOption

-o

-r

-p

-l

Default value: $NVTHOME/conf/security

-f

Default value: NVTLOGyyyy mm dd

-h

Sample Commands for the nvtencrsrv Utility

Table 4-3 Sample Commands for nvtencrsrv

Add an encrypted word to the file

./nvtencrsrv o add r refname p password Neo

Example:

./nvtencrsrv o add r p1 p Neo

Action to perform on the file, either add or delete an entry. Valid arguments:

• add

• del

Reference name for the add or delete action.

Plain text word tobe encrypted — valid only for the add option.

Log file directory

Log file name

Display the help text

ResultForm of the CommandAction

1. p1 is encrypted.

2. p1 is entered in the file encctlprop.txt,

in the form:

p1 = {DES}6rOGfryxsWk=

Remove a reference name

Display the help message

./nvtencrsrv o del r refname

Example:

./nvtencrsrv -o del -r p1

./nvtencrsrv or

./nvtencrsrv –h

1. p1 is removed from the file

encctlprop.txt.

2. A success message is displayed.

The list of options for nvtencrsrv is 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.

The options section sets global options for all jobs controlled by a control file. 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

OptionsControl File Section

N.A.“Version” (page 27)

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

“Dataformats” (page 32)

“Maps” (page 36)

Formats in which data types can be represented. This control file section has no unique options.

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

OptionsControl File Section

“Sources” (page 40)

“Jobs” (page 42)

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)

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)

Version

“Comments” (page 44)

“Include” (page 45)

version versionnumber;

Comments you want to include in the file. This control file section has no unique options.

Includes a control file segment in this file. This control file section has no unique options.

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 components are separated by a period (.). Only the major version is required. The components of the version number correspond to the major, minor, and patch versions of the Transporter product, respectively. If Transporter sees an incompatible version number in the control file,

Control File Organization and Syntax 27

Options

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

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

One or more occurrences of option-name,separated by a comma (,). The last option-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

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

Defines the pattern for this field. For a load operation, formatstring tells Transporter how to interpret source file values. For an extract operation, formatstring tells Transporter how to write target file values.

• Character data types

format_definition [, format_definition ]...

formatname datatype formatstring

Specify “default”. It is the only format supported.

• Integral data types — decimal — values are interpreted as decimal values with an optional leading sign

character

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

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 portion of the value from the fractional portion. For example, the value -10.5 can be 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

+ 80 hidden pages