Transferring Users Between Databases

Sometimes you might want to transfer all user information from a source database to a target database, for example, during the upgrade process or when moving users from the production environment to a development or a testing environment. PeopleSoft applications provide Data Mover (DMS) scripts that export and import user profile security information. These scripts transfer user profile data from a source to a target database. The scripts use these tables:

  • PSOPRDEFN

  • PSOPRALIAS

  • PSROLEUSER

  • PSUSERATTR

  • PSUSEREMAIL

  • PSUSERPRSNLOPTN

  • ROLEXLATOPR

  • PS_RTE_CNTL_RUSER

Note: Use the Application Designer upgrade feature to upgrade both roles and permission lists.

One script exports User Profile data from the source database. The source database refers to the database that contains the User Profiles that you want to migrate. The target database refers to the database to which you are copying the user information.

After exporting the security information from the source database, you then run the import script against the target database. The target database refers to the database to which you want to transfer the security data. The scripts involved in transferring security information from one database to another are:

  • USEREXPORT.DMS.

    This script exports User Profiles from the source database and stores them in a Data Mover DAT file. The output file is named USEREXPORT.DAT.

  • USERIMPORT.DMS.

    This script reads the file created by USEREXPORT.DMS and copies the User Profile data into the target database.

You will find this set of scripts in the <PS_HOME>/scripts directory.

Note: Using Data Mover to transfer user profiles from one database to another does not trigger user profile synchronization.

Considerations

Before running scripts to export and import your security information, you should consider these topics:

  • Duplicate Rows

    If the target database already contains a row of data with identical keys to a row transferred by the import script, the duplicate row will not be transferred to the target. The scripts make no attempt to merge the duplicate row; the row is not transferred.

    To ensure that you do not have data rows with duplicate keys, you must ensure that the source database does not contain a User Profile with the same name as in the target database.

    You should not have data rows with duplicate keys in your source and target database when you begin the copy, as this can lead to unexpected results that compromise database integrity.

  • Release Levels

    Because the PeopleTools table structures change between major releases (8.59 to 8.60,for example), you cannot transfer users between databases that run different versions of PeopleTools. Before starting the migration process, upgrade your source and target databases so the release levels match.

Running the Scripts

Complete the following procedure to run the user transfer scripts.

  1. Using Data Mover, sign in to the source database and run USEREXPORT.DMS for user definitions.

    You can edit this script to specify the location and file name of the output file and the log file.

  2. Using Data Mover, sign in to the target database and run USERIMPORT.DMS for user definitions.

    You can edit the script to specify the location and file name of the input file and the log file. The name and location of the input file must match the output file you specified in Step 1.

  3. After copying user and role definitions, run the PeopleTools audits, including DDDAUDIT and SYSAUDIT, to check the consistency of your database.