ICM Repeated DB Execute for any task steps

Introduction

In our real world we always found new needs that cannot done easily throw out-of-the-box tools. Currently I am using IBM Case Manager for delivering Automation solution, but I realized that our solution needs a custom database, during discussing with my technical colleagues, we came up with the following solution and now I am sharing this with you.

Use case

You are working in a new IBM Case Manager solution, and you have to log some case properties details in custom SQL Database for each and every step, you can do this regularly by calling stub step after each and every step, but this solution with complex your tasks design.
I am going to share with you another solution to do same requirements with better and simple solution.

Solution

Our solution is simply built on creating an automatic task (dbexec) that run with precondition based on case property value, this task actually used to run the DB Execute system step. Other task steps will set specific case property to trigger the dbexec task.

Walkthrough

Let go throw this solution in more details.

Procedure 1: Create the solution, case and tasks

1- Open Case Manager Builder from the browser, and login with valid user name and password.

2- Create solution name loans, then create the following properties (Loan Name, tmpstepid) and create role named loan manager

3- Create case named Loan DB Exec and modify the tmpstepid property to be hidden from properties, then add both properties to the case properties.

4- Create new automatic tasks named Main Task , and another automatic tasks named dbexec but with condition and repeated as shown in the following pictures
 

Procedure 2: Design tasks

1- Open the main task designer and drag Role Lane then add three steps names (step1, step2, complete) to it.

2- Assign the two case properties for each step, then draw the connectors between launch Step and other three steps as shown below

3- Open dbexec designer and drag Role Lane then add step to it, after that add another stub step to the system Lane and draw connectors as follows

Procedure 3: Prepare SQL table and procedure

1- Here we are going to create the SQL table and stored procedure that needs to execute for each and every step in the Main Task2.

2- Below SQL scripts shows how to create both of them:

   1: CREATE TABLE [dbo].[loans](

   2:     [stepid] [nchar](10) NULL,

   3:     [loanname] [nvarchar](50) NULL

   4: ) ON [PRIMARY]

   5:  

   6: create PROCEDURE [dbo].[sp_addloan]

   7:         @loanname varchar(100) output

   8:     ,@stepid varchar(100) output    

   9: AS

  10: BEGIN

  11: insert into loans values(@loanname,@stepid)    

  12:     END

  13: GO

Procedure 4: Modify the main task from Process Designer

1- In Workplace XT, click Tools > Advanced tools > Process Designer.

2- Click File > Solution > Edit > case_manager_design_object_store_name > IBM Case Manager > Solutions > Loans > Solution Definition

3- Open the Main Task and Assign the tmpstepid property after or before execution for each step as follows

3- task care you have to assign different value for each step to launch the DBexec task

Procedure 5: Modify the DBexec from Process Designer

1- Open the dbexec task in designer as shown in procedure 4, steps 1 & 2

2- Configure the DBexecute step

3- Select the Route between the Stub and Dummy step and modify it to be a conditional  Route the in the expression space enter the following line 1=2, as shown below:

4- Yes you are right this condition will never happen and the task will ends after the Db execution.

Procedure 6: Deploy and test the solution

1- From Process Designer Check in the solution

2- Open Case Manager Builder and deploy the solution

3- Open Case Manager Client and create new case from Loan Db Exec

4- Go throw step1, step2 and complete steps.

5- Open the database and you will find three records in you custom table.

Conclusion

In this post we have seen how to run repeated DB execute task for each step in other tasks. This solution may be used in other ways for different requirements for example you can use it to send email instead of executing DB. i hope that help.

Reference

before ending this post i would like to thank Mr. ahmed Elwakeel my dear colleague who worked with me to implement this solution.