PeopleSoft Data Mover, as the name suggests, is a PeopleTool utility that is used to import / export 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.
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 PeoplSoft 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.
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 is 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.
There are three ways in which you can specify a comment in a DMS file.
REMARK REM --
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.
Here are two Templates that you can download and use for your projects. These will serve as starting point for your DMS scripting.