The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Saturday 2 February 2013

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

10 comments:

  1. Hi, Thanks for providing the detailed approach for implementing parallel processing.

    In the third part can you please explain what is the base table here? Do you mean staging table?

    Thanks,
    Deepak

    ReplyDelete
    Replies
    1. Hi Deepak,

      Sorry for the confusion. The base table referred in this table is BASE TRANSACTION TABLE. It is the table from where you pull the data to be processed and update the status once processing is completed. For example ex_sheet_hdr and ex_sheet_line are transaction tables related to expense reports.

      Thanks,
      Tony

      Delete
    2. Hi Tony,

      Thank you for the reply. So, the base table is the base transaction table like ex_sheet_hdr. I think these are the delivered tables and you have mentioned above that we should alter it to include process instance and processed flag fields.

      Do we really have to alter the delivered tables to achieve parallel processing?

      Thanks,
      Deepak

      Delete
    3. Hi Deepak,

      This is just an example I have mentioned. Most of the delivered process comes with parallel processing implemented. So you need not do any modifications. If you are to implement parallel processing to your custom/bolt on modules, then these are the approaches you need to follow. If you require to make any delivered process to be processing in concurrent, then you can raise a ticket with Oracle.

      Hope it helps.

      Thanks,
      Tony

      Delete
    4. Ok... Ya, it really gave me good idea...

      Thanks,
      Deepak

      Delete
  2. Where exactly we have to mention code that divide data into 5 BU or divide data into pre divided category of employees?

    ReplyDelete
    Replies
    1. Chand,

      Dividing of data is something that need to be handled when you create the runcontrol id and it is not something to be coded unless it is an exceptional scenario.

      Delete
    2. How can split the data into chunks using run control parameters and upload the data to your temp table instance.
      plz share code

      Delete
  3. You r not sulaiman...Infact hanuman :)

    ReplyDelete

Note: only a member of this blog may post a comment.

Followers