Moving PeopleSoft ERP Data Between Databases With Data Mover Scripts

Why Transfer or Move Data?

Transferring PeopleSoft application data between databases is necessary for various business needs, including:

1. Application upgrades
2. User or system testing
3. Training environment setup

This article covers the essential commands and step-by-step instructions needed to perform data transfer effectively.

About Data Mover

Oracle PeopleSoft provided a tool called "Data Mover," a powerful tool useful in:

1. Transferring application data between PeopleSoft databases

2. Migrating databases across different operating systems and database types

Types of PeopleSoft Data Tables

1. System Tables
2. PeopleTools meta tables
3. Application Data tables - Stores business user-entered data (e.g., employee personal data, job data, salary and payroll information in PeopleSoft HCM)

Important DMS Commands and Their Syntax

Below are essential commands for working with Data Mover scripts:

1. SET OUTPUT

SET OUTPUT <file name>;

• Example: SET OUTPUT c:\dms\EmployeeData.dat;
• Purpose: To store export data in an output file.

2. SET LOG

SET LOG <Filename>;

• Example: SET LOG c:\dms\EmployeeDataExport.log;
• Purpose: To write to log file.

3. EXPORT

EXPORT <Record name>;

• Example: EXPORT Job;
• Purpose: To export the table's data into the output file.

4. IMPORT

IMPORT <Record Name>;

• Example: IMPORT Job;
• Purpose: To import Job record data from the input data file into the target database.

5. REPLACE_DATA

REPLACE_DATA <Record name>;

• Example: REPLACE DATA Employment;
• Purpose: Delete the existing data and import the data from the input file.

6. SET IGNORE DUPS

Ignores the duplicate record errors during execution.

Supported SQL Commands in DMS Scripting

We can use the SQL Commands in conjunction with the DMS Commands within the DMS scripts to enhance the power of Data Mover. The following SQL commands are supported.

CREATE, ALTER, DELETE, DROP, INSERT, UPDATE, TRUNCATE

How to Export Data

In order to export data from the source database, we need to create an "Export DMS script" by including all the required records and then executing it to export data into an output data file.

Note: When you log in to Data Mover, please make sure that:

1. Log in to database directly.
2. Data Mover has two operating modes: regular and bootstrap. For export, use regular mode, and use bootstrap for import.

Step 1

Log in to Data Mover, create a new file, and include all required records to be exported. Upon saving the script, the file will be saved as a .DMS file. 

Below is an example of a DMS Export script file to export PeopleSoft HCM Personal and Job data records.

REM -- This Export DMS Script is to export employee personal data tables and Job data in PeopleSoft HCM;
	SET OUTPUT c:\dms\EmployeeData.dat;
	SET LOG c:\dms\EmployeeExport.log;
	
REM -- Personal Data tables;
	EXPORT Personal_data;
	EXPORT Names;
	EXPORT Addresses;
	EXPORT Phones;
	
REM -- Job data tables;
	EXPORT Job;
	EXPORT Job_jr;
	EXPORT Job_Earns_dist;
	EXPORT Compensation;
	

Step 2

Execute the DMS script, and the data will be exported into the EmployeeExport.Dat file.

How to Import the Data

An "Import DMS script" is required to import data into the target database.

Step 1

Log in to Data Mover in the target database, create a new file, include necessary import commands, and save the script file. 

Below is the sample Import DMS script that corresponds to the previously exported data.

REM -- This Import DMS Script is to Import employee personal data tables and Job data in PeopleSoft HCM;
	SET IGNORE_DUP;
	SET COMMIT 10;
	SET INPUT c:\dms\EmployeeData.dat;
	SET LOG c:\dms\EmployeeImport.log;
    
REM --Delete tables before import;
	DELETE Personal_data;
	DELETE Names;
	DELETE Addresses;
	DELETE Phones;
REM -- Import Personal Data tables;
	IMPORT Personal_data;
	IMPORT Names;
	IMPORT Addresses;
	IMPORT Phones;

REM -- Delete Job data tables;
	DELETE Job;
	DELETE Job_jr;
	DELETE Job_Earns_dist;
	DELETE Compensation;
REM -- Import Job data tables;
	IMPORT Job;
	IMPORT Job_jr;
	IMPORT Job_Earns_dist;
	IMPORT Compensation;

Note: The SET COMMIT 10 command advises the system to perform a commit for every 10 records.

Best Practices

Data Mover scripts are widely used in PeopleSoft to transfer data and migrate certain objects between databases, making them an essential tool for Developers and PeopleSoft Database Administrators (DBA). Below are some best practices.

1. Encrypt Sensitive Data

When exporting the data, it is advised to encrypt sensitive information 

2. Avoid Common Mistakes

Developers and DBAs should be mindful of the following common mistakes when preparing and executing the Export and Import DMS scripts:

1. Ensure that all records in the import script match those in the export script. This is one of the most commonly overlooked issues, leading to data loss. If there is a mismatch, you may end up deleting more tables while importing fewer tables. 
2. Verify that the input file name and location are correctly mentioned in the import script.
3. Ensure the correct operating mode is selected when using the DMS script for Export and Import.
4. Use the SET NO COMMIT command when performing bulk operations to improve performance.

Additional Resources

For more detailed examples and commands, refer to the official Oracle PeopleSoft Data Mover website.

This tutorial provides a solid foundational understanding of how to create and execute export and import Data Mover scripts to transfer application data and other PeopleSoft objects between databases. Data Mover is a powerful tool that offers many benefits. By combining it with SQL commands, you can perform advanced data administrative tasks.