Transferring Users Between Databases

You occasionally need to copy security information from one database to another. Typically, you do this as part of an upgrade or to transfer security information from your production environment to your development or testing environment. PeopleTools provides a set of Data Mover (DMS) scripts designed to export and import user profile security information. The provided scripts transfer user profile data from a source to a target database using these tables:

  • PSOPRDEFN

  • PSOPRALIAS

  • PSROLEUSER

  • PSUSERATTR

  • PSUSEREMAIL

  • PSUSERPRSNLOPTN

  • ROLEXLATOPR

  • PS_RTE_CNTL_RUSER

Note: Use 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 folder.

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, then 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, ensure that a User Profile in the source database does not exist in the target database with the same name.

    You should not have data rows with duplicate keys in your source and target databases when you begin the copy, as unexpected results may occur that will compromise database integrity.

  • Release Levels

    Because the PeopleTools table structures change between major releases (6.X to 7.X or 7.X to 8.X), 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.