In a mapping, you can configure a Source transformation to represent a 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 source 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.
Select distinct rows only
This property doesn't apply for the Source transformation.
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 read data from PostgreSQL.
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:
1In the Source transformation, select the Source Type as Multiple Objects.
2From the Actions menu, select Add Source Object.
3In the Select Source Object window, select the source object that you want to add, and then click OK.
4From the Actions menu, select Advanced Relationship.
5In the Advanced Relationship window, click Add Object to add more objects.
6In the Select Source Object window, select the source object with which you want to define a relationship, and then click OK.
7In the Advanced Relationship window, select the required fields, and set the conditions or specify a query to define a relationship between the tables.
8Click OK.
The following image shows an example of an advanced relationship condition defined between the PostgreSQL tables:
Rules and guidelines for adding multiple source objects
Consider the following rules and guidelines when you add multiple source objects:
•You cannot use a self join when you add multiple source objects.
•You cannot search for a schema when you add a related source object. You must scroll down and manually select the schema.
•When you select parent and child objects that have a primary key and foreign key relationship, ensure that the foreign key of the related object is not a primary key in the table.
