Netezza Connector > Mappings and mapping tasks with Netezza Connector > Netezza objects in mappings
  

Netezza objects in mappings

When you create a mapping, you can configure a Source or Target transformation to represent a Netezza object.

Netezza sources in mappings

In a mapping, you can configure a Source transformation to represent a Netezza source.
The following table describes the Netezza source properties that you can configure in a Source transformation:
Property
Description
Connection
Name of the source connection.
Source type
Type of the source object. Select Single Object, Multiple Objects, or Parameter.
Object
Name of the source object.
The following table describes the Netezza filter options that you can configure in a Source transformation:
Property
Description
Filter
Filter value in a read operation. Click Configure to add conditions to filter records and reduce the number of rows that the Secure Agent reads from the source.
You can specify the following filter conditions:
  • - Not parameterized. Use a basic filter to specify the object, field, operator, and value to select specific records.
  • - Completely parameterized. Use a parameter to represent the field mapping.
  • - Advanced. Use an advanced filter to define a more complex filter condition on a dimension or metric.
The following table describes the Netezza advanced source properties that you can configure in a Source transformation:
Property
Description
Pipe Directory Path
Path for the Secure Agent to create the pipe for the external table.
If you do not specify the path, the Secure Agent uses the following directory to create the pipe for the external table:
<Secure Agent installation directory>/apps/Data_Integration_Server/data/temp
Delimiter
Required. Delimiter separates successive input fields.
You can enter any value supported by the Netezza Performance Server. The value can be a part of the data for the Netezza source.
Default is |.
NullValue
NullValue parameter of an external table.
The Secure Agent uses the NullValue internally. Maximum value is one character.
Default is blank.
EscapeCharacter
Escape character of an external table.
If the data contains NULL, CR, and LF characters in the Char or Varchar field, add an escape character in the source data before you extract. Enter an escape character before the data.
The supported escape character is backslash (\).
Socket Buffer Size
Required. The socket buffer size that you must set to increase the mapping performance.
Set from 25 to 50% of the DTM buffer size You might need to test different settings for optimal performance. Enter a value between 4096 and 2147483648 bytes.
Default is 8388608 bytes.
SQL Query Override
Overrides the default query.
Enter the SQL query that the Secure Agent must use to query data from the Netezza source.
Owner Name
The Netezza schema name.
Source Table Name
Overrides the source table name.
For example, you can parameterize the source table name to override the table name in the mapping.
PreSQL
Pre-SQL statement that the Secure Agent runs before reading data from the source.
PostSQL
Post-SQL command that the Secure Agent runs after reading data from the source.
Tracing Level
Amount of detail that appears in the log for the transformation.
Use the following tracing levels:
  • - Terse
  • - Normal
  • - Verbose Initialization
  • - Verbose
Default is normal.

Partitioning for Netezza sources

When you read data from Netezza sources, you can configure passthrough partitioning to read data in parallel and optimize the mapping performance at run time.
The Secure Agent distributes rows of data based on the number of partitions you define. Click the Partitions tab in the Source transformation to define the number of partitions you want to use to read data in parallel.

Netezza targets in mappings

To write data to Netezza, configure a Netezza object as the target in a mapping.
Specify the name and description of the Netezza target. Configure the target and advanced properties for the target object.
The following table describes the target properties that you can configure in a Target transformation:
Property
Description
Connection
Name of the target connection.
Target Type
Type of the target object. Select Single Object, Multiple Objects, or Parameter.
Object
Name of the target object.
Operation
Target operation. Select Insert, Update, Upsert, Delete, or Data Driven.
The following table describes the Netezza advanced target properties:
Property
Description
Pipe Directory Path
Path for the Secure Agent to create the pipe for the external table.
If you do not specify the path, the Secure Agent uses the following directory to create the pipe for the external table:
<Secure Agent installation directory>/apps/Data_Integration_Server/data/temp
Error Log Directory Name
Not applicable.
Enable Insert
The Secure Agent inserts rows into the target table and enforces key constraints.
Default is selected.
Enable Delete
The Secure Agent deletes rows from the target table.
Default is selected.
Enable Update
Required. The Secure Agent updates the rows based on the update strategy option that you specify. Select one of the following values:
  • - Update as Update. The Secure Agent updates all rows flagged for update.
  • - Update as Insert. The Secure Agent inserts all rows flagged for update.
  • - Update else Insert. The Secure Agent first updates all rows flagged for update if they exist in the target. It then inserts the remaining rows marked for insert.
  • - None. The Secure Agent does not update any row.
Default is None.
When you use the Update property, the Secure Agent does not enforce key constraints and writes duplicate rows with the same primary key into the target table.
Truncate Target Table Option
The Secure Agent truncates the target before loading.
Run the truncate table command.
Default is disabled.
Delimiter
Required. Set the delimiter to any value supported by the Netezza Performance Server. The delimiter separates successive input fields. The value must not be a part of the input data.
Default is |.
NullValue
NullValue parameter of the external table. The Secure Agent uses the NullValue internally. Maximum value is one character.
Default is blank.
EscapeCharacter
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
QUOTEDVALUE 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 Secure Agent can write duplicate rows with the same primary key to the target.
Default is disabled.
The Secure Agent ignores this value when the target operation is “update as update” or “update else insert.”
Duplicate Row Handling Mechanism
Determines how the Secure Agent handles duplicate rows. Select one of the following values:
  • - First Row. The Secure Agent passes the first row to the target and rejects the rows that follow with the same primary key.
  • - Last Row. The Secure Agent passes the last duplicate row to the target and discards the rest of the rows.
Default is First Row.
Bad File Name
Not applicable.
Socket Buffer Size
Required. The socket buffer size to set to increase session performance.
Set a size of 25 to 50% of the DTM buffer size. You might need to test different settings for optimal performance. Enter a value between 4096 and 2147483648 bytes.
Default is 8388608 bytes.
Control Character
Required. CTRLCHARS parameter of the external table to transfer data that contains 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
Required. 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.
Table Name Prefix
Prefix that the Secure Agent must add to the target table name.
For example, you can enter a target owner name to override the table name prefix in the mapping.
Target Table Name
Overrides the target table name.
For example, you can parameterize the target table name to override the table name in the mapping.
PreSQL
Pre-SQL statement that the Secure Agent runs before writing data to the target.
PostSQL
Post-SQL statement that the Secure Agent runs after writing data to the target.
Success File Directory
Not applicable.
Error File Directory
Not applicable.
Forward Rejected Rows
Determines whether the transformation passes rejected rows to the next transformation or drops rejected rows.
By default, the agent forwards rejected rows to the next transformation.

Partitioning for Netezza targets

When you use a Netezza connection in a mapping to write data to Netezza, you can configure partitioning for the Netezza target.
In the mapping, you do not have to specify the number of partitions for the target separately. The Secure Agent considers the same number of partitions for the target as the number defined for the source partitioning.

Netezza target data update

The Secure Agent updates target rows based on the update options and the duplicate row handling.

Update as insert

When you configure the mapping to update as insert rows, the Secure Agent uses the following process to update target rows:
The following table describes how the Secure Agent updates the target:
Source Data
Target Data
Updated Target Data
Comment
1,a,1a1
-
-
The source primary key is found in the target. The row is not inserted.
1,b,1b1
-
1,b,1b1
Inserts 1,b,1b1.
1,a,1a2
-
-
The source primary key is found in the target. The row is not inserted.
1,c,1c1
1,c,1c1
1,c,1c1
The source primary key is found in the target. The existing row 1,c,1c1 is retained. No insert is required.
1,d,1d1
-
1,d,1d1
Inserts 1,d,1d1.
1,a,1a3
1,a,1a3
1,a,1a3
The source primary key is found in the target. The existing row 1,a,1a3 is retained. No insert is required.

Update else insert

When you configure the mapping to update else insert rows, the Secure Agent uses the following process to update target rows:
The following table describes how the Secure Agent updates the target:
Source Data
Target Data
Updated Target Data
Comment
1,2
1,6
1,2
Updates 1,6 with 1,2.
The source primary key is found in the target. The target row is updated based on duplicate row handling to use first row.
1,3
1,8
1,2
Updates 1,8 with 1,2.
Duplicate row handling is configured to update with first source row. Subsequent target rows with primary key “1” are updated with first source row.
2,4
-
2,4
Inserts 2,4.
The source primary key is not found in the target. The row is inserted.
2,5
-
-
Drops 2,5.
The source primary key is found in the target, and first duplicate row has been updated in the target.
-
3,7
3,7
Retains 3,7.
No update required.

SQL statements

When you create a mapping in Cloud Data Integration, you can specify SQL statements in the advanced properties for a Netezza source or target.
You can define the following SQL statements for the source:
Pre-SQL
When you define a pre-SQL statement for a Netezza source in a mapping, the Secure Agent runs the pre-SQL statement before reading data from Netezza.
Post-SQL
When you define a post-SQL statement for a Netezza source in a mapping, the Secure Agent runs the post-SQL statement after reading data from Netezza.
You can define the following SQL statements for the target:
Pre-SQL
When you configure a pre-SQL statement for a Netezza target, the Secure Agent runs the pre-SQL statement before writing data to Netezza.
Post-SQL
When you configure a post-SQL statement for a Netezza target, the Secure Agent runs the post-SQL statement after writing data to the target.

Mapping Scenario with SQL Statements Defined for Netezza Sources and Targets

Consider a mapping where you want to read data from a Netezza source and write data to a Netezza target and you want to define SQL statements both for the Netezza source and target:
  1. 1. Define the following pre-SQL statement for the Netezza source: delete from employee_tgt where job_id is null;
  2. 2. Define the following post-SQL statement for the Netezza source: update employee set first_name = 'First_name' where employee_id = 2
  3. 3. Define the following pre-SQL statement for the Netezza target: insert into emp_dept(emp_id,ename,dname) select employee_id,first_name,dept_name from department1,employee where employee.department_id = department1.dept_id
  4. 4. Define the following post-SQL statement for the Netezza target: insert into Staging_SalesData select * from SalesData where sales_dateTime > CURRENT_TIMESTAMP - 10
When you run the mapping, the Secure Agent uses the following sequence for the tasks:
  1. 1. Runs the pre-SQL statement in the source Netezza database and deletes data of employees whose job IDs show null.
  2. 2. Runs the pre-SQL statement in the target Netezza database to update the first name of employees whose employee ID is 2.
  3. 3. Reads the data from the Netezza source database.
  4. 4. Writes the data to the Netezza target database.
  5. 5. Runs the post-SQL statement for staging data populated for another mapping in the Netezza source database and reads data from department1 and employee table based on the department ID.
  6. 6. Runs the post-SQL statement to insert additional sales data available from the last 10 days into the sales data staging table.