Good Practice for executing PSBUILD scripts

I have had change control call me several times in the past to restore a table because it was accidentally dropped when executing the SQL script generated using Application Designer. Whenever you use Application Designer to generate a script with “Alter by Table Rename” option checked, the script will have the following SQL’s

– Start the Transaction

– Create temporary table

– Copy from source to temp table

– CAUTION: Drop Original Table

– Rename Table

– Done

The risk of running this script As-Is is that if any of the above SQL encounters an error then the script will still proceed to run the next SQL in the script. So if there is an error while running any SQL prior to dropping the original table then we would have lost the table and all the data.

Hence the best practice for running any scripts created using Application Designer is to include ‘WHENEVER SQLERROR EXIT’ at the beginning of the script. This will ensure that the script will abort anytime there is an error. Making this part of our Change Control checklist has ensured that there is no reoccurrence of this issue.
Note:

Scripts generated using Change Assistant automatically have ‘whenever sqlerror exit’ at the beginning of the script.

Read More about  PSBUILD Scripts

Not able to view record in PeopleSoft Query Manager!!

Sometime back, I was approached with a request from a developer who had created a new record in Application Designer but was not able to view this record in Query Manager. So in this post, I will demonstrate the steps required to view a new or existing record in Query Manager. Below steps are from Tools version 8.47.02.

1. New record HX_TEST has been created and we would like to use it in our PS Query.
2. Navigate to PeopleTools > Security > Query Security > Query Access Manager
3. Search for the Tree which should have access to this record.

1. Click on the hyperlink and add the record HX_TEST

1. Open the appropriate Permission List which has access to HX_TEST_TREE and click on the Query tab

1. Click on Access Group Permissions

1. Verify the access

1. Open PeopleSoft Query Manager and you should have access to this new record.

 

Note that this activity is the realm of PeopleSoft Security Administrator since it needs careful analysis of the question

PeopleSoft Flashback…

Flashback query has been a boon from Oracle since its introduction in 9i. In a PeopleSoft environment there are many areas a DBA can utilize this feature. The commands are straightforward and will make your life easier if you remember them.

Consider a scenario where you are doing a data fix in production database. You have been extremely busy today and swamped with 4 high priority problem tickets. You maintain your composure and use your multitasking skills.

The Functional Consultant gave you the below SQL to perform the data fix.

DELETE FROM PS_ABC_VCH_APPR WHERE VOUCHER_ID IN (‘7865419’,’7865438’,’7865428’) AND ENTRY_STATUS IN (‘P’,’X’);

It will delete 12 rows and you confirm the same. You issue commit, close the problem ticket and start working on the next high priority ticket. Phew, one down and 3 more to go…

You get a phone call from the functional consultant 15 mins after you closed the data fix problem ticket. He wants to re-open the problem ticket and request a restore. The fix did not work and they have identified a workaround that can be performed online. He thanks you for taking care of this so quickly and requests you to call him when the data is restored. As you hang up, you begin to feel a little bit of nervousness. Did you or did you not take a backup? Then it strikes you that you forgot to take the backup in your rush to execute the ticket and assuming that the functional team rarely request restore!! Hmm…

In these scenarios, the below SQL will be a life saver!

INSERT INTO PS_ABC_VCH_APPR SELECT * FROM PS_ABC_VCH_APPR AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘20’ MINUTE) WHERE VOUCHER_ID IN (‘7865419’,’7865438’,’7865428’) AND ENTRY_STATUS IN (‘P’,’X’)

The SQL above will insert into PS_ABC_VCH_APPR all rows that existed for the above criteria 20 minutes ago.

Or

INSERT INTO PS_ABC_VCH_APPR SELECT * FROM PS_ABC_VCH_APPR AS OF TIMESTAMP TO_TIMESTAMP(‘24-aug-07 14:35:00′,’DD-MON-YY HH24: MI: SS’)WHERE VOUCHER_ID IN (‘7865419’,’7865438’,’7865428’) AND ENTRY_STATUS IN (‘P’,’X’)

The SQL above will insert into PS_ABC_VCH_APPR all rows that existed for the above criteria at the date and time mentioned.

þ You get a call at 4am from the Help Desk, an incomplete migration yesterday evening has resulted in messing up the data after the custom SQR process ran and failed, the functional analyst is requesting an emergency restore of the database. He wants the database to be restored to a point in time just prior to running the SQR process, and complete the migration. Why does this always happen when you are on-call??

You know that restoring the database is going to take 3-4 hours and keep your finger crossed (hoping the hot backup and archive logs are valid).

Instead, you call the functional analyst, check out the custom SQR process and determine that the batch job only impacts 12 tables. You tell him that it is not too late and you will use the flashback feature to get back the data in those 12 tables to a state prior to execution of the batch job!! This has saved your client 3+hrs of downtime and we all know that time is $$$.

Note 1 – It is not recommended to use the flashback feature as a backup policy but as an option in emergency scenarios where you forgot to take a backup or the backup got corrupted.

Note 2 – Do not forget to read about the limitations. You can only flashback if your UNDO segments have this information. This is driven by the parameter UNDO_RETENTION and the fact that Oracle can only honor if enough undo space is available for active transactions.

Summary – Though we still do not have the capability to flashback in our real life (oh we wish we could go back in time and correct that one mistake we made in our life) but there are many scenarios in a real world PeopleSoft environment which can utilize the flashback feature. We just have to keep our mind open to the possibilities that this feature provides.

How do you determine PeopleSoft Portal Navigation?

I was composing this post when there was a comment from “Spamboy” for my previous post where he provided the SQL to identify the portal navigation. Thanks for your comments!!

In this post I will share the SQL I use when I want to determine the portal navigation. Here it is …

SELECT LPAD(‘–’,2*(LEVEL-1)) || PORTAL_LABEL “NAVIGATION”

FROM (SELECT PORTAL_LABEL, PORTAL_PRNTOBJNAME, PORTAL_OBJNAME, PORTAL_URI_SEG2 FROM PSPRSMDEFN A

WHERE PORTAL_NAME = ‘EMPLOYEE’ ) B

WHERE B.PORTAL_PRNTOBJNAME != ‘ ‘

START WITH (B.PORTAL_URI_SEG2 IN

(SELECT D.PNLGRPNAME

FROM PSMENUITEM A, PSMENUDEFN B, PS_PRCSDEFNPNL C, PSPNLGROUP D

WHERE A.MENUNAME=B.MENUNAME

AND A.PNLGRPNAME = C.PNLGRPNAME

AND A.PNLGRPNAME = D.PNLGRPNAME

AND PRCSNAME LIKE UPPER(‘&PRCSNAME’))

)

CONNECT BY PRIOR B.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME;

NAVIGATION

———————————-

Load Transactions into AM

–Load Transactions

–Send/Receive Information

–Asset Management

Also, if you have built the search index (PeopleTools > Portal > Build Registry Search Index) and know the component name. You can directly enter the component name in the search box. Here is what you get.

 

Read More about  Peoplesoft