Data Mover Basic

Here I will brief How we can use Data Mover to Move Data from one database to another :
 Data Mover can be used

Transfer application data between PeopleSoft databases.

Move PeopleSoft databases across operating systems and database platforms.

Execute Structured Query Language (SQL) statements against any PeopleSoft database.

There are two mode in which we can run DMS: 

Regular mode. Most of the time, you use regular mode. To sign in to regular mode, enter your PeopleSoft user ID and password during sign-in. In regular mode, all commands are valid.

PeopleSoft Data Mover supports the following standard SQL commands:

• ALTER
• COMMIT
• CREATE
• DELETE
• DROP
• INSERT (cannot be used with SET NO COMMIT or SET NO TRACE.)
• ROLLBACK

For example you can run sql "delete from ps_table_name where field=value;"

 Select sql is not allowed.

Bootstrap mode. In bootstrap mode, you use a database access ID and password when signing in. Typically, you use bootstrap mode for database loading, because no PeopleSoft security tables are established yet. 

To Move data  first we will move the data to a Dat file.(Export operation)

After that we read data from that file to record (import operations)

Basic DM Commands .

Export: Select record information and data from records and store the result set in a file. You can use the generated export file as input for migrating to another platform. (file should be .dat type)

Import: Insert data into tables using the information in an export file. If a tablespace or table does not exist, this command creates tablespace, table, and indexes for the record, using the information in the export file, and inserts the data.

REM, REMARK:Indicate comment statements.

Rename:Rename a PeopleSoft record, a field in one record, or a field in all records.

REPLACE_ALL:This is a variation of the IMPORT command. If a table already exists, use this command to drop the table and its indexes from the database. It then does the following:

1. creates the table.
2. creates any triggers.
3. inserts the data.(from export file)
4. creates indexes.

REPLACE_DATA:

 This is a variation of the IMPORT command. Delete data in existing tables and insert the corresponding data from the export file.No need to run Import statment.

SET NO RECORD   :Prevents records from being created during an import.

Example:

On Source Data Mover:

set output  c:\path\file.dat;

set log c:\path\file.log;

export ps_test_tbl  where field=value;

It will create a .dat & .log file .Dat file willbe used as Input on target DataMover.

on target DataMover

set input c:\path\file.dat;

set log c:\path\file.log;

import ps_test_tbl ;
--or you can use
Replace_data  ps_test_tbl ;

This will Read data from input (.dat ) and load it into the table.