Connect CDC SQData

The CREATE PROC command defines a procedure in a Apply Engine script. PROCs provide a method of modularizing the Apply Engine script and are invoked using the CALLPROC function within the SELECT sub-command.

PROCs are typically used to:

•Structure all but the simplest of Engine scripts.

•Perform business logic including the application of filter criteria that may determine if, when and how captured data should be applied to target datastore(s).

•Isolate mapping and mapping functions into separate modules to simplify maintenance and facilitate source control.

•Promote re-use in multiple Apply Engine scripts using the #INCLUDE Parser directive, ie the same mapping an business logic in both Unload and CDC Engines.

•Create common ‘utility routines’ that can be used by multiple  Apply Engine scripts. For example, a common key validation PROC that can be used by multiple applications.

Syntax

CREATE PROC <procedure_name> AS SELECT;

 

Keyword and Parameter Descriptions

<procedure_name>  This parameter specifies the name of the PROC being defined. Later in the script, the PROC can be called using the CALLPROC function.

Example

The following example defines a PROC  named P_EMP that maps the fields in target datastore DESCRIPTION T_EMP.

CREATE PROC P_EMP AS SELECT

{

  T_EMP.FIELD1   = STRING(‘A’)

  T_EMP.FIELD2   = I_EMP.FIELD2

}

FROM CDCIN;

 

The PROC may be called from inside the SELECT sub-command in the Processing Section as shown below.

PROCESS INTO TARGET;

SELECT

{

   CASE DB2TBLNAME(CDCIN)

      WHEN 'EMPLOYEE'   { CALLPROC(P_EMP) APPLY (TARGET, T_EMP) }

      WHEN 'DEPARTMENT' { CALLPROC(P_DEPT) APPLY (TARGET, T_DEPT) }

}

FROM CDCIN;