Property type

Either Built-in or
Repository
.

Built-in: No property data stored
centrally.

Repository: Select the repository
file in which the properties are stored. The fields that follow are
completed automatically using the data retrieved.

Host

Database server IP address.

Port

Listening port number of the DB server.

Database

Name of the Netezza database.

Username and
Password

DB user authentication data.

To enter the password, click the […] button next to the
password field, and then in the pop-up dialog box enter the password between double quotes
and click OK to save the settings.

Table

Name of the table into which the data is to be inserted.

Action on table

On the table defined, you can perform one of the following
operations before loading the data:

None: No operation is carried
out.

Drop and create a table: The table
is removed and created again.

Create a table: The table does not
exist and gets created.

Create table if not exists: The
table is created if it does not exist.

Drop table if exists and create:
The table is removed if it already exists and created again.

Clear table: The table content is
deleted before the data is loaded.

Truncate table: executes a truncate
statement prior to loading the data to clear the entire content of
the table.

Schema and Edit
Schema

A schema is a row description. It defines the number of fields
(columns) to be processed and passed on to the next component. When you create a Spark
Job, avoid the reserved word line when naming the
fields.

Built-In: You create and store the schema locally for this component
only.

Repository: You have already created the schema and stored it in the
Repository. You can reuse it in various projects and Job designs.

Click Edit
schema to make changes to the schema. If the current schema is of the Repository type, three options are available:

• View schema: choose this
  option to view the schema only.

• Change to built-in property:
  choose this option to change the schema to Built-in for local changes.

• Update repository connection:
  choose this option to change the schema stored in the repository and decide whether
  to propagate the changes to all the Jobs upon completion. If you just want to
  propagate the changes to the current Job, you can select No upon completion and choose this schema metadata
  again in the Repository Content
  window.

Data file

Full path to the data file to be used. If this component is used
on its own (not connected to another component with input flow) then
this is the name of an existing data file to be loaded into the
database. If it is connected, with an input flow to another
component; this is the name of the file to be generated and written
with the incoming data to later be used with nzload to load into the
database.

Use named-pipe

Select this check box to use a named-pipe instead of a data file.
This option can only be used when the component is connected with an
input flow to another component. When the check box is selected, no
data file is generated and the data is transferred to nzload through
a named-pipe. This option greatly improves performance in both Linux
and Windows.

Named-pipe name

Specify a name for the named-pipe to be used. Ensure that the name
entered is valid.

Additional JDBC Parameters

Specify additional JDBC parameters for the
database connection created.

Use existing control file

Select this check box to provide a control file to be
used with the nzload utility instead of specifying all the options
explicitly in the component. When this check box is selected, Data file and the other nzload related
options no longer apply. Please refer to Netezza’s nzload manual for
details on creating a control file.

Control file

Enter the path to the control file to be used, between double
quotation marks, or click […] and
browse to the control file. This option is passed on to the nzload
utility via the -cf argument.

Field separator

Character, string or regular expression used to separate
fields.

Wrap quotes around fields

This option is only applied to columns of
String, Byte,
Byte[], Char, and
Object types. Select either:

None: do not wrap column values in
quotation marks.

Single quote: wrap column values in
single quotation marks.

Double quote: wrap column values in
double quotation marks.

Advanced options

Set the nzload arguments in the corresponding table. Click
[+] as many times as required
to add arguments to the table. Click the Parameter field and choose among the arguments from
the list. Then click the corresponding Value field and enter a value between quotation
marks.

For details about the available parameters, see Parameters.

Encoding

Select the encoding type from the list.

Specify nzload path

Select this check box to specify the full path to the nzload
executable. You must check this option if the nzload path is not
specified in the PATH environment variable.

Full path to nzload executable

Full path to the nzload executable on the machine in use. It is
advisable to specify the nzload path in the PATH environment
variable instead of selecting this option.

tStatCatcher Statistics

Select this check box to collect log data at the component
level.

Enable parallel execution

Note that when parallel execution is enabled, it is not possible to use global
variables to retrieve return values in a subjob.

Global Variables

NB_LINE: the number of rows processed. This is an After
variable and it returns an integer.

ERROR_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error check box is
cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable
functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl +
Space to access the variable list and choose the variable to use from it.

For further information about variables, see
Talend Studio

User Guide.

Usage rule

This component is mainly used when non particular transformation
is required on the data to be loaded ont to the database.

This component can be used as a standalone or an output
component.

-If

Name of the log file to generate. The logs will be appended if the
log file already exists. If the parameter is not specified, the
default name for the log file is
‘<table_name>.<db_name>.nzlog‘.
And it’s generated under the current working directory where the job
is running.

-bf

Name of the bad file to generate. The bad file contains all the
records that could not be loaded due to an internal Netezza error.
The records will be appended if the bad file already exists. If the
parameter is not specified, the default name for the bad file is
‘<table_name>.<db_name>.nzbad‘.
And it’s generated under the current working directory where the job
is running.

-ouputDir

Directory path to where the log and the bad file are generated. If
the parameter is not specified the files are generated under the
current directory where the job is currently running.

-logFileSize

Maximum size for the log file. The value is in MB. The default
value is 2000 or 2GB. To save hard disk space, specify a smaller
amount if your job runs often.

-compress

Specify this option if the data file is compressed. Valid values
are “TRUE” or “FALSE“. Default value if “FALSE“.

This option is only valid if this component is used by itself
and not connected to another component via an input flow.

-skipRows <n>

Number of rows to skip from the beginning of the data file. Set
the value to “1” if you like to skip the header row from the data
file. The default value is “0”.

This option should only be used if this component is used by
itself and not connected to another component via an input
flow.

-maxRows <n>

Maximum number of rows to load from the data file.

This option should only be used if this component is used by
itself and not connected to another component via an input
flow.

-maxErrors

Maximum number of error records to allow before terminating the
load process. The default value is “1”.

-ignoreZero

Binary zero bytes in the input data will generate errors. Set this
option to “NO” to generate error or to “YES” to ignore zero bytes.
The default value is “NO”.

-requireQuotes

This option requires all the values to be wrapped in quotes. The
default value is “FALSE”.

This option currently does not work with input flow. Use this
option only in standalone mode with an existing file.

-nullValue <token>

Specify the token to indicate a null value in the data file. The
default value is “NULL”. To improve slightly performance you can set
this value to an empty field by specifying the value as single
quotes: “””.

-fillRecord

Treat missing trailing input fields as null. You do not need to
specify a value for this option in the value field of the table.
This option is not turned on by default, therefore input fields must
match exactly all the columns of the table by default.

Trailing input fields must be nullable in the database.

-ctrlChar

Accept control chars in char/varchar fields (must escape NUL, CR
and LF). You do not need to specify a value for this option in the
value field of the table. This option is turned off by
default.

-ctInString

Accept un-escaped CR in char/varchar fields (LF becomes only end
of row). You do not need to specify a value for this option in the
value field of the table. This option is turned off by
default.

-truncString

Truncate any string value that exceeds its declared char/varchar
storage. You do not need to specify a value for this option in the
value field of the table. This option is turned off by
default.

-dateStyle

Specify the date format in which the input data is written in.
Valid values are: “YMD”, “Y2MD”, “DMY”, “DMY2”, “MDY”, “MDY2”,
“MONDY”, “MONDY2”. The default value is “YMD”.

The date format of the column in the component’s schema must
match the value specified here. For example if you want to load
a DATE column, specify the date format in the component schema
as “yyyy-MM-dd” and the -dateStyle option as “YMD”.

For more description on loading date and time fields, see Loading DATE, TIME and TIMESTAMP columns.

-dateDelim

Delimiter character between date parts. The default value is “-”
for all date styles except for “MONDY[2]” which is ” ” (empty
space).

The date format of the column in the component’s schema must
match the value specified here.

-y2Base

First year expressible using two digit year (Y2) dateStyle.

-timeStyle

Specify the time format in which the input data is written in.
Valid values are: “24HOUR” and “12HOUR”. The default value is
“24HOUR”. For slightly better performance you should keep the
default value.

The time format of the column in the component’s schema must
match the value specified here. For example if you want to load
a TIME column, specify the date format in the component schema
as “HH:mm:ss” and the -timeStyle option as “24HOUR”.

For more description on loading date and time fields, see Loading DATE, TIME and TIMESTAMP columns.

-timeDelim

Delimiter character between time parts. The default value is “:”.

-timeRoundNanos

Allow but round non-zero digits with smaller than microsecond
resolution.

-boolStyle

Specify the format in which Boolean data is written in the data.
The valid values are: “1_0”, “T_F”, “Y_N”, “TRUE_FALSE”, “YES”. The
default value is “1_0”. For slightly better performance keep the
default value.

-allowRelay

Allow load to continue after one or more SPU reset or failed over.
The default behaviour is not allowed.

-allowRelay <n>

Specify number of allowable continuation of a load. Default value
is “1”.