Converting to Unicode on Microsoft SQL Server and DB2 Databases

On Microsoft SQL Server and DB2 databases, PeopleSoft supports converting a PeopleSoft database to Unicode by re-creating the database using Data Mover to import database contents. For the best performance, run Data Mover on the same machine as the database server.

PeopleSoft requires that an entire database be created as a Unicode database or as a non-Unicode database – mixing CHAR and NVARCHAR columns in a single PeopleSoft database is not supported.

Sign into the PeopleSoft database using PeopleSoft Data Mover in non-bootstrap mode, and run the following script.

SET NO TRACE;
SET OUTPUT outputfile; 
EXPORT *;

The DAT file created by PeopleSoft Data Mover will contain all the data in the PeopleSoft database, so be sure that the volume to which it will be written has sufficient disk space to handle and store the exported data.

This process will create a file containing the structure and contents of your PeopleSoft database.

You will need to create a new SQL Server or DB2 database corresponding to your target PeopleSoft Unicode database. On SQL Server this database can co-exist on the same server as your original non-Unicode database, as SQL Server allows Unicode and non-Unicode databases to reside on a single server, regardless of the character set selected during SQL Server installation.

To create this database, follow the steps in the appendix “Creating a Database Manually” in the PeopleTools installation guide, from the beginning of the appendix. Stop at the step titled “Creating Data Mover Import Scripts” and continue with the steps below. You can also create a new database using the Database Configuration Wizard, but do not allow the wizard to start importing data.

See the product documentation for PeopleSoft 9.2 Application Installation for Microsoft SQL Server, “Creating a Database Manually.”

See the product documentation for PeopleSoft 9.2 Application Installation for DB2 for z/OS, “Creating a Database Manually.”

Run PeopleSoft Data Mover in bootstrap mode against the new database. Login in bootstrap mode using the OwnerID and password. Once signed in, run the following script:

SET NO TRACE;
SET INPUT inputfile;
SET UNICODE ON;
SET ENABLED_DATATYPE 9.0;
IMPORT *;

This will import the entire contents of your original database into the new database, while creating character columns using the NVARCHAR data type (for SQL Server) or VARBINARY data type (for DB2), and converting your data to Unicode.

When the import has completed successfully, you must specify in your PeopleSoft system that the database is now a Unicode database. To do this, sign into the Oracle SQL utility of your choice using the database owner ID. Run the following SQL statement:

UPDATE PSSTATUS SET UNICODE_ENABLED=1

Since you set ENABLED_DATATYPE previously, you also need to specify that the database uses Oracle CLOB and BLOB fields, as follows:

UPDATE PSSTATUS SET DATABASE_OPTIONS=34;

Before signing onto Application Designer to create your views, you must first re-run GRANT.SQL (provided in your PeopleTools installation in the SCRIPTS directory) to grant access to the newly created tables to your connect ID. Once this is completed, you should be able to sign into Application Designer.