Friday, October 21, 2016

Automated Data Archive

Setting up a process to archive data in PeopleSoft requires two steps.  The first copies the data to your defined history table.  Oracle has forced you to run a second step where you choose the batch ID of your first step to delete the data from the source table.  It is well documented that you "Must have a secondary step to delete archived data from online tables".  The problem I was having is that our requirement was to have this to all happen on a schedule without manual intervention.  I used this solution to setup the entire process in a scheduled Jobset.

PIA Setup

Data Archive Manager

  PeopleTools  \ Data Archive Manager \  Homepage
  1. Create a Archive Record following Oracle Documentation and your Application Engine.
  2. Define an Archive Object identifying the Archiving table and the history record from step 1
  3. Create a Public Query to identify the rows you want archived
  4. Define your Archive Template using the Archive Object from step 2
    1. Add your Query for the Selective Archiving Query
    2. Add Archive Selection AE Process Post AE Program MCM_ARCH_BAT
  5. Create Run Controls
    1. My Archive example I name it MCM_ARCHIVE
      • Using your new Archive Template, Selection process type
      • Set the selective query to your query added in the template
    2. Create your delete run control with the same name adding "_DELETE" in my example it us named MCM_ARCHIVE_DELETE
      • Using your new Archive Template, Delete process type
      • Note:  You need to run the full MCM_ARCHIVE process and delete once manually in order to create a delete run control because Batch Number is a required field.

Jobs Setup

  PeopleTools \  Process Scheduler \  Jobs

Create a job and add 2 application process instances of PSARCHIVE.


 PeopleTools \ Process Scheduler \ Schedule JobSet Definitions

You can now add the Job you created to a existing Jobset or a new Jobset.

  1. Set the first instance to your ARCHIVE run control
  2. Set the second instance to the _DELETE run control



Running the Job

Now when this Jobset is run the job will first execute the PSARCHIVE step copying the selection criteria data to the history table.  Because of the Archive Template post AE task an App Engine will run updated your batch ID in the second Run Control to the same batch ID run in the first process.  The job then executes the second process to delete the batch from the archiving table.

Record and Application Engine Setup

  1. Create a Archive Record following Oracle Documentation 
  2. Create your Application Engine as defined below.  In my example it was named MCM_ARCH_BAT
Your Application Engine needs 2 actions. The first one is a standard SQL Init step that loads your state record. I used the delivered record ARCH_RPT_AET as a state record because it already contained the fields I needed.

%Select(ARCH_RPT_AET.OPRID,ARCH_RPT_AET.RUN_CNTL_ID,ARCH_RPT_AET.PSARCH_ID) 
 SELECT OPRID 
 , RUN_CNTL_ID 
 , PSARCH_ID 
  FROM PSARCHRUNCNTL 
 WHERE OPRID = %OperatorId 
   AND RUN_CNTL_ID = %RunControl

The Second is a PeopleCode Action to update the Delete version of the run Control.
/* Description:  The AppEngine will be used as in a Archive Template 
              to update a specific Delete Run Control
*/

Local number &batchNumber;
Local string &DeleteRunControl;
Local Record &Rec;

/* What process is currently running */
MessageBox(0, "", 0, 0, "AET:%1, %2, %3", 
           ARCH_RPT_AET.PROCESS_INSTANCE, ARCH_RPT_AET.RUN_CNTL_ID, ARCH_RPT_AET.PSARCH_ID);

/* What Batch ID was created for this Archive Process */
Local String &qry = "SELECT PSARCH_BATCHNUM FROM PSARCHBATCH WHERE PROCESS_INSTANCE = :1";
SQLExec(&qry, ARCH_RPT_AET.PROCESS_INSTANCE, &batchNumber);
MessageBox(0, "", 0, 0, "This Batch ID: %1", &batchNumber);

/* Check that the matching Delete Run Control exists */
&DeleteRunControl = ARCH_RPT_AET.RUN_CNTL_ID | "_DELETE";

&Rec = CreateRecord(Record.PSARCHRUNCNTL);
&Rec.OPRID.Value = ARCH_RPT_AET.OPRID;
&Rec.RUN_CNTL_ID.Value = &DeleteRunControl;
&Rec.PSARCH_ID.Value = ARCH_RPT_AET.PSARCH_ID;
If &Rec.SelectByKey() Then
   /* Delete Run Control Found */
   If &Rec.PSARCH_PROCESS.Value = "D" Then
      /* Set batch value */
      &Rec.PSARCH_BATCHNUM.Value = &batchNumber;
      If Not &Rec.Update() Then
         MessageBox(0, "", 0, 0, "Error updating %1", &DeleteRunControl);
      End-If;
   Else
      MessageBox(0, "", 0, 0, "Your matching run control is not setup as Delete");
   End-If;
Else
   MessageBox(0, "", 0, 0, "You do not have a matching run control for the DELETE step");
   MessageBox(0, "", 0, 0, "Missing Run Control %1, ARCHIVE ID %2 for user %3"
              , &DeleteRunControl, ARCH_RPT_AET.PSARCH_ID, ARCH_RPT_AET.OPRID);
End-If;

No comments:

Post a Comment