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

PostgreSQL objects in mappings

When you create a mapping, you can configure a Source or Target transformation to represent a PostgreSQL object.
When you configure a mapping, you can parameterize the source object and the PostgreSQL connection.

PostgreSQL sources in mappings

In a mapping, you can configure a Source transformation to represent an PostgreSQL source.
The following table describes the PostgreSQL source properties that you can configure in a Source transformation:
Property
Description
Connection
Name of the source connection, or create a connection parameter.
If you want to overwrite the target connection properties at runtime, select the Allow parameter to be overridden at run time option. Specify the parameter file directory and name in the advanced session properties.
Source type
Type of the source object. Select Single Object, Multiple Objects, Query, or Parameter.
When you select multiple objects as the source type to read from multiple PostgreSQL sources, you can use the advanced relationship option to define the relationship for the objects that you want to join.
When you select query as the source type, specify the SQL statement in the Query field.
You can partially parameterize the query source type. If you want to overwrite the query object at runtime, select the Allow parameter to be overridden at run time option.
When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Object
Name of the source object.
Parameter
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the source object or click New Parameter to define a new parameter for the source object.
The Parameter property appears only if you select parameter as the source type.
If you want to overwrite the source object at runtime, select the Allow parameter to be overridden at run time option.
When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
The following table describes the PostgreSQL query 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.
Sort
Add conditions to sort records.
You can specify the following sort conditions:
  • - Not parameterized. Select the fields and type of sorting to use.
  • - Parameterized. Use a parameter to specify the sort option.
  • - Sort Order. Sorts data in ascending or descending order, according to a specified sort condition.
The following table describes the PostgreSQL advanced source properties that you can configure in a Source transformation:
Property
Description
Pre-SQL
The pre-SQL commands to run a query before you read data from PostgreSQL.
You can partially parameterize pre-SQL with values specified in a parameter file.
Post-SQL
The post-SQL commands to run a query after you write data to a target.
You can partially parameterize post-SQL with values specified in a parameter file.
Fetch Size
Determines the number of rows to read in one resultant set from PostgreSQL. Specifying a number limits the number of rows to fetch with each trip to the database and avoids unnecessary memory consumption.
You can specify a maximum fetch size of 2147483647. Default is 100000.
Schema Name
Overrides the schema name of the source object.
Source Table Name
Overrides the default PostgreSQL source table name.
Tracing Level
Sets the amount of details that appear in the log file.
You can choose terse, normal, verbose initialization, or verbose data.
Default is normal.
SQL Override
The SQL statement to override the default query generated from the specified source type to read data from the PostgreSQL source.
You can partially parameterize SQL override with values specified in a parameter file.
Ensure that the list of selected columns, data types, and the order of the columns that appear in the query matches the columns, data types, and order in which they appear in the source object.
Note: SQL override is not applicable when you enable partitioning. If you specify an SQL override and configure partitioning, the mapping fails.

Adding multiple source objects

When you create a Source transformation, you can select multiple PostgreSQL objects as the source type, and then configure an advanced relationship to combine the tables.
Perform the following steps to join multiple objects in a Source transformation:
    1. In the Source transformation, select the Source Type as Multiple Objects.
    2. From the Actions menu, select Add Source Object.
    Select add source object in the objects and relationships section.
    3. In the Select Source Object window, select the source object that you want to add, and then click OK.
    Source object is selected to configure a relationship.
    4. From the Actions menu, select Advanced Relationship.
    Added related objects for the advanced relationship.
    5. In the Advanced Relationship window, click Add Object to add more objects.
    To add more objects, you can select the Add Object option.
    6. In the Select Source Object window, select the source object with which you want to define a relationship, and then click OK.
    The selected object with which you want to configure a relationship.
    7. In the Advanced Relationship window, select the required fields, and set the conditions or specify a query to define a relationship between the tables.
    The defined relationship between the source objects.
    8. Click OK.
    The following image shows an example of an advanced relationship condition defined between the PostgreSQL tables:
    The defined relationship between the tables with the advanced relationship option.

Rules and guidelines for adding multiple source objects

Consider the following rules and guidelines when you add multiple source objects:

Configuring key range partition

Configure key range partition to partition PostgreSQL data based on field values. Key range is supported for only the numeric and date/time data types.
    1. In Source Properties, click the Partitions tab.
    2. Select the required Partition Key from the list.
    3. Click Add New Key Range to add partitions.
    The following image displays the details of the Partitions tab. The Edit Partition Point dialog box shows Partition#1 and Partition#2. Partition#2 is selected. At the bottom of the dialog box is the partition type selection list. Key Range is selected.
    4. Specify the Start range and End range.

PostgreSQL targets in mappings

To write data to PostgreSQL, configure a PostgreSQL object as the target in a mapping.
Specify the name and description of the PostgreSQL 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, or create a connection parameter.
If you want to overwrite the target connection properties at runtime, select the Allow parameter to be overridden at run time option. Specify the parameter file directory and name in the advanced session properties.
Target Type
Type of the target object. Select Single Object or Parameter.
Object
Name of the target object. You can select an existing target object from the displayed list or you can create a target at runtime. When you want to create a target at runtime, specify the target object name and the path for the target object.
For a list of supported data types that you can write to PostgreSQL using the Create New at Runtime option, see the Data Type References chapter.
Parameter
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the target object or click New Parameter to define a new parameter for the target object.
The Parameter property appears only if you select parameter as the target type.
If you want to overwrite the target object at runtime, select the Allow parameter to be overridden at run time option.
When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Operation
Type of the target operation.
Select Insert, Upsert, Update, Delete, and Data Driven.
Data Driven Condition
Enables you to define expressions that flag rows for an insert, update, delete, or reject operation.
Appears only when the operation type is Data Driven.
Note: When you configure the DD_REJECT operation in the data driven mode to reject data to PostgreSQL, the rejected records are not written to the error files and the session log shows the number of rejected rows as zero.
Update Columns
Specify the columns that you want to use as a logical primary key for performing update, upsert, and delete operations on the target.
This field is not required if the target table already has a primary key. If the target table does not have a primary key, ensure that the columns selected in the Update Columns field has a unique constraint for the upsert operation.
This property is not applicable for the insert operation.
The following table describes the PostgreSQL advanced target properties:
Property
Description
Update Mode
Specifies the mode to write data to PostgreSQL target. You can specify the following modes:
  • - Update As Update. Updates all rows flagged for update if the entries exist.
  • - Update Else Insert. Updates all rows flagged for update if the entries exist in the target. If the entries do not exist, the Secure Agent inserts the entries.
Schema Name
Overrides the schema name of the target object.
Target Table Name
Overrides the default PostgreSQL target table name.
Pre-SQL
The pre-SQL commands to run a query before you read data from a source.
You can partially parameterize pre-SQL with values specified in a parameter file.
Post-SQL
The post-SQL commands to run a query after you write data to PostgreSQL.
You can partially parameterize post-SQL with values specified in a parameter file.
Truncate Target
The Secure Agent truncates the target before writing the data.
Enable target bulk load
Performs bulk upload when you configure an insert operation to write to PostgreSQL. Select this option to improve the performance of inserting data in bulk to PostgreSQL.
Default is unselected.
Note: When you enable the target bulk mode to insert data to PostgreSQL, error files are not generated for rejected records.
Batch size
The number of rows that the Secure Agent writes in a single batch to PostgreSQL. Specify a batch size value that is greater than zero.
Applicable if you select the Enable target bulk load option.
Forward Rejected Rows
Not applicable.

Specifying a target

You can use an existing target or create a target to hold the results of a mapping.
To specify the target properties, perform the following tasks:
    1. Select the Target transformation in the mapping.
    2. On the Incoming Fields tab, configure field rules to specify the fields to include in the target.
    3. To specify the target, click the Target tab.
    4. Select the target connection as PostgreSQL.
    5. For the target type, choose Single Object or Parameter.
    6. Specify the target object or parameter. Click Select, choose a target object, and then click OK.
    You can select an existing target object or create a new target object at run time and specify the object name.
    7. To select an existing target object, select Existing and then select the required target table.
    The following image shows the available target tables:
    You can view the target objects from the list.
    8. To create a new target at run time, perform the following tasks:
    Note: When you choose to create a target at runtime and if the target table does not exist in the target database, the Secure Agent creates the table when you run the task. If a table with the same name already exists in the database, the Secure Agent uses the existing table.
    1. a. Select Create New at Runtime.
    2. b. In the Object Name field, specify the table name for the PostgreSQL target.
    3. c. In the Path field, specify the schema for the PostgreSQL target.
    4. You must specify the schema in the following format: <Schema_Name>/<TableType>, where the TableType is TABLE. For example, if you specify the path as private/TABLE, the table is created in the schema named private in PostgreSQL. If you do not specify the path, the Secure Agent considers the schema from the connection properties. If you do not specify the schema in the connection properties, the target table is created in the public schema.
      The following image shows the target object properties for the Create New at Runtime option:
      You can view the target object properties for creating a target table at runtime.
    9. Specify the operation and advanced properties for the target, as required.

PostgreSQL lookups in mappings

In a mapping, you can configure a Lookup transformation to represent an PostgreSQL lookup.
The following table describes the PostgreSQL lookup properties that you can configure in a Lookup transformation:
Property
Description
Connection
Name of the lookup connection, or create a connection parameter.
If you want to overwrite the lookup connection properties at runtime, select the Allow parameter to be overridden at run time option. Specify the parameter file directory and name in the advanced session properties.
Source type
Type of the lookup object. Select Single Object or Parameter.
Object
Name of the lookup object.
Parameter
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the lookup object or click New Parameter to define a new parameter for the lookup object.
The Parameter property appears only if you select parameter as the lookup type.
If you want to overwrite the lookup object at runtime, select the Allow parameter to be overridden at run time option.
When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Multiple Matches
The behavior when the lookup condition returns multiple matches. You can return all rows, any row, the first row, the last row, or an error.
You can select from the following options in the lookup object properties to determine the behavior:
  • - Return first row
  • - Return last row
  • - Return any row
  • - Return all rows
  • - Report error
Schema Name
Overrides the schema name of the lookup object.
Source Table Name
Overrides the default PostgreSQL source table name in the Lookup transformation.

Rules and guidelines for custom query, SQL override, and batch size

Consider the following rules and gudelines when you configure a custom query or SQL override: