Data Warehouse Systems

Writing Stored Procedure in Netezza

Intent of this article to help those who want to write stored procedure with multiple DML operations and do not have SP development background using Netezza. I did not find any article that could help new developer to write stored procedure in Netezza with OLTP development background. There are many documents available on this subject but I did not find any of these helpful so thought of putting this together.

Scenario: Requirement was to transfer data between tables with following conditions:

  1. Delete corresponding records from Final table matching Stage table records
  2. Insert data into Final Table from Stage table
  3. Delete Stage table for new load

Solution: This procedure was written for transferring data from Microsoft SQL Server to Netezza database using Change Data Capture

CREATE OR REPLACE PROCEDURE CHANGEDATACAPTURE_AUDIT_DI()
RETURNS BOOLEAN
LANGUAGE NZPLSQL AS
BEGIN_PROC

BEGIN

execute immediate 'delete from ADMIN."AUDIT" t1
where exists (select t2.AuditID from ADMIN."STGAUDIT" t2 where t1.AuditID = t2.AuditID)'
;

execute immediate 'insert into ADMIN."AUDIT" select * from ADMIN."STGAUDIT"';

execute immediate 'delete from ADMIN."STGAUDIT"';

END;

END_PROC;

In above example, AUDIT is Final table, and STGAUDIT is Stage table. In order to execute above scripts to create stored procedure, follow steps below:

  1. Select all scripts
  2. Execute it in single batch as shown below (Tool used for this example : Aginity Workbench).

 

Now, to execute; run following command:

EXECUTE CHANGEDATACAPTURE_AUDIT_DI();

 

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top