Table of Contents

Search

  1. Preface
  2. Introduction to TDM Administration
  3. User and Role Administration
  4. Security Administration
  5. System Preferences
  6. TDM Server Administration
  7. Connections
  8. Pass Phrases
  9. Dictionaries
  10. User-Defined Datatypes

Netezza Connections

Netezza Connections

You can create a Netezza connection in the Test Data Manager to perform data subset and data masking operations.
The following table describes the database connection properties for a Netezza database:
Property
Description
Name
Required. Name of the connection. The name is not case sensitive and must be unique within the domain. It cannot exceed 128 characters, start with a number, contain spaces, or contain the following special characters:
~ ` ! $ % ^ & * ( ) - + = { [ } ] | \ : ; " ' < , > . ? /
Connection Type
Required. The connection type. Select Netezza.
Description
The description of the connection. The description cannot exceed 255 characters.
User Name
Required. The database user name.
Use Parameter in Password
Indicates the password for the database user name is a session parameter. $Param
Name
. Define the password in the workflow or session parameter file, and encrypt it using the
pmpasswd
CRYPT_DATA option.
Used for data subset and data masking operations. Default is disabled.
Password
Required. The password for the database user name.
Owner
The owner of the connection. Default is the user who creates the connection. You can change the owner of the connection.
Metadata Connection String
Required. The JDBC connection URL used to access metadata from the database. Enter
jdbc:netezza://<host>:5480/<databasename>
.
Used for all operations.
JDBC Login Password
Required if Use Parameter in Password is selected. The password for the JDBC user.
Used for import from source and data discovery operations.
Code Page
Code page the Integration Service uses to read from a source database or write to a target database or file.
Data Access Connection String
The connection string used to access data from the database. Enter <database name>.
Environment SQL
SQL commands to set the database environment when you connect to the database. The PowerCenter Integration Service runs the connection environment SQL each time it connects to the database.
Transaction SQL
SQL commands to set the database environment when you connect to the database. The PowerCenter Integration Service runs the transaction environment SQL at the beginning of each transaction.
Connection Retry Period
Number of seconds the Integration Service attempts to reconnect to the database if the connection fails. If the Integration Service cannot connect to the database in the retry period, the operation fails.
Default is 0.
You can transfer data in Netezza by using bulk mode. Use bulk mode to increase session performance.
In bulk mode, the PowerCenter Integration Service reads and writes Netezza data through an external table. An external table definition is stored within the Netezza database but the data is saved externally in a location that is accessible to the Netezza host or the client system. Create external tables to structure your loading operation and manipulate data by using Netezza SQL.
The following table describes Bulk Writer properties that you can enter for a Netezza connection to use the bulk loader option to write data:
Property
Description
Delimiter
The delimiter separates successive input fields. Set the delimiter to any value supported. The value must not be a part of the input data. Default is |.
Null value
The null value parameter of the external table. The PowerCenter Integration Service uses the null value internally. Maximum value is one character. Default is blank.
Escape Character
Escape character of the external table. If the data contains NULL, CR, and LF characters in the Char or Varchar field, you need to add an escape character for these fields before loading. Enter a backslash (\) as the escape character.
Quoted Value
The quoted value parameter of the external table. Select SINGLE or DOUBLE to enclose the field in single or double quotes. Select NO to omit quotes. Default is NO. The quoted value is not a part of the data.
Ignore Key Constraints
Ignores constraints on primary key fields. When you select this option, the PowerCenter Integration Service can write duplicate rows with the same primary key to the target. Default is disabled. The PowerCenter Integration Service ignores this value when the target operation is "update as update" or "update else insert."
Duplicate Row Handling
Determines how the PowerCenter Integration Service handles duplicate rows. Select one of the following values:
  • First Row. The PowerCenter Integration Service passes the first row to the target and rejects the rows that follow with the same primary key.
  • Last Row. The PowerCenter Integration Service passes the last duplicate row to the target and discards the rest of the rows.
Default is First Row.
Bad File Name
Directory where the PowerCenter Integration Service creates a reject file. The file contains the rejected records.
By default, the PowerCenter Integration Service creates a bad file in the following directory:
$PMBadFileDir
Socket Buffer Size
Buffer size that the PowerCenter Integration Service uses to write data. Set the socket buffer size to 25 to 50 % of the DTM buffer size to increase session performance. You might need to test different settings for optimal performance. Enter a value between 4096 and 2147483648 bytes.
Default is 8388608 bytes.
Control Character
The control character parameter of the external table to transfer data containing control characters. You can enter control characters for Char and Varchar fields. If you enter a control character, you must add an escape character for the NULL, CR, and LF fields. Default is TRUE.
CRINSTRING
The CRinstring parameter to transfer data containing carriage returns (CR). You can enter a non escape CR in Char or Varchar fields. To load the control characters present in the Char and Varchar fields, set the CTRLCHARS and CRINSTRING parameters to TRUE in the session properties for the Netezza source.
Default is TRUE.
Pipe Directory Path
Path for the PowerCenter Integration Service to create the pipe for the external table. If you do not specify the path, the PowerCenter Integration Service uses the following directory to create the pipe for the external table:
<Informatica installation directory>/server/bin
Required if the machine hosting the PowerCenter Integration Service is on HP-UX and the following directory is on an NFS-mounted directory:
<Informatica installation directory>/server/bin>
Enter a path that does not use an NFS mount.
Error Log Directory Name
Error log directory can reside on the machine where the PowerCenter Integration Service runs. For example, you can use the following directory:
$PMBadFileDir
By default, the PowerCenter Integration Service creates the error log in the following directory on the machine hosting the NetezzaPerformance Server:
/tmp
The PowerCenter Integration Service creates a reject file in the error log directory if the data is not valid.
The following table describes Bulk Reader properties that you can enter for a Netezza connection to use the bulk loader option to read data:
Property
Description
Delimiter
The delimiter separates successive input fields. Set the delimiter to any value supported. The value must not be a part of the input data. Default is |.
Null value
The null value parameter of the external table. The PowerCenter Integration Service uses the null value internally. Maximum value is one character. Default is blank.
Escape Character
Escape character of the external table. If the data contains NULL, CR, and LF characters in the Char or Varchar field, you need to add an escape character for these fields before loading. Enter a backslash (\) as the escape character.
Socket Buffer Size
Buffer size that the PowerCenter Integration Service uses to read data. Set the socket buffer size to 25 to 50% of the DTM buffer size to increase session performance. You might need to test different settings for optimal performance. Enter a value between 4096 and 2147483648 bytes.
Default is 8388608 bytes.
Pipe Directory Path
Path for the PowerCenter Integration Service to create the pipe for the external table. If you do not specify the path, the PowerCenter Integration Service uses the following directory to create the pipe for the external table:
<Informatica installation directory>/server/bin
Required if the machine hosting the PowerCenter Integration Service is on HP-UX and the following directory is on an NFS-mounted directory:
<Informatica installation directory>/server/bin>
Enter a path that does not use an NFS mount.