Parallel Processing using Temporary Record with Application Engine in PeopleSoft

Parallel Processing using Temporary Record with Application Engine in PeopleSoft

This is another important question we often encounter during the interviews. I have been struggling a lot to understand and finally did it with a classic example.

Parallel processing comes in picture when there is a requirement to process large bulk of rows without compromising the performance which might otherwise get impacted greatly with non-parallel based processing. We would try to understand this concept with an actual example where this concept has been used.

Requirement - There are 100,000 employees in the organization and many of them have more than one assignments (EMPL_RCD) hence the total number of rows in the PER_ORG_ASGN record collectively will be 120,000. Develop a program to update the primary job indicator flag for each employee in the table PRIMARY_JOBS.

Implementing Parallel Processing using Temporary Record with Application Engine

Step :1 Open the App Designer

Step :2 Create three record definitions BN_EMPLID_WRK, BN_EMPLID1_WRK and BN_JOB_WRK of type "Temporary Table"

Step 3: Create a record BN_AGE50_AET of type "Derived/Work"

Step 4: Create a new Application Engine program "BN_UPD_PRIM" in app designer.

Step 5: Open the Application engine properties, go to "Temp Tables" tab and assign all three temp tables. Give the instance count as 10.

Step 6: Likewise, go to "State Records" tab and add the record BN_AGE50_AET.

Step 7: Build the temporary records BN_EMPLID_WRK, BN_EMPLID1_WRK and BN_JOB_WRK. App designer will create the number of instances for each of these temporary tables equal to given in the Program Properties -> Temp Tab - Instance count box which is "10" in our case which means the tables for each temp record will be created as below:

  BN_EMPLID_WRK -  BN_EMPLID_WRK1, BN_EMPLID_WRK2... up to 10 instances

  BN_EMPLID1_WRK - BN_EMPLID1_WRK1, BN_EMPLID1_WRK2.... up to 10 instances

  BN_JOB_WRK - BN_JOB_WRK1, BN_JOB_WRK2.... up to 10 instances

Step 8:  In the MAIN section of app engine (Or create a new section) add a Step/Action of SQL type, then add below SQL in that.

This SQL will load all the employees into temp table instances BN_EMPLID_WRK1, BN_EMPLID_WRK2... etc, Please make sure that you are using the meta sql %Table with the temp table name.

Lets say we have 100,000 employees in PS_PERSON record and each employee has just one assignment in PS_PER_ORG_ASGN. Since we have instance count for the temp record to 10 so when the program is run, The selected 100000 rows will be divided into like 100000/10 = 10000 and each chunk of 10000 rows will be inserted into one of the available instances BN_EMPLID_WRK1, BN_EMPLID_WRK2 ...etc.

Please note that we don't have to worry about which instance a particular chunk is being inserted into instead, %Tablemetasql does that for us. However, Its not necessary that application engine will process the rows in exactly same way explained above, its just a logical explanation which is trying to convey that the number of selected rows i.e 100,000 will be divided and loaded into appropriate temp table instances to be processed simultaneously which is nothing but parallel processing.

Once above step is processed, the temp table BN_EMPLID_WRK has been loaded with large volume of data (1000,000 rows) of employees eligible to be processed. How many rows have been loaded in which temp table instance (BN_EMPLID_WRK1, BN_EMPLID_WRK2... etc) is none of our concerns.

Step 9: Add below Step/Action to update the statistics of temp table BN_EMPLID_WRK for the indexes to improve the performance when the rows are selected from the instances of this table in further steps.

Step 10: Add below Step/Action to get the JOB data (EFFDT, EFFSEQ and EMPL_STATUS) for each employee loaded in BN_EMPLID_WRK, and insert into another temp table BN_JOB_WRK (BN_JOB_WRK1, BN_JOB_WRK2 ... upto 10 instances).

Here again as you notice, we just used %Table metasql with the table BN_JOB_WRK in the SQL which will take care as to how should be the allocation of rows for the instances BN_JOB_WRK1, BN_JOB_WRK2...etc.

Step 11:  Add below Step/Action to update the statistics of temp table BN_JOB_WRK for the indexes to improve the performance when the rows are selected from the instances of this table in further steps.

Step 12: Add below Step/Action to select those employees loaded in the temp table BN_EMPLID_WRK who are all don't have more than one assignments (EMPL_RCD) for a benefit record number (BENEFIT_RCD_NBR), and insert into another temp table BN_EMPLID1_WRK (BN_EMPLID1_WRK1, BN_EMPLID1_WRK2...upto 10 instances).

Step 13: Add below Step/Action to update the statistics of temp table BN_EMPLID1_WRK for the indexes to improve the performance when the rows are selected from the instances of this table in further steps.

Step 14: Finally, mark the current EMPL_RCD as "Primary Job" in the  table PS_PRIMARY_JOBS for the processed employees by joining all three temp tables.

Well, it's quite evident that the procedure to implement parallel processing appears simpler than we thought. However, if we try to compare it with the non-parallel way of addressing the same request then it would certainly help us understand the concept clearly.

Let's discuss how would it have been addressed if there was no mechanism called "Parallel Processing".

1) Record Definition - The number of records will be same but the record type for BN_EMPLID_WRK, BN_EMPLID1_WRK and BN_JOB_WRK will be "Table".

2) Since there is no temporary record created hence no need to assign the temp records in Program Properties -> Temp Tables. It won't be applicable.

3) No Need to use %Table metasql while referring to the records.

Everything else will remain exactly same. We also need to understand how different it will behave without parallel processing.

In this case, When we run the program, The system will  store all the 100,000 rows into the table BN_EMPLID_WRK during the execution of Step 1 explained earlier unlike, into various temp table instances BN_EMPLID_WRK1, BN_EMPLID_WRK2..etc. So, when execution goes further then all the 100,000 rows are fetched from one table and processed but in case of parallel processing the chunk of rows i.e 10000 if fetched from each temp table instance and processed all simultaneously.

Hence, If time taken to complete the processing is 20 Mins then it will be 20/10 ~ 2 to 5 Mins if parallel processing is used.

Useful Security Queries

SET SCHEMA PSUSER

1) Users with a Particular Role

Select ROLEUSER, ROLENAME FROM PSROLEUSER

WHERE ROLENAME = 'DP_ALLPNLS'

ORDER BY ROLEUSER

2) Users with a particular Permission list

Select OPRID, OPRCLASS

FROM PSOPRCLS

WHERE OPRCLASS = 'EVERYONE'

ORDER BY OPRID

3) Users with a particular Primary permission list

Select OPRID, OPRCLASS FROM PSOPRDEFN

WHERE OPRCLASS = 'EVERYONE'

ORDER BY OPRID

4) Users with a Particular Row security Permission List

SELECT OPRID, ROWSECCLASS

FROM PSOPRDEFN

WHERE ROWSECCLASS = 'EVERYONE'

ORDER BY OPRID

5) Users, Role & Class with Access to a particular page

Select B.ROLEUSER,C.OPRDEFNDESC, A.ROLENAME, A.CLASSID

FROM PSROLECLASS A , PSROLEUSER B , PSOPRDEFN C

WHERE A.ROLENAME = B.ROLENAME

AND B.ROLEUSER = C.OPRID

AND C.ACCTLOCK = 0

AND A.CLASSID IN (SELECT DISTINCT CLASSID

FROM PSAUTHITEM

WHERE PNLITEMNAME = 'VCHR_SUMMARY_PG'

AND DISPLAYONLY = 0)

ORDER BY B.ROLEUSER

6) Roles & Class with Access to a particular page

SELECT A.ROLENAME, A.CLASSID

FROM PSROLECLASS A, PSROLEDEFN B

WHERE A.CLASSID IN

(SELECT DISTINCT CLASSID

FROM PSAUTHITEM

WHERE PNLITEMNAME = 'VNDR_LOC')

AND A.ROLENAME = B.ROLENAME

ORDER BY A.ROLENAME

7) Permission Lists & Details with Access to a Particular page

SELECT * From PSAUTHITEM

WHERE PNLITEMNAME =  'VNDR_LOC'

8) Roles Assigned to a particular User

SELECT ROLEUSER, ROLENAME

FROM PSROLEUSER

WHERE ROLEUSER = 'nukas'

ORDER BY ROLENAME

10) Permission lists Assigned to a particular user

SELECT OPRID, OPRCLASS

FROM PSOPRCLS

WHERE OPRID = 'nukas'

ORDER BY OPRCLASS

11) Roles & Permission lists Assigned to a Particular User

SELECT A.ROLEUSER, A.ROLENAME, B.CLASSID

FROM PSROLEUSER A, PSROLECLASS B

WHERE A.ROLEUSER = 'nukas'

AND A.ROLENAME = B.ROLENAME

12) Permission lists assigned to a particular Role

SELECT * FROM PSROLECLASS

WHERE ROLENAME = 'APDEVELOPER'

ORDER BY CLASSID

13) All Pages Accessible by a user

SELECT A.ROLEUSER, A.ROLENAME , B.CLASSID,

C.MENUNAME, C.BARNAME, C.BARITEMNAME,

C.PNLITEMNAME, C.DISPLAYONLY, C.AUTHORIZEDACTIONS

FROM PSROLEUSER A, PSROLECLASS B, PSAUTHITEM C

WHERE A.ROLEUSER = 'nukas'

AND B.CLASSID = C.CLASSID

ORDER BY A.ROLENAME, B. CLASSID,

C.MENUNAME, C.BARITEMNAME, C.PNLITEMNAME

14) All Pages Accessible by a Role

SELECT DISTINCT A.ROLENAME, B.CLASSID,

C.MENUNAME, C.BARNAME, C.BARITEMNAME,

C.PNLITEMNAME, C.DISPLAYONLY, C.AUTHORIZEDACTIONS

FROM PSROLEUSER A, PSROLECLASS B, PSAUTHITEM C

WHERE A.ROLENAME = 'APDEVELOPER'

AND B.CLASSID = C.CLASSID

ORDER BY B.CLASSID,

C.MENUNAME, C.BARITEMNAME, C.PNLITEMNAME

15) All Pages Accessible by a Permission list

SELECT * FROM PSAUTHITEM

WHERE CLASSID = 'AEPNLS'

ORDER BY MENUNAME, BARITEMNAME, PNLITEMNAME

16) All users with Access to Maintains Security. (Excluding change password and user self service.

SELECT B.ROLEUSER, C.OPRDEFNDESC, A.ROLENAME, A.CLASSID

FROM PSROLECLASS A, PSROLEUSER B, PSOPRDEFN C

WHERE B.ROLENAME = A.ROLENAME

AND B.ROLEUSER = C.OPRID

AND C.ACCTLOCK = 0

AND A.CLASSID IN

(SELECT DISTINCT CLASSID

FROM PSAUTHITEM

WHERE MENUNAME LIKE 'MAINTAIN_SECURITY%' AND PNLITEMNAME NOT IN

('CHANGE_PASSWORD', 'USER_SELF_SERVICE')

AND DISPLAYONLY = 0)

ORDER BY ROLENAME, ROLEUSER

17) All Users with Access to Application Designer

SELECT DISTINCT A.OPRID, B.OPRDEFNDESC

FROM PSOPRCLS A, PSOPRDEFN B

WHERE A.OPRID = B.OPRID

AND B.ACCTLOCK = 0

AND A.OPRCLASS IN

(SELECT DISTINCT CLASSID

FROM PSAUTHITEM

WHERE MENUNAME LIKE 'APPLICATION_DESIGNER%'

AND DISPLAYONLY = 0)

18) PS record is associated with which Query security tree 

SELECT

A.TREE_NAME,

A.DESCR,

B.PARENT_NODE_NAME,

B.TREE_NODE,

B.TREE_NODE_TYPE,

C.CLASSID,

B.TREE_NODE_NUM,

B.PARENT_NODE_NUM,

C.ACCESS_GROUP,

C.ACCESSIBLE

FROM PSTREEDEFN A,

PSTREENODE B,

PS_SCRTY_ACC_GRP C

WHERE A.SETID = ' '

AND A.TREE_STRCT_ID = 'ACCESS_GROUP'

AND A.SETID = B.SETID

AND A.SETCNTRLVALUE = B.SETCNTRLVALUE

AND A.TREE_NAME = B.TREE_NAME

AND B.TREE_NODE = 'AR_ACCESS_GROUP'

AND B.TREE_NAME = C.TREE_NAME

ORDER BY 7, 8, 6

19) Roles attached to a component (portal_uri_seg2) or content reference (portal_objname)

SELECT DISTINCT

C.PORTAL_NAME,

C.PORTAL_OBJNAME,

B.ROLENAME,

E.DESCR

FROM PSROLECLASS B, PSPRSMPERM C, PSPRSMDEFN D, PSROLEDEFN E

WHERE C.PORTAL_OBJNAME = D.PORTAL_OBJNAME

AND B.CLASSID = C.PORTAL_PERMNAME

AND B.ROLENAME = E.ROLENAME

AND (D.PORTAL_URI_SEG2 = 'ACL_PURGE_USER_RUN' OR D.PORTAL_OBJNAME = 'ACL_PURGE_PW_RUN_GBL');

20) Permissions attached to a component (portal_uri_seg2) or content reference (portal_objname)

SELECT DISTINCT

D.PORTAL_NAME,

D.PORTAL_OBJNAME,

C.PORTAL_PERMNAME,

E.CLASSDEFNDESC

FROM PSPRSMPERM C , PSPRSMDEFN D , PSCLASSDEFN E

WHERE D.PORTAL_OBJNAME = C.PORTAL_OBJNAME

AND   D.PORTAL_NAME    = C.PORTAL_NAME

AND   C.PORTAL_PERMNAME = E.CLASSID

AND (D.PORTAL_URI_SEG2 = 'ACL_PURGE_USER_RUN' OR D.PORTAL_OBJNAME = 'ACL_PURGE_PW_RUN_GBL');

21) Users that can access a component (portal_uri_seg2) or content reference (portal_objname)

SELECT DISTINCT

C.PORTAL_NAME,

C.PORTAL_OBJNAME,

A.ROLEUSER,

E.OPRDEFNDESC

FROM PSROLEUSER A,

PSROLECLASS B,

PSPRSMPERM C,

PSPRSMDEFN D,

PSOPRDEFN E

WHERE D.PORTAL_OBJNAME = C.PORTAL_OBJNAME

AND A.ROLENAME= B.ROLENAME

AND B.CLASSID = C.PORTAL_PERMNAME

AND A.ROLEUSER = E.OPRID

AND (D.PORTAL_URI_SEG2 = 'ACL_PURGE_USER_RUN' OR D.PORTAL_OBJNAME = 'ACL_PURGE_PW_RUN_GBL');

22)  Finding Navigation Path by component or page name (component (portal_uri_seg2))

SELECT

RTRIM(E.PORTAL_LABEL)

|| ' >> ' || RTRIM(D.PORTAL_LABEL)

|| ' >> ' || RTRIM(C.PORTAL_LABEL)

|| ' >> ' || RTRIM(B.PORTAL_LABEL)

|| ' >> ' || RTRIM(A.PORTAL_LABEL)

FROM PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME = A.PORTAL_NAME

AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN C

ON C.PORTAL_NAME = B.PORTAL_NAME AND C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN D

ON D.PORTAL_NAME = C.PORTAL_NAME AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN E

ON E.PORTAL_NAME = D.PORTAL_NAME AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME WHERE

A.PORTAL_URI_SEG2 = 'ACL_PURGE_USER_RUN'

23) Users that can access a to Access group, tree name and tree node

SELECT DISTINCT C.ROLEUSER, C.ROLENAME, B.CLASSID, A.TREE_NAME, A.ACCESS_GROUP, A.ACCESSIBLE, D.TREE_NODE 
FROM PS_SCRTY_ACC_GRP A, PSROLECLASS B, PSROLEUSER C, PSTREENODE D 
WHERE A.CLASSID = B.CLASSID 
AND B.ROLENAME = C.ROLENAME 
AND C.ROLEUSER = ‘nukas’
AND A.TREE_NAME = D.TREE_NAME 
AND D.EFFDT (SELECT MAX(D_ED.EFFDT) FROM PSTREENODE D_ED 
                          WHERE D.SETID = D_ED.SETID 
                           AND D.SETCNTRLVALUE = D_ED.SETCNTRLVALUE 
                           AND D.TREE_NAME = D_ED.TREE_NAME 
                           AND D_ED.EFFDT <= SYSDATE) 

Peoplesoft Audit

PeopleSoft Audit

To help us keep track of changes done to sensitive data by appropriate user, we use Audit.
Later we can query the Audit record to see the list of changes happened.

In PeopleSoft, we can set Audit at
1. Record Level
2. Field Level
3. Database Level

For Record Level and Field Level, Audit captures changes done to data for the online page. If the data is changed by any program using a SQL statement then Audit is not captured.

For Database Level, It covers both online and database changes done to the transaction record.

Base Records 

The base record is the record that you want to monitor, or audit, as in BU_ITEMS_INV. Presumably; the base record contains fields that you want to monitor. Limit the auditing of tables to the application tables and avoid auditing People Tools tables.

Audit Record

The audit record is a custom record that you create with Application Designer. It stores the audit information for the fields on the base record that the trigger collects. A sample name for audit record might be DPI_BUITM_AUDIT.

Implement Record level Audit 

Record level auditing allows you to have separate audit tables dedicated to one database record.

A)     Choose the Record:

Not all records in PeopleSoft need auditing. When there is such a business requirement, choose the record(s) that needs to be audited.

B)      Create the Audit Record:

The Audit record is essentially a copy of the record being audited. The easiest way to create a copy of your record is to do a Save As. Name the new record under standard record naming convention prefixed with AUDIT_

C)      Remove the unwanted: Delete fields that won’t be audited and Remove all key structures

D)     Add Audit Fields:

Insert the below fields (also known as audit fields) at the top of the new record in the below order

AUDIT_OPRID (Captures the User who performed an action)
AUDIT_STAMP (Captures the date and time when the action was performed)
AUDIT_ACTN (Captures the type of action performed)
AUDIT_RECNAME (Captures the name of the audited record definition. This field is used only when the same audit record is used for auditing multiple records)

On the Record Field Properties for AUDIT_STAMP, we need to check the Auto-Update checkbox. This is essential to correctly populate the date-time stamp.

E)      Build the audit record:
Your Audit record must be a Database record and you should be able to access it using Query.

F)      Link them up
Open the Base Record that is to be audited. Open its properties. On the Use tab, under the Record Audit, specify the name of the audit record that we just created as the Record Name. Choose Audit Options based on your requirement.

Add – An Audit table row is inserted when a row is inserted in the base record
Delete – An Audit table row is inserted when a row is deleted from the base record
Change – Audit table row(s) is inserted when a row changes in the base record
Selective – Audit table row(s) is inserted when common field (present in both base record and audit record) changes

G)     Test the Auditing:

Based on the Audit option that you have chosen, perform some transaction that can trigger the audit. Now query the audit record and check for new rows. Try to infer the meaning of each row that got added

H)     AUDIT_ACTN :

A – Row inserted
D – Row deleted.
C – Row changed (updated), but no key fields changed.
K – Row changed (updated), and at least one key field changed.
N – Row changed (updated), and at least one key field changed.

In case of C and K the system writes old values to the audit table. While for N, the new values are written into the audit table.

Implement Field level Audit 

Field-level audits record when you insert, update, or delete a field value. The system writes a row of audit data to the PSAUDIT table for each field marked for audit. You may write multiple rows of audit data for a single transaction.

1.        The PSAUDIT table contains the following fields:

 • AUDIT_OPRID
• AUDIT_STAMP
• AUDIT_ACTN
• RECNAME
• FIELDNAME
• OLDVALUE
• NEWVALUE
• KEY1, KEY2, …

2.      A field level audit is a record field property. To configure a field level audit

a)     Open the desired record.

b)     Double click the field you would like to audit.

c)      In the Audit section, select Field Add, Field Change, or Field Delete.

                Field Add: Audits this field whenever a new row of data is added

               Field Change: Audits this field whenever the content changes.

               Field Delete: Audits this field whenever a row of data is deleted.
 

d)     SELECT * FROM PSAUDIT WHERE RECNAME='YOUR RECORD' AND FIELDNAME ='FIELD NAME';

Implement Database level Audit 

PeopleSoft's delivered Audit feature works only for changes made from online. We depend on RDBMS's trigger feature to track changes made through non-PeopleSoft methods. PeopleSoft has come up with a Database Level auditing feature using which we can combine both the PS delivered Audit and Database's Trigger feature

Trigger

The trigger is the mechanism that a user invokes upon making a change to a specified field. The trigger stores the audit information in the audit table. PeopleSoft enables you to create triggers. A sample name for a trigger might be VENDOR_TR.

Note: If you modify the record definition of the base record, then you must modify the audit record and re-create the associated trigger.

     Update Database Level Auditing:

Navigation: People Tools > Utilities > Audit > Update Database Level Auditing

1) Search for the PeopleSoft record name to audit. The system by default will create audit trigger record with the base record name suffixed by _TR

2) Select the Audit record name.

3) Audit Options available are Add, Change or Delete

4) Click on "Generate Code". This will create the SQL statement to use for the Trigger creation

5) "Create Trigger Statement" section should have the SQL statement generated by above action

Note:

1) All the information entered above, gets captured in the PeopleTools trigger definition table – PSTRIGGERDEFN.

2) EnableDBMonitoring domain parameter must be set in PSADMIN for this feature to work

Perform Database Level Audit:

Navigation: People Tools > Utilities > Audit > Update Database Level Auditing

1) Select or create a Run Control ID

2) Select either "Create All Triggers" or "Create Trigger(s) On" depending on the requirement

3) Click Run which will trigger the Audit Application Engine - TRGRAUDPROG(Auditing Triggers). This process will fetch every row in PSTRIGGERDEFN and writes Create Trigger statements to the file - TRGCODEX.SQL, where Z represents a digit determined by the number of files that already exist in the output directory.

4) This step only creates the SQL Statement, and it should be run using SQL tool to create trigger(s)

       To Delete Audit Trigger:

            1) Select People Tools, Utilities, Audit, Update Database Level Auditing

            2) Open the trigger that you want to delete

            3) Clear all the Audit options (Add, Change, and Delete)

           4) Click Generate Code

           5) Click Save

           6) Drop the trigger name from the database

Migrating:-

When migrating projects containing records that had audit record changes made to them, ensure the “Set Target Audit Flags from Source” flag is set in Copy Options:

Otherwise, the audit properties for these records won’t get copied across to the target environment.