PostgreSQL Connector > PostgreSQL pushdown optimization > Cross-schema pushdown optimization
  

Cross-schema pushdown optimization

You can use cross-schema pushdown optimization for a mapping task to read from or write data to PostgreSQL objects associated with different schemas within the same PostgreSQL database.
To use cross-schema pushdown optimization, create two PostgreSQL ODBC connections and specify the schema in each connection. Ensure that the schema in the source connection is different from the schema in the target connection, but both the schemas must belong to the same database. When you configure pushdown optimization for the mapping task, enable cross-schema pushdown optimization in the advanced session properties. By default, the check box is selected.

Configuring cross-schema optimization for PostgreSQL mapping

Create two PostgreSQL mappings. For example, perform the following steps to configure cross-schema pushdown optimization for a PostgreSQL mapping task:
    1. Create the following two PostgreSQL ODBC connections, each defined with a different schema:
    1. a. Create an psql_odbc1 PostgreSQL ODBC connection and specify PSQL_SCHEMA1 schema in the connection properties.
    2. b. Create psql_odbc2 PostgreSQL ODBC connection and specify PSQL_SCHEMA2 schema in the connection properties.
    2. Create a PostgreSQL mapping, m_psql_pdo_acrossSchema. Perform the following tasks:
    1. a. Add a Source transformation and include a PostgreSQL source object and connection psql_odbc1 to read data using PSQL_SCHEMA1.
    2. b. Add a Target transformation and include a PostgreSQL target object and connection psql_odbc2 to write data using PSQL_SCHEMA2.
    3. Create a PostgreSQL mapping task, and perform the following tasks:
    1. a. Select the configured PostgreSQL mapping, m_psql_pdo_acrossSchema.
    2. b. On the Schedule tab, in the Pushdown Optimization section, set the pushdown optimization value to Full.
    3. c. In the Advanced Session Properties section, select the Enable cross-schema pushdown optimization check box.
    4. The following image shows the configured Enable cross-schema pushdown optimization property:
      The Schedule tab shows the configured Enable cross-schema pushdown optimization property in the Advanced Session Properties section.
    5. d. Save the task and click Finish.
    When you run the mapping task, the Secure Agent reads data from the PostgreSQL source object associated with the PSQL_SCHEMA1 schema and writes data to the PostgreSQL target object associated with PSQL_SCHEMA2 schema.