Example–Defining a variable comparison operator with a stored procedure operator

This example uses the output of a stored procedure operator as an input to a variable comparison operator.

Before you begin

Before you begin
  • In the Db2 command prompt, create a stored procedure ret_dec() and store it inside the GOSALES schema of the GSDB database. The SQL statement to create the stored procedure is: 
    create procedure GOSALES.ret_dec 
(in in_val decimal(31,6), out out_val decimal(31,6))
language sql 
begin set out_val = in_val+1; end
  • Include the stored procedure in the physical database metadata so that it is available in the control flow. To do so, create a physical database model file for the GSDB database.
  • Create a variable group GRP1 in the Design Studio and add a variable var1 of type decimal in this group.

Example

In this example, the stored procedure accepts a fixed input value of type decimal, and adds one to it. The output from the stored procedure is compared with a fixed value. When the comparison succeeds (true or false), an e-mail is sent.

To define a variable comparison operator that uses the output of a stored procedure operator as an input:
  1. Create the control flow as shown in the figure.
    Figure 1. Control flow that shows the use of the variable comparison operator with the stored procedure operator.
    Figure shows the complete control flow that uses the variable comparison operator with the stored procedure operator.
  2. Define the properties of the stored procedure operator as follows:
    1. In the Stored procedure field, click the ellipsis (...) button and select the stored procedure ret_dec from the GOSALES schema of the GSDB database. In the Schema name field, the Design Studio displays the schema name GOSALES where the stored procedure is stored.
    2. Click the IN Parameters tab.
    3. In the IN_VAL field, type 1.2.
    4. Click the OUT Parameters tab.
    5. In the OUT_VAL field, click the ellipsis (...) button and select the var1 variable from the GRP1 variable group.
  3. Define the properties of the variable comparison operator as follows:
    1. In the Comparison type list, select Numeric.
    2. In the Variable field, click the ellipsis (...) button and select the var1 variable from the GRP1 variable group.
    3. In the Arithmetic operator list, select =.
    4. In the Numeric value field, type 0.2.
  4. Specify the sender, recipient e-mail addresses and subject lines for each of the e-mail operators in the flow.
  5. Save, validate, and execute the control flow.