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;