Appendix D: Custom Interfaces
The workflow module of Database Monitor is highly extensible. With its ability to call or submit custom programs or to write data queue entries at any of the steps of the workflow, actions can be performed that are specific to the underlying application being run. The following panel image shows how this is configured.
To illustrate this point, let us take a hypothetical situation where a lot of a pharmaceutical product is added to the database. Database Monitor can be configured so that the addition of the lot of a product can cause a workflow to be initiated, requiring signatures from one or more supervisory personnel. If one of the steps in this approval process is a quality assurance check, should any impurities be observed Database Monitor can be made to automatically change the status of the lot so that it cannot be used. When signing for the workflow step, the QA person would use a reason code of "REJ" to specify that the lot had been rejected.
A sample custom program TSTLOTWFL which performs the rest of the task is shipped with Database Monitor. The source for the program can be found in SAMPLSRC.
How it works
Database Monitor identifies each captured change to the database using a unique fifteen digit transaction ID; highlighted below. It is this value that is passed by the workflow step to the custom program.
Using the transaction ID, a programmer can get back to the actual record in the database that has been changed. This is achieved using an API that will allow easy access to metadata about the change; and provide the ability to retrieve the key, and before and after images of any change. IDTAPI is a callable program that can be accessed with several different functions and will return appropriate information. Please see later in this section for more technical details on IDTAPI.
The TSTLOTWFL custom program performs the following tasks:
- Using IDTAPI, retrieves the key of the TSTLOT record that was added
- Loops through all of the approval signatures for this workflow
- If any are rejected, accesses the lot record and changes the status to "H"
Source for TSTLOTWFL
IDTAPI - Database Monitor API Program
Database Monitor uses some of the more sophisticated functionality of the AS/400 to capture and store audit data. By creating the IDTAPI program we hope to insulate the programmer from having to delve too much into the structure of our files.
This API can be called with several different functions and will return appropriate data elements. To be able to test the workings of the API and better understand its capabilities we also ship a program that behaves as the front end to the API. This is only an investigative and learning utility and has no real production role. The source for this program is also in SAMPLSRC.
IDTAPIT may be called without any parameters and will display the following panel:
In this example, the "K" function is being used to retrieve the key of the record which was added. The transaction number is the only other piece of data that needs to be supplied.
You can see from the return code of 0 that the information was successfully retrieved. The actual value of the key can be seen in the buffer1 field.
This is the equivalent of the following call as used in the TSTLOTWFL program, where P15 contains the transaction number and P3 contains the data in buffer1
The P3 and P4 parameters must be defined to be large enough to bring back the data. In this case, the lot number key field is 10 characters. Each parm can be defined as large as 32767. When appropriate, the name of data structures can be used as the parameter, loading all of the subfields upon return.
The following panel shows the retrieval of before and after images of the lot status field; Buffer1 shows the before and Buffer2 the after image.
The information, inset below, is returned within the APIDS data structure.
The following is the breakdown of the fields within APIDS:
| Parm | Begin | End | Description |
|---|---|---|---|
| FUNC | 1 | 1 | Must be supplied 0=Unload IDTAPI, K=Get key, R=Get Record, F=Get Field |
| FIELD | 2 | 11 | Field name - can only be supplied if function of "F" is used. |
| RETC | 12 | 12 | Return Codes 0 = Success 1 = Non-numeric transaction passed 2 = Transaction not found in transaction log file 3 = Transaction cannot be 4 = Individual fields captured but entire rec requested 5 = Image not found for this transaction 6 = Value not captured for this field on this change 7 = Error retrieving format for this field |
| EVENT | 13 | 13 | A- add, C-change, D-delete, R-read |
| LIBNM | 14 | 23 | Library of the affected file |
| FILENM | 24 | 33 | Name of the affected file |
| MEMBNM | 34 | 43 | Member name |
| CDATE | 44 | 51 | Activity date |
| CTIME | 52 | 57 | Activity time |
| CPROG | 58 | 67 | Program used in activity |
| CUSER | 68 | 77 | User name |
| CREAS | 78 | 80 | Reason code if pop up signature window used |
| CCOMM | 81 | 140 | Comments form pop up window |
| CSIGN | 141 | 141 | Yes/no if signed in pop up window |
| PTRN | 142 | 156 | No longer used by Database Monitor |
| NTRN | 157 | 171 | No longer used by Database Monitor |
Complexities of data capture
Given the three retrieval options of, K=Get key, R=Get Record, F=Get Field it is important to understand how Database Monitor retains data.
Most of the time, Database Monitor captures individual fields that are changed. If for example, the expiration date on the lot record is modified, the unmodified lot potency value is not captured to the Database Monitor database. If then, the transaction ID for the expiration date change is used with the field name of LOSTAT the API will return a "6 = Value not captured for this field on this change."
It is an option to configure fields to be captured every time regardless of having been changed or not.
There are two occasions that Database Monitor will capture the entire record; whenever a record is added or it is deleted. In these cases the action of "R" may be used and the entire record buffer is returned.
Retrieving the key
The fields which make up the file's key are those specified in the IDT500 configuration program. These fields' values will be returned to the API in the order they are sequenced in the configuration panels. (Note: Versions prior to V2R1 returned the key fields' values in alphabetical order, instead).
Previous and next transaction numbers
In earlier versions of Database Monitor, the actual transaction numbers for Previous and Next transaction were stored in the IDTLOH file. Due to performance constraints, these were removed, as the application can always use the IDTLOHL01 logical, knowing the transaction number and key and be able to perform the same previous/next function.