Netezza Connector

Netezza Connector

SQL statements

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. Define the following pre-SQL statement for the Netezza source:
    delete from employee_tgt where job_id is null;
  2. Define the following post-SQL statement for the Netezza source:
    update employee set first_name = 'First_name' where employee_id = 2
  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. 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. Runs the pre-SQL statement in the source Netezza database and deletes data of employees whose job IDs show null.
  2. Runs the pre-SQL statement in the target Netezza database to update the first name of employees whose employee ID is 2.
  3. Reads the data from the Netezza source database.
  4. Writes the data to the Netezza target database.
  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. Runs the post-SQL statement to insert additional sales data available from the last 10 days into the sales data staging table.

0 COMMENTS

We’d like to hear from you!