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