Tuning Data Conversion AE during PeopleSoft Upgrade – I

In this post I will share a tuning tip that I used to improve the performance of the PeopleSoft Upgrade data conversion AE.

Data Conversion AE was truncating the temporary table -> Inserting 35 million rows into temporary table from transaction table -> Updating stats

The above steps combined took around 6hrs to complete. It ran in 30mins after the below changes to AE.

e dropped the index on temporary table

NSERT was modified to use APPEND hint

Recreated the index using NOLOGGING

Summary: PeopleSoft does not necessarily ship optimal code. We have to keep our eyes open to identify and modify the problem code to improve conversion performance

Read More About  Tuning Data Conversion

How to build an efficient interface using AE or SQR?

While building an Inbound/Outbound interface, most of the time we may be in dilemma whether to use Application Engine (AE) or SQR. Here are the tips to build an efficient interface using either AE or SQR.

If we use Application Engine, below points are to be taken into account

• If the interface is INBOUND, its better to go for Application Engine
• If the program fails, we can make use of Restart Control to restart the program from the position where it abends
• PeopleSoft – Business Process related Validation will be taken care automatically when we go for AE/CI.
• Parallel Processing can be easily achieved using AE. This can be done by using Temporary Tables and State Records.
• Reusability of SQLs is very much easy when we go for AE.
• Bulk Insertion of data and Set Processing of data can be easily done in an AE
• COMMIT levels can be maintained either at SECTION or STEP levels
• Generation of Reports using File Layout, Workflow related batch interface can be easily developed using an AE
• XML related processing is quiet easy when we go for AE
• When we go for Upgrade of the Current PS System, Retrofitting of AE is done by easily identifying the impacted objects.

If we are going to use SQR, below points can be taken into account to build an interface

• Formatting of Reports is very much easy in SQR using Begin-Header, Begin-Footer and Begin-Setup commands.
• If we are going to consider the Performance of the Interface Program, its better to go for SQR.
• Better Performance can be achieved by using Load-Lookup Arrays and Dynamic SQL
• Reusability of SQLs can be achieved by creating either generic Procedures or SQCs

Load Lookup Arrays in SQR

How to improve the performance of the SQR using Load Lookup Arrays?

For Simple database related validation, we will fire a SQL to check the sanity of data. If this data validation is to be fired for each and every row of data, then it is better to go for Load-Lookup Arrays. This is like Prompt table validation in the case of Online Pages.

In this way, we can reduce the database hits and thereby increasing the Performance of the SQR Program.

For Eg: If we want to validate the Earnings Codes present in the file, we can use these arrays to validate the Earning Code data.

Generally we will use the below SQL to validate the ERNCD data

SELECT PET.ERNCD, PET.EFFECT_ON_FLSA, PET.ADD_GROSS
FROM PS_EARNINGS_TBL PET
WHERE PET.EFFDT= (SELECT MAX (EFFDT) FROM PS_EARNINGS_TBL
WHERE PET.ERNCD = ERNCD
AND EFFDT <=(SYSDATE)

AND PET.EFF_STATUS =”A”

AND PET.ERNCD = $Erncd
In order to avoid these database hits for each and every row of data, we can the below code to load all the valid Earning Codes at once from the Database to Load Lookup Array Name EARN and then use the array to validate the input ERNCD data.

! Lookup array for EARNINGS CODES

Let $where = ‘PET.EFFDT = (SELECT MAX (EFFDT) ‘||

‘FROM PS_EARNINGS_TBL ‘||

‘WHERE PET.ERNCD = ERNCD ‘||

‘AND EFFDT <=SYSDATE) ‘||

‘AND EFF_STATUS =”A”’

LOAD-LOOKUP

NAME = EARN

TABLE = ‘PS_EARNINGS_TBL PET’

KEY = ERNCD

RETURN_VALUE = ERNCD||’-'||EFFECT_ON_FLSA||’-'||ADD_GROSS

WHERE = $where

QUIET

In this case, KEY (This is nothing but Input Data value) is ERNCD and RETURN_VALUE (Output data value) is Combination of ERNCD, EFFECT_ON_FLSA and ADD_GROSS database field’s value.

! Checking Load lookup array for data validation

Lookup EARN $input_data $output_data

Note: It is good to initialize these Load Lookup Arrays in the SETUP Section related procedures.

Read More About Load Lookup Arrays

How to Monitor PeopleSoft Messages/Subscription/Publication Contracts?

One of the hallmarks of a good administrator is that they are already troubleshooting the problem before the users complain!

The key to ensuring that the system does not spring surprises is to automate monitoring (without impacting the system performance). In my previous posts, I have provided steps to monitor the process scheduler and application server. This post will provide insights into monitoring the integration broker messages, publication and subscription contracts.

Use the below SQLs for monitoring.

SUBSCRIPTION

SELECT A.CHNLNAME,

DECODE(A.SUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.SUBCONSTATUS) STATUS, COUNT(*)

FROM PSAPMSGSUBCON A

WHERE

– not older than 99 days

A.LASTUPDDTTM >= sysdate – 99

– in error or timeout status

AND (A.SUBCONSTATUS IN (0, 6)

– in NEW status but was last updated 30 mins agao.

OR (A.SUBCONSTATUS = 1 AND A.LASTUPDDTTM < SYSDATE – 1/48))

GROUP BY A.CHNLNAME, DECODE(A.SUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.SUBCONSTATUS)

/

MESSAGE

SELECT A.CHNLNAME, DECODE(A.PUBSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.PUBSTATUS) STATUS, COUNT(*)

FROM PSAPMSGPUBHDR A

WHERE

– not older than 99 days

A.LASTUPDDTTM >= sysdate – 99

– in error or timeout status

AND (A.PUBSTATUS IN (0, 6)

– in NEW status but was last updated 30 mins agao.

OR (A.PUBSTATUS = 1 AND A.LASTUPDDTTM < SYSDATE – 1/48))

GROUP BY A.CHNLNAME, DECODE(A.PUBSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.PUBSTATUS)

/

PUBLICATION

SELECT A.CHNLNAME, DECODE(A.PUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.PUBCONSTATUS) STATUS, COUNT(*)

FROM PSAPMSGPUBCON A

WHERE

– not older than 99 days

A.LASTUPDDTTM >= sysdate – 99

– in error or timeout status

AND (A.PUBCONSTATUS IN (0, 6)

– in NEW status but was last updated 30 mins agao.

OR (A.PUBCONSTATUS = 1 AND A.LASTUPDDTTM < SYSDATE – 1/48))

GROUP BY A.CHNLNAME, DECODE(A.PUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.PUBCONSTATUS)

/

Value	Status
0	ERROR
1	NEW
2	STARTED
3	WORKING
4	DONE
5	RETRY
6	TIMEOUT
7	EDITED
8	CANCELED
9	HOLD

Read More About  PeopleSoft