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 want 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 it 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.

Note: We will import the PSPROJECTDEFN table exported above from HRSTST as “REMOTE_PROJECT”. The “As” modifier in the IMPORT statement is very important.

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 databases 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 exist in both databases but may have different project update times
• It finds projects that exist in HRDEV that do not exist 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;