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

— space — tab — comma (,) — semicolon (;) — slash (/) — backslash (\\)

Period (.) is the only supported separator between integral and fractional seconds.

The following are defaults and examples: — Date values

◦ Default: %C%y-%m-%d ◦ Example: The input characters “2007–10–05” can be processed without a format

definition and is interpreted as October 5, 2007.

— Time values

◦ Default: %H:%M:%s ◦ Example: The input characters “13:32:00” can be processed without a format

definition and is interpreted as 1:32 p.m.

— Timestamp values

◦ Default: %C%y-%m-%d %H:%M:%S ◦ Example: The input characters “2007–10–05 13:32:00.000000” can be processed

without a format definition and is interpreted as 1:32 p.m on October 5, 2007.

• Interval data types

Interval data types require a formatstring.

There is no default for interval fields.

These field specifiers are allowed:

— For year-month ranges:

◦ %Y — year ◦ %M — month

— For day-time ranges:

◦ %D — day ◦ %H — hour ◦ %M — minutes ◦ %S — seconds (can include fractional seconds)

The values for specifiers must be positive integers.

Specifiers must appear in order from day to seconds. For example, %M (minutes) cannot precede %H (hours).

Negative interval values always have a minus sign to the left of the integers, with no spaces between the minus sign and the integers.

The supported field delimiters are: — hyphen (-) for year-month ranges — colon (:) for day-time ranges

Examples are: — To specify a formatstring for a year-month duration of 3 years and 2 months

formatted as “3-2”:

“%Y-%M”

— To specify a formatstring for a year-month duration of 10 years formatted as

“10”:

Control File Organization and Syntax 31

“%Y”

— To specify a formatstring for a day-time duration of 30.5 seconds formatted as

“30.5”:

“%S”

— To specify a formatstring for a day-time duration of 1 day, 2 hours, 3 minutes,

and 4 seconds formatted as “1:2:3:4”:

“%D:%H:%M:%D”

— The following formatstring is invalid because fields are not specified in order

from day to seconds:

“%H:%M:%D:%S”

Table 5-2 Typeformats: format_definition Date/Time Format String Examples

ExampleDescription

Specify a formatstring for a date field without field delimiters, in the format of "20070131"

Specify a formatstring for a date field with hyphen delimiters, in the format of "2007-01-31"

Specify a formatstring for a timestamp field with mixed delimiters, in the format of "10/1/2007 12:45"

Two typeformats of datatype date, with formats of "20070131" and "2007-01-31", respectively.

Dataformats

The dataformats section describes the data format of sources and targets. Format definitions are for delimited, fixed-length, or Neoview SQL table format.

dataformats

{

format_definition [, format_definition ]...

}

format-definition:

delimited-format | fixed-length-format | SQL-table-format

"%C%y%m%d"

"%C%y-%m-%d"

"%m/%d/%y %H:%M"

typeformats { formatname date "%C%y%m%d", formatname date "%C%y-%m-%d" }

format-definition

One or more occurrences of format-definition, separated by a comma (,). The last format-definition should not use the comma separator.

32 Control File Organization and Syntax

Dataformats: Delimited Format

formatname delimited

[options format-options]

[fields delimited by character] [records separated by character] [optionally qualified by character]

field-definitions

format-options

(

option_name = option_value [, option_name = option_value ]...

)

formatname

The name you choose for this dataformat. The name must be unique within the dataformats namespace. formatname is case-insensitive and cannot exceed 64 bytes.

options format-options

“crossoveryear” (page 47)

fields delimited by character

defines the character separating each data field.

For information, see “Field Delimiter Character” (page 64).

records separated by <nl> [ <nl> ...]

Defines the character sequence separating each data record. The only allowed record separator is one or more <nl>'s.

For information, see “Record Separator Character ” (page 64).

optionally qualified by {' | "}

Specifies a quoting character for each data field.

For information, see “Quote Processing For Load Operations” (page 65)

field-definitions

See Dataformats: Field Definitions for Source and Target (page 34).

Dataformats: Fixed Length Format

In a fixed-length record file, the width of each field in the record is consistent across all records. Records are separated by one or more system EOL characters.

formatname fixed

[options format-options]

record length number fixed-field-definitions field-definitions

format-options

(

option_name = option_value [, option_name = option_value ]...

)

formatname

The name you choose for this dataformat. The name must be unique among existing dataformats namespace. formatname is case-insensitive and cannot exceed 64 bytes.

Control File Organization and Syntax 33

options format-options

“crossoveryear” (page 47)

record-length number

Specifies the length of a data record in unit of characters.

For example, each record is 256 characters:

record length 256

Dataformats: Fixed Field Definitions

The fixed-field-definitions list defines the offset for each data field in order. It begins with keyword fields positions followed by a parenthesized list of offsets in units of characters. Offsets are 1–based, meaning the first character of a record is at position 1. Offsets are separated by commas.

These additional rules apply:

• Offsets must be listed in increasing order.

• The first offset can be greater than or equal to one.

• No two offsets can be equal.

field-definitions

See Dataformats: Field Definitions for Source and Target (page 34).

fields positions

( field1_offset, field2_offset, ... fieldn_offset )

Dataformats: Neoview SQL Table Format

formatname sql fields

fields-definition

formatname

The name you create for this delimited dataformat. The name must be unique among existing dataformats.

Dataformats: Field Definitions for Source and Target

field-definitions: (

field-definition [, field-definition ]... ) field-definition: fieldname datatype [format formatname] [options field-options]

field-options

(

option_name = option_value [, option_name = option_value ]...

)

field-definition

One or more occurrences of field-definition, separated by a comma (,). The last field-definition should not use the comma separator.

34 Control File Organization and Syntax

fieldname

The name you select for the field of the format. If the format is SQL table, the fieldname must be one of the column names of the associated SQL table or query. Otherwise, the fieldname must be unique within this record format.

When a Neoview SQL table format is used for an extract operation, the following rules and considerations apply:

• For table sources, fieldname must be one of the column names of the associated table.

• For Neoview SQL sources (where the extract source is a database query): — fieldname must be one of the 1-part column names of the associated query.

That is, fieldname must be a simple column name only without any table qualifier. For example, for a column named ADDRESS from a table named EMPLOYEES:

fieldname must have the value: ADDRESS

fieldname cannot have the value: EMPLOYEES.ADDRESS

• If multiple columns of the query have names matching fieldname, the field-definitions is assumed to refer only to the first column position where

a matching name is found. Subsequent columns with the matching name are not accessible within this dataformat.

There are two workarounds for this problem: — Derived columns of the query, for example, the results of Neoview SQL

expressions and aggregate functions, by default are identified by the name “(EXPR)”. If multiple derived columns are produced by a single query, use an AS clause in the query text to associate a name with each derived column. For example:

SELECT max(a) as MAX_A, min(a) as MIN_A FROM myTable

— If multiple table columns produced by the query have the same 1-part column

name, use an AS clause in the query text to associate unique names with those columns. For example, if tables T1 and T2 both have columns named A and B:

SELECT T1.B as T1_B, T2.B as T2_B FROM T1, T2 WHERE T1.A = T2.A

For SQL table format, if fieldname is not double-quoted, it is interpreted as an HP Neoview SQL regular identifier. If fieldname is double-quoted, it is interpreted as an HP Neoview SQL delimited identifier.

Delimited identifiers differ from regular identifiers in many ways, including:

• Delimited identifiers are case-sensitive.

• Spaces within a delimited identifier are significant except for trailing spaces.

• You can use reserved words as delimited identifiers.

See the HP Neoview SQL Reference Manual for details on SQL identifiers.

datatype

can be one of the supported data listed in “Supported Data Types” (page 45).

For Neoview SQL table formats, datatype must be one of:

• the data type of the associated table or query column

• varchar

Control File Organization and Syntax 35

Maps

Only the simple data type name should be used. For example, if a table column named FIRSTNAME is of data type varchar(8), you should specify “FIRSTNAME varchar” instead of “FIRSTNAME varchar(8)”.

format formatname

one of the typeformats you defined in the typeformats section. This is optional and not applicable for SQL columns.

options field-options

Defines options for the field. These field-options are supported:

• “crossoveryear” (page 47)

• “nullstring” (page 51)

The maps section describes how one data format is mapped to another when data is moved from source to target.

maps

{ map-definition [, map-definition ]... } map-definition: mapname from sourceformat to targetformat

field-mappings: (

field-mapping-definition [, field-mapping-definition ]...

)

field-mapping-definition: fieldname_target [ = field-source-ref] [options field-mapping-options]

field-source-ref: { fieldname_source [add | subtract] |

}

field-mapping-options: (

option_name = option_value [, option_name = option_value ]...

)

map-definition

One or more occurrences of map-definition, separated by a comma (,). The last map-definition should not use the comma separator.

mapname

The name you create for the map. The name must be unique within the maps namespace.

mapname is case-insensitive and cannot exceed 64 bytes.

sourceformat

The source dataformat as defined in “Dataformats” (page 32).

For a load operation, sourceformat can be delimited or fixed-length dataformat.

For an extract operation, sourceformat must be an SQL table dataformat.

36 Control File Organization and Syntax

targetformat

The target dataformat as defined in “Dataformats” (page 32).

For a load operation, targetformat must be an SQL table dataformat.

For an extract operation, targetformat can be delimited or fixed-length dataformat.

field-mapping-definition

One or more occurrences of field-mapping-definition, separated by a comma (,). The last field-mapping-definition should not use the comma separator.

fieldname_target, fieldname_source

fieldname_target and fieldname_source must refer to names defined in the source dataformat and targetdataformat, respectively. For more information about dataformats, refer to “Dataformats” (page 32).

For a simple mapping, where the source field and target field are the same and there are no specific map functions required (for example, expressions and identity columns), field-source-ref can be omitted.

If a field defined in the target dataformat is missing from the map’s field-mappings section:

For a load operation, Transporter does not include the missing field in any SQL operations.

For a load operation, the following rules apply:

• A field-mapping-definition must be specified for all primary key columns.

• If a field-mapping-definition is omitted for a non-primary key column, no changes are applied to that column when updating rows and the SQL default value is used for that column when inserting a new row.

For an extract operation where the target format is delimited, Transporter does not include the missing field in the output records.

For an extract operation where the target format is fixed-length, Transporter includes the missing field in all outputrecords and its value always consists entirely of space characters.

fieldname_source_ref

For an extract operation, fieldname_source_ref must be one of the following:

• fieldname_source without the add or subtract option

• NULL

• constant

fieldname_source_ref can be of one of the following forms:

fieldname_source

Data from this field of the source is assigned to the fieldname_target field of the target.

This table lists the acceptable datatype mappings:

Table 5-3 Datatype Mappings

Can be Mapped to a Target Field of Type...Source Field of Type...

any typeany character type

any integral type

any floating-point type

any character type any integral type

any floating-point type

any character type any integral type

any floating-point type

Control File Organization and Syntax 37

Table 5-3 Datatype Mappings (continued)

Can be Mapped to a Target Field of Type...Source Field of Type...

date

time

timestamp

any character type date

timestamp

any character type time

timestamp

any character type date

time

timestamp

intervalinterval

For interval mappings, both fieldname_source and fieldname_target must have an associated typeformat. Both typeformats must either be year-month, or day-time ranges.

[add | subtract]

are used for reflexive update and apply only to update and upsert operations. When the job operation is “insert” and one of these options is specified, the operation becomes “upsert”.

These options are not supported for extract operations.

These options are not allowed on primary key columns.

If add or subtract is specified, the new value of the target field is:

old value of the target field +/- value of the source field

These options are only valid for integral data types.

NULL

A NULL value goes in the target field. For load operations, NULL is not allowed on primary key columns.

Example:

INFO = NULL

assigns NULL to the target field called INFO.

default

The default value for the column of table is used at loading. This applies when the target format is an SQL table format. This option is not allowed on primary key columns and is not supported for extract operations.

constant constant_value

constant means the constant_value goes to the assigned target field.

Encloseconstant_value in double quotes ("").

Example:

INFO = constant "N/A"

assigns the string “N/A” to the target field INFO.

identity (system | loader)

A keyword to specify that the target field takes on a unique value in each record. The unique values are generated by Transporter. The only option supported is loader. If system is specified, Transporter acts as though loader is specified. identity map entries cannot be used as condition fields for update and upsert operations. See the

38 Control File Organization and Syntax

discussion of the condition option below for more detail. This option is not supported for extract operations.

• system — the Neoview system generates the key

• loader — the loader generator is used

Example:

EMP_ID = identity loader

Asks the loader to generate a key for target field EMP_ID.

scalar-expression

A simpleexpression composed of a fieldname_source, constant,and SQL functions connected by simplearithmetic operators.This expression must be syntactically valid for fitting into a corresponding SQL insert, update, or upsert statement. The maximum length of a scalar-expression is 256 characters. This option is not supported for extract operations and is not allowed on primary key columns.

field-source-ref

For an extract operation, field-source-ref must be one of the following:

• fieldname-source (without the add or subtract option)

• NULL

• constant

field-mapping-options

A list of options. condition = “true” | “false”

This option is only applicable for load operations where the operation type is update or upsert. If condition is true, this field becomes part of the conditions in the WHERE clause of the generated SQL statement.

This option is not allowed for an identity field mapping.

The default is “false” for non-key columns and “true” for key columns.

update = “true” | “false”

This option is only applicable for load operations where the operation type is update or upsert. If update is true, this field becomes part of the UPDATE clause of the generated SQL statement

This option cannot be set to “true” for primary key columns.

The default is “false” for key columns and “true” for non-key columns.

The condition and update options are ignored for insert operations.

The condition and update options cannot both be “true” for a field mapping unless it is a reflexive update field mapping.

Examples of Field Mapping

• Assigns source field FIRST_NAME to target field FIRSTNAME:

FIRSTNAME = FIRST_NAME

• Adds the value of source field VALUE_SOURCE to the current value of target field

VALUE_TARGET:

VALUE_TARGET = VALUE_SOURCE add

• Assigns NULL to the target field INFO:

INFO = NULL

• Assigns a constant string"N/A" to the target field INFO:

INFO = constant "N/A"

• Asks Transporter to generate unique key values for the target field EMP_ID:

Control File Organization and Syntax 39

Sources

EMP_ID = identity loader

• Increments the source field VALUE_SOURCE by 10 and assigns the resulting value to the

target field VALUE_TARGET:

VALUE_TARGET = expression ":VALUE_SOURCE + 10"

The sources section describes the location of data source and target, and their provider options.

sources

{

source-definition [, source-definition ]...

}

source-definition:

specific-source [, options source-options ]

specific-source

sourcename file filename | sourcename pipe pipename | sourcename {odbc | jdbc} {sql sqltext|table tablename} |

sourcename jms jmsname

source-definition

One or more occurrences of source-definition, separated by a comma (,). The last source-definition should not use the comma separator.

sourcename

A nameyou create for the source. The name must be unique within the sourcesnamespace. sourcename is case-insensitive and cannot exceed 64 bytes.

sourcename file filename [options source-options]

Specifies a data file as the data source/target. filename specifies the location of the file, including both the path and the file name.

Transporter assumes the data file is uncompressed.

Example:

Specify a plain data file, datafile_1.dat, in the current directory with source name

data1:

data1 file "./datafile_1.dat"

sourcename pipe pipename [options source-options]

Specifies a named pipe as the data source/target. pipename is the name of the pipe.

sourcename { odbc | jdbc } { sql sqltext | table tablename } [options source-options]

Specifies an SQL table or query accessed through an ODBC or JDBC data connection to the Neoview system.

odbc, jdbc, sql, and table are keywords.

The Java client does not support ODBC sources.

Neoview SQL views and synonyms are not supported for JDBC table extracts.

tablename is a one-part or two-part table name in the format [schema].table. For a load operation, tablename can be a synonym or insertable view.

If schema or table is double-quoted (""), it is interpreted as an HP Neoview SQL delimited identifier. If schema or table is not double-quoted, it is interpreted as an HP Neoview SQL regular identifier.

Delimited identifiers differ from regular identifiers in many ways, including:

40 Control File Organization and Syntax

• Delimited identifiers are case-sensitive.

• Spaces within a delimited identifier are significant except for trailing spaces.

• You can use reserved words as delimited identifiers.

See the HP Neoview SQL Reference Manual for details on SQL identifiers.

For an extract operation, the sql option can be used to specify a SELECT query instead of a table name. The sql option cannot be used for a load operation.

When a SELECT query is specified for an extract operation, sqltext must be a double-quoted ("") string. The string can span multiple lines. If double-quote characters are required in the query text, they can be preceded by a backslash (\).

For example, a SELECT query spans multiple lines and includes a delimited schema name that requires double-quoting:

extract source odbc sql "SELECT * FROM \"SQL\".myTable WHERE myColumn > 10"

Considerations for an extract operation where the sql option is used and parallelstreams is greater than one:

• If the value of parallelstreams exceeds the number of configured CPUs on the

Neoview system, Transporter logs a warning message and performs the extract operation as if parallelstreams was set to the number of CPUs.

• There are certain queries that cannot be parallelized by SQL. If the specified query cannot be parallelized, Transporter logs a warning message and performs the extract operation as if parallelstreams was set to 1.

• SQL constructs that cannot be parallelized include: — embedded updates or deletes — rowset parameters — [FIRST/LAST N] syntax — SQL table-valued functions — stream access to tables

Examples

• Specify a source named “table” to access SQL table sch.dept:

table1 odbc table sch.dept

• Specify a source named “table2” to access an SQL table with a delimited schema and table name:

table1 odbc table "SQL"."MyTable"

• Specify a source names “query1” to access an SQL join query:

query1 jdbc sql "select * from sch.dept where number_of_employees > 10"

sourcename jms jmsname [options source-options]

This specifies a Java Messaging Service (JMS) source. jmsname is a JMS name.

source-options

one or more occurrences of source-options, separated by a comma (,). The last source-options should not use the comma separator.

These options can be specified at the source level or provided for all sources as global options:

Options for file sources:

• “forcestaging” (page 49)

• “multipart” (page 50)

• “sorted” (page 53)

Control File Organization and Syntax 41

Options for pipe sources:

• “endseq” (page 48)

• “forcestaging” (page 49)

• “multipart” (page 50)

• “retries” (page 53)

• “sorted” (page 53)

• “startseq” (page 53)

• “tenacity” (page 54)

• “timeout” (page 54)

Options for odbc and jdbc sources:

• “datasource” (page 47)

• “noaudit” (page 51)

• “parallelstreams” (page 52)

• “password” (page 52)

• “retries” (page 53)

• “system” (page 54)

• “tenacity” (page 54)

• “url” (page 54) (for jdbc sources only)

• “user” (page 55)

Options for jms sources:

• “password” (page 52)

• “retries” (page 53)

• “tenacity” (page 54)

• “timeout” (page 54)

• “url” (page 54)

• “user” (page 55)

Jobs

The jobs section specifies a list of Transporter jobs. A job is a collection of job entries. Jobs are processed serially, one at a time. This section starts with the keyword jobs followed by a list of job definitions surrounded by curly braces.

42 Control File Organization and Syntax

The Java client accepts control files containing multiple jobs.

jobs

{

job-definition [{job-definition ] ...

}

job-definition: jobname { load | extract }[options job-options] { job-entry-list }

job-options: (

option_name = option_value [, option_name = option_value

)

job-entry-list:

job-entry [, job-entry ]...

job-entry:

source sourcename target { targetname | ( targetname-list )}

map mapname [ options job-entry-options ]

targetname-list:

targetname [, targetname ]...

job-entry-options

( option_name = option_value [, option_name = option_value ] ... )

job-definition

one or more occurrences of job-definition, separated by a comma (,). The last job-definition should not use the comma separator.

jobname

The name you choose for this job. jobname must be unique within the jobs namespace. jobname is case-insensitive and cannot exceed 64 bytes.

load | extract

“load” or “extract” means this job is a load job or an extract job, respectively. A load job is used to load data into SQL tables, while an extract job is used to extract data from SQL tables.

options

one or moreoccurrences ofjob-options, separated by a comma (,). The last job-options should not use the comma separator.

The options you define at the job level override global options defined in the options section. The following job options are supported:

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

Control File Organization and Syntax 43

job-entry-list

one or more occurrences of job-entry, separated by a comma (,). The last job-entry should not use the comma separator.

This specifies a list of job entries for the job. Each entry specifies its own source, target, and a mapping between them.

Each job entry within a job must specify a unique (sourcename, targetname) pair.

For a load operation, if the parallel option is true and multiple job entries specify the same target, every mapname specified by those job entries must either be the same, or must use the same targetformat and identical field-mappings.

source sourcename

Specifies the source of data for the job entry. sourcename refers to a source defined in the sources section.

For a load operation, the following source types are allowed:

• file

• named pipe

• JMS (Java client only)

For an extract operation, the following source types are allowed:

• JDBC (Java client only)

target targetname

Specifies the target of data for the job entry. targetname refers to a source defined in the sources section.

For a load operation, the following target types are allowed:

• JDBC (Java client only)

For an extract operation, the following target types are allowed:

• file

• named pipe

map mapname

options job-entry-options

Comments

You can embed single-line and multiple-line comments anywhere in a control file.

• A single-line comment starts with “#” and ends with the line terminator. For example,

• A multiple-line comment is surrounded by “/*” and “*/”. For example,

Specifies a mapping between the source and target. mapname refers to a map defined in the maps section.

one or more occurrences of job-entry-options, separated by a comma (,). The last entry-option should not use the comma separator.

Options defined at the job entry level override global options defined in the options section and override options defined at the job level. The following job entry options are supported:

• “baddatafile” (page 47)

• “faileddatafile” (page 49)

# This is my comment.

/* Your comment line 1 Your Comment line 2 ... */

44 Control File Organization and Syntax

Include

You can include one or more control file fragments from other control files. A control file fragment is a control file without a version number. You can define common sections in a control file fragment and include them from other control files. This avoids having duplicate definitions and makes it easier to manage your control files. Be aware that you cannot have duplicate definitions between included files and the main control file, otherwise the control file is rejected. HP recommends that you use control file fragments for reusable definitions and include them in control files as needed.

Use the %include statement to include a control file fragment from another file. For example:

%include "included_controlfile.txt"

When the included filename does not begin with an absolute or relative file system path, Transporter first looks for the file in the current directory. If the file is not found, Transporter looks in the directory $NVTHOME/conf. If the file is again not found, Transporter returns an error.

Supported Data Types

Transporter supports these data types:

• Character types

— char[acter] — varchar

• Integral types

— byte — int[eger] — byteint — smallint — largeint — longint — numeric — decimal

• Floating point types

— real — float — double

• Date/time types

— date — time — timestamp — datetime

• Interval types

— interval

Supported Data Types 45

6 Control File Options

baddatafile

baddatafile = filename

The default value is current-working-directory/badrecord.dat.

For a load operation, this option specifies where to put source records that fail internal processing before being written to the database. For example, a record that only contains 6 fields while 8 fields are expected.

Load records written baddatafile appear in the same format as the source file.

For an extract operation, this option specifies where to put records retrieved from the Neoview database that could not be properly written into the target format. For example, the database value is a string of ten characters but a fixed-width format of only eight characters is specified for the target file.

The record format for extract records written to baddatafile are:

• Delimited format

• Field separator is comma

• Record separator is the system EOL separator

• Quoting character is a double-quote

commitsize

commitsize = number

The default value is 100,000.

This option is used for a load operation. It specifies the number of source rows to be processed in a single transaction.

Some loads are non-transactional. This option does not apply to non-transactional loads.

number must be an integer greater than zero.

This option is ignored for extract operations.

crossoveryear

crossoveryear = number

There is no default value. If crossoveryear is not specified, this option is ignored.

Applies only to date values in source records for a load operation. This option is ignored for extract operations.

When a date value is to be interpreted according to a data format specified in the control file and the data format uses a two-digit year instead of a four-digit year, the crossoveryear specifies the year the century changes from 20 back to 19.

number must be an integer in the range from zero to 99.

For example, to specify that all two-digit years below 50 be under the century 20, and all years greater than or equal to 50 be under century 19:

crossoveryear = 50

datasource

datasource = "datasource_name"

The default value is “Admin_Load_DataSource”. Enclose the data source name in double quotes (").

baddatafile 47

Specifies the data source name for ODBC and JDBC connections to the Neoview system.

datasource_name must be a server-side data source defined on the Neoview system.

Example:

datasource = "LOAD01"

Specifies a data source named LOAD01.

deleteonerror

deleteonerror = "true" | "false"

The default is “false”.

For an extract operation: when this option is set to “false”, Transporter does not delete target files or intermediate files when the operation terminates due to an error.

For an extract operation: when this option is set to “true”, Transporter deletes target files and intermediate files when the operation terminates due to an error.

See the description of the “multipart” (page 50) option for more detail on intermediate files created by Transporter.

This option is ignored for load operations.

For extract operations where the target is a named pipe, this option applies only to intermediate files. Transporter never attempts to delete target pipes.

discards

discards = number

The default value is 1000.

• For a job where the “parallel” (page 52) option is “true”:

When the number of records written to the baddatafile file for the job reaches number, Transporter stops processing the job.

• For a job where the “parallel” (page 52) option is “false”:

When the number of records written to the baddatafile file for a job entry reaches number, Transporter stops processing the job entry.

If number is zero, this option is disabled. Transporter does not stop processing a job or job entry based on the number of discarded records.

Example:

discard = 2000

Tells Transporter to stop the job after 2000 discards.

enabletriggers

enabletriggers = "true" | "false"

The default is “false”.

If you want to enable triggers on a table during load jobs, set this option to “true”.

If you want to disable triggers on a table during load jobs, set this option to “false”.

This option is ignored for extract operations.

endseq

endseq = "sequence of characters"

endseq, along with the “startseq” (page 53) option, are specific to named pipe sources and allow

specification of start and end sequence records.

48 Control File Options

errors

This is an optional feature to allow data synchronization between Transporter and your applications.

The start and end sequence records are considered “substitute” records. Whenever one of these records is written to the pipe (either by Transporter during an extract operation, or by the application during a load operation), it is terminated by an appropriate record separator.

sequence of characters should not contain a record separator.

For a load operation when a pipe is read, Transporter discards any records read until the startseq record is seen. Transporter stops reading records once the endseq record has been seen.

For an extract operation, Transporter writes the startseq record into the pipe before the first data record. After all records have been written, the endseq record is written. The endseq record is written even if errors are encountered.

errors = number

The default value is 1000.

• For a job where the “parallel” (page 52) option is “true”:

When the number of records written to the “faileddatafile” (page 49) file for the job reaches number, Transporter stops processing the job.

• For a job where the “parallel” (page 52) option is “false”:

When the number of records written to the “faileddatafile” (page 49) file for a job entry reaches number, Transporter stops processing the job entry.

If number is zero, this option is disabled. Transporter does not stop processing a job or job entry based on the number of failed records.

This option is ignored for extract operations.

faileddatafile

faileddatafile = filename

The default value is:

current-working-directory/failedrecord.dat

For a load operation, this option specifies where to put source records that have a valid format but could not be written to the Neoview database. For example, a record that fails a data conversion step or violates a uniqueness constraint.

Load records written to faileddatafile appear in the same format as the source file.

This option is ignored for extract operations.

forcestaging

forcestaging = "true" | "false"

The default is “false”.

For a file or named pipe load source, this option specifies whether rows should be written to internally-managed staging tables on the Neoview system before populating target tables. If the

“operation” (page 52) option is not “insert”, the forcestaging setting is ignored and the

option is implicitly set to “false”.

This option is ignored for extract operations.

errors 49

This option can be specified at the global level or for individual file or pipe sources that are part of a load operation. When the “parallel” (page 52) option is set to "true" for a given job, all sources within the job must have the same forcestaging setting.

For a load source where the “operation” (page 52) option for the job is “insert”, the forcestaging and “noaudit” (page 51) options cannot both be "true". If the “noaudit”

(page 51) option is "true", the forcestaging option must be “false”, otherwise an error is

returned.

Because the operation is non-transactional, the “commitsize” (page 47) option is ignored.

When staging tables are used for an “insert” operation:

• Each target table has its own staging table.

• The names of staging tables are generated by Transporter and written to the Transporter

• Source rows are first written to staging tables. When all source rows for a given target table

• Jobs using staging tables cannot be recovered if the failure occurs during the staging table

multipart

multipart = "true" | "false"

The default is “false”.

log.

have been written to the staging table, Transporter populates the target table.

to target table row transfer.

For an extract operation when this option is set to “false”:

• Transporter uses the value of “parallelstreams” (page 52) to determine how many internal

data streams to manage and records from all internal streams are merged into a single target file or pipe.

• Rows in the target file or pipe do not appear in any particular order. If the extract source is

a Neoview SQL query containing an ORDER BY clause, the specified ordering is not maintained.

• If the extract target is of type file and parallelstreams is greater than one, Transporter

first writes records to a collection of intermediate files and then merges these files into the single target file.

• The number of intermediate files is determined by the parallelstreams option. The

names of the intermediates files are:

name-of-target-file.NNNN

Where NNNN is a zero-padded four digit integer.

For example, if the specified target file is “extractData” and the value of

parallelstreams is 4, the following intermediate files are used:

extractData.0001 extractData.0002 extractData.0003 extractData.0004

For an extract operation when this option is set to “true”:

• Target file or pipe names in the control file are interpreted as a prefix only.

• If the extract source is a Neoview SQL query containing an ORDER BY clause, each target

file or pipe is ordered accordingly. However, there is no guaranteed ordering relationship among the different targets. For example, if the ORDER BY list is a single integer column, values for the ordering column might be [1,3,5] in one target and [2,4,6] in another.

• Transporter generates a unique suffix for each actual target file or pipe. The number of actual

targets is determined by the parallelstreams option.

The suffix is:

50 Control File Options

noaudit

.NNNN

Where NNNN is a zero-padded four digit integer.

For example, if the specified prefix is “extractData” and the value of parallelstreams is 4, the following targets are used:

extractData.0001 extractData.0002 extractData.0003 extractData.0004

This option is ignored for load operations.

noaudit = "true" | "false"

The default is false.

For a load operation, this option specifies whether Transporter should use non-transactional database operations for improved performance. If the “operation” (page 52) option is not “insert”, the noaudit setting is ignored and noaudit is implicitly set to false.

If parallel = true for a job, all targets in that job must have the same setting for noaudit.

This option is ignored for extract operations.

If noaudit is true:

• The “sorted” (page 53) option must be true.

• The “truncate” (page 54) option must be true.

• The “forcestaging” (page 49) option must be false.

• An error is returned if any of the above conditions are not met.

If noaudit is false:

• The forcestaging option determines whether Transporter uses transactional or

nullstring

nullstring = "character-string"

There is no default value for a load operation or an extract operation.

This option specifies how to interpret NULL values. This option can be specified as a global option or in a field definition.

Leading and trailing white space characters are removed from character-string before any NULL value processing is performed.

character-string is allowed to be an empty string or to consist entirely of white space characters. Both are equivalent because leading and trailing white space are always removed.

The length of character-string cannot exceed 64 characters.

For a load operation:

• If an input field matches character-string, the field is interpreted as a NULL value.

• For delimited data formats, if nullstring is not specified, only an empty input field

• For fixed-width data formats, if nullstring is not specified, it is not possible to represent

non-transactional database operation. See “forcestaging” (page 49) for details.

Leading and trailing white space in the input field are ignored in this comparison.

containing no white space is considered a NULL value. For example, when two consecutive field delimiters appear, this indicates a NULL field value. An input field containing only white space is not considered a NULL value. It is considered an empty string if the corresponding data type is a character type, otherwise the record is rejected.

NULL values in input records. A character field containing only white space is considered

noaudit 51

For an extract operation:

• This option specifies the character string that should be written to targets to represent NULL

• For delimited data formats, if nullstring is not specified, Transporter does not write any

• For fixed-width data formats, if nullstring is not specified, Transporter rejects any

operation

operation = "insert" | "update" | "upsert"

The default is “insert”.

For a load operation, this option specifies the type of Neoview SQL operation to be performed:

• “insert” — inserts data into a Neoview SQL table

• “update” — updates data in a Neoview SQL table

• “upsert” — updates data in a Neoview SQL table if the data exists, otherwise inserts data

This option is ignored for extract operations.

parallel

an empty string. For all other types, a field containing only white space is rejected and the record is written to the “baddatafile” (page 47) file.

values.

data characters to the target field to represent a NULL value.

retrieved record containing NULL values and writes the record to the baddatafile file.

parallel = "true" | "false"

“true” — all job entries of a given job execute in parallel

“false” — all job entries of a given job execute serially

The default is “false”.

parallelstreams

parallel = number

This option specifies the number of data connections Transporter establishes for accessing the Neoview database.

number must be an integer greater than or equal to one.

For a load operation or a TABLE extract operation:

• Transporter internally limits the maximum value of parallelstreams to the number of

partitions for the table.

• If this option is not specified, Transporter establishes one data connection for every four

partitions of the table.

For a Neoview SQL extract operation:

• Transporter internally limits the maximum value of parallelstreams to the number of

CPUs on the Neoview system.

• If this option is not specified, Transporter establishes one data connection for every four

CPUs on the Neoview system.

password

password = "password" | ${referencename | $E{referencename}

This option is required.

52 Control File Options

password specifies the password to use when connecting to a JDBC, ODBC, or JMS data source.

• password specifies a plain-text password.

• ${referencename} refers to a plain-text password in the plain property file. Transporter

• $E{referencename} refers to an encrypted password in the encrypted property file.

The maximum length of any plain text password is 64 characters.

retries

retries = number

The default value is 3.

This option specifies how many retry attempts Transporter should make to establish a database connection, open a JMS source, or open a named pipe on behalf of a job.

retries = number can be zero or a positive integer.

The “tenacity” (page 54) option specifies how long to wait on each attempt.

rowsetsize

rowsetsize = number_of_records

The value must be an integer in the range from 1 to 100000.

This option specifies the number of records in each batch of rows Transporter exchanges with the Neoview database.

If this option is not specified, Transporter chooses an optimized value based on the Neoview SQL table or query.

automatically substitutes the corresponding password when it processes the control file.

Transporter automatically substitutes the corresponding decrypted plain-text password when it processes the control file.

sorted

sorted = true | false

The default is false.

For a load operation, this option specifies whether source files are sorted by the table’s primary key. Transporter can improve performance and parallelism of certain database operations when data is sorted by the primary key.

Errors can be returned if sorted is true and input data is not properly sorted.

For a load operation where the “operation” (page 52) is “insert”, if the “noaudit” (page 51) option is true, the sorted option must also be true, otherwise an error is returned.

This option is ignored for extract operations.

startseq

startseq = "sequence of characters"

startseq, along with the “endseq” (page 48) option, are specific to named pipe sources and

allow specification of start and end sequence records.

This is an optional feature to allow data synchronization between Transporter and your applications.

sequence of characters should not contain a record separator.

retries 53

For a load operation when a pipe is read, Transporter discards any records read until the startseq record is seen. Transporter stops reading records once the endseq record has been seen.

system

system = "unqualified_Neo_systemname"

This option is required. There is no default value.

This option specifies the unqualified name of the primary segment on a Neoview system.

Example: to specify a Neoview system with the name neo0101.domain.com:

system = "neo0101"

tenacity

tenacity = number_of_seconds

The default is 15 seconds.

This option specifies how long, in seconds, Transporter should wait between attempts to establish a database connection, open a JMS source, or open a named pipe before retrying.

number_of_seconds can be zero or a positive integer.

The “retries” (page 53) option specifies how many retry attempts should be made for a given resource.

timeout

timeout = number_of_seconds

The default value is 60 seconds.

After a named pipe or JMS source has been successfully opened, this option specifies how long Transporter waits, in seconds, for a read or write operation to complete before timing out and returning an error.

number_of_seconds can be zero or a positive integer.

truncate

truncate = "true" | "false"

The default is “false” (do not delete data from the target).

This option specifies whether Transporter should truncate target tables (for a load operation) or target data files (for an extract operation) before job processing begins.

true means Transporter will delete all data from the target table or file before job processing begins.

false means Transporter will not delete data from the target before job processing begins.

For a load operation where the “operation” (page 52) is “insert”, if the “noaudit” (page 51) option is set to true then truncate must also be set to true otherwise an error is returned.

url

url = "urlname"

This option specifies an access point for:

54 Control File Options

user

• JDBC connections to the Neoview system

• JMS sources

There is no default value.

The Transporter Java client uses this URL for its internal database connections.

A JDBC connection URL must be of the form of:jdbc:hpt4jdbc://hostname:port/.

See the Neoview JDBC Type 4 Driver Programmer’s Reference for complete details on JDBC connection URLs.

Example: a JDBC connection URL for a Neoview system name neo0101.domain.com:

jdbc:hpt4jdbc://neo0101.domain.com:18650/

This option is not applicable to file or named pipe data sources.

This option is required for the Java client.

user = "username" | ${referencename} | $E{referencename}

This option is required. It specifies the username to use when connecting to a data source:

• username specifies a plain-text password.

• ${referencename} refers to a plain-text user name defined in the plain property file.

Transporter automatically substitutes the corresponding user name when it processes the control file.

• $E{referencename} refers to an encrypted user name in the encrypted property file.

Transporter automatically substitutes the corresponding decrypted user name when it processes the control file.

user 55

7 Load and Extract Operations

The Transporter Client performs load and extract operations based on the content of a control file. This chapter addresses the following topics:

“Steps for Performing Load and Extract Operations” (page 57) “Starting the Transporter Client ” (page 57) “Stopping the Client” (page 58) “The Transporter Job” (page 58) “Sources and Targets” (page 59) “Transaction Modes for Load Jobs” (page 60) “Loading Data With JMS Trickle Feed” (page 60) “Parallel Processing for Load Operations” (page 61) “Considerations for Extract Jobs” (page 62)

These are the steps for performing a data movement operation:

Steps for Performing Load and Extract Operations

Table 7-1 Steps for Load and Extract Operations

Look here for more informationDo ThisStep

Create a control file and put it in $NVTHOME/conf.

NOTE: You can use a control file in another

location. Simply fully-qualify the file name when you start the client.

operation.

Confirm completionof the operation by checking console messages and log files.

In case of failure, you can correct any problems and resubmit the operation using “ The

-recover Command Line Option” (page 68) .

Starting the Transporter Client

IMPORTANT: HP recommends running only one Transporter client at a time on the client

platform.

• The control file: “Control File Organization and

Syntax” (page 25) and “Control File Options” (page 47)

• Creating a control file:

“Control File Editor ” (page 97)

“Control File Generator” (page 99)

• “Control File Examples” (page 73)

• Ensuring that input data is properly formatted: “Data

For Load Operations” (page 63)

The documentation for your platform.Log on to your account on your client platform.2

“Starting the Transporter Client ” (page 57)Start the Transporter client and submit the

“Logs” (page 67)Troubleshooting: “Troubleshooting” (page 67)

Resubmitting the failed operation: “Starting the

Transporter Client ” (page 57)

Recovering failed jobs: “Job Recovery” (page 67)

Use these commands to start the Transporter client on Linux and Windows systems. The client stops automatically at the end of a job or if the job fails. Use the -run attribute to submit a job for the first time. Use the -recover attribute if a job using this control file has failed, you are resubmitting it, and you want Transporter to perform recovery on the job. For more information about -recover, refer to “ The -recover Command Line Option” (page 68).

Steps for Performing Load and Extract Operations 57

Starting the Client on Linux Systems

Use this shell command to start the Transporter Client. You specify the control file depending on where the file resides. If the control file is not found, Transporter exits with an error.

• The control file is in $NVTHOME/conf:

jtrans.sh {-run | -recover}MyControlFile.txt

• The control file is in the current directory:

jtrans.sh {-run | -recover} ./MyControlFile.txt

• The control file is in a directory you specify — in this case, my/trans/dir:

jtrans.sh {-run | -recover} /my/trans/dir/MyControlFile.txt

Starting the Client on Windows Systems

Type this at the Command prompt on a Windows system to start the Transporter Client. You specify the control file depending on where the file resides. If the control file is not found, Transporter exits with an error.

• The control file is in $NVTHOME\conf. This is the default.

jtrans.bat {-run | -recover} MyControlFile.txt

• The control file is in the current directory:

jtrans.bat {-run | -recover} .\MyControlFile.txt

• The control file is in a directory you specify — in this case, my/trans/dir:

jtrans.bat {-run | -recover} /my/trans/dir/MyControlFile.txt

Command prompt on a Windows system

1. Click Start –> Run

2. In the Open text box, type cmd.exe

Stopping the Client

The Transporter client stops automatically when a data movement operation directed by a control file has completed. If you need to abruptly stop a running data movement operation, use this command:

• For Linux: stopjtrans.sh MyControlFile.txt

• For Windows: stopjtrans.bat MyControlFile.txt

You can recover this operation later, unless the job to recover involves one of these:

• An extract operation

• A load operation using a staging table

• A load operation with the “noaudit” (page 51)

option set to true.

• A load operation using a named pipe or JMS trickle feed source.

For more information about job recovery, refer to “Job Recovery” (page 67).

The Transporter Job

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

A job entry is a unique source/target pairing consisting of:

58 Load and Extract Operations

• A source for the data, named in the “Sources” (page 40) section of the control file

• A target for the data, also named in the “Sources” (page 40) section of the control file

• The mapping information you provide in the “Maps” (page 36) section of the control file

(for example, the table columns in which to put the data)

NOTE: If the control file encounters a duplicate source/target pair, Transporter exits with an

error.

Several jobs make up a Transporter control file. For more information about the control file, see

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

Each job is a load or an extract with your desired control file options applied. A control file can have several load and extract jobs, but a job must be either load or extract. If a Transporter operation fails, the job that failed is the recoverable entity. For more information about job recovery, see “Job Recovery” (page 67).

Sources and Targets

Targets for load jobs and sources for extract jobs reside on the Neoview platform. These are the possible source and target combinations for load and extract jobs:

Table 7-2 Sources and Targets for Loading and Extracting Data

ExtractLoad

• Files

• Named pipes

• JMS Trickle Feed

One of these, existing on the Neoview platform:

• A Neoview SQL table

• An insertable view

• A synonym

For information about Neoview SQL tables, views, and synonyms, refer to the Neoview SQL Reference Manual.

Parallel Streams

The “parallelstreams” (page 52) option tells Transporter how many data streams to establish between the client and the Neoview platform. This section discusses how the use of parallel streams applies to load and extract jobs.

Parallel Streams for Load Jobs

Transporter manages the parallel streams and moves data to the appropriate columns in the target table. Each stream is assigned to a subset of the table partitions.

Parallel Streams for Extract Jobs

• An existing SQL table on the

Neoview platform

• A Neoview SQL query

Data resulting from the Neoview SQL query moves to the designated target.

TargetsSourcesTargetsSources

• Files

• Named pipes

If parallelstreams = 1, parallelism is disabled and Transporter creates one target file or pipe.

To perform a parallel extract to multiple files or pipes:

• Set “parallelstreams” (page 52) = n

• Set “multipart” (page 50) = true

Sources and Targets 59

CAUTION: If multipart = false, and parallelstreams is enabled, Transporter still

moves data off the Neoview platform in multiple streams. However, all the streams merge into one file or named pipe. Ordering is not maintained if the files are merged for an ORDER BY query. See “multipart” (page 50) for details.

You provide a base filename for the targets and Transporter creates the specified number of files or pipes with an incremental extension, (myfile.0001, myfile.0002, myfile.0003), up to the number of parallel streams.

NOTE: If you specified ORDER BY for a query extract operation, each parallel data stream is

ordered, even is there is only one stream. However, ordering is not guaranteed among the different streams. For example, if the ORDER BY list is a single integer column, values for the ordering column could be 1,3,5 in one stream and 2,4,6 in another.

Transaction Modes for Load Jobs

When a load job runs and the table operation is INSERT, Transporter selects one of two transaction modes, depending on the values for the options “forcestaging” (page 49) and “noaudit” (page 51). If the “parallel” (page 52) option is set to true, the entire job runs under one transaction mode. If individual job entries have settings that prevent them from running under the selected transaction mode, Transporter reports an error.

The transaction mode determines whether Transporter uses transactional database operations and whether a Transporter load job can be recovered. For information about transactional database operations, refer to the HP Neoview SQL Reference Manual.

Table 7-3 Transaction Modes for Load Jobs

forcestaging is false and

noaudit is false.

Staging tables cannot be used in this mode.

a transaction.

A failed job can be recovered. Only source rows not successfully inserted are involved in the recovery. Recovery resumes the load process from the last point of failure.

Also usedfor UPDATE, UPSERT, or for a reflexive update operation. (A reflexive update operation in a map implicitly changes an INSERT to UPSERT.)

Loading Data With JMS Trickle Feed

Non-transactional ModeTransactional Mode

Either forcestaging or noaudit must be true.

If forcestaging is true, the operation uses staging tables.

NOTE: Transporter returns an error if both are true.

commitsize is ignored.commitsize determines the number of source rows in

Transporter uses non-transactional database operations.Transporter manages internal transactions.

If forcestaging is true, and failure occurs during transfer from a staging table to the target row, the job cannot be recovered, it must be restarted.

When recovery is possible, all source rows are recovered, because the load process starts from the beginning of the job.

This section discusses the requirements for using JMS Trickle Feed and the differences between JMS Trickle Feed and other types of load jobs.

60 Load and Extract Operations

Requirements

These are the requirements to use the Trickle Feed feature:

• ActiveMQ (JMS provider), which is included with the Java client package

• A separate (and dedicated) instance of the Transporter Java client

A JMS trickle feed load is similar to loads from flat files or named pipes, except for the following differences:

• Workflow

When the Java client parses the control file and determines that the source is JMS, a trickle feed process known as a JMS Consumer is launched to consume data from the JMS queue. The JMS Consumer waits up to “timeout” (page 54) for data to arrive. If no data arrives during this time, the JMS Consumer shuts down.

• JMS Queue Message Handling

— Each message received is considered a single data record. — Messages are in JMS text format, and may be either delimited or fixed-width format. — Messages are read from the JMS Queue asynchronously and removed automatically as

— Transporter continues to load data in real time continuously, a record at a time, until

• Message Characteristics

— Message domain is Point-To-Point – each message has a single consumer, and the

— Message format is JMS text message type.

• Control File Specification

sourcename jms jmsname [options provider-options]

jmsname

provider-options

• Limitations

The Java Consumer process commits to the JMS Queue after the data (row) is committed on the Neoview platform. However, there is a window where a row may be inserted twice. This situation couldoccur if the row is committed on the Neoview platform, butthe network connection between the client and server is dropped before confirmation is received by the client.

they are read.

the JMS Consumer reads the “endseq” (page 48) sequence.

message is retained in the queue until it is consumed.

The name of the queue.

The options are: — “endseq” (page 48) — “tenacity” (page 54) — “timeout” (page 54) — “retries” (page 53)

For information about Java, refer to these web sites:

• http://java.sun.com/products/jms/index.jsp

• http://java.sun.com/products/jms/tutorial/

Parallel Processing for Load Operations

If you want job entries in a load operation to process in parallel, you must set “parallel” (page 52) = true. These considerations apply:

Parallel Processing for Load Operations 61

• The set of targets (that you have set in the “Sources” (page 40) section) must be the same

for all sources in the job.

• If multiple job entries specify the same target, either all mapnames specified by the job entries

must be identical, or they must use the same targetformat, the same fieldmappings, and a sourceformat with identical field names and field order. See “Maps” (page 36) for details about mappings.

• For options specified at the job entry level, settings (explicit or default) must be identical for

all job entries.

• For options specified at the “Sources” (page 40) level, input data sources must have identical

settings (explicit or default) for these options: — File and Named Pipe Sources

◦ “forcestaging” (page 49) ◦ “sorted” (page 53)

— JDBC Sources

◦ “datasource” (page 47) ◦ “noaudit” (page 51) ◦ “password” (page 52) ◦ “system” (page 54) ◦ “url” (page 54) ◦ “user” (page 55)

• Database connections and transactions managed internally by Transporter are shared by all

job entries.

• Rows from different sources can be processed in the same transaction. There is no guarantee

that a specific number of rows from a given source is included in each transaction.

• For non-SQL dataformats referenced by maps to a specific SQL target, the fields must have

identical names and be in the same order.

Considerations for Extract Jobs

While extract jobs are specified in the control file and run with load jobs in the same control file invocation, there are these differences from load jobs:

• Extract jobs cannot be recovered.

• You cannot use staging tables with extract jobs.

• Database isolation levels are different, depending on the source:

— For a table source, Transporter issues queries with an isolation level of READ

UNCOMMITTED.

— For a Neoview SQL query source, Transporter issues queries with an isolation level of

READ COMMITTED. However, you can change the isolation level by explicitly specifying it in the query.

62 Load and Extract Operations

8 Data Processing

This chapter addresses these topics:

“Data For Load Operations” (page 63) “Processing Data for Load Operations” (page 63) “Delimited Data” (page 64) “Considerations for Character Data” (page 66)

Data For Load Operations

This section addresses how Transporter processes data for a load operation, and how it treats delimited and fixed-width data.

Table 8-1 Delimited and Fixed-width Data Descriptions

DescriptionType of Input Data

Delimited Data

Fixed-Width Data

Fields are separated by delimiter characters.

You specify a delimiter character in the control file.

The default character is the comma (,).

The width of each field in a record is the same across all records.

You enter the data as text, without quotes.

Records are separated by the system EOL ('\n') , or a sequence of them.

Processing Data for Load Operations

This section describes how Transporter addresses and processes the data for load jobs.

Delimited Data for Load Operations

Transporter addresses and processes a delimited input field as follows, in this order:

1. Transporter considers the following to be a NULL value: An input field of zero length before

white space trimming or quote processing is performed.

2. Trim leading and trailing white space characters. This does not involve characters inside

double quotes. (“)

3. If “nullstring” (page 51) is specified, determine whether the input field matches the

nullstring value. If it matches, the input is considered a NULL value.

4. If the “Dataformats” (page 32) clause optionally qualified by is specified and the

first character is the quoting character, perform quote processing. See “Quote Processing

For Load Operations” (page 65).

5. If the target data type is a character type, truncate any characters beyond the field length

that NeoviewSQL expects for the column or SQL expression. If any of the truncated characters are not white space, reject the record and write it to the “baddatafile” (page 47) file.

6. If no characters remain in the input field:

a. if the target data type is a character type, consider the value as an empty string. b. if the target data type is a type other than a character type, reject the record and write

it to the “baddatafile” (page 47) file.

7. Process remaining characters as appropriate for the target data type. If a field cannot be

processed according to the specified “Typeformats” (page 29) rules, reject the record and write it to the “baddatafile” (page 47) file.

Data For Load Operations 63

Fixed—Width Data for Load Operations

Transporter addresses and processes a fixed—width input field as follows, in this order:

1. If “nullstring” (page 51) is specified, determine whether the input field matches the

nullstring value, ignoring leading and trailing white space. If it matches, the input is considered a NULL value.

2. If the target data type is not a character type, trim leading and trailing white space characters.

3. If the target data type is a character type, trim only trailing white space characters.

4. If the target data type is a character type, truncate any characters beyond the field length

that NeoviewSQL expects for the column or SQL expression. If any of the truncated characters are not white space, reject the record and write it to the “baddatafile” (page 47) file.

5. If no characters remain in the input field:

a. if the target data type is a character type, consider the value as an empty string. b. if the target data type is a type other than a character type, reject the record and write

it to the “baddatafile” (page 47) file.

6. Process remaining characters as appropriate for the target data type. If a field cannot be

processed according to the specified “Typeformats” (page 29) rules, reject the record and write it to the “baddatafile” (page 47) file.

Delimited Data

This section addresses field delimiter characters, record separator characters, and delimited data in load operations.

Field Delimiter Character

When you submit delimited input data, you separate the data fields with a delimiter character. The default value is the comma (,) or you can specify a delimiter in the Control File with the

fields delimited by character option.

character can be a comma (,) a semicolon (;), or any character you specify in octal, with these

exceptions:

• The lowercase letters a though z

• The uppercase letters A though Z

• Decimal numbers 0 though 9

• Carriage return (\015) or line feed (\012)

Examples:

fields delimited by <,> fields delimited by <;> fields delimited by <\024>

Record Separator Character

The record separator character is one or more <nl>'s. The default is one <nl>. For example:

records separated by <nl><nl>

indicates that each record is separated by two <nl>'s.

• For a load operation, <nl> matches any of these:

— A single carriage return (CR) character (hex 0x0D) — A single line feed (LF) character (hex 0x0A) — A single carriage-return/line feed (CR/LF) pair

• For an extract operation, <nl> represents the system end-of-line (EOL) sequence:

64 Data Processing

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

Quote Processing For Load Operations

For a load operation involving a delimited dataformat:

• There is no default quoting character. Quote processing is disabled if this option is not

specified.

• When optionally qualified by is specified and the first non-blank character in an

input field is the quoting character, Transporter treats all characters between that leading quote character and the corresponding closing quote as input data. Inside the leading and closing quotes, any double-occurrences of the quoting character are considered part of the input data and are interpreted as single occurrences.

• Field delimiters are allowed within quotes and are treated as part of the input data.

• If no closing quote is found, the input record is rejected and written to the “baddatafile”

(page 47) file.

• If a closing quote is found but it is not the last non-blank character of the input field, the

input record is rejected and written to “baddatafile” (page 47)

• If quoting characters are present in an input field but the first non-blank character is not the

quoting character, no quote processing is performed.

For example, assume the quoting character is a double-quote ("") and the field delimiter is a comma:

Quote Processing For Extract Operations

For an extract operation involving a delimited dataformat:

• There is no default quoting character. Use optionally qualified byto specify a quoting

character, otherwise quote processing is disabled.

• When you specify optionally qualified by:

— Date/Time and Character values are quoted. — For other data types, any value containing the field delimiter is quoted. — Within a quoted value, any occurrence of the quoting character is doubled. For example

if the quoting character is a single quote (') and the character value ab'cd'ef is moved to an extract target, the characters written to the target are: 'ab''cd''ef'.

• Other considerations for quoted data:

— Field delimiters are allowed and not treated as special characters within a quoted value.

This allows cases where, for example, an address field contains commas and comma (,) is the field delimiter. Record separators cannot be embedded into a quoted value.

— You can use fixed-width data formats to avoid the overhead of quote processing.

Examples: — To qualify by double-quotes (""):

optionally qualified by "

Interpreted as ...Input Data

New York, NY“New York, NY”

Not “quoted”Not “quoted”

Yes “Quoted”“Yes ""quoted"""

— To qualify by single-quotes (""):

Delimited Data 65

optionally qualified by '

Considerations for Character Data

• For Load Operations:

— Character fields can include any characters valid for the character set of the target data

type.

— If an input field has fewer characters than the target column for target data type CHAR,

values written to the target table are padded with trailing spaces. This does not apply to VARCHAR.

— If the length of a character input value used in an SQL expression or written to a database

column exceeds the length of the expected data type, the additional characters are truncated. If the additional characters are not all white space, the record is rejected and written to the “faileddatafile” (page 49) file. If the additional characters are white space, processing continues normally.

— Most SQL expressions that accept character values as input expect the values to be of

type CHAR(255). If your input value for a field can exceed this length, use a CAST expression to increase the maximum expected length. For example, for a field called FIELD_1:

substring(CAST(:FIELD_1 as CHAR(512)), 2, 10)

• For Extract Operations:

— For fixed-width format, if a character field written to a targetfile or named pipe exceeds

the specified number of bytes, and the additional characters are all white space, they are discarded and processing continues. If the additional characters are not all white space, the record is rejected and written to the“baddatafile” (page 47) file.

— Input data is read as a stream of bytes. Transporter will neither interpret nor convert

character values destined for Neoview SQL character columns with the ISO88591 character set.

66 Data Processing

9 Troubleshooting

This section covers job recovery and solutions to problems you might encounter.

• “Logs” (page 67)

• “Job Recovery” (page 67)

• “Job Statistics on the Neoview Platform ” (page 68)

Logs

If errors occur during a data movement operation, Transporter logs appropriate messages to log files on the client, describing the errors encountered. Important messages are also logged to the console and EMS log on the Neoview platform. Check these log files when a job fails:

“baddatafile” (page 47)

“faileddatafile” (page 49)

$NVTHOME/log/java Log files on the client

“Job Statistics on the Neoview Platform ” (page 68)

Log Files on the Client

Log files on the Transporter client reside in the $NVTHOME/log/java directory. A new log file is created daily, and is named DBTransporter.log. Log files for past days have the date appended to the filename extension: DBTransporter.log.YYYY-MM-DD.

Logging to the Console

INFO messages are sent to standard out.

WARN, ERROR and FATAL messages are sent to standard error.

NOTE: If Transporter encounters a problem writing to the console, a warning message is

written to the client log file, once per client invocation.

The EMS Log

All FATAL messages are duplicated to the EMS log on the Neoview platform. You can access Transporter EMS log entries with the OSM Event Viewer.

NOTE: If Transporter encounters a problem writing to the EMS log, a warning message is

written to the client log file, once per client invocation.

Job Recovery

“The Transporter Job” (page 58) is the smallest control file unit for data movement in Transporter,

and when a failure occurs, the job is the only unit that can be recovered. Suppose your control file consists of 5 jobs and processing fails on job 3. If you resubmit the operation with the

-recover command line option, job 3 is recovered from the point of failure, while jobs 4 and 5 are started from the beginning. What Transporter actually recovers is the one job that was started but did not complete.

Logs 67

Considerations for Job Recovery

Transporter jobs can be recovered, with these considerations:

• Only load jobs with file sources can be recovered. Job recovery is not supported for named

pipes or JMS Trickle Feed.

• Job recovery is not supported for extract operations.

• Only jobs started using the -recover command line option can be recovered.

The -recover Command Line Option

To recover a job, use the —recover option on the command line, and provide the name of the original control file for the load. The Transporter client determines whether there are job definitions in the control file that can be recovered. If recoverable jobs are found, Transporter starts these jobs again from the last point of failure. Non-recoverable jobs are ignored. If all jobs are non-recoverable, Transporter logs an error message and exits.

CAUTION:

• You must use the original control file to recover load jobs. If the control file has changed,

Transporter reports an error and exits.

• The original and unmodified data files must be available to Transporter during recovery.

• Do not attempt to recover a job that is still running.

• Do not attempt to recover a job that is already in the recovery process.

Job Statistics on the Neoview Platform

Job statistics on the Neoview platform are used by Transporter to facilitate job recovery. The job statistics table, HP_TRANSPORTER.BASE_JOB_STATS, maintains this information for every job:

• job name

• job progress and status

• source and target for each job entry

• start and end time for each job entry

Each row in the BASE_JOB_STATS table represents a job entry instance. The ROWS_PROCESSED column reflects the number of rows processed for a particular job entry instance, including discarded and failed records. The number of rows logged in this table is not cumulative. For example, if you must run several instances of a control file because of errors in a job entry, you must add together the number of rows processed from all instances to get the total number of rows processed for that job entry.

Example 9-1 Sample HP_TRANSPORTER_BASE_JOB_STATS File

| FILE_ID | JOB_NAME | SOURCE_NAME | TARGET_NAME | START_TIME | ROWS_PROCESSED | LAST_FILE_OFFSET | TRANSACTION_SIZE | ROW_LEN | END_TIME | SQL_TEXT | | 17714284327823 | load1 | src1 | tgt1 | 2008-02-13 16:50:44.503 | 7 | 2406 | 777 | 342 | 2008-02-13 16:50:46.284 | INSERT INTO NEO.SALES.INFORMATION (FIRST_NAME,LAST_NAME,ADDRESS,ZIP,PHONE,SSN,INFO2) VALUES (?,?,?,?,?,?,?) | | 17713711815864 | load1 | src1 | tgt1 | 2008-02-13 16:50:53.56 | 33 | 3452 | 777 | 120 | 2008-02-13 16:50:55.61 | INSERT INTO NEO.SALES.ORDERS (O_ORDERDATE,O_ORDERKEY,O_CUSTKEY,O_SHIPPRIORITY,O_TOTALPRICE,O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_COMMENT) VALUES (?,?,?,?,?,?,?,?,?) |

You can use a simple Neoview SQL query to retrieve all of the statistics for a particular Neoview platform:

SELECT * FROM HP_TRANSPORTER.BASE_JOB_STATS

These are the static and dynamic statics that you can retrieve:

68 Troubleshooting

• Static

— Extract source: Neoview SQL Text or Multi-Partition Set Table Extract — Single Stream or Parallel Extract Streams — Target information: name of file, named pipe or data source — Indication if recovery operation was initiated by user — Load target table name — Text of DML query for load

• Dynamic

— Run status: scheduled, in progress, complete or error — Exit status or error reason: Transporter exit error message — Internal recovery attempt triggered due to tenacity setting: for example, 2 of 3 — Total rows extracted — Extract statistics per stream basis — Start time of the job entry — Time for next retry operation if applicable — End time of the job entry

Control File Metadata Tables

You can retrieve control file related data from these metadata tables on the Neoview platform:

• HP_TRANSPORTER.CONTROL_FILES — list of control files including the host name,

absolute path, and modification time. Use this list to locate a particular control file.

Example 9-2 Sample HP_TRANSPORTER_CONTROL_FILES File

| FILE_ID | FILE_HOST | FILE_ABS_PATH | FILE_ENCODING | FILE_VERSION | FILE_MODTIME | | 17714284327823 | mynode.intranet.foo.bar.net | /mydir/control-files/control_info.txt | UTF-8 | 1 | 2008-02-13 16:47:18.0 | | 17713711815864 | mynode.intranet.foo.bar.net | /mydir/control-files/control_ordr.txt | UTF-8 | 1 | 2008-02-13 16:47:27.0 |

• HP_TRANSPORTER.CONTROL_FILE_TEXT — the text of a particular control file

Example 9-3 Sample HP_TRANSPORTER_CONTROL_FILE_TEXT File

| FILE_ID | SEQ_NUM | FILE_TEXT | | 17714284327823 | 1 | version 1.0; options { errors = 3 discards = 8, commitsize = 777, rowsetsize = 777 } ... | | 17713711815864 | 1 | version 1.0; options { errors = 5, discards = 1, commitsize = 777, rowsetsize = 777 } ... |

NOTE: Password values have already been encrypted before control files are saved on the

Neoview platform.

Use these two metadata tables to find and examine the control file used to run a particular job in the job statistics table. You can use the information from these tables to find and fix problems that caused a job to fail.

Outdated information must be removed manually from these metadata tables.

CAUTION: Running jobs are actively using rows in the metadata tables. Do not delete these

rows or any other rows that might be needed for the recovery of failed jobs.

Using Transporter Metadata Tables

This section describes several scenarios, with examples, for statistics retrieval and metadata maintenance.

Job Statistics on the Neoview Platform 69

Example 9-4 Identify All Job Entries that Successfully Completed Within the Last Seven Days

SELECT B.FILE_HOST, B.FILE_ABS_PATH, B.FILE_MODTIME, A.JOB_NAME, A.SOURCE_NAME, A.TARGET_NAME, A.START_TIME, A.END_TIME FROM HP_TRANSPORTER.BASE_JOB_STATS A, HP_TRANSPORTER.CONTROL_FILES B WHERE A.FILE_ID = B.FILE_ID AND A.END_TIME IS NOT NULL AND DATEDIFF(DAY, A.END_TIME, CURRENT_TIMESTAMP) < 7 ORDER BY B.FILE_HOST, B.FILE_ABS_PATH, B.FILE_MODTIME, A.JOB_NAME, A.SOURCE_NAME, A.TARGET_NAME, A.START_TIME FOR READ UNCOMMITTED ACCESS;

FILE_HOST FILE_ABS_PATH FILE_MODTIME JOB_NAME SOURCE_NAME TARGET_NAME START_TIME END_TIME mynode.intranet.foo.bar.net /mydir/control-files/control_BAT001.txt 2008-02-13 16:06:05.0 load1 src1 tgt1 2008-02-13 16:06:22.592 2008-02-13 16:06:27.437 mynode.intranet.foo.bar.net /mydir/control-files/control_BAT001.txt 2008-02-13 16:30:47.0 load1 src1 tgt1 2008-02-13 16:31:01.407 2008-02-13 16:31:04.443 mynode.intranet.foo.bar.nett /mydir/control-files/control_BAT001.txt 2008-02-13 16:47:18.0 load1 src1 tgt1 2008-02-13 16:47:28.818 2008-02-13 16:47:31.875 mynode.intranet.foo.bar.net /mydir/control-files/control_BAT001.txt 2008-02-13 17:25:59.0 load1 src1 tgt1 2008-02-13 17:26:08.466 2008-02-13 17:26:12.965 mynode.intranet.foo.bar.nett /dbt/testdir/test-file.cf 2008-02-01 17:53:33.0 LOAD1 SRC1 TGT1 2008-02-13 18:04:50.0 2008-02-13 18:05:10.0 mynode.intranet.foo.bar.net /dbt/testdir/test-file.cf 2008-02-01 17:53:33.0 LOAD1 SRC1 TGT1 2008-02-13 18:10:43.0 2008-02-13 18:11:03.0

Example 9-5 Identify All Job Entries That Were Started in the Last 24 Hours But Have Not Completed — Either Failed or In Progress

SELECT B.FILE_HOST, B.FILE_ABS_PATH, B.FILE_MODTIME, A.JOB_NAME, A.SOURCE_NAME, A.TARGET_NAME, A.START_TIME FROM HP_TRANSPORTER.BASE_JOB_STATS A, HP_TRANSPORTER.CONTROL_FILES B WHERE A.FILE_ID = B.FILE_ID AND A.END_TIME IS NULL AND DATEDIFF(HOUR, A.START_TIME, CURRENT_TIMESTAMP) < 24 ORDER BY B.FILE_HOST, B.FILE_ABS_PATH, B.FILE_MODTIME, A.JOB_NAME, A.SOURCE_NAME, A.TARGET_NAME, A.START_TIME FOR READ UNCOMMITTED ACCESS;

FILE_HOST FILE_ABS_PATH FILE_MODTIME JOB_NAME SOURCE_NAME TARGET_NAME START_TIME CACSELLK T:\sellk_Java_DBT_1\transporter\DBTransporter\src\conf\fayecf1.txt 2008-02-13 15:24:16.0 extract_wjp_char_fix_20 wjp_char_fix_20_ex ex_wjp_fix_20 2008-02-13 15:38:40.224 anbe-xp C:\dbt\delimited+fixed\combined_cf.txt 2008-02-04 12:49:07.0 load1 src1 tgt1 2008-02-13 17:22:13.644 anbe-xp C:\dbt\delimited+fixed\combined_cf.txt 2008-02-04 12:49:07.0 load1 src1 tgt1 2008-02-13 17:28:12.421 anbe-xp C:\dbt\delimited+fixed\combined_cf.txt 2008-02-04 12:49:07.0 load1 src1 tgt1 2008-02-13 17:29:32.779 anbe-xp C:\dbt\delimited\TPCH_ORDERS_cf.txt 2008-02-13 17:34:22.0 load1 src1 tgt1 2008-02-13 17:34:55.228 mynode.intranet.foo.bar.net /mydir/control-files/control_BAT004.txt 2008-02-13 16:06:05.0 load1 src1 tgt1 2008-02-13 16:07:11.506 mynode.intranet.foo.bar.net /mydir/control-files/control_BAT004.txt 2008-02-13 16:06:05.0 load1 src2 tgt1 2008-02-13 16:07:11.506 mynode.intranet.foo.bar.net /dbt/mydir/test1.cf 2008-02-01 17:53:33.0 LOAD1 SRC1 TGT1 2008-02-13 15:31:53.0 mynode.intranet.foo.bar.net /dbt/mydir/test_data_50M-1.cf 2008-02-05 18:05:52.0 LOAD1 SRC1 TGT1 2008-02-13 18:15:10.0 mynode.intranet.foo.bar.net /dbt/mydir/control-files/NBATS/control_BAT006.txt 2008-02-13 16:26:32.0 LOAD1 SRC1 TGT1 2008-02-13 16:29:16.0 mynode.intranet.foo.bar.net /dbt/mydir/control-files/NBATS/control_BAT011.txt 2008-02-13 16:26:32.0 LOAD1 SRC1 TGT1 2008-02-13 16:31:12.0 mynode.intranet.foo.bar.net /dbt/mydir/control-files/NBATS/control_BAT100.txt 2008-02-13 18:13:03.0 LOAD1 SRC1 TGT1 2008-02-13 18:20:15.0

70 Troubleshooting

Example 9-6 Identify All Jobs Executed From Host abc Using the Latest Version of Control File

/home/control files/ControlFile.txt

SELECT DISTINCT A.JOB_NAME FROM HP_TRANSPORTER.BASE_JOB_STATS A, HP_TRANSPORTER.CONTROL_FILES B WHERE A.FILE_ID = B.FILE_ID AND B.FILE_HOST = 'abc' AND B.FILE_ABS_PATH = '/home/control_files/ControlFile.txt' AND B.FILE_VERSION = (SELECT MAX(FILE_VERSION) FROM HP_TRANSPORTER.CONTROL_FILES WHERE FILE_HOST = B.FILE_HOST AND FILE_ABS_PATH = B.FILE_ABS_PATH) FOR READ UNCOMMITTED ACCESS;

JOB_NAME july_info_load july_orders_load july_revenue_load

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

DELETE FROM HP_TRANSPORTER.BASE_JOB_STATS WHERE DATEDIFF(MONTH, START_TIME, CURRENT_TIMESTAMP) >= 3;

Example 9-8 Delete All Versions of Control File /home/control files/ControlFile.txt That Have Been Used On Host abc

NOTE: The three delete statements must be used in this order.

DELETE FROM HP_TRANSPORTER.BASE_JOB_STATS WHERE FILE_ID IN (SELECT FILE_ID FROM HP_TRANSPORTER.CONTROL_FILES WHERE FILE_HOST = 'abc' AND FILE_ABS_PATH = '/home/control_files/ControlFile.txt');

DELETE FROM HP_TRANSPORTER.CONTROL_FILE_TEXT WHERE FILE_ID IN (SELECT FILE_ID FROM HP_TRANSPORTER.CONTROL_FILES WHERE FILE_HOST = 'abc' AND FILE_ABS_PATH = '/home/control_files/ControlFile.txt');

DELETE FROM HP_TRANSPORTER.CONTROL_FILES WHERE FILE_HOST = 'abc' AND FILE_ABS_PATH = '/home/control_files/ControlFile.txt';

Job Statistics on the Neoview Platform 71

A Control File Examples

Control File Examples

Following are control file examples for:

• Data types Example A-1 (page 74)

• Extract from a Neoview SQL source Example A-2 (page 77)

• Extract from a table source to a named pipe Example A-3 (page 79)

• Load fixed-width data Example A-4 (page 81)

• Include file for defining data formats Example A-5 (page 83)

• Load from a file with an Include file Example A-6 (page 84)

• Load and extract Example A-7 (page 85)

• Multiple data formats and maps Example A-8 (page 87)

• Reflexive update Example A-9 (page 89)

• Update with constant, null, or expression Example A-10 (page 90)

• IDENTITY column Example A-11 (page 92)

• Usage of NULLSTRING Example A-12 (page 93)

• A valid NOAUDIT load Example A-13 (page 94)

• A forcestaging load with multiple job entries in a single job with parallel set to true

Example A-14 (page 95)

Example A-1 Control File: Datatypes

/* All Data Types */

version 1;

options { errors = 1000, discards = 500, rowsetsize = 1000 }

typeformats {

# comment: show examples of multiple date, time and interval formats date_frmt date '%C%y-%m-%d' time_frmt time '%H:%M:%s' timestamp_frmt datetime '%C%y-%m-%d %H:%M:%s' intv_frmt_00 interval '%Y' intv_frmt_01 interval '%Y-%M' intv_frmt_02 interval '%M' intv_frmt_03 interval '%D' intv_frmt_04 interval '%D:%H' intv_frmt_05 interval '%D:%H:%M' intv_frmt_06 interval '%D:%H:%M:%S' intv_frmt_07 interval '%H' intv_frmt_08 interval '%H:%M' intv_frmt_09 interval '%H:%M:%S' intv_frmt_10 interval '%M' intv_frmt_11 interval '%M:%S' intv_frmt_12 interval '%S' }

# comment: use all the data types supported by Transporter dataformats {

# comment: dataformat for the input data file

dataformat1 delimited records separated by <nl> fields delimited by <,> optionally qualified by <"> ( smin1 smallint, smin2 smallint, inte1 integer, inte2 integer, lint1 largeint, nume1 numeric, nume2 numeric, deci1 decimal, char1 char, vchr1 varchar, date1 date format date_frmt, time1 time format time_frmt, time2 time format time_frmt, tims1 timestamp format timestamp_frmt, tims2 timestamp format timestamp_frmt, intv00 interval format intv_frmt_00, intv01 interval format intv_frmt_01, intv02 interval format intv_frmt_02, intv03 interval format intv_frmt_03, intv04 interval format intv_frmt_04, intv05 interval format intv_frmt_05, intv06 interval format intv_frmt_06, intv07 interval format intv_frmt_07, intv08 interval format intv_frmt_08, intv09 interval format intv_frmt_09,

74 Control File Examples

intv10 interval format intv_frmt_10, intv11 interval format intv_frmt_11, intv12 interval format intv_frmt_12, flot1 float, flot2 float )

# comment: data format for the target SQL table on the Neoview platform */ dataformat2 sql fields ( smin1 smallint, smin2 smallint, inte1 integer, inte2 integer, lint1 largeint, nume1 numeric, nume2 numeric, deci1 decimal, char1 char, vchr1 varchar, date1 date, time1 time, time2 time, tims1 timestamp, tims2 timestamp, intv00 interval, intv02 interval, intv03 interval, intv05 interval, intv07 interval, intv08 interval, intv09 interval, intv10 interval, intv12 interval, intv13 interval, intv14 interval, intv16 interval, intv17 interval, flot1 float, flot2 float ) }

sources { src01 file "./data_files/sn_nvt_all_typ.dat"

tgt01 jdbc table NEO.nvt.sn_all_typ options ( system = "asl0101", user = "user", password = "transport", datasource = "Admin_Load_DataSource", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/" ) }

maps { map01 from dataformat1 to dataformat2 ( smin1 = smin1, smin2 = smin2, inte1 = inte1, inte2 = inte2, lint1 = lint1, nume1 = nume1, nume2 = nume2, deci1 = deci1, char1 = char1, vchr1 = vchr1, date1 = date1, time1 = time1, time2 = time2,

tims1 = tims1, tims2 = tims2, intv00 = intv00, intv02 = intv02, intv03 = intv03, intv05 = intv05, intv07 = intv07, intv08 = intv08, intv09 = intv09, intv10 = intv10, intv12 = intv12, intv13 = intv13, intv14 = intv14, intv16 = intv16, intv17 = intv17, flot1 = flot1, flot2 = flot2 ) }

jobs { load01 load options ( parallel = "false" ) ( source src1 target tgt01 map map01 ) }

76 Control File Examples

Example A-2 Control File: Extract From Neoview SQL Source

/*----------------------------------------*/ /* Simple Extract From Neoview SQL Source To File */ /*----------------------------------------*/

version 1.0;

options { errors = 100, discards = 100, rowsetsize = 1000 }

dataformats { dataformat1 delimited records separated by <nl> fields delimited by <,> optionally qualified by <"> ( SSN largeint, FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, INFO1 char, INFO2 char )

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char ) }

sources {

src1 jdbc sql "select * from NEO.nvt.sn_nvt_ext order by SSN" options ( system = "asl0101", user = "user", password = "transport", datasource = "Admin_Load_DataSource", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/" )

tgt1 file "./data_files/sn_ext.dat" }

maps { map1 from dataformat2 to dataformat1 ( SSN = SSN, FIRST_NAME = FIRST_NAME,

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

jobs { extract1 extract options ( parallel = "true" ) ( source src1 target tgt1 map map1 ) }

78 Control File Examples

Example A-3 Control File: Extract From Table Source to Named Pipe

/*------------------------------------------------*/ /* Simple Extract From Table Source To Named Pipe */ /*------------------------------------------------*/

version 1;

options { errors = 1000, rowsetsize = 5000, discards = 100 }

dataformats { dataformat1 delimited records separated by <nl> fields delimited by <,> optionally qualified by <"> ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char )

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char ) }

sources { src1 jdbc table NEO.nvt.sn_nvt_ext options ( system = "asl0101", user = "user", password = "transport", datasource = "Admin_Load_DataSource", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/" )

tgt1 pipe "./data_files/sn_nvtpipe" } maps { map1 from dataformat2 to dataformat1 ( FIRST_NAME = FIRST_NAME, LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP, PHONE = PHONE, SSN = SSN, INFO1 = INFO1, INFO2 = INFO2 ) } jobs

{ extract1 extract options ( parallel = "false" ) ( source src1 target tgt1 map map1 ) }

80 Control File Examples

Example A-4 Control File: Load Fixed Width Data

/* Load Fixed Width Data */

version 1;

options { errors = 1000, discards = 100, rowsetsize = 5000 }

dataformats {

# fixed width data source file dataformat1 fixed record length 443 fields positions ( 1, 13, 37, 165, 169, 179, 187, 315 ) ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char )

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char ) }

sources { src1 file "./data_files/sn_nvt_fw.dat"

tgt1 jdbc table NEO.nvt.sn_nvt options ( system = "asl0101", user = "user", password = "transport", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/", datasource = "Admin_Load_DataSource" ) }

maps { map1 from dataformat1 to dataformat2 ( FIRST_NAME = FIRST_NAME,

LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP, PHONE = PHONE, SSN = SSN, INFO1 = INFO1, INFO2 = INFO2 ) }

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

82 Control File Examples

Example A-5 Control File: Include Defining Dataformats

You can use an Include file such as this to define data formats for many control files. See

Example A-6, in which this file, ./CF/include_part_2.cf has been included to define the

data formats.

/* Simple Load From File To Table Using INCLUDE To Include Control File Fragment (File 2/2) */

# comment: control file fragment defines data formats, can be included in other control files dataformats { dataformat1 delimited records separated by <nl> fields delimited by <,> optionally qualified by <"> ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char )

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char )

}

Example A-6 Control File: Include — Load From File to Table

/* Simple Load From File To Table Using INCLUDE To Include Control File Fragment (File 1/2) */

version 1.0;

# comment: control file fragment include_part_2.cf defines the data formats %include "./CF/include_part_2.cf"

options { errors = 1000, discards = 100, rowsetsize = 5000 }

sources { src1 file "./data_files/sn_nvt.dat"

tgt1 jdbc table NEO.nvt.sn_nvt options ( system = "asl0101", user = "user", password = "transport", datasource = "Admin_Load_DataSource", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/" ) }

maps { map1 from dataformat1 to dataformat2 ( FIRST_NAME = FIRST_NAME, LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP, PHONE = PHONE, SSN = SSN, INFO1 = INFO1, INFO2 = INFO2 ) }

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

84 Control File Examples

Example A-7 Control File: Load and Extract

/* Simple Load From File To Table & Extract From Neoview SQL Source To File */

version 1.0;

options { errors = 1000, discards = 100, rowsetsize = 5000, }

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char )

dataformat3 delimited records separated by <nl> fields delimited by <,> optionally qualified by <"> (

( SSN largeint, FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, INFO1 char, INFO2 char )

}

sources { src1 file "./data_files/sn_nvt.dat"

tgt1 jdbc table NEO.nvt.sn_nvt options ( system = "asl0101", user = "user", password = "transport", datasource = "Admin_Load_DataSource", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/" )

src2 jdbc sql "select * from NEO.nvt.sn_nvt_ext order by SSN" options ( system = "asl0101", user = "user", password = "transport", datasource = "Admin_Load_DataSource", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/" )

tgt2 file "./data_files/sn_ext.dat" }

maps { map1 from dataformat1 to dataformat2 ( FIRST_NAME = FIRST_NAME, LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP,

PHONE = PHONE, SSN = SSN, INFO1 = INFO1, INFO2 = INFO2 )

map2 from dataformat2 to dataformat3 ( FIRST_NAME = FIRST_NAME, LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP, PHONE = PHONE, SSN = SSN, INFO1 = INFO1, INFO2 = INFO2 ) }

# comment: define 2 separate jobs - one for load, one for extract # comment: note that load or extract is a job attribute, and cannot be combined into a single job

jobs { load1 load options ( parallel = "false" ) ( source src1 target tgt1 map map1 )

extract1 extract options ( parallel = "true" ) ( source src2 target tgt2 map map2 ) }

86 Control File Examples

Example A-8 Control File: Multiple Dataformats and Maps

/*-----------------------------*/ /* Multiple Dataformats & Maps */ /*-----------------------------*/

version 1.0;

options { errors = 100, discards = 100, rowsetsize = 10 }

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char ) dataformat3 delimited records separated by <nl> fields delimited by <,> optionally qualified by <"> ( C1 int, C2 int, C3 char )

dataformat4 sql fields ( C1 int, C2 int, C3 char ) }

sources { src1 file "./data_files/sn_nvt.dat"

tgt1 jdbc table NEO.nvt.sn_nvt options

( system = "asl0101", user = "user", password = "transport", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/", datasource = "Admin_Load_DataSource" )

src2 file "./data_files/sn_nvt_sml.dat"

tgt2 jdbc table NEO.nvt.sn_nvt_sml options ( system = "asl0101", user = "user", password = "transport", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/", datasource = "Admin_Load_DataSource" ) }

maps { map1 from dataformat1 to dataformat2 ( FIRST_NAME, LAST_NAME, ADDRESS, ZIP, PHONE, SSN, INFO1, INFO2 )

map2 from dataformat3 to dataformat4 ( C1 = C1, C2 = C2, C3 = C3 ) }

jobs { load1 load options ( parallel = "false" )

( source src1 target tgt1 map map1 source src2 target tgt2 map map2 ) }

88 Control File Examples

Example A-9 Control File: Reflexive Update

/*-------------------------*/ /* Simple Reflexive Update */ /*-------------------------*/

version 1.0;

options { errors = 1000, discards = 100, rowsetsize = 100 }

dataformats { dataformat1 delimited records separated by <nl> fields delimited by <,> optionally qualified by <"> ( C1 int, C2 int, C3 char )

dataformat2 sql fields ( C1 int, C2 int, C3 char ) }

sources { src1 file "./data_files/sn_nvt_refu.dat"

tgt1 jdbc table NEO.nvt.sn_nvt_refu options ( system = "asl0101", user = "user", password = "transport", datasource = "Admin_Load_DataSource", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/" ) }

maps { map1 from dataformat1 to dataformat2 ( C1 = C1, C2 = C2 add, C3 = C3 ) }

jobs { load1 load options ( parallel = "true" ) ( source src1 target tgt1 map map1 ) }

Example A-10 Control File: Update with Constant, NULL, or Expression

/*--------------------------------*/ /* Constants, NULLs & Expressions */ /*--------------------------------*/

version 1;

options { errors = 1000, discards = 100, rowsetsize = 5000 }

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char ) }

sources { src1 file "./data_files/sn_nvt.dat"

tgt1 jdbc table NEO.nvt.sn_nvt options ( system = "asl0101", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/", user = "user", password = "transport", datasource = "Admin_Load_DataSource" ) }

maps { map1 from dataformat1 to dataformat2 (

# comment: use a constant for the target field PHONE PHONE = CONSTANT "8001237890", SSN = SSN, FIRST_NAME = FIRST_NAME, LAST_NAME = LAST_NAME, ADDRESS = ADDRESS,

# comment: increment source field ZIP by 1 and assign the resulting value to target field ZIP ZIP = EXPRESSION ":ZIP + 1",

# comment: assign NULL to the target field INFO1 INFO1 = NULL, INFO2 = INFO2 ) }

jobs {

90 Control File Examples

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

Example A-11 Control File: Identity column

This example of defines an IDENTITY column. CUSTOMER_NUMBER can be any numeric data type (SMALLINT, INTEGER, LARGEINT) column.

/*--------------------------------*/ /* IDENTITY column */ /*--------------------------------*/

mapexpr from dataformat1 to dataformat2 ( PHONE = PHONE, SSN = SSN, FIRST_NAME = FIRST_NAME, LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP,

CUSTOMER_NUMBER = identity loader,

INFO1 = INFO1, INFO2 = INFO2, DATE_ENTERED = expression ("current_date") )

92 Control File Examples

Example A-12 Control File: Usage of NULLSTRING

/*--------------------------------*/ /* Usage of NULLSTRING */ /*--------------------------------*/

version 1.0;

options { errors = 1000 ,rowset = 5000 ,nullstring = "^" }

dataformats { dataformat1 delimited records separated by <nl> fields delimited by <,> optionally qualified by <"> ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, SSN int, INFO1 char, INFO2 char )

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN int, INFO1 char, INFO2 char )

}

sources { src1 file "./data_files/sn_nvt_nls.dat"

tgt1 jdbc table NEO.nvt.sn_nvt_nls options ( system = "asl0101", user = "gcscsup", password = "transport", datasource = "TDM_Default_DataSource", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/" )

}

Example A-13 Control File: NOAUDIT mode

/*---------------------------------------------------------------------------------------*/ /* A Single Load Job Using NOAUDIT Mode, A Single Source File, And A Single Target Table */ /*---------------------------------------------------------------------------------------*/

version 1;

options { errors = 1000, discards = 100, rowsetsize = 5000, sorted = "true", noaudit = "true" }

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char ) }

sources { src1 file "./data_files/sn_nvt_01.dat"

tgt1 jdbc table NEO.nvt.sn_nvt options ( system = "asl0101", user = "gcscsup", password = "transport", datasource = "TDM_Default_DataSource", url = "jdbc:hpt4jdbc://txnaslan01.txn.cpqcorp.net:18650/", ) }

maps { map1 from dataformat1 to dataformat2 ( FIRST_NAME = FIRST_NAME, LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP, PHONE = PHONE, SSN = SSN, INFO1 = INFO1, INFO2 = INFO2 ) }

jobs { load1 load ( source src1 target tgt1 map map1 ) }

94 Control File Examples

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

/*-------------------------------------------------------------------------------------*/ /* A Single Load Job Using Staging Tables And Multiple Job Entries Running In Parallel */ /*-------------------------------------------------------------------------------------*/

version 1;

options { errors = 1000, discards = 100, rowsetsize = 5000, forcestaging = "true", sorted = "true" }

dataformat2 sql fields ( FIRST_NAME char, LAST_NAME char, ADDRESS char, ZIP int, PHONE char, SSN largeint, INFO1 char, INFO2 char ) }

sources { src1 file "./data_files/sn_nvt_01.dat" src2 file "./data_files/sn_nvt_02.dat" src3 file "./data_files/sn_nvt_03.dat"

maps { map1 from dataformat1 to dataformat2 ( FIRST_NAME = FIRST_NAME, LAST_NAME = LAST_NAME, ADDRESS = ADDRESS, ZIP = ZIP, PHONE = PHONE, SSN = SSN, INFO1 = INFO1, INFO2 = INFO2 ) }

jobs { load1 load options ( parallel = "true" ) ( source src1 target tgt1 map map1

source src2 target tgt1 map map1 source src3 target tgt1 map map1 ) }

96 Control File Examples

B Control File Editor and Control File Generator

This chapter addresses the following topics:

• The “Control File Editor ” (page 97)

• The “Control File Generator” (page 99)

Control File Editor

The Control File Editor is a Java-based GUI editor for creating, editing, and managing the Transporter control file. This tool can be used either on the loader platform that hosts the Transporter client component, or on your local workstation or PC to create, edit, and syntactically validate the control file for correctness.

Features

With the control file editor, you can:

• create a control file

• edit an existing control file

• validate a control file for correctness

• transfer (FTP) a control file to a remote system

• support user preferences — default file save location, system name,user name (not password)

• support a GUI and a text pane

Restrictions

Control file editor restrictions are:

• Transfer of files can only be one way (to the remote system).

• Parsing errors while opening an existing control file to populate the editor's GUI pane results

in the file being displayed only in the text pane. You need to correct the errors before the GUI pane can be populated.

Packaging

The control file editor is packaged as part of the Transporter Java client package. It is located in the $NVTHOME/utils directory.

To use the editor on a local workstation or PC, copy the jar file from the loader platform onto your local system.

Using the Control File Editor

This section describes how to use the control file editor for various purposes:

Creating a New Control File

To create a new control file: Click File —> New or type Ctrl+N.

The control file editor opens in GUI mode.

To work in text mode:

Click View —> Text.

Opening an Existing Control File

To open an existing control file: Click File —> Open or type Ctrl+O.

If a control file is not syntactically correct, it opens in text mode only. Fix all syntax errors before viewing the file in GUI mode. See “Checking the Syntax of a Control File” (page 98).

Control File Editor 97

Adding Content to a Control File

Click the appropriate tab to select the section of the control file you want to create or change. An Input Pane and Entered Input Table appear for each section in the file.

Input Pane: the left pane where you create the control file.

FunctionButton

Default

Clear Comment

Clear

Enter

Entered Input Table: the pane on the right of your screen where you change ordelete your input.

Edit

Delete

Saving a Control File to a Local System

To save a control file: Click File —> Save or type Ctrl+S.

NOTE: If the control file editor is in the GUI view when you save the control file, the control

file editor checks for syntax errors before saving. Otherwise, you must explicitly check the syntax. See “Checking the Syntax of a Control File” (page 98).

Sets default values for a section.

Clears all entered comments.

Clears all entered inputs.

Submits all entered inputs to the Entered Input Table.

FunctionButton

Edit selected input.

Delete selected input.

Checking the Syntax of a Control File

To check the syntax of a control file:

Click Tool —> Check Syntax or click the F6 button.

Validating a Control File

To validate a control file:

Click Tool —> Validate or click the F7 button.

Setting the Default Folder

To set the default working folder: Click Tool —> Set Default Folder or click the Ctrl+P.

Switching Views in the Control File Editor

You can work in two views in the Control File Editor:

• GUI view (default view)

The GUI view assists you in creating, editing, and viewing your control file. Select one of eight tabs to create the eight components of a control file.

• text view

Text view displays plain editable text where you can create, edit, and view your control file.

Including Another Control File

To include another control file:

98 Control File Editor and Control File Generator

Click Insert —> Include or click the Ctrl+I.

Then select the control file that you want to insert into the current control file.

Control File Generator

The Java-based control file generator tool can be used to create sample control files quickly using either a standard template that you have or based on tables already created in the Neoview database schemas.

To use the control file generator:

java nvtControlFileGen

-url URL_of_the_Neoview_server

-user username

-pw password -cat catalog_name

-schema schema_name

-table table_name

-sys system_name [ -file pathname ] [ -temp template_file ]

You can place the arguments in any order.

Required Arguments

These arguments are required:

—url

The URL of the server to connect to.

—user

The username needed to connect with the database referenced in the control file. This field accepts both plain text and encrypted format usernames.

—pw

The password for the Neoview platform. This field accepts both plain text and encrypted format passwords.

—schema

The schema information to put into the generated control file. If —schema is specified without

-table, control files are generated for all tables within the schema that can be accessed

without error.

—table

specifies the table name to be used in generating a control file.

-sys

The system name of the server to connect to.

Optional Arguments

These arguments are optional:

—cat

The server catalog.

—file

Is the directory or file name where the control file(s) are created. If not specified, it defaults to the local control file directory under $NVTHOME.

—temp

The name of the custom template file that is used to generate the control file(s). If unspecified, it defaults to contemplate.txt in the control file directory under$NVTHOME.

Control File Generator 99

Error Message and Behavior

The control file generator can encounter connection, SQL, and file I/O type errors. If a connection or a file I/O error is generated, the files may not be written. If a table is “skipped”, it is due to an SQL error only.

Packaging

The control file generator is packaged as part of the Transporter package. It is located in the Transporter client directory structure in $NVTHOME/utils.

100 Control File Editor and Control File Generator

HP Neoview Release 2.3 Software User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

HP Neoview Transporter User Guide

Table of Contents

About This Document

Intended Audience

Related Documentation

Neoview Customer Library

HP Encourages Your Comments

General Syntax Notation

Publishing History

1 Overview

Network Changes for the Neoview Platform

Load and Extract Operations

The Transporter Control File

Client Components

2 Installation and Configuration

Installation Requirements

Installing Transporter

Client Installation

Required Environment Settings For Client Installation

The Client Installation GUI

Client Directory Structure

Security Parameters Saved

Utilities

3 Functions and Features

Staging Tables

Parallel Streams

Parallel Query Extract

Prehashing

JMS Trickle Feed For Data Loads

Transporter Job Statistics on the Neoview Platform

Reflexive Update

Reflexive Update Considerations

Updates Using Constant, NULL, or Expression

Identity Columns

4 Security

Password Encryption

Encryption Files

The Encryption Utility — nvtencrsrv

Sample Commands for the nvtencrsrv Utility

5 Control File Organization and Syntax

Creating a Control File

Control File Limits

Control File Organization and Syntax

Version

Options

Typeformats

Dataformats

Maps

Sources

Jobs

Comments

Include

Supported Data Types

6 Control File Options

baddatafile

commitsize

crossoveryear

datasource

deleteonerror

discards

enabletriggers

endseq

errors

faileddatafile

forcestaging

multipart

noaudit

nullstring

operation

parallel

parallelstreams

password

retries

rowsetsize

sorted

startseq