Implementing Parallel processing in Peoplesoft - Part III/III

How to implement parallel processing?

Implementing parallel processing in Peoplesoft is a simple task if you make sure that you follow all the steps systematically. Below is the generic guide line to implement parallel processing in peoplesoft.

   1.       Include locking fields in the base table – The main/ driver table for your transaction should contain a field to map that the table is used by an application engine program. The best way to do so is to add field PROCESS_INSTANCE in your base table. If you are allowing users to run the same process in online mode also it is recommended that you add one more fied PROCESSED_FALG. The significance of these fields will be explained in the following steps.

   2.       Create temporary tables – Temporary tables is the heart of peoplesoft parallel processing. For all the temporary data collections and manipulations, it is recommended that you do it inside a table. This will avoid data being transferred out of the database and makes the processing faster. The temporary tables should contain PROCESS_INSTANCE as one of the key. The usual naming convention will be the table name ending with _TAO or _TMP. The record type should be selected as Temporary Table.

   3.       Add the Temporary Table to application engine – Go to the Temp Tables tab of application engine properties and all the temp tables created for your process in this tab. Also provide the Instance count at this tab. Instance count will be the number of process you need to run in parallel in batch mode. To provide the instance count for online mode set it in the PeopleTools Options page (People Tools > Utilities > Administration >PeopleTools Options). There will be another option in the Temp Tables tab of app engine property called “Runtime”. If you select Shared, then the program will use your base temp table if the total number of parallel instances exceeds the count you have mentioned. The program will Abort if you have selected “Abort” option for the same.

   4.       Build the Temp Tables – You need to build the temp tables only after assigning it to the app engine. Once you build the table, it will generate copies for the table for as many instance you have mentioned (online+batch), the maximum being 99. Each instance will end with specific instance count TAO1, TAO2 etc…

   5.       Locking logic – Now you are done with the configurations and needs to write the code to suite parallel processing. Locking logic is important one. If two processes are initiated for same transactions at the same time, then both will process the transaction and at the end when inserting data back to the tables. Both the process will try to insert the same data and as a result the process errors out or in case of updating the process will end up in updating wrong data. To overcome this situation, we use locking logics. For this we have created fields in step 1. At the beginning of the program, write an update statement as below to update the base table with the process instance of the current process.

UPDATE PS_BASE_TABLE SET PROCESS_INSTANCE =%ProcessInstance WHERE PROCESS_INSTANCE = 0

So when the second program tries to work on this transaction, it will see that this is used by some other application engine and leaves it. Thus avoiding duplicate processing and chances of error. Now comes another scenario, if you are also running the process instance in online mode then your Process Instance will be always zero and the previous sql will not help you out. In such cases we need to use the second field added in step 1. Then the sql needs to be modified as below.

UPDATE PS_BASE_TABLE SET PROCESS_INSTANCE = %ProcessInstance , PROCESSED_FLAG=’Y’ WHERE PROCESS_INSTANCE = 0 and PROCESSED_FLAG = ‘N’

   6.       Drag data to temp table and do the processing – Now you can start dragging the data from driver table to the temp tables based on the process instance and the do the processing.

   7.       Use %Table() metasql – When you do all the processing with temp tables, always make sure that you wrap your Temporary record name with %Table() metasql. It will automatically unwind to the current instance name. For example if your current instance is 6, then the below sql will unwind to UPDATE PS_SAL_TAO6 SET SAL=SAL*10

UPDATE %Table(SAL_TAO) SET SAL+SAL*10

   8.       Unlock the base table when done with processing -  Once you are done with the processing you can now unlock the base tables by setting the PROCESS_INSTANCE to 0. Otherwise any other process run at a later time for the same transactions will never be picked up for processing. If your business logic needs that transaction to be processed only once, then you can avoid this step.

   9.       Avoid Truncating tables – Most people are used to truncating the temporary tables as the last step for the processing to clear up the temporary data. You can now avoid this step as in the latest versions of PeopleTools, this is taken care internally. Once your program ends, application engine will automatically issue the truncate statements.

I am sure if you follow these steps carefully while creating your program, you can easily build up your process to work in parallel.

Read Related: Implementing Parallel processing in Peoplesoft -Part II/III