Application Engine - Part 4 - Temporary Tables and Parallel Processing

In this article we will discuss Temporary tables, their instances, parallel processing, COMMIT usage and Restarting Application Engine.
This part of the Application Engine article is in continuation for Part-I in the series of Application Engine articles posted on the blog. We will be using the Application Engine AJ_AE_SAMPLE, that we developed in Part-I.
We will create four different run controls to cover scenarios of Re-Startable APPLICATION ENGINE and non-ReStartable Application Engine with and without COMMIT usage. We will use an invalid Meta-SQL in one of the step after COMMIT to cause abends for all four run controls. We will then analyze the abends and resolutions.
This article is going to be long as we will be covering multiple inter-connected topics. So please follow each step closely as any change could cause different results than discussed.

The example has been created and tested in Peoplesoft environment with DEMO database.

The major steps involved will be
 1) Create two records for temporary tables to be used by Application
    Engine.
 2) Change the Application Engine properties for using 2 instances of
    Temporary tables by Application Engine.
 3) Modify Application Engine AJ_AE_SAMPLE to utilize temporary tables
    instead of copying journals from base tables to base tables.
 4) Configure the PSOptions for temporary tables instances.
 5) Build/Rebuild temporary tables.
 6) RESTART feature and COMMIT usage in Application Engines.
 7) Create 4 different run controls to be used for 4 executions of the
    Application Engine.
 8) Scenario 1: Execute run control TEST1 with COMMIT and
    ReStart=Enabled.
 9) Scenario 2: Execute run control TEST2 with COMMIT and
    ReStart=Disabled.
10) Scenario 3: Execute run control TEST3 with no COMMIT and
    ReStart=Enabled.
11) Scenario 4: Execute run control TEST4 with no COMMIT and
    ReStart=Disabled.
12) Review the records updated and Allocation of Temporary Tables at runtime.
13) Analyze Scenario 1: abend and resolution.
14) Analyze Scenario 2: abend and resolution.
15) Analyze Scenario 3: abend and resolution.
16) Analyze Scenario 4: abend and resolution.
17) Review the data in Journal tables.


Create two records for temporary tables to be used by Application Engine
Open the Application Designer. Create two records AJ_JRN_HD_TMP and AJ_JRN_LN_TMP as defined below. They could be copied from JRNL_HEADER and JRNL_LN. Once copied, move PROCESS_INSTANCE field from column position 34 to 1 in AJ_JRN_HD_TMP and make it part of the key. Similarly move PROCESS_INSTANCE field from column position 33 to 1 in AJ_JRN_LN_TMP and make it part of the key.

 Set the Record Type as Temporary.
Go to Tools-->> Admin -->> Indexes. Make sure you delete all non-key indexes for AJ_JRN_HD_TMP record as they are not required. We need to have just one index as shown below.

 

 

Set the Record Type as Temporary.
Go to Tools-->> Admin -->> Indexes. Make sure you delete all non-key indexes for AJ_JRN_LN_TMP record. We need to have just one key index as shown below.

 We will not build the records as we have not configured the number of instances required yet.


Change the Application Engine properties for using 2 instances of temporary tables by Application Engine
Open the Application Engine program AJ_COPY_JRNL in Application Designer (AJ_COPY_JRNL was developed in Part 1).

 Select File, Definition Properties and then select the Temp Tables tab.
Assign two records AJ_JRN_HD_TMP and AJ_JRN_LN_TMP and set the instance count dedicated to this Application Engine as 2.

Modify Application Engine AJ_AE_SAMPLE to utilize temporary tables instead of copying journals from base tables to base tables
Open the Application Engine program AJ_COPY_JRNL in Application Designer, if not opened already.
In the MAIN section, insert one step CPHD2BSE after step ReadReq1. Change the description of step CPHD2BSE to "Copy Jrnls HDRs TMP to Base". Change the description of SQL action under step CPHD2BSE to "Copy Jrnl Headers".
In the MAIN section, insert one step CPLN2BSE after step CPHD2BSE. Change the description of step CPLN2BSE to "Copy Jrnls Lines TMP to Base". Change the description of SQL action under step CPLN2BSE to "Copy Jrnl Lines".
The MAIN section should like this.

The following four SQL are copying the data from base tables to temporary tables. These are examples of Row-by-Row Processing.
Modify the SQL of Step=CPJRNLHD and Action=SQL To the following. 
%InsertSelect(%table(AJ_JRN_HD_TMP), JRNL_HEADER, PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE), JOURNAL_ID = %Bind(NEW_JOURNAL_ID), JOURNAL_DATE = %Bind(NEW_JOURNAL_DATE)) FROM PS_JRNL_HEADER WHERE BUSINESS_UNIT = %Bind(BUSINESS_UNIT) AND JOURNAL_ID = %Bind(JOURNAL_ID) AND JOURNAL_DATE = %Bind(JOURNAL_DATE);
When we try to resolve the above Met-SQL, it fails. Looks like %InsertSelect is not going well with %table. So we will use the following SQL instead.
INSERT INTO %Table(AJ_JRN_HD_TMP) (PROCESS_INSTANCE
 , BUSINESS_UNIT
 , JOURNAL_ID
 , JOURNAL_DATE
 , UNPOST_SEQ
 , BUSINESS_UNIT_IU
 , ADJUSTING_ENTRY
 , FISCAL_YEAR
 , ACCOUNTING_PERIOD
 , ADB_DATE
 , LEDGER_GROUP
 , LEDGER
 , AUTO_GEN_LINES
 , REVERSAL_CD
 , REVERSAL_DATE
 , REVERSAL_ADJ_PER
 , REVERSAL_CD_ADB
 , REVERSAL_DATE_ADB
 , UNPOST_JRNL_DATE
 , JRNL_TOTAL_LINES
 , JRNL_TOTAL_DEBITS
 , JRNL_TOT_CREDITS
 , JRNL_NET_UNITS
 , SOURCE
 , TRANS_REF_NUM
 , JRNL_BALANCE_STAT
 , CONTROL_TOTAL_STAT
 , JRNL_EDIT_ERR_STAT
 , JRNL_HDR_STATUS
 , SUSP_RECON_STATUS
 , JRNL_PROCESS_REQST
 , JRNL_SUMLED_REQST
 , SJE_TYPE
 , SCHEDULE
 , EVENT_OCCURRENCE
 , POSTED_DATE
 , SOURCE_INSTANCE
 , TRANSACTION_DATE
 , LAST_LN_COMMITTED
 , OPRID
 , DTTM_STAMP_SEC
 , DESCR
 , CURRENCY_CD
 , FOREIGN_CURRENCY
 , RT_TYPE
 , CUR_EFFDT
 , RATE_DIV
 , RATE_MULT
 , SYSTEM_SOURCE
 , DOC_TYPE
 , DOC_SEQ_NBR
 , DOC_SEQ_DATE
 , DOC_SEQ_STATUS
 , ACCTG_DEF_NAME
 , BUDGET_HDR_STATUS
 , KK_AMOUNT_TYPE
 , KK_TRAN_OVER_FLAG
 , KK_TRAN_OVER_OPRID
 , KK_TRAN_OVER_DTTM
 , KK_SKIP
 , JOURNAL_LOCKED
 , PROC_PART_ID
 , GL_ADJUST_TYPE
 , DATE_CODE_JRNL
 , JOURNAL_DATE_ORIG
 , JOURNAL_CLASS
 , KK_TRAN_BYPAS_FLAG
 , EE_HDR_STATUS
 , IU_SYS_TRAN_CD
 , IU_TRAN_CD
 , JRNL_CREATE_DTTM
 , ALC
 , FED_DISTRIB_STATUS
 , DESCR254
 , ATTACHMENT_EXIST
 , SCE_ID
 , SOURCE_DATA)
 SELECT %Bind(PROCESS_INSTANCE)
 , BUSINESS_UNIT
 , %Bind(NEW_JOURNAL_ID)
 , %Bind(NEW_JOURNAL_DATE)
 , UNPOST_SEQ
 , BUSINESS_UNIT_IU
 , ADJUSTING_ENTRY
 , FISCAL_YEAR
 , ACCOUNTING_PERIOD
 , ADB_DATE
 , LEDGER_GROUP
 , LEDGER
 , AUTO_GEN_LINES
 , REVERSAL_CD
 , REVERSAL_DATE
 , REVERSAL_ADJ_PER
 , REVERSAL_CD_ADB
 , REVERSAL_DATE_ADB
 , UNPOST_JRNL_DATE
 , JRNL_TOTAL_LINES
 , JRNL_TOTAL_DEBITS
 , JRNL_TOT_CREDITS
 , JRNL_NET_UNITS
 , SOURCE
 , TRANS_REF_NUM
 , JRNL_BALANCE_STAT
 , CONTROL_TOTAL_STAT
 , JRNL_EDIT_ERR_STAT
 , JRNL_HDR_STATUS
 , SUSP_RECON_STATUS
 , JRNL_PROCESS_REQST
 , JRNL_SUMLED_REQST
 , SJE_TYPE
 , SCHEDULE
 , EVENT_OCCURRENCE
 , POSTED_DATE
 , SOURCE_INSTANCE
 , TRANSACTION_DATE
 , LAST_LN_COMMITTED
 , OPRID
 , DTTM_STAMP_SEC
 , DESCR
 , CURRENCY_CD
 , FOREIGN_CURRENCY
 , RT_TYPE
 , CUR_EFFDT
 , RATE_DIV
 , RATE_MULT
 , SYSTEM_SOURCE
 , DOC_TYPE
 , DOC_SEQ_NBR
 , DOC_SEQ_DATE
 , DOC_SEQ_STATUS
 , ACCTG_DEF_NAME
 , BUDGET_HDR_STATUS
 , KK_AMOUNT_TYPE
 , KK_TRAN_OVER_FLAG
 , KK_TRAN_OVER_OPRID
 , KK_TRAN_OVER_DTTM
 , KK_SKIP
 , JOURNAL_LOCKED
 , PROC_PART_ID
 , GL_ADJUST_TYPE
 , DATE_CODE_JRNL
 , JOURNAL_DATE_ORIG
 , JOURNAL_CLASS
 , KK_TRAN_BYPAS_FLAG
 , EE_HDR_STATUS
 , IU_SYS_TRAN_CD
 , IU_TRAN_CD
 , JRNL_CREATE_DTTM
 , ALC
 , FED_DISTRIB_STATUS
 , DESCR254
 , ATTACHMENT_EXIST
 , SCE_ID
 , SOURCE_DATA
  FROM PS_JRNL_HEADER
 WHERE BUSINESS_UNIT = %Bind(BUSINESS_UNIT)
   AND JOURNAL_ID = %Bind(JOURNAL_ID)
   AND JOURNAL_DATE = %Bind(JOURNAL_DATE);

Modify the SQL of Step=CPJRNLLN and Action=SQL To the following.
%InsertSelectWithLongs(%TABLE(AJ_JRN_LN_TMP), JRNL_LN, PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE), JOURNAL_ID = %Bind(NEW_JOURNAL_ID), JOURNAL_DATE = %Bind(NEW_JOURNAL_DATE))
  FROM PS_JRNL_LN
 WHERE BUSINESS_UNIT = %Bind(BUSINESS_UNIT)
   AND JOURNAL_ID = %Bind(JOURNAL_ID)
   AND JOURNAL_DATE = %Bind(JOURNAL_DATE);
Here again, when we try to resolve the above Met-SQL, it fails. So we will use the following SQL instead.
INSERT INTO %TABLE(AJ_JRN_LN_TMP) (PROCESS_INSTANCE
 , BUSINESS_UNIT
 , JOURNAL_ID
 , JOURNAL_DATE
 , UNPOST_SEQ
 , JOURNAL_LINE
 , LEDGER
 , SPEEDCHART_KEY
 , SPEEDTYPE_KEY
 , ACCOUNT
 , ALTACCT
 , DEPTID
 , OPERATING_UNIT
 , PRODUCT
 , FUND_CODE
 , CLASS_FLD
 , PROGRAM_CODE
 , BUDGET_REF
 , AFFILIATE
 , AFFILIATE_INTRA1
 , AFFILIATE_INTRA2
 , CHARTFIELD1
 , CHARTFIELD2
 , CHARTFIELD3
 , BOOK_CODE
 , GL_ADJUST_TYPE
 , BUDGET_PERIOD
 , SCENARIO
 , CURRENCY_CD
 , BUSINESS_UNIT_PC
 , PROJECT_ID
 , ACTIVITY_ID
 , RESOURCE_TYPE
 , RESOURCE_CATEGORY
 , RESOURCE_SUB_CAT
 , ANALYSIS_TYPE
 , STATISTICS_CODE
 , MONETARY_AMOUNT
 , MOVEMENT_FLAG
 , STATISTIC_AMOUNT
 , JRNL_LN_REF
 , SUSPENDED_LINE
 , LINE_DESCR
 , JRNL_LINE_STATUS
 , JOURNAL_LINE_DATE
 , FOREIGN_CURRENCY
 , RT_TYPE
 , FOREIGN_AMOUNT
 , RATE_DIV
 , RATE_MULT
 , DOC_TYPE
 , DOC_SEQ_NBR
 , DOC_SEQ_DATE
 , DOC_SEQ_STATUS
 , JRNL_LINE_SOURCE
 , BUDGET_DT
 , BUDGET_LINE_STATUS
 , SETTLEMENT_DT
 , DATE_CODE
 , CLOSING_STATUS
 , ENTRY_EVENT
 , EE_PROC_STATUS
 , JOURNAL_LINE_GFEE
 , IU_TRAN_GRP_NBR
 , IU_ANCHOR_FLG
 , PC_DISTRIB_STATUS
 , SCE_ID
 , SOURCE_DATA) 
 SELECT %Bind(PROCESS_INSTANCE)
 , BUSINESS_UNIT
 , %Bind(NEW_JOURNAL_ID)
 , %Bind(NEW_JOURNAL_DATE)
 , UNPOST_SEQ
 , JOURNAL_LINE
 , LEDGER
 , SPEEDCHART_KEY
 , SPEEDTYPE_KEY
 , ACCOUNT
 , ALTACCT
 , DEPTID
 , OPERATING_UNIT
 , PRODUCT
 , FUND_CODE
 , CLASS_FLD
 , PROGRAM_CODE
 , BUDGET_REF
 , AFFILIATE
 , AFFILIATE_INTRA1
 , AFFILIATE_INTRA2
 , CHARTFIELD1
 , CHARTFIELD2
 , CHARTFIELD3
 , BOOK_CODE
 , GL_ADJUST_TYPE
 , BUDGET_PERIOD
 , SCENARIO
 , CURRENCY_CD
 , BUSINESS_UNIT_PC
 , PROJECT_ID
 , ACTIVITY_ID
 , RESOURCE_TYPE
 , RESOURCE_CATEGORY
 , RESOURCE_SUB_CAT
 , ANALYSIS_TYPE
 , STATISTICS_CODE
 , MONETARY_AMOUNT
 , MOVEMENT_FLAG
 , STATISTIC_AMOUNT
 , JRNL_LN_REF
 , SUSPENDED_LINE
 , LINE_DESCR
 , JRNL_LINE_STATUS
 , JOURNAL_LINE_DATE
 , FOREIGN_CURRENCY
 , RT_TYPE
 , FOREIGN_AMOUNT
 , RATE_DIV
 , RATE_MULT
 , DOC_TYPE
 , DOC_SEQ_NBR
 , DOC_SEQ_DATE
 , DOC_SEQ_STATUS
 , JRNL_LINE_SOURCE
 , BUDGET_DT
 , BUDGET_LINE_STATUS
 , SETTLEMENT_DT
 , DATE_CODE
 , CLOSING_STATUS
 , ENTRY_EVENT
 , EE_PROC_STATUS
 , JOURNAL_LINE_GFEE
 , IU_TRAN_GRP_NBR
 , IU_ANCHOR_FLG
 , PC_DISTRIB_STATUS
 , SCE_ID
 , SOURCE_DATA 
  FROM PS_JRNL_LN 
 WHERE BUSINESS_UNIT = %Bind(BUSINESS_UNIT) 
   AND JOURNAL_ID = %Bind(JOURNAL_ID) 
   AND JOURNAL_DATE = %Bind(JOURNAL_DATE);

Modify the SQL of Step=UPDJRNLH and Action=SQL To the following.
UPDATE %TABLE(AJ_JRN_HD_TMP)
  SET UNPOST_SEQ = 0, JRNL_HDR_STATUS = 'N', JRNL_CREATE_DTTM = SYSDATE, (FISCAL_YEAR,ACCOUNTING_PERIOD) = (
 SELECT FISCAL_YEAR
 ,ACCOUNTING_PERIOD
  FROM ps_cal_detp_tbl
 WHERE setid = 'SHARE'
   AND calendar_id = '01'
   AND %Bind(NEW_JOURNAL_DATE) BETWEEN begin_dt AND end_dt)
 WHERE PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
   AND BUSINESS_UNIT = %Bind(BUSINESS_UNIT)
   AND JOURNAL_ID = %Bind(NEW_JOURNAL_ID)
   AND JOURNAL_DATE = %Bind(NEW_JOURNAL_DATE);

Modify the SQL of Step=UPDJRNLL and Action=SQL To the following.
UPDATE %TABLE(AJ_JRN_LN_TMP)
  SET UNPOST_SEQ = 0
 WHERE PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
   AND BUSINESS_UNIT = %Bind(BUSINESS_UNIT)
   AND JOURNAL_ID = %Bind(NEW_JOURNAL_ID)
   AND JOURNAL_DATE = %Bind(NEW_JOURNAL_DATE);

Let us add SQLs for copying data from temporary tables to base tables. Since the two steps CPHD2BSE and CPLN2BSE are executed when the journal copy iterations in step ReadReq1 are completed, all the selected journals will be copied from temporary tables to base tables by one SQL. These are examples of Set Processing.
Add following SQL to Step=CPHD2BSE and Action=SQL for copying journal headers from temporary table to base table.
%InsertSelect(JRNL_HEADER, %table(AJ_JRN_HD_TMP) T)
  FROM %Table(AJ_JRN_HD_TMP) T
 WHERE T.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE);

Add following SQL to Step=CPLN2BSE and Action=SQL for copying journal lines from temporary table to base table.
%InsertSelectWithLongs(JRNL_LN, %table(AJ_JRN_LN_TMP) T)
  FROM %Table(AJ_JRN_LN_TMP) T
 WHERE T.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE);


Configure the PSOptions for temporary table’s instances
Select PeopleTools, then select Utilities, then select Administration, then select PeopleTools Options to access the PeopleTools Options page.
Leave the Temp Table Instances (Online) to 3. This relates to the number of temporary table instances for Application Engine processes started online from the PeopleCode CallAppEngine function.
Leave the Temp Table Instances (Total) to 3.

As per the peoplesoft peoplebook for Application Engine
….if the value for the Temp
Table Instances (Online) field is less than the value for the Temp Table Instances (Total) field, the difference between the two numbers provides a pool of tables for backward compatibility for developers who took advantage of the %Table(record_name, instance_number) approach for manually managing temporary table locking, (such as PeopleSoft EPM)…..
In our case we do not have backward compatibility issue so we have specified the two parameters same value.
The number of Global Instances is saved in PSOPTIONS. The following SQL may be used to inquire without navigating to that page.
SELECT TEMPTBLINSTANCES,TEMPINSTANCEBATCH,TEMPINSTANCEONLINE FROM PSOPTIONS;

 To increase the number of Temporary Table Instances for all Application Engine programs go to Home > PeopleTools > Utilities > Use PeopleTools Options and then increase the value of “Temp Table Instances (Total)” and the corresponding value of “Temp Table Instances (Online)” will be automatically increased to the same value. For our examples, we are not going to do that.
Application Designer creates multiple copies of the table and uses the view PSTEMPTBLCNTVW to calculate the total number of instances of the temporary table required by all Application Engine programs. The following SQL may be used to inquire PSTEMPTBLCNTVW.
select * from PSTEMPTBLCNTVW where recname like 'AJ_JRN%';

Build/Rebuild temporary tables
The system determines the total available number of temporary table instances for a base temporary table according to your settings for total and online instances on PeopleTools Options page and on the value of Instant Count, in Application Engine Program Properties, Temp Tables tab in Application Designer.
The number of temporary table instances built for a specific temporary table record during the SQL Build process is the value of the total temporary table instances from the PeopleTools Options page added to the sum of all the instance count values specified on the Temp Table tab for the Application Engine programs that use that temporary table.
In our case the PeopleTools Option is set to 3 and Instance Count is 2. So there will be 6 tables in the System Catalog (one base and five instances) like PS_AJ_JRN_HD_TMP, PS_AJ_JRN_HD_TMP1, PS_AJ_JRN_HD_TMP2, PS_AJ_JRN_HD_TMP3, PS_AJ_JRN_HD_TMP4 and PS_AJ_JRN_HD_TMP5.
The system creates a maximum of 99 temporary table instances, even if the sum exceeds 99 for a particular temporary table.
Let us build the two temporary tables now and examine how many instances are created. In Application Designer, open the record AJ_JRN_HD_TMP and build the record. Do the same for AJ_JRN_LN_TMP.
After building the temporary tables, we can see in the log that the following tables are created.
PS_AJ_JRN_HD_TMP
PS_AJ_JRN_HD_TMP1
PS_AJ_JRN_HD_TMP2
PS_AJ_JRN_HD_TMP3
PS_AJ_JRN_HD_TMP4
PS_AJ_JRN_HD_TMP5
and
PS_AJ_JRN_LN_TMP
PS_AJ_JRN_LN_TMP1
PS_AJ_JRN_LN_TMP2
PS_AJ_JRN_LN_TMP3
PS_AJ_JRN_LN_TMP4
PS_AJ_JRN_LN_TMP5
As an alternate way to see the tables created, we can execute the following SQL.
select * from all_tables where table_name like 'PS_AJ_JRN_%TMP%' order by table_name;

RESTART feature and COMMIT usage in Application EnginesAn Application Engine has built-in checkpoint and ReStart capabilities. If a program step terminates abnormally or fails, you can restart the request from the last successful checkpoint. Application Engine programs, by default, perform a COMMIT only when an entire program successfully completes. We must set individual COMMITs where appropriate. The COMMIT level that we select affects how ReStart works in a program. Each time Application Engine issues a COMMIT with ReStart enabled, it records the current state (check point) of the program. Using the ReStart feature enables to perform commits more often in a program and it reduces the number of rows that are locked by the program.
Since we want to use RESTART option, we will change the state record from Derived/Work record to a regular table. Open the record GL_JRCPSJE_AET in Application Designer and save as AJ_JRCPSJE_AET. Change the record Type from Derived/Work to SQL Table and build the table. GO to Application Engine properties and on the State Records tab, add the record AJ_JRCPSJE_AET, make it default and delete the GL_JRCPSJE_AET record from that list.

Create 4 different run controls to be used for 4 executions of the Application Engine
The run control TEST1 that we used in Part 1 of Application Engine series, will be reused here. We will copy the Run control TEST1 to TEST2, TEST3 and TEST4 using the following SQLs.
insert into PS_AJ_JRNL_COPY_RC
   (OPRID, RUN_CNTL_ID, REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, NEW_JOURNAL_ID, NEW_JOURNAL_DATE, PROCESS_STATUS)
select OPRID, 'TEST2',  REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, 'TST2000018', NEW_JOURNAL_DATE, PROCESS_STATUS
  from PS_AJ_JRNL_COPY_RC where OPRID = 'VP1' and RUN_CNTL_ID = 'TEST1' and NEW_JOURNAL_ID = 'TSTS000018';

insert into PS_AJ_JRNL_COPY_RC
   (OPRID, RUN_CNTL_ID, REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, NEW_JOURNAL_ID, NEW_JOURNAL_DATE, PROCESS_STATUS)
select OPRID, 'TEST2',  REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, 'TST2000019', NEW_JOURNAL_DATE, PROCESS_STATUS
  from PS_AJ_JRNL_COPY_RC where OPRID = 'VP1' and RUN_CNTL_ID = 'TEST1' and NEW_JOURNAL_ID = 'TSTS000019';

insert into PS_AJ_JRNL_COPY_RC
   (OPRID, RUN_CNTL_ID, REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, NEW_JOURNAL_ID, NEW_JOURNAL_DATE, PROCESS_STATUS)
select OPRID, 'TEST3',  REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, 'TST3000018', NEW_JOURNAL_DATE, PROCESS_STATUS
  from PS_AJ_JRNL_COPY_RC where OPRID = 'VP1' and RUN_CNTL_ID = 'TEST1' and NEW_JOURNAL_ID = 'TSTS000018';
insert into PS_AJ_JRNL_COPY_RC
   (OPRID, RUN_CNTL_ID, REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, NEW_JOURNAL_ID, NEW_JOURNAL_DATE, PROCESS_STATUS)
select OPRID, 'TEST3',  REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, 'TST3000019', NEW_JOURNAL_DATE, PROCESS_STATUS
  from PS_AJ_JRNL_COPY_RC where OPRID = 'VP1' and RUN_CNTL_ID = 'TEST1' and NEW_JOURNAL_ID = 'TSTS000019';

insert into PS_AJ_JRNL_COPY_RC
   (OPRID, RUN_CNTL_ID, REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, NEW_JOURNAL_ID, NEW_JOURNAL_DATE, PROCESS_STATUS)
select OPRID, 'TEST4',  REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, 'TST4000018', NEW_JOURNAL_DATE, PROCESS_STATUS
  from PS_AJ_JRNL_COPY_RC where OPRID = 'VP1' and RUN_CNTL_ID = 'TEST1' and NEW_JOURNAL_ID = 'TSTS000018';

insert into PS_AJ_JRNL_COPY_RC
   (OPRID, RUN_CNTL_ID, REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, NEW_JOURNAL_ID, NEW_JOURNAL_DATE, PROCESS_STATUS)
select OPRID, 'TEST4',  REQUEST_NBR, PROCESS_FREQUENCY, BUSINESS_UNIT,
    JOURNAL_ID, JOURNAL_DATE, 'TST4000019', NEW_JOURNAL_DATE, PROCESS_STATUS
  from PS_AJ_JRNL_COPY_RC where OPRID = 'VP1' and RUN_CNTL_ID = 'TEST1' and NEW_JOURNAL_ID = 'TSTS000019';

We will also delete journals ('TSTS000018','TSTS000019' for BUSINESS_UNIT 'FED01' in case they exists from previous runs.
delete from PS_JRNL_HEADER where BUSINESS_UNIT = 'FED01' and JOURNAL_ID in ('TSTS000018','TSTS000019');
delete from PS_JRNL_LN where BUSINESS_UNIT = 'FED01' and JOURNAL_ID in ('TSTS000018','TSTS000019');

Since the temporary tables are unlocked and cleared when the Application Engine runs successfully, we may not be able to view the locking and dedicated temporary tables’ assignments with small examples. For this reason we will use our small example and cause the Application Engine to abend in all four scenarios to demonstrate the dedicated temporary tables handling and parallel processing.
All the four run controls TEST1, TEST2, TEST3 and TEST4 are setup to copy the journals from 'GTAS000018' and 'GTAS000019'. But will have left an invalid Meta-SQL to cause abends for our purpose.
We have enabled trace also.

Scenario 1: Execute with run control TEST1 with COMMIT and
ReStart=Enabled
For this scenario, let us go to the section MAIN, step ReadReq1 and change the COMMIT After option from 'default' to 'After Step'.

 
But there is an exception. As per Peoplesoft documentation 
“The only restriction for batch runs occurs when you have ReStart enabled, and you are inside a Do Select action that is of the Select/Fetch type (instead of Re-select or ReStartable). With Select/Fetch, all COMMITs inside the loop are ignored, including the commit frequency if it is set.”
So we will set COMMIT at section level.

 We will change COMMIT at step level from Default to Later for step GoPrgMsg

We will keep COMMIT settings at step level step ReadReq1 to After Step.
We will change COMMIT at step level from Default to Later for steps CPHD2BSE and CPLN2BSE as well.
Go to Application Engine properties, Advanced tab and uncheck 'Disable ReStart' checkbox.
Execute the Application Engine using the run control TEST1. The run control page looks like

 The program abends. The process instance is 18823.

 Click on Details link.

 Click on View Log/Trace link.

 Click on AE_AJ_COPY_JRNL_18823.AET link. The Log file opens in a new window. Scroll down till you see insert into PS_AJ_JRN_HD_TMP4.

 Application Engine dedicated instance number 4 to this run. Since we had COMMIT after inserting into temp tables, and Application Engine abended, we should have data in TMP4 table.
select PROCESS_INSTANCE , JOURNAL_ID from PS_AJ_JRN_HD_TMP4;

 Let us review some other Application Engine tables.
select * from PS_AERUNCONTROL where PROCESS_INSTANCE = 18823;

 select * from PS_AETEMPTBLMGR where PROCESS_INSTANCE = 18823;

 Let us leave this scenario here for now, we will review these soon.


Scenario 2: Execute with run control TEST2 with COMMIT andReStart=Disabled
Go to Application Engine properties, Advanced tab and check 'Disable ReStart' checkbox.
Execute the Application Engine using the run control TEST2. The run control page looks like

 The program abends. The process instance is 18824.
Application Engine dedicated instance number 5 to this run. Since we had COMMIT after inserting into temp tables, and Application Engine abended, we should have data in TMP5 table.
select PROCESS_INSTANCE , JOURNAL_ID from PS_AJ_JRN_HD_TMP5;

 Let us review some other Application Engine tables.
select * from PS_AERUNCONTROL where PROCESS_INSTANCE = 18824;

 select * from PS_AETEMPTBLMGR where PROCESS_INSTANCE = 18824;

 Let us leave this scenario here for now, we will review these soon.


Scenario 3: Execute with run control TEST3 with no COMMIT and ReStart=Enabled
Go to Application Engine properties, Advanced tab and check 'Disable ReStart' checkbox.
Let us go to the section MAIN, step ReadReq1 and change the COMMIT After option from 'After Step' to 'default'.
Execute the Application Engine using the run control TEST3. The run control page looks like

 The program abends. The process instance is 18825.
Application Engine dedicated instance number 5 to this run. Why? (Because the TMP5 locked by previous Application Engine run did not keep it locked as Application Engine was not ReStartable in previous run). At the time of dedicating TMP5, Application Engine clears any earlier rows, that is why previous rows are deleted. Since there is no COMMIT, no new rows are saved either.
select PROCESS_INSTANCE , JOURNAL_ID from PS_AJ_JRN_HD_TMP5;

 

 Let us review some other Application Engine tables.
select * from PS_AERUNCONTROL where PROCESS_INSTANCE = 18825;

 There is data as the Application Engine was ReStartable.
select * from PS_AETEMPTBLMGR where PROCESS_INSTANCE = 18825;

 There is data as the Application Engine was ReStartable.
 Let us leave this scenario here for now, we will review these soon.


Scenario 4: Execute with run control TEST4 with no COMMIT and
ReStart=Disabled
Go to Application Engine properties, Advanced tab and Uncheck 'Disable ReStart' checkbox.
Execute the Application Engine using the run control TEST4. The run control page looks like

 The program abends. The process instance is 18826.
Application Engine used the shared (base or default) temp table for this run.
select PROCESS_INSTANCE , JOURNAL_ID from PS_AJ_JRN_HD_TMP;

 There is no data as there was no COMMIT.
Let us review some other Application Engine tables.
select * from PS_AERUNCONTROL where PROCESS_INSTANCE = 18826;

 There is no data as the Application Engine was not ReStartable.
select * from PS_AETEMPTBLMGR where PROCESS_INSTANCE = 18826;

 There is no data as the Application Engine was not ReStartable.
 Let us leave this scenario here for now, we will review these soon.


Review the Records updated and Allocation of Temporary Tables at runtime
select * from PS_AERUNCONTROL where PROCESS_INSTANCE > 18822;

 select * from PS_AETEMPTBLMGR where PROCESS_INSTANCE > 18822;

 Select PeopleTools, then select Application Engine, then select Review Temp Table Usage to access the Temp Table Usage by Record page. (Note: The next 2 snapshots were taken after ReStarting instance 18823 successfully. So the numbers do not include that execution or abend data. But you do see the navigation and data related to other abends).

 Select PeopleTools, then select Application Engine, then select Review Temporary Table Usage, then select Temp Table Settings by Program to access the Temp Table Settings by Program page.

 Let us make the Application Engine ReStartable and place the COMMIT(s) back.
Let us fix the Meta-SQLs which caused the abends.
The following SQL for Step=CPHD2BSE and Action=SQL caused abends.
%InsertSelect(JRNL_HEADER, %table(AJ_JRN_HD_TMP) T)
  FROM %Table(AJ_JRN_HD_TMP) T
 WHERE T.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE);

We will replace %table(AJ_JRN_HD_TMP) with AJ_JRN_HD_TMP in the first place only. So the SQL will look like
%InsertSelect(JRNL_HEADER, AJ_JRN_HD_TMP T)
  FROM %Table(AJ_JRN_HD_TMP) T
 WHERE T.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE);

Here the Alias from AJ_JRN_HD_TMP T will help in resolving the SQL correctly.
Similarly the SQL for Step=CPLN2BSE and Action=SQL will be changed from
%InsertSelectWithLongs(JRNL_LN, %table(AJ_JRN_LN_TMP) T)
  FROM %Table(AJ_JRN_LN_TMP) T
 WHERE T.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE);
to
%InsertSelectWithLongs(JRNL_LN, AJ_JRN_LN_TMP T)
  FROM %Table(AJ_JRN_LN_TMP) T
 WHERE T.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE);


Analyze Scenario 1: abend and resolution
Application Engine dedicated instance number 4 for PS_AJ_JRN_HD_TMP and PS_AJ_JRN_LN_TMP.
Application Engine was ReStartable, we have data in PS_AERUNCONTROL to be used for ReStarting. We also have temporary records stuck in PS_AETEMPTBLMGR so the Application Engine will use the right instances of temp tables when rerun.
We had COMMIT after inserting into temp tables therefore we have data in temporary tables.
Let us ReStart Application Engine for process instance 18823. Since we are not going to start from the begining, we do not have to delete records from PS_AERUNCONTROL and PS_AJ_JRCPSJE_AET.
We could restart from the command line on the server. But we will restart from Process Request Page for now.
Go to Process Monitor page. Click on Details link for the process instance 18823. Click on Restart Request radio button. Click on Ok.
The Application Engine completes to success.
The tables PS_AERUNCONTROL and PS_AETEMPTBLMGR are cleared for the process instance 18823.
The table PS_AJ_JRN_HD_TMP4 is not cleared for process instance 18823 as we did not add logic to do that. We will delete those using following SQLs for now.
delete from PS_AJ_JRN_HD_TMP4 where PROCESS_INSTANCE = 18823;
delete from PS_AJ_JRN_LN_TMP4 where PROCESS_INSTANCE = 18823;


Analyze Scenario 2: abend and resolution
Application Engine dedicated instance number 5 for PS_AJ_JRN_HD_TMP and PS_AJ_JRN_LN_TMP.
Application Engine was not ReStartable, we do not have data in PS_AERUNCONTROL. So ReStart is not possible. Similarly no temporary records got stuck in PS_AETEMPTBLMGR.
We had COMMIT after inserting into temp tables therefore we had data in temporary tables. But because the Application Engine was not ReStartable, the dedicated temp tables were not kept locked by instance 18824. The next instance 18825 got the same TMP5 tables dedicated and cleared the data before using them. Otherwise we needed to clean up that data from temp tables PS_AJ_JRN_HD_TMP and PS_AJ_JRN_LN_TMP.
Let us delete that request for instance 18824 and submit a new one for run control TEST2 from Process Request Page for now.
The Application Engine completes to success.


Analyze Scenario 3: abend and resolution
Application Engine dedicated instance number 5 for PS_AJ_JRN_HD_TMP and PS_AJ_JRN_LN_TMP.
Application Engine was ReStartable, we have data in PS_AERUNCONTROL to be used for ReStarting. We also have temporary records stuck in PS_AETEMPTBLMGR so the Application Engine will use the right instances of temp tables when rerun.
We did not have COMMIT after inserting into temp tables therefore we have no data in temporary tables.
So if we ReStart Application Engine for process instance 18825, we will not get desired results. We will delete the request for process instance 18825 and resubmit a new request for run control TEST3. We do not have to delete records from PS_AERUNCONTROL and PS_AJ_JRCPSJE_AET as deleting request will take care of that.
Let us delete that request for process instance 18825 and submit a new one for run control TEST3 from Process Request Page for now.
The Application Engine completes to success.


Analyze Scenario 4: abend and resolution
Application Engine shared (base or default) temp tables PS_AJ_JRN_HD_TMP and PS_AJ_JRN_LN_TMP for this run.
Application Engine was not ReStartable, we do not have data in PS_AERUNCONTROL. So ReStart is not possible. Similarly no temporary records got stuck in PS_AETEMPTBLMGR.
We did not have COMMIT after inserting into temp tables therefore no need of clearing temporary tables PS_AJ_JRN_HD_TMP and PS_AJ_JRN_LN_TMP.
Let us delete that request for instance 18826 and submit a new one for run control TEST4 from Process Request Page for now.
The Application Engine completes to success with process instance 18829.
The table PS_AJ_JRN_HD_TMP4 is not cleared for process instance 18829 as we did not add logic to do that. We will delete those using following SQLs for now.
delete from PS_AJ_JRN_HD_TMP4 where PROCESS_INSTANCE = 18823;
delete from PS_AJ_JRN_LN_TMP4 where PROCESS_INSTANCE = 18823;


Review the data in Journal tables
Let us see just the journal_header table that the new journals are created. We will use the following SQL.
select * from PS_JRNL_HEADER
 where BUSINESS_UNIT = 'FED01' and JOURNAL_ID like 'TST%';

Similarly we can use the following SQL.
select * from PS_JRNL_LN
 where BUSINESS_UNIT = 'FED01' and JOURNAL_ID like 'TST%';



That is it in this article for now. It was a long one. Relax for now and revisit if needed. After that you may read peoplesoft documentation for deeper understanding of temporary tables and achieve better performance with Application Engine Parallel Processing.