![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Data transfer among multiple applications which comprise a variety of sources and involve numerous interdependent processes can be a big challenge to schedule and monitor with proper sequencing. Abnormally terminated processes often need to restart from their failover point to fulfill business requirements. Also, users often needs to receive information about the process history for audit tracking and monitoring.
This article describes a solution approach that has been proven to be extremely useful in meeting these requirements and challenges, and can be built with minimal resources. The solution can be build using IBM® DB2® relational database capabilities or other relational database systems. There is no need for any specific scheduler or expensive monitoring tool to be used to monitor or schedule the complex processes. The approach is extremely cost-effective and requires minimal skill to develop and maintain.
Here are some of the business problems related to database processes and scheduling. These problems can affect profitability and prevent users from achieving their functional and operational goals.
- Abnormal execution and completion of the internal database processes which cannot be tracked outside the database often need trained resources to understand and monitor them from either the database level or the operating system level.
- Processes which may be completed successfully but require abnormal execution time, resulting in unexpected values, can be a real threat to the business. This may happen even within the lowest level of the dependent processes, resulting in operation failure and malfunction for the other major processes as well. Businesses need to be ensured of the perfect execution of those small dependent processes.
- Multiple sources may pass data into various processes in different sequences and timelines. These processes must be managed and scheduled properly to avoid data inconsistency and overlapping of processes. This may be a big challenge in multi-format complex data integration projects, to achieve the proper scheduling.
- Abnormal process termination or unsuccessful completion of a process is a common scenario in the data integration process flow. Data may need to be restored from the failover point either immediately through manual intervention, or automatically, based on the business requirements.
The following use cases effectively answer these business problems.
- The first use case is, consulting the process history by participants of a process execution.
- The second use case is business activity monitoring. Business activity monitoring stores, queries, or analyze process execution logs to find useful statistical information about the processes. For example, this kind of monitoring can reveal how much time is spent on average in each step of the process and where the botttlenecks are.
- The next use case is the undo functionality and recovery of certain failed processes. Process logs can be used to implement the undo and recoverability of the unsuccessful processes.
- The last use case is the process scheduler, which answers the complex data load frequency.
To solve these problems, you must first identify the components based on their functional nature and designs the data model to store and retrieve essential data elements for the log. Once the components have been realized and the data model has been prepared, the process module can be developed and tested as using the operational and functional test cases. The developed module can be used as plug-in with the existing ETL system and used to insert or update data in the respective database entities. The logging operation starts with a process log footmark in the database and continues logging until the process completes, irrespective of the success or failure of the execution.
The system architecture consists of three major components:
- Process logging component: This fulfills use cases 1, 2, and 3.
This component is responsible for generating process-related information for a specific process in a module. Every time a new process is initiated, this component logs its information with a predefined execution state. Until the process has been completed successfully or abandoned, the component stays alive and tries to log the latest state of the process in the database. The component interacts with the process scheduling component at the time of process initiation and also with the process error management component to log errors that occurred while processing. - Process and program scheduling component: This fulfills use case 4.
This component controls the initiation sequence of the processes. This is a dependent component that interacts with the process logging component and executes the code associated with the process according to the schedule. - Process error management component: This fulfills use cases 2, 3 and 4.
This component is responsible for generating and updating error data from the predefined error list and updating information to the process logging component.
The solution of the problem is very effective especially in data integration and data transformation (ETL) applications. The solution is also effective in other message-driven integration projects where many processes are being executed at regular intervals. Figure 1 illustrates the solution data flows.
Figure 1. Data flow diagram
The solution has two major parts:
- Designing the data model
- Designing and building the logic of the operation
The logical data model contains the following entities:
- Process master
- Process program
- Process program schedule
- Process log detail
- Error master
Figure 2 illustrates the logical data model.
Figure 2. Diagram of the logical data model
The following sections examine each of these entities.
This entity contains the information about all the processes available in the application. This is master information, stored in the table in hierarchical format. That is, one process can have a parent process and can also have a grandparent process as well. The process hierarchy needs to be defined accurately to ensure proper sequencing of the interlinked processes. Other information can be also useful to store, though not mandatory.
In a data warehousing system, numerous processes are involved, such as staging of the source data, data quality analysis, transformation and consolidation, loading data into the datamart, and so on. In order to define and distribute those processes into the Process Master, we can arrange data in the following manner:
Table 1. Process master
| Process Description | Process ID | Parent Process ID | Comments |
|---|---|---|---|
| Database Process | 00000 | 00000 | Database process starts with "00000" |
| Source Data Staging Process | 10000 | 00000 | Out of 3 major processes "Source Data Staging" is the first process whose Parent is "Database Process", this Process ID starts with "1" (10000) |
| Staging Source System -1 | 11000 | 10000 | There are multiple source systems, and the "Source System -1" got Process ID "11000", which means, system can deal with 9000 source systems |
| Staging Source System -1 – Subsystem – 1 | 11100 | 11000 | There are many Subsystems linked with the "Source System -1", and thus "Subsystem -1" got Process ID "11100", which means the system can deal upto 900 sub-systems |
| Staging Source System -1 – Subsystem – 1 – Europe Data | 11101 | 11100 | This sub-system runs in different countries, and source data can be obtained from those countries as well, here the system can deal upto 90 countries. |
| Data Transformation Process | 20000 | 00000 | Transformation process is the 2nd major process of the parent "Database Process", which starts the ID with "2" |
| Transform Source System -1 | 21000 | 20000 | In the same way like "Staging processes", the Transformation processes can be distributed, all with prefix with "2" determines the "Transformation Process" for each of the "Staging Process" |
| Transform Source System -1 – Subsystem – 1 | 21100 | 21000 | Child of 21000 |
| Transform Source System -1 – Subsystem – 1 – Europe Data | 21101 | 21100 | Child of 21100 |
| Hyperion Module Process | 90000 | 90000 | This is a different independent module, and can be taken part in the process master (or can be attached with "Database Process" as well) |
| Hyperion Module Process – Data Manipulation Process -1 | 91000 | 90000 | It has it's own process distribution, and can be scheduled and logged in the output stream as well |
Table 2. Process Master Entity Structure
| Column | Column Description | DataType and Width | Default Value | Mandatory or Optional |
|---|---|---|---|---|
| Process ID | Contains Unique Process Identification Number | Char 5 | NA | M |
| Process Parent ID | Contains Parent Process ID for a specific process and establish a process hierarchi | Char 5 | NA | M |
| Process Description | Description of the process | Varchar 200 | NA | M |
| Process Sequence | Sequence number of the process in it's hierarchy (say this process is the 5th of it's parent hierarchy, so the value would be "5") | Char 2 | NA | O |
| Process CL Parameters | Process Command Line arguments | Varchar 2000 | NA | O |
| Process Max Error Allowed | How many error this process should allow (system can look up this number and based on that can terminate the process forcefully) | Char 3 | 999 | O |
| Application Name | Name of the Application for which this process has been defined | Varchar 1000 | NA | M |
| Module Name | Name of the Module for which this process has been defined | Varchar 1000 | NA | O |
| Process Logging Flag | Whether the process needs to be logged | Char 1 | Y | M |
| Scheduler Flag | Whether Scheduler attached with this process | Char 1 | Y | M |
A process can consist of one or more programs (code and data structure). Once a process is executed, the system starts searching for the associated programs from the database tables. (Note: This happens only if the scheduler has not been attached for this process. See Scheduler Flag in the Process Master table.) One process can be associated with multiple programs, so, this entity has one-to-many cardinality from process to program.
Table 3. Process-program entity structure
| Column | Column Description | DataType and Width | Default Value | Mandatory or Optional |
|---|---|---|---|---|
| Process Program Surrogate Key | Contains Surrogate key for this Entity | Integer | NA | M |
| Program Name | Contains name of the program (we can separately keep program master Entity and reference program ID here) | Varchar 50 | NA | M |
| Process ID | Process Identification Number | Char 5 | NA | M |
| Program CL Parameters | Program Command Line arguments | Varchar 2000 | NA | O |
Each individual program can be scheduled to be executed in some predefined sequence. This entity facilitates the program to be associated with one or more schedules, and also capable of defining and constructing the repetitive process-program information with a specific frequency. (For example, one program can be run at 5 minutes intervals.) This entity has one to many cardinality from Process-Program entity to Process-Program-Schedule entity.
Table 4. Process-Program-Schedule Entity Structure
| Column | Column Description | DataType and Width | Default Value | Mandatory or Optional |
|---|---|---|---|---|
| Process Program Schedule Surrogate Key | Contains Surrogate key for this Entity | Integer | NA | M |
| Process Program Surrogate Key | Contains Surrogate key from Process Program (Foreign Key) | Integer | NA | M |
| Schedule Year | Contains Year | Varchar 4 | NA | M |
| Schedule Month | Contains Month Name | Varchar 20 | NA | M |
| Schedule Day | Contains Day | Varchar 2 | NA | M |
| Schedule Hour | Contains Hour | Varchar 2 | NA | M |
| Schedule Minute | Contains Minute | Varchar 2 | NA | M |
| Repeat Frequency | This field contains frequency information (i.e. #5 means this program will be executed every 5 minutes from the start time) | Varchar 2 | NA | M |
| Skip Schedule | Whether the program will not be executed in it's schedule | Char 1 | NA | M |
| Trigger File Flag | Whether this program expects a "Trigger File" from another application | Char 1 | N | O |
Once a process has been initiated, the system starts searching for the particular program from the Process-Program-Schedule and creates an entry in the Process Log table. The system controls the execution of a process based on the status of the process. In each execution step of a process, there is an attached specific status code, and the system updates the status code field on the basis of the execution status of the process itself. Different status codes are available for a particular process. Table 5 lists the status codes.
Table 5. Process Execution State - Status Code Example
| Status Code | Status Description | Comments |
|---|---|---|
| 0 | Failed with known error code | Error Code can be obtained from Error Master |
| 1 | Process starts | Waiting to complete |
| 2 | Process successfully ends | Only when the process ends without error |
| 3 | Zero record processed | Target record count is zero |
| 4 | Zero record in Source Entity | Source count is zero |
| 5 | Process Link Error | Process cannot be linked with it's Parent process (in the case of dependent process) |
| 6 | Abnormal completion time | Process success but taken abnormal time |
| 7 | Process progression error | Process initiated but next record read not happened |
| 8 | Process restarted | Process restart code |
| 9 | Failed with UNKNOWN error | Error code can not be obtained |
| * | Status of the associated parent process (only when its previous status was "2") | Once a child or dependent process start to execute (only possible when it's parent process had been successfully completed and the status was "2") system converts it's parent process status from "2" to " * " |
| # | Process in execution state | While executing of the dependent process system converts parent process status from "*" to "#" |
| $ | Parent process success | Once dependent process successfully completed, system converts its parent status from "#" to "$". That means no need to process that again. |
- Start a process
- Search the Process-Program-Schedule for confirmation about the schedule and get the program name.
- Get the Process ID
- Insert a new record in Process Log Entity
- Process Status Flag should start with "1"
- Execute the program with it's command line parameters
- Commit
- Upon successful completion of the process:
- Update the record with the following detail:
"Process End Time" = (end timestamp)
"Process Target Record Count" = (target table record count)
"Process Status Flag" = "2" (if successful)
"Process Execution Time" = (end time - start time)
"Process Comments" = (comments with SQL ERROR code and description) - If this is a dependent process, convert the "Parent Process" status flag from "2" to "*"
- Update this parent process status as per the list. This mechanism ensures restorability and continuation of the process and controls the dependent process execution as well.
- Update the record with the following detail:
- Upon unsuccessful completion:
- Update the same record
- "Status Code" should not be "2". Choose appropriate status code from the list.
- Commit
Table 6. Process Log Entity Structure
| Column | Column Description | DataType and Width | Default Value | Mandatory or Optional |
|---|---|---|---|---|
| Process Log ID | Uniquely identify one Process Log record. This ID may be inserted in the target file / table (in a separate column per record basis) in order to control the next dependent process execution. The next process will access those records only from the table / file which has been processed and was waiting for further transformation. | Integer | NA | M |
| Process ID | Process ID which is getting processed | Char 5 | NA | M |
| Process Start Time | The Start timestamp of the process | Timestamp | NA | M |
| Process Log ID | Uniquely identify one Process Log record. This ID may be inserted in the target file / table (in a separate column per record basis) in order to control the next dependent process execution. The next process will access those records only from the table / file which has been processed and was waiting for further transformation. | Integer | NA | M |
| Process End Time | The End timestamp of the process | Timestamp | NA | M |
| Process Source File Name | Name of the file / table of the source | Varchar 100 | NA | O |
| Process Source Record Count | The start record count in the source file / table | Integer | NA | M |
| Process Target Record Count | The end record count of the process | Integer | NA | M |
| Process Status Flag | The status of the process (get from the status list) | Varchar 2 | NA | M |
| Process Start Module | The module which starts the process | Varchar 100 | NA | O |
| Process End Module | The module where process ends | Varchar 100 | NA | O |
| Process Execution Time | Actual process execution time (may not be exactly deduction from end time and start time) | Decimal | NA | M |
| Process User Log ID | The user who starts the process | Varchar 50 | NA | M |
| Process System Status | System (OS / Network) related information | Varchar 2000 | NA | O |
This entity has been defined the process related predefined error codes and the description, which are used in a process execution step. When an error occurrs, the system looks for the related error code from this table and updates the Process Log table according to the Process Log ID from the table.
Table 7. Process-Error Entity Structure
| Column | Column Description | DataType and Width | Default Value | Mandatory or Optional |
|---|---|---|---|---|
| Error Code | Contains Error Code | Char 5 | NA | M |
| Error Description | Contains Description of the error | Varchar 100 | NA | M |
| Error Type | Which type of error: "SQL", "SYSTEM", "DATA", "CODE" | Varchar 10 | NA | M |
| Critical Status | 5 = most critical, 1= lowest critical | Char 1 | NA | O |
A sample pseudo-code example is given below. This pseudo-code inserts record in the Process Log table.
Main Procedure "Process Log Insert" Define 5 input parameters 1st parameter is the input "Process ID" [by searching right process from Process- Program-Schedule" Entity ] 2nd parameter is the "Start Time" 3rd parameter is the "Start Module" 4th parameter is the "Mode" [either "Insert" or "Update"] 5th parameter is the "Process Log ID" (optional - for control the next process execution) >>Insert a new record in the "Process Log" Entity with the input parameter specified above, use DB2 INSERT statement to insert the record. >> Update existing record in the "Process Log" Entity, use DB2 UPDATE statement to update the record. |
The most important part of the solution is to build the Process Master entity with the proper hierarchy of the processes. Once Process Master has been created successfully, you need to define the Process Program Scheduler data according to the running sequence and frequency of the processes. These two activities are the prerequisites for the overall solution.
You now should be able to track all processes for the whole application and also the individual nested fine-grained processes as well. This solution is a concept and can be built with any RDBMS (including DB2 or Informix) without having much development effort or resource utilization.
To get optimum performance, you need to create appropriate database indexes in the physical data model on the Process Log table. Periodic archiving of the table is also advisable. Another use of the solution is process restorability, if the application crashes and needs to be restarted. This can be performed based on the Status Code for that particular process entry in the Process Log table. In order to restart a process, the system needs to obtain the latest status code and detailed information about that process.
This solution doesn't offer any interface to load master data or generate report for the end user. The user needs to build them separately. Also, automatic restorability needs to be designed separately.
- In the
Information Management area
on developerWorks, get the resources you need to advance your skills in the IBM Information Management
product portfolio.
- Check out
developerWorks
blogs and get involved in the
developerWorks community.
- Download
IBM product evaluation versions
or
explore
the online trials in the IBM SOA Sandbox and get your hands on
application development tools and middleware products from
DB2, Informix®, Lotus®, Rational®, Tivoli®, and
WebSphere®.
Arindam Chakraborty is a data architect in the Enterprise Application Development and Management Practice. Mr. Chakraborty has over 13 years experience in database design and consulting with emphasis on technical management; software lifecycle methodologies; database administration, performance, and architectural design; and application development and implementation. He has managed the relationships between Information Services and business unit senior management to plan, develop, and implement numerous applications providing solutions to real business problems. Specific areas of software expertise include an Oracle DBA and DB2 certification gained through competitive examination and driving with consulting approach following IBM proven methodologies. Mr. Chakraborty has expertise in data architecture, data modeling, enterprise data warehouse design and data mart, and database administration with performance optimization.
