PeopleSoft Data Mover, as the name suggests, is a PeopleTool that is used to import / export data between databases. Apart from this, Data Mover can be used for a variety of functions.
A Data Mover operates in two different modes – Regular mode and Bootstrap mode. To sign into the Data Mover in the regular mode, use your PeopleSoft userID and password. To get the Dava Mover in Bootstrap mode, use the Database user ID and password.
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.
****************************************************************
PeopleSoft Data Mover is a stand-alone two-tier program, which you can run through a graphical interface on Microsoft Windows or with a command line interface on either Microsoft Windows or UNIX systems.
PeopleSoft Data Mover enables you to perform the following tasks:
– 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, regardless of the underlying operating system or database platform.
– Export data in a platform independent manner.
– Control database security and access.
– Create, edit, and run scripts which combine SQL commands and PeopleSoft Data
Mover commands for exporting and importing data.
****************************************************************
Export Script
-- REF0001 DD/MM/YYYY Author
-- Brief explanation of what is being exported
set log C:\REF0001_EXPORT.log;
set output C:\REF0001.dat;
export TABLE_NAME;
----------------------------
Import Script
-- REF0001 DD/MM/YYYY Author
-- Brief explanation of what is being imported
set log C:\REF0001_IMPORT.log;
set input C:\REF0001.dat;
import TABLE_NAME;
-----------------------------
Difference between Datamover and Import Manager
The difference between Datamover and Import Manager is that Datamover is used to move data between PeopleSoft environments. Import Manager is used to load data from an external source into PeopleSoft. Datamover cannot be used to import data that was not exported with Datamover.
Import Manager works only with flat files. It works best with small tables. Once you go over about 8,000 rows the utility takes much more time than the alternatives. Import manager also attempts to fire all the PeopleCode refined for the PeopleSoft record that you're loading. The load will fail if any of the code brings back an error. To get around this problem, PeopleSoft suggests that you remark out the code causing the problem. After you finish with the load you can then un-remark the code so that the panels work correctly. As you can see it can become a great deal of work to load the data correctly.
So what are the alternatives?
For small tables that are not dependant on a parent child relationship and for data that needs to be scrubbed while loading, Import Manager is a good choice.
For larger tables that require a one time load, the database utility loaders work great. They vary with the database platform. Oracle uses SQL Loader. Sybase and SQL Server have their own. Your DBA can help you there.
Lastly, for the table that needs to be loaded on a regular basis such as loading timesheet data into the pay records for the Payroll Module, writing a program is usually the best solution. SQR works great for this.
Import manager allows you to specify a flat file format, to do some simple data translation, and to make use of the Peoplecode behind the table, to insure data integrity on import. You can default any field in the table you are importing into, if you don't have that field present in your flat file.
A Data Mover operates in two different modes – Regular mode and Bootstrap mode. To sign into the Data Mover in the regular mode, use your PeopleSoft userID and password. To get the Dava Mover in Bootstrap mode, use the Database user ID and password.
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.
****************************************************************
PeopleSoft Data Mover is a stand-alone two-tier program, which you can run through a graphical interface on Microsoft Windows or with a command line interface on either Microsoft Windows or UNIX systems.
PeopleSoft Data Mover enables you to perform the following tasks:
– 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, regardless of the underlying operating system or database platform.
– Export data in a platform independent manner.
– Control database security and access.
– Create, edit, and run scripts which combine SQL commands and PeopleSoft Data
Mover commands for exporting and importing data.
****************************************************************
-- REF0001 DD/MM/YYYY Author
-- Brief explanation of what is being exported
set log C:\REF0001_EXPORT.log;
set output C:\REF0001.dat;
export TABLE_NAME;
----------------------------
Import Script
-- REF0001 DD/MM/YYYY Author
-- Brief explanation of what is being imported
set log C:\REF0001_IMPORT.log;
set input C:\REF0001.dat;
import TABLE_NAME;
-----------------------------
Difference between Datamover and Import Manager
The difference between Datamover and Import Manager is that Datamover is used to move data between PeopleSoft environments. Import Manager is used to load data from an external source into PeopleSoft. Datamover cannot be used to import data that was not exported with Datamover.
Import Manager works only with flat files. It works best with small tables. Once you go over about 8,000 rows the utility takes much more time than the alternatives. Import manager also attempts to fire all the PeopleCode refined for the PeopleSoft record that you're loading. The load will fail if any of the code brings back an error. To get around this problem, PeopleSoft suggests that you remark out the code causing the problem. After you finish with the load you can then un-remark the code so that the panels work correctly. As you can see it can become a great deal of work to load the data correctly.
So what are the alternatives?
For small tables that are not dependant on a parent child relationship and for data that needs to be scrubbed while loading, Import Manager is a good choice.
For larger tables that require a one time load, the database utility loaders work great. They vary with the database platform. Oracle uses SQL Loader. Sybase and SQL Server have their own. Your DBA can help you there.
Lastly, for the table that needs to be loaded on a regular basis such as loading timesheet data into the pay records for the Payroll Module, writing a program is usually the best solution. SQR works great for this.
Import manager allows you to specify a flat file format, to do some simple data translation, and to make use of the Peoplecode behind the table, to insure data integrity on import. You can default any field in the table you are importing into, if you don't have that field present in your flat file.
