How to use Data Mover Script in peoplesoft

Introduction to PeopleSoft Data Mover

PeopleSoft Data Mover Scripts are mainly used to migrate data from one PeopleSoft database to another, or to move data between PeopleSoft environments, such as from a development environment to a production environment. Data Mover scripts help automate the process of migrating data.

Data Mover processes the text files that contain a series of commands that the Data Mover utility will execute to perform a specific task, such as copying data from one table to another or exporting data to a file.

Sample PeopleSoft Data Mover Script

Here is a sample PeopleSoft Data Mover script that demonstrates how to export data from a PeopleSoft table and import it into another PeopleSoft database.
/* Export data from PS_JOB to a file.

/*You must specify a name of the file for the SET LOG statement or else a log file is not created */
SET LOG C:\TEMP\JOB_TBL.LOG; 

SET OUTPUT C:\TEMP\JOB_TBL.DAT; 

EXPORT PS_JOB ;

/* Import data from the export file into the target database */

SET LOG C:\TEMP\JOB_TBL.LOG; 
SET INPUT C:\TEMP\JOB_TBL.DAT; 
SET NO SPACE; /*Prevents tablespaces from being created.*/
SET NO RECORD; 
SET NO INDEX; 
IMPORT *; /* Insert data into tables using the information in an export file */

/* Verify that the import was successful */ 

SELECT COUNT() FROM PS_JOB;

In this example, the script first exports data from the PS_JOB table to a file located at /temp/ps_job_export.txt. The script then imports the data from the export file into the target database. Finally, the script selects the number of rows in the PS_JOB table to verify that the import was successful.

When running this script, it is important to ensure that the PeopleSoft database is set up correctly and that the appropriate Data Mover environment variables are set. These variables define the connection information for the source and target databases, as well as other important configuration settings.

Demo example

Navigation : Go > Data Mover and the below window will open

Conclusion

PeopleSoft Data Mover scripts are an essential tool for managing and transferring data between PeopleSoft databases. With the help of Data Mover scripts, you can automate the process of migrating data, saving time and reducing the risk of errors. The sample script above demonstrates how to export data from a PeopleSoft table and import it into another PeopleSoft database.

Click here to learn about sending an email using a generic template.

Frequently asked Questions (FAQ)

How do I roll back the changes in PeopleSoft after a migration? 

If you are working in PeopleSoft, we may have to face this kind of issue during migration 

We all have something in mind about how to roll back the changes in PeopleSoft after a migration. 

Unfortunately, we don’t have that option. But we can take the backup from the target instance before doing the migration. 

This would be the simple way 

For our understanding, let us consider HR_DEV (development instance) and HR_PROD (production instance). 

Step-1 

Go to the upgrade session for the HR_DEV instance. 

Step – 2 

Uncheck all the upgrade boxes. 

Repeat it for all the definitions in the project and save the project 

Step – 3 

Go to Tools > copy Project> To Database 

Now copy project to HR_PROD 

Note: Since we unticked the upgrade checkbox, only definitions will be migrated . 

Step-4 

Go the HR_PROD 

File > open> project> project_name 

Verify the definitions by comparing to HR_dev 

Step-5 

Go to tools > copy Project > To file 

Now you have successfully taken backups of the original codes. This may help you to roll back the changes. If there is any issue with migration,. 

Script to delete an AWE Process ID for particular effective date in peoplesoft?

In most of the cases deleting the AWE for the particular effective date is difficult because deletion need to perform in multiple tables 

Here we listed all the tables   

DELETE FROM PS_EOAW_STEP WHERE EOAWPRCS_ID = 'process_name' AND EOAWDEFN_ID = 'defn_name' AND EFFDT = '30-11-2022'; 
 
DELETE FROM PS_EOAWCRTA WHERE EOAWCRTA_ID LIKE '%Process_nameDefn_name30-11-2022%'; 
 
 DELETE FROM PS_EOAWCRTA_REC WHERE EOAWCRTA_ID LIKE '%Process_nameDefn_name30-11-2022%'; 
 
 DELETE FROM PS_EOAWCRTA_VAL WHERE EOAWCRTA_ID LIKE '%Process_nameDefn_name30-11-2022%'; 
 
 DELETE FROM PS_EOAW_TIMEOUT WHERE EOAWPRCS_ID = 'TheProcess_name' AND EOAWDEFN_ID = 'TheDefn_name' AND EFFDT='30-11-2022'; 
 
 DELETE FROM PS_EOAW_PATH WHERE EOAWPRCS_ID = 'TheProcess_name' AND EOAWDEFN_ID = 'TheDefn_name' AND EFFDT='30-11-2022'; 
 
 DELETE FROM PS_EOAW_STAGE WHERE EOAWPRCS_ID = 'TheProcess_name' AND EOAWDEFN_ID = 'TheDefn_name' AND EFFDT='30-11-2022'; 
 
 DELETE FROM PS_EOAW_PRCS WHERE EOAWPRCS_ID = 'TheProcess_name' AND EOAWDEFN_ID = 'TheDefn_name' AND EFFDT='30-11-2022'; 
commit;