Data Mover

Data Mover Directories

Data mover directories play an important role when you installing PeopleSoft. They are configured through Configuration Manager (pscfg.exe) and live in the profile settings.

The full path to them is through:

Configuration Manager > Profile tab > Edit [relevant profile, e.g. Default] > Common tab

Here's a screenshot:

datamover-directories-setup.png

Data Mover Encrypt Password JVM error

As of PeopleTools 8.53 you might get the following error when running ENCRYPT_PASSWORD *; in data mover:

Error: Process aborted. Possibly due to JVM is not available or missing java class or empty password.

To fix you need to add %PS_HOME%\jre\bin\ to Windows your path as Java is now used as part of the encryption process.

Control Panel > System > Advanced > Environment Variables
(or search settings for Edit the System Environment Variables).

Scroll through System Variables and find PATH, and append the location of %PS_HOME%\jre\bin\ to the end e.g.

windows-system-path.png

Note once you do this, you need to close and re-open data mover for it pick up the new path.

Change Access Password

Data mover includes a command called CHANGE_ACCESS_PASSWORD. The purpose of this command is to reset the database password in the PSACCESSPRFL record for the database system user that typically owns the PeopleSoft schema (e.g. SYSADM for Oracle or sa for Microsoft SQL Server). Effectively, it is a way of synchronising a change in the database system user password for these accounts with PeopleSoft.

A typically use for this command might be the scenario of refreshing databases. The SYSADM or sa account in production will likely have a different password to a test PeopleSoft environment for security reasons. In order to ensure that the refresh works correctly, part of the refresh process includes issuing the CHANGE_ACCESS_PASSWORD command to update the PSACCESSPRFL record accordingly.

The syntax for the command is:

CHANGE_ACCESS_PASSWORD [SymbolicID] [Password]

The SymbolicID is the account used by PeopleSoft to link users (operators) to the system account which is stored as ACCESSID in the PSACCESSPRFL record and encrypted.

Update User Password

This is a very simple Data mover script to update a user's password in PeopleSoft (PSOPRDEFN) and re-encrypt it. Useful if you can't access the PIA (e.g. during installation) but need to update user passwords.

Replace new_password with the new password and YOUR_USER_OPRID with your user's operator ID.

-- Change a user's password and re-encrypt it.
-- PeopleSoft Wiki: https://www.peoplesoftwiki.com
 
update PSOPRDEFN
set OPERPSWD = 'new_password', ENCRYPTED = 0 
where OPRID = 'YOUR_USER_OPRID';
 
encrypt_password YOUR_USER_OPRID;

If you do not have access to another account to run data mover script. You can use the following method described below.

Logon to a different PeopleSoft database where you can login thru PIA and run the following SQL.

select OPERPSWD
from PSOPRDEFN
where OPRID = 'YOUR_USER_OPRID';

Copy the value in OPERPSWD. Logon to the database where your PIA logon is not working and run the following SQL.

update PSOPRDEFN
set OPERPSWD = 'new_password',
LASTPSWDCHANGE = '1-jan-2099',
ACCTLOCK = 0
where EMPLID = 'YOUR_USER_EMPLID'
OR OPRID = 'YOUR_USER_OPRID';

The password is reset as the same password that works in the other environment.

Data Mover Script Templates

Data mover is probably the best way to export and import data between PeopleSoft databases and the scripts are very easy to write. This wiki page provides template export and import scripts to save you having to search around for the last one you wrote!

Some important points:

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;

Bootstrap Mode

Bootstrap mode uses a database user to login to the database. In this mode Datamover can perform many system adminstration related commands as well and some steps in an installation or upgrade require Bootstrap mode to work correctly.

If you get the message:

Invalid Access ID and password for signon -- see your security administrator

When logging into datamover in bootstrap mode (e.g. as sysadm or sa), then it indicates a connectivity problem with your Oracle client or ODBC connection.

If you are installing PeopleTools 8.5x on a 64 bit operating system and using ODBC (Microsoft SQL Server) there is an additional catch. You need to set up the System DSN from:

If you don't set up the System DSN to your database for SQL server in the 32bit ODBC setup, you will get the error. Hard to catch as when you look at the ODBC settings you will see the 64 bit version of the application and everything looks fine!