Tip 019: Data Mover Scripts - BareFoot PeopleSoft

Data Mover | Transferring table data between environments

A. What is Data Mover?

Data Mover is one of the standard utility programs that come shipped with PeopleTools. If you perform a standard PeopleTools installation on your PC – having ticked the appropriate setting – you should find the Data Mover program installed along with App Designer and Configuration Manager.

Data Mover is designed to shift large quantities of data, typically entire tables, from one database to another. Data Mover can also be used to back-up the contents of an existing table, with a view to possibly restoring that table in the future should the need arise (for example, if you’re about to do an ‘Alter Table’ in App Designer on an especially crucial table). In all cases, the table data is saved in a file with a ‘.DAT’ extension.

Data Mover is commonly used by system administrators when running automated scripts for upgrade purposes. Data Mover can also be a handy tool for developers who want to quickly copy the contents of a table from one database to another. Selectively copying a large quantity of records to/from the Message Catalog is one common task.

B. Data Mover Scripts (DMS)

Data Mover scripts generally come in pairs – one script performs the export function, while the other performs the import.

Firstly, here is an example of a basic export script:

--
-- Copying all messages out
--
SET LOG c:\TEMP\MESSAGES_OUT.LOG;
SET OUTPUT c:\TEMP\MESSAGES.DAT;

EXPORT PSMSGCATDEFN;

And secondly, here is an example of an import script:

--
-- Copying all messages in
--

SET LOG c:\TEMP\MESSAGES_IN.LOG;
SET INPUT c:\TEMP\MESSAGES.DAT;

IMPORT PSMSGCATDEFN;

C. Import Choices

There are three commands available to import the contents of the DAT file back into a database.

‘IMPORT’ is the least destructive of the various commands available to copy back data. ‘IMPORT’ will only insert records if the key combination does not already exist in the target database. If the key combination does exist, then no update will be performed. So in other words, if there is a conflict between the DAT file and the database, the database version will be retained.

As an alternative to ‘IMPORT’, ‘REPLACE_DATA’ will remove any duplicate data from the target database first before re-inserting from the DAT file. In this case, if there is a conflict between the DAT file and the database, it is the DAT file that will win out.

Finally in terms of importing table, ‘REPLACE_ALL’ is the most powerful command of them all. It will completely drop the entire target table first, before re-creating the table from scratch and reloading all the contents from the DAT file. Therefore, ‘REPLACE_ALL’ should be used with extreme caution (and not at all if you’re in any doubt).

D. Selective Copying

As mentioned earlier, Data Mover is typically used with full table copies. However, it also provides you with an SQL-like way to restrict the data that is copied:

EXPORT PSMSGCATDEFN
WHERE MESSAGE_SET_NBR >= '30000';

In terms of whether to use ‘IMPORT’ or ‘REPLACE_DATA’ for the second part of the DMS copy, this will depend on the circumstances of the copy. If you’d just like to add missing messages to the target table, without having an impact on any existing messages, then ‘IMPORT’ would be more appropriate. If however, you’d like to both insert and update messages into the target, then ‘REPLACE_DATA’ would be the better option. You definitely want to steer well clear of ‘REPLACE_ALL’. This command would cause every single message to firstly be wiped, before re-inserting only the messages from set 30000 onwards. All messages with a set number less than 30000 would be lost for good.

<< Previous | Next >>