Configuring Greenplum Target Session Properties

Greenplum Database session properties configure the services provided by GPSS in the Informatica mapping for the Greenplum loader target. When you create a new loader target mapping, the session properties are configured to use defaults. Consider modifying the session properties for common tasks such as:

• Changing the method that GPSS uses to modify Greenplum Database tables. GPSS can modify target tables using either Insert, Update, or Merge operations.
• For Update or Merge operations, specifying the columns to update and the criteria used to determine when to update the columns.
• Determining whether to truncate the target table before loading data.
• Placing limits on the number of rows that can be discarded due to formatting errors.
• Specifying custom SQL statements to run before or after load operations.

You can also use session properties to customize other aspects of the load session, such as temporary tables names and the character encoding to use. See Greenplum Session Property Reference.

Procedure for Configuring Greenplum Session Properties

1. Start the Informatica Developer Tool application.

2. If the Object Explorer view is not visible, display it by selecting Window > Show View > Object Explorer.

3. Click the name of the Greenplum Database loader target to display its configuration window.

4. Select Data Object Operations to display information about the target and its configuration properties. This view contains several tabs that provide the following information:

   • General displays the configured name and description for the loader target.
   • Ports displays a list of column names in the target, along with information about the data contained in each column.
   • Target lists the Greenplum Database table(s) that were selected as part of the loader target.
   • Runtime shows the connection name associated with the target (used for communicating with GPSS).
   • Advanced provides a list of options to configure the services provided by GPSS in the Greenplum Database cluster for this target.

5. Click the Advanced tab to display or modify any of the Session properties described in Greenplum Session Property Reference.

   

Greenplum Session Property Reference

The following table describes the available properties for configuring an Informatica session that uses a Greenplum Database loader target.

Property Name	Description
ErrorLimitRows	Optional. Enables single row error isolation mode for this load operation. When enabled, input rows that have format errors will be discarded provided that the error limit count is not reached on any Greenplum Database segment instance during input processing. If the error limit is not reached, all good rows will be loaded and any error rows will either be discarded or captured as part of error log information. The default is to halt the load operation on the first error encountered. Note that single row error isolation only applies to data rows with format errors; for example, extra or missing attributes, attributes of a wrong data type, or invalid client encoding sequences. Constraint errors, such as primary key violations, will still cause the load operation to halt if encountered.
ErrorLimitPercent	Optional. Specifies an error limit for the load operation as a percentage of rows, instead of as a fixed number of rows as with ErrorLimitRows. If both ErrorLimitRows and ErrorLimitPercent are configured, ErrorLimitRows takes precedence and ErrorLimitPercent is ignored.
OperationType	Required. Specifies the method that GPSS uses to load data into Greenplum Database tables for this session. There are three available load modes: Insert - Loads data into the target table using the following method: INSERT INTO target_table SELECT * FROM input_data; Update - Updates the UpdateColumns of the target table where the rows have MatchColumns attribute values equal to those of the input data, and the optional Condition is true. Merge - Inserts new rows and updates the UpdateColumns of existing rows where attribute values are equal to those of the input data, and the optional MatchColumns is true. New rows are identified when the MatchColumns value in the source data does not have a corresponding value in the existing data of the target table. In those cases, the entire row from the source file is inserted, not only the those specified in MatchColumns and UpdateColumns. If there are multiple new MatchColumns values that are the same, only one new row for that value will be inserted. Use Condition to filter out the rows to discard. OperationType uses Insert mode by default.
TruncateTable	Optional. Specifies whether to truncate the Greenplum Database target table before loading data into it.
UpdateColumns	Required if the OperationType is Update or Merge. Specifies the column(s) to update for the rows that meet the MatchColumns criteria and the optional Condition for updates. Values for this property are case sensitive, and cannot contain a quotation mark (") or comma (,).
MatchColumns	Required if the OperationType is Update or Merge. Specifies the column(s) to use as the join condition for the update. The attribute value in the specified target column(s) must be equal to that of the corresponding source data column(s) in order for the row to be updated in the target table. Values for this property are case sensitive, and cannot contain a quotation mark (") or comma (,).
Condition	Optional. Specifies a Boolean condition that must be met in order for a row in the target table to be updated. The value of this property must be valid SQL that can appear in the WHERE clause of a Greenplum Database SQL Statement. Use the token into_table to refer to the target table name.
StagingSchema	Optional. Specifies the name of the schema where GPSS creates all temporary and external tables during the load operation. If this property is not set, GPSS creates external and temporary tables in the same schema as the target table. The name of each external table is formed from the hash value of the gpfdist IP address, the target table name, and the target schema name. External tables are reused by default during load operations.
Encoding	Optional. The character set encoding of the source data. Specify a string constant (such as ‘SQL_ASCII’), an integer encoding number, or ‘DEFAULT’ to use the default client encoding. If not specified, the default client encoding is used.
Timeout	Optional. Specifies the maximum amount of time, in seconds, that the Connector will wait for GPSS to respond to a request. The Connector closes the connection to GPSS if the timeout value is reached. A value of “0” (the default) deactivates the timeout, and the Connector will wait indefinitely for a response from GPSS. Enter a value from 0 to 30 seconds.
PreSQL, PostSQL	Optional. Defines SQL commands to run before and/or after the load operation. You can specify multiple commands to run. List commands in the order of desired execution, and enclose commands in quotes.
MaxErrorRows	Optional. Identifies the maximum number of error rows to write to the session log. The default value is 0.