By Chris Malek | 17 January 2013
A Simple Procedure to Compare Application Designer Projects Prior to a DB Refresh
This article shows two different methods to determine what PeopleSoft projects may be lost due to a database refresh. There are two methods that will accomplish the same goal.
- The first method can be completed by any developer comfortable with datamover.
- The second method requires that your DBA create a database link.
You should use whatever method you feel more comfortable with. They both get to the same destination. In the document below, we assume the following:
- You are on an Oracle database
- If you are on another platform, the SQL for the link in Method 2 will need to be tweaked.
- You have two databases called HRDEV and HRTST.
- You are wanting to refresh HRDEV but want to make sure that all the projects from HRDEV are backed up to HRTST.
Method 1: DataMover method
This method requires no DB Links or DBA involvement. We just need datamover access in both databases. We will export the PSPROJECTDEFN data from the database that is NOT being refreshed (HRTST) and then import it into the database that IS being refreshed (HRDEV) as a different name (REMOTE_PROJECT). This can be accomplished with DataMover with ease. We then can look at what projects are missing or more recent in the database that is going to be refreshed.
Step 1 – Export the PSPROJECTDEFN table from the retained DB.
Run this template DATAMOVER script in the database that is NOT being refreshed (HRTST). You may need to tweak slightly.
SET LOG c:\temp\HRTST_PROJECTEXPORT.LOG; SET OUTPUT c:\temp\HRTST_PROJECTEXPORT.DAT; EXPORT PSPROJECTDEFN;
Step 2 – Import as a Different Name
Log into the database that will be refreshed (HRDEV) with DataMover. Run the following template script.
SET LOG c:\temp\HRTST_PROJECTCTIMPORT.LOG; SET INPUT c:\temp\HRTST_PROJECTEXPORT.DAT; IMPORT PSPROJECTDEFN AS REMOTE_PROJECT ;
Step 3 – Analyze Projects
Now you can run the SQL below to see what projects are more recent or only in the database that will be refreshed. It should print out time stamp differences between the two databases. It should find:
- Projects that exist in both database that have a different LASTUPDDTTM. This would mean that something has likely changes in one database and the projects may not be the same.
- It will also find projects that do not exist in the retained database (HRTST). The are projects that will be lost in the refresh.
SELECT DEVP.LASTUPDOPRID, DEVP.PROJECTNAME, DEVP.LASTUPDDTTM as dev_lastupdate , TSTP.LASTUPDDTTM as tst_lastupdate, 'date difference between databases' FROM PSPROJECTDEFN DEVP, REMOTE_PROJECT tstp WHERE DEVP.PROJECTNAME = tstp.PROJECTNAME AND TRUNC(DEVP.LASTUPDDTTM) - TRUNC(TSTP.LASTUPDDTTM) > 0 UNION select DEVP.LASTUPDOPRID, DEVP.PROJECTNAME, DEVP.LASTUPDDTTM as dev_lastupdate, NULL, 'does not exists in remote' FROM PSPROJECTDEFN DEVP where NOT EXISTS (SELECT 'X' FROM REMOTE_PROJECT tstp WHERE DEVP.PROJECTNAME = tstp.PROJECTNAME) ORDER BY 1;
Method 2: Database Link Method
This method requires a DBA to create a database link between the two databases. It will accomplish the same outcome as method 1 without moving data.
Step 1: Create DB Link
Ask your friendly DBA to establish a database link between HRDEV and HRTST for the db level user ID you use.
Step 2: Run This SQL
- It finds projects that may exists in both databases but may have different project update times
- It finds projects that exists in HRDEV that do not exists in HRTST.
- This sql uses the “@” db link notation so look for that in the sql.
- This SQL was designed to be run in HRDEV.
SELECT DEVP.LASTUPDOPRID, DEVP.PROJECTNAME, DEVP.LASTUPDDTTM as dev_lastupdate , TSTP.LASTUPDDTTM as tst_lastupdate, 'date difference between dev/tst' FROM PSPROJECTDEFN DEVP, PSPROJECTDEFN@HRTST tstp WHERE DEVP.PROJECTNAME = tstp.PROJECTNAME AND TRUNC(DEVP.LASTUPDDTTM) - TRUNC(TSTP.LASTUPDDTTM) > 0 UNION select DEVP.LASTUPDOPRID, DEVP.PROJECTNAME, DEVP.LASTUPDDTTM as dev_lastupdate, NULL, 'does not exists in tst' FROM PSPROJECTDEFN DEVP where NOT EXISTS (SELECT 'X' FROM PSPROJECTDEFN@HRTST tstp WHERE DEVP.PROJECTNAME = tstp.PROJECTNAME) ORDER BY 1;