User Functions are actually just a special type of Procedure defined by a CREATE PROC command. The difference is that these User Functions can be called from anywhere in the Apply Engine script to perform some sort of specialized processing that may be called from one or more of the usual Called Procs that process specific source CDC records. User functions also support parameter passing in both directions and unlike most programming "function" constructs can pass different numbers of parameters in both directions. In other words the calling Proc might pass one parameter to the User Function and receive two back.
User Functions typically include local variables that make them independent of the Calling Procs and the Engine scripts that may use them. There is no practical limitations on what a User Function can do.
Syntax
CREATE
PROC <F_procedure_name> (<L_variable1 [, <L_variable2, ...]) ASSELECT;
| Keyword | Description |
|---|---|
| <F_procedure_name> | This parameter specifies the name of the User Function PROC being defined. By convention (only), Precisely recommends the "F_" prefix on the procedure name. Later in the script, the PROC can be called using the CALLPROC function. |
| <L_variableN> | One or more Local variables DECLARE'd implicitly or explicitly in the User Function. The calling Procedure must supply constants, variables or source field/column names that correspond positionally with those defined in the User Function. |
Example
Eliminate control characters that sometimes are found in Records and Segments created by business applications without strong data validation routines. The following Function will transform any specified EBCDIC Hex character to a space and return the field to the calling Proc.
The syntax of the CALL command in the Calling Procedure might look like the following:
<target_description_alias>.<target_column> = CALL(F_ODDCHAR,<source_description_alias>.<source-field>
CREATE PROC F_ODDCHAR (L_CHAR_FIELD) AS SELECT
-- Replace specific ECBDIC characters with either a space or an alternative character.
-- Add or remove individual Translate statements below as required. This routine may be
-- used by Apply Engines running on zOS and other platforms.
--
-- Note: Source fields must be RETYPED DTBINARY in the Main Engine script for this function to
-- reference the EBCDIC Hex source characters
DECLARE L_CHAR_FIELD;
DECLARE L_ALLCHARS;
L_ALLCHARS = X2C(
'40404040404040404040404040404040' /* 00-0F */
'40404040404040404040404040404040' /* 10-1F */
'40404040404040404040404040404040' /* 20-2F */
'39383736353433323130404040404040' /* 30-3F */
'40404040404040404040404040404040' /* 40-4F */
'40404040404040404040404040404040' /* 50-5F */
'40404040404040404040404040404040' /* 60-6F */
'40404040404040404040404040404040' /* 70-7F */
'40404040404040404040404040404040' /* 80-8F */
'40404040404040404040404040404040' /* 90-9F */
'40404040404040404040404040404040' /* A0-AF */
'40404040404040404040404040404040' /* B0-BF */
'40404040404040404040404040404040' /* C0-CF */
'40404040404040404040404040404040' /* D0-DF */
'40404040404040404040404040404040' /* E0-EF */
'F0F1F2F3F4F5F6F7F8F9404040404040' /* F0-FF */
);
{
-- OUTMSG(0,STRING(V_COUNT, ' Before Translate L_CHAR_FIELD=', L_CHAR_FIELD))
-- 00-0F 10-1F 20-2F 30-3F 40-4F
-- NUL to space DLE to space to space to space SPACE to space
-- SOH to space DC1 to space SOS to space to space SOH-VALUE to space
-- STX to space DC2 to space FS to space SYN to space STX-VALUE to space
-- ETX to space DC3 to space WUS to space IR to space SOT-VALUE to space
-- SEL to space RES/ENP to space BYP/INP to space PP to space ETX-VALUE to space
-- TAB to space NL to space LF to space TRN to space EOT-VALUE to space
-- RNL to space BS to space ETB to space NBS to space
-- DEL to space POC to space ESC to space EOT to space
-- GE to space CAN to space SA to space SBS to space
-- SPS to space EM to space SFE to space IT to space n tilde to vert bar
-- RPT to space UBS to space SM/SW to space RFF to space
-- VT to space CU1 to space CSP to space CU3 to space
-- FF to space IFS to space MFA to space DC4 to space
-- CR to space IGS to space ENQ to space NAK to space
-- SO to space IRS to space ACK to space to space
-- SI to space ITB/IUS to space BEL to space SUB to space
-- L_CHAR_FIELD = TRANSLATE(L_CHAR_FIELD, X2C('9C'), X2C('40')) -- DLM-VALUE to space
-- L_CHAR_FIELD = TRANSLATE(L_CHAR_FIELD, X2C('BA'), X2C('40')) -- OPN-SQR-BRKT-VALUE to space
-- L_CHAR_FIELD = TRANSLATE(L_CHAR_FIELD, X2C('BB'), X2C('40')) -- CLS-SQR-BRKT-VALUE to space
-- Convert_CCSID required only by Linux CDC Apply Engine. Code pages may need to be modified
-- to match source/target systems
if V_ENGINE_TYPE = 'C'
L_CHAR_FIELD = CONVERT_CCSID(L_CHAR_FIELD,819,1047)
return(L_CHAR_FIELD);
}
FROM CDCIN;