PeopleSoft Data Mover

PeopleSoft Data Mover, as the name suggests, is a PeopleTools utility that is used to export/import data between databases.

Apart from this, Data Mover can be used for a variety of functions like database security management, running SQL scripts etc.

On Windows machines, you can use the Data Mover using the GUI as well as from the command prompt while UNIX variants support only command-line operation.

Here DB1 is your source database and DB2 is your target database.

Data Mover Command Line

To invoke the Data Mover from the command line, use the below command. All parameters in square brackets are optional.

psdmtx -CT dbtype [-CS server] -CD database_name -CO user_ID -CP user_password [-CI connect_ID -CW connect_password] [-I process_instance] -FP dms_filepath

Regular vs Bootstrap

Data Mover can operate in two different modes – Regular mode and Bootstrap mode.
To sign into Data Mover in the Regular mode, use your PeopleSoft userID and password.
This is the mode that you will be using most of the time. The operating mode (on the status bar) would be blank in this case.

To get the Data Mover working in Bootstrap mode, use the Database Access ID and password.
The status bar would display the operating mode as “Bootstrap” when you are using this mode.
Bootstrap mode is mainly used to load databases as at that point there would be no PeopleSoft security tables.

Input / Output and Log

While using a Data Mover, it is a standard practice to set a log file and an input/output file.
Export scripts use the output file and Import scripts use input files. Log files are used by both.
We use the following commands to set input/output and log files.

SET OUTPUT C:\DATA_FOLDER\DATA_FILE1.DAT;
SET LOG C:\LOG\EXPORT_LOG1.LOG;

SET INPUT C:\DATA_FOLDER\DATA_FILE1.DAT;
SET LOG C:\LOG\IMPORT_LOG1.LOG;

If you do not specify the OUTPUT file, then the Date Mover writes the output into a file named DATAMOVE.DAT.

Similarly, in the absence of a log file specification, the log is written to DATAMOVE.LOG.

However, in the absence of SET INPUT, Data Mover throws an error on encountering an import statement and stops operation.

Common Commands

The commonly used Data Mover commands are:

EXPORT – Used to export data out of the database. A file is specified and the data gets loaded into it.
Later this data can be used for importing into the same/different databases.

IMPORT – This command is used to load data from a file into the database.
This command also takes care of the table spacing.

ENCRYPT_PASSWORD – This command can be used to get all or any user passwords encrypted.

RUN – If you follow a structured approach to DMS and have different script files for different modules, this command would help you invoke all those scripts from a master script file.

Comments

There are three ways in which you can specify a comment in a DMS file.

REMARK
REM
--

REM and REMARK comments all statements till it finds a delimiter. -- is a single line comment.

REM This can comment everything
 till it finds a 
 delimiter;
 
REMARK This is no different from the above one;
 
-- However, for this to work
-- we need to specify it on each line.

Data Mover Script Templates

Here are two Data Mover Script Templates that you can download and use for your projects.
These will serve as a starting point for your DMS scripting.

Export DMS Template
Import DMS Template