Appendix: Converting PeopleSoft Systems to Unicode Databases

This appendix discusses:

Click to jump to parent topicUnderstanding Converting PeopleSoft Systems to Unicode Databases

PeopleSoft can manage data in many languages in a single database by using Unicode. The Unicode Standard, published by The Unicode Consortium, and synchronized with the International Standard ISO 10646, provides a character set that can support all the characters needed to write virtually every business language in use today.

By creating a PeopleSoft database using Unicode, you can maintain application data, reports, user interface components, and other linguistic elements of your PeopleSoft system in as many languages as you want, all in a single database.

Creating a Unicode database will be of benefit to your organization if you plan on maintaining multiple languages in your PeopleSoft system, or if one or more of the languages you are using are non-Western European languages.

Click to jump to parent topicUnderstanding Planning Unicode Conversions

This section describes considerations to take into account when planning to convert to a Unicode database.

Click to jump to top of pageClick to jump to parent topicPlatform Support

Unicode data storage is currently supported on the following PeopleSoft database platforms:

For specific versions or required patches for these database platforms, see the PeopleTools 8.52 Hardware and Software Requirements book for your PeopleTools release.

Click to jump to top of pageClick to jump to parent topicDatabase Sizing

When planning to convert to a Unicode database consider the impact on the physical size of your data.

Depending on the transformation of Unicode that your database platform uses and the mix of languages you plan to use in the PeopleSoft database, you can expect to see the database grow.

Click to jump to top of pageClick to jump to parent topicCoexistence of Unicode and Non-Unicode Databases

PeopleSoft supports the coexistence of Unicode and non-Unicode databases in a single implementation. Should you need to maintain databases in both Unicode and non-Unicode character sets, most PeopleSoft utilities and commands can operate across both databases seamlessly.

Depending on the character set of the non-Unicode database, some character data may be lost when transferring data between databases. For example, if you load data from a Unicode Japanese and English database into a Latin-1 non-Unicode database, the English data will load correctly, but the Japanese characters will be lost and converted into replacement characters such as question marks.

Although it is easy to have Unicode and non-Unicode databases co-exist, you will need to maintain two separate PeopleSoft file server and batch server environments for each database type, because the COBOL code supplied with some PeopleSoft applications differs significantly between Unicode and non-Unicode databases. Therefore, if you will be maintaining Unicode and non-Unicode databases and you require COBOL, you will need to install the PeopleTools and application CDs twice, and answer the question about Unicode during each installation appropriately

Data Mover

DAT files created by Data Mover are always encoded in Unicode. Data Mover can read these Unicode DAT files and load their contents into either Unicode or non-Unicode databases. When creating a new Unicode database, ensure that the Data Mover command SET UNICODE ON is issued before the IMPORT * command.

Upgrade Compare/Copy

Upgrade Compare and Upgrade Copy supports copying objects between Unicode and non-Unicode databases. There are no restrictions on the source and target database character sets.

Cache Files

Cache files on the PeopleSoft application server are always stored in Unicode format. If you rebuild your non-Unicode database as a Unicode database, you can still use the cache files you may have pre-loaded on the application server.

SQR

On database platforms supporting Unicode, SQR always connects to the database using a Unicode character set via the database vendor’s API. Therefore, you can use SQR to copy data to and from Unicode and non-Unicode databases. When writing and reading files with SQR, you can select the character set that should be used to encode the file using the ENCODING parameter to the SQR OPEN command.

SQR may require some settings to be defined in the PSSQR.INI (or PSSQR.UNX) file for SQR to correctly read and write files in Unicode.

Click to jump to parent topicConverting to Unicode on Oracle Databases

This section provides an overview of converting to Unicode on an Oracle database and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding Converting PeopleSoft Systems on Oracle Databases to Unicode

Converting a PeopleSoft system on an Oracle database to Unicode requires creating a new Oracle instance with a Unicode character set. If your current database uses byte semantics (typical for PeopleSoft 8.9 applications or earlier), you must also create new column sizes for PeopleSoft columns created as VARCHAR2.

Note. The AL32UTF8 character set is the preferred character set for the Oracle database. However, the UTF8 character set is also supported.

Be sure to specify CHARACTER SET AL32UTF8 (or CHARACTER SET UTF8) at the end of the CREATE DATABASE command.

For more information on CREATE DATABASE syntax information:

See Oracle Database SQL Language Reference SQL Statements: CREATE CLUSTER to CREATE JAVA CREATE DATABASE

For small databases, the simplest way of converting a PeopleSoft database on Oracle to Unicode is to export the entire database using PeopleSoft Data Mover, create a new Oracle instance using a Unicode character set, and re-import the entire database using PeopleSoft Data Mover. The details of this process are not provided in this appendix.

For larger databases, a better process is to use PeopleSoft Data Mover to pre-build the structure of the PeopleSoft tables and then use Oracle database utilities to move the bulk of the data between the old and new Oracle instances. The details of these processes are provided in this appendix.

If you use this process, you have two different sets of Oracle utilities you can use to export and import the database content:

Instructions for each utility are documented in this appendix.

Click to jump to top of pageClick to jump to parent topicExporting PeopleSoft Table Structures Using PeopleSoft Data Mover

This section describes how to use PeopleSoft Data Mover to export the PeopleSoft database structure to a DAT file.

Note. This process exports the database table structure only and does not include the database contents.

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

SET NO TRACE;
SET OUTPUT ​output_file.dat;
SET NO DATA;
EXPORT *;

The DAT file created by PeopleSoft Data Mover will contain only the structure of your PeopleSoft tables, views and indexes,not the data.

Save the file created by this process. It will be referred to as the structural export in this appendix.

Click to jump to top of pageClick to jump to parent topicExporting Database Contents

This section provides an overview of exporting database contents and discusses how to:

Understanding Exporting Database Contents

You have two different sets of Oracle utilities you can use to export and import the database content:

Important! Ensure that you follow only the instructions for the pair of utilities that you have chosen to use.

Setting the NLS_LANG Variable

Before running the export process, verify that the character set specified by NLS_LANG is set to match the character set of the database. NLS_LANG is an environment variable on Unix and a registry setting under HKEY_LOCAL_MACHINE\Software\Oracle on Microsoft Windows.

To verify the current character set of the Oracle database, sign in the Oracle SQL utility of your choice as the database administrator. Then, execute the following SQL statement:

SELECT VALUE FROM SYS.V_$NLS_PARAMETERS
WHERE PARAMETER ='NLS_CHARACTERSET';

For example, if the database character set is WE8ISO8859P1, NLS_LANG should be set to AMERICAN_AMERICA.WE8ISO8859P1, or another language combination with WE8ISO8859P1 as the character set.

Creating and Running the Export Preparation Script for Oracle Data Pump

Create a directory for the export file such as c:\temp\dmpdir

In a command prompt, set your Oracle SID 

set ORACLE_SID = <SID>

In SQL*Plus run three commands to specify your directory and grant permissions to the username you will use to run the export.

Sign into the Oracle SQL utility of your choice as the database administrator: 

sqlplus / as sysdba

  1. Create directory dmpdir as 'your_directory';

  2. Grant read,write on directory dmpdir to username;

  3. Grant EXP_FULL_DATABASE to username;

Check for invalid objects with this command

select object_name from dba_objects where status='INVALID';

If any rows are returned, you may be able to fix them by running dbmsdb.sql and/or utlrp.sql. There should be no invalid rows before proceeding.

Quit out of sql plus.

The data export will specify two schemas: PS,and the ownerid specified in the PSSTATUS.OWNERID table.On the command line,run the expdp command:

expdp username/password@SID directory=dmpdir dumpfile=filename 
schemas=OWNERID,ps

For instance:

 expdp system/manager@HR directory=dmpdir dumpfile=exp.dmp schemas=SYSADM,ps

At the end of the export,look for the final line such as :”Job 'OWNERID'.'SYS_EXPORT_SCHEMA_01' successfully completed.

In case of errors, you can rerun and add the parameter trace =1FF0F00, to generate trace files.

Note. Ensure that the file system on which you will be creating the export file has sufficient space available to accommodate all the data in the PeopleSoft database. A good estimate of the space needed is to calculate the amount of space currently taken by tables owned by the PeopleSoft owner ID and adding 20 percent. You can get this data from querying the DBA_FREE_SPACE and DBA_DATA_FILES catalog views. Remember that index data is not stored in export files, only the index definition. If you have insufficient space on any one file system to store the export file, you can use the filesize parameter of the Oracle Export utility. See your Oracle utilities guide for more information on multi-file exports.

Exporting Database Contents Using the Oracle Export Utility

Exporting Database Contents Using the Oracle Export Utility:

exp username/password@SID buffer=100000 file=outputfile.dmp rows=Y 
indexes=Y log=logfile.txt compress=Y owner=ownerid

For instance:

exp system/manager@HR buffer=100000 file=outputfile.dmp rows=Y indexes=Y 
log=logfile.txt compress=Y owner=SYSADM

The export (.dmp) file created by this process will be referred to as the database export in this document.

Note. Ensure that the file system on which you will be creating the export file has sufficient space available to accommodate all the data in the PeopleSoft database. A good estimate of the space needed is to calculate the amount of space currently taken by tables owned by the PeopleSoft owner ID and adding 20 percent. You can get this data from querying the DBA_FREE_SPACE and DBA_DATA_FILES catalog views. Remember that index data is not stored in export files, only the index definition. If you have insufficient space on any one file system to store the export file, you can use the filesize parameter of the Oracle Export utility. See your Oracle utilities guide for more information on multi-file exports.

Click to jump to top of pageClick to jump to parent topicCreating a New Oracle Database Instance

This section provides an overview of creating a new Oracle database instance and discusses how to:

Creating the new database instance involves creating the directories to hold the database, the init <SID>.ora file, and the tnsnames.ora file. On windows,the oradim command is used to start the instance such as. 

oradim -new sid HR -intpwd manager -startmode auto -pfile 
C:\HR\initHR.ora

Sign into the Oracle SQL utility of your choice using the database administrator ID and password., run the startup nomount command such as this:

startup nomount pfile=c:\HR\initHR.ora

The first four are run as sysdba (for instance,sqlplus / as sysdba), and the others are run as the system user ( for instance, sqlplus system / manager).

Some of these scripts must be edited to contain the information specific to your database . Check the sizes of the DBF files in the xxDDL.sql script and ensure they will be large enough for the new database.

Understanding the New Oracle Database Instance

First, you must create a new Oracle instance with a Unicode encoding. This database will become the new PeopleSoft Unicode database.

This database must contain the same tablespaces as the original database, if one or more objects owned by the PeopleSoft owner ID were in those tablespaces.

For example, if the original database had 10 tablespaces, and PeopleSoft used five, you must create those five tablespaces with the same names (but not necessarily the same file paths) in the new Unicode instance.

Important! The AL32UTF8 character set is the preferred Unicode character set for the Oracle database.

When you create the new Oracle instance, be sure to specify CHARACTER SET AL32UTF8 (or CHARACTER SET UTF8) at the end of the CREATE DATABASE command.

For More Information on CREATE DATABASE sql command:

See Oracle Database SQL Language Reference SQL Statements: CREATE CLUSTER to CREATE JAVA CREATE DATABASE

Pre-Creating Objects in the New Oracle Database Instance

You must pre-create the following objects in the new Oracle database instance:

You must set the NLS_LENGTH_SEMANTICS=CHAR variable in your init.ora file after running connect.sql, and restart the database.. The installation guide provides information on the use of these scripts and on setting this and other variables.

See PeopleTools 8.52 Installation for Oracle.

Adding NLS_LENGTH_SEMANTICS=CHAR

For Apps 8.9 or later, make sure that NLS_LENGTH_SEMANTICS=CHAR is added to the init.ora file after running CONNECT.SQL and before running Data Mover.

Add to init.ora file if not there already: NLS_LENGTH_SEMANTICS=CHAR, and then restart the database. Check with this SQL: 

select * from nls_database_parameters where parameter in 
('NLS_LENGTH_SEMANTICS','NLS_CHARACTERSET');

Assuming Apps 8.9 or later, the character set should be UTF8 or AL32UTF8, and the length semantics should be CHAR.

To create a new oracle database instance:

Open Command Prompt and set the ORACLE_SID

set ORACLE_SID=HR

If on Windows, create the new service using oradim, for instance for a database named HR, and a system password of manager:

oradim -new -sid HR -intpwd manager -startmode auto -pfile C:\HR\initHR.ora

Sign into the Oracle SQL utility of your choice using the database administrator ID and password.

Open each sql file from PS_HOME\scripts\nt folder and edit the file to add necessary parameters and execute the sql in sqlplus.

The scripts are located in the PS_HOME\scripts\nt directory.

Script

Location

CREATEDB10.SQL

PS_HOME\scripts\nt\createdb10.sql;

UTLSPACE.SQL

PS_HOME\scripts\nt\utlspace.sql;

DBOWNER.SQL

PS_HOME\scripts\nt\dbowner.sql;

xxDDL.SQL

Tablespace size defaults may need to be increased in the script.

PS_HOME\scripts\nt\ptddl.sql;

xxDDL.SQL (for app example HCDDL.SQL) – Tablespace size defaults may need to be increased in the script.

PS_HOME\scripts\nt\hcddl.sql;

PSROLES.SQL

PS_HOME\scripts\nt\psroles.sql;

PSADMIN.SQL

PS_HOME\scripts\nt\psadmin.sql;

Examples at prompts: SYSADM, SYSADM, PSDEFAULT

CONNECT.SQL

PS_HOME\scripts\nt\connect.sql;

After running connect.sql, edit your init.ora file and add the parameter NLS_LENGTH_SEMANTICS=CHAR and restart the database.

For more information on scripts:

See PeopleTools 8.52 Installation for Oracle

Click to jump to top of pageClick to jump to parent topicPre-Creating the PeopleSoft Table Structures Using PeopleSoft Data Mover

Sign on to the newly created Oracle database with PeopleSoft Data Mover in bootstrap mode using the database administrator ID and password.

Once signed in, execute the following Data Mover script, substituting output_file with the name of the structural export file you created in PeopleSoft Data Mover previously: 

SET NO TRACE;
SET NO DATA;
SET INPUT ​output_file;
SET LOG ​log_file;
SET UNICODE ON;
SET STATISTICS OFF;
SET ENABLED_DATATYPE 9.0;
IMPORT *;

This script creates all the tables in the original PeopleSoft database in the new database but with no rows of data. You can speed up the data load time by adding the commandSET NO INDEX; If you do that, remember to create them in Application Designer later.

Note. The ENABLED_DATATYPE 9.0 command applies only if the PeopleSoft application version is 9.0 or higher. With this flag set, the VARCHAR2 columns will use character semantics instead of byte semantics..The ENABLED_DATATYPE command is related to the NLS_CHARACTER_SEMANTICS setting in the init.ora file. The NLS_CHARACTER_SEMANTICS is not set at the beginning of database creation, but is added to the init.ora file before connecting to Data Mover.

To check the effect of these parameters after the structural import, in sqlplus run the command desc PSSTATUSand verify that the CHAR fields are defined as VARCHAR2(n CHAR) rather than VARCHAR2 (nx3).

Click to jump to top of pageClick to jump to parent topicImporting Database Contents

This section provides an overview of importing database contents and discusses how to:

Understanding Importing Database Contents Using the Oracle Data Pump Utility

Once the PeopleSoft tables have been pre-created by PeopleSoft Data Mover, you are ready to import the database export you created.

Important! Ensure that you follow only the instructions for the pair of utilities that you have chosen to use.

Clearing the NLS_LANG Variable

Before running the Oracle import process, clear the NLS_LANG environment variable (Unix) or remove the NLS_LANG registry setting (Microsoft Windows). Doing so ensures that the Oracle import process reads the character set information from the export file and performs the appropriate conversion to the database’s selected Unicode character set.

Importing Database Contents Using Oracle Data Pump

In a command prompt, set your new Oracle SID

Set ORACLE_SID=<SID>

Sign into the Oracle SQL utility of your choice using the database administrator ID and password. Run three commands to specify your directory and grant permissions to the username you will use to run the import.

 sqlplus / as sysdba

  1. Ceate directory dmpdir as 'your_directory';

  2. Grant read,write on directory dmpdir to username;

  3. Grant IMP_FULL_DATABASE to username;

  4. Quit SQLPlus.

On the command line, run the impdp command.

impdp username/password@SID directory=dmpdir dumpfile=filename schemas=OWNERID,ps content=data_only For instance:

impdp system/manager@HR directory=dmpdir dumpfile=exp.dmp ⇒
schemas=SYSADM,ps content=data_only

Look for: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed.

In case of errors, you can rerun and add the parameter trace=1FF0F00, to generate trace files. One common error is running out of space in a tablespace, due to setting it too small in the xxDDL.SQL scripts. To fix this, use a command like the following: 

alter database datafile 'filename.DBF'resize new_size;

Ensure that the import completes successfully with no errors before continuing.

If importing using imp, run the following 

imp ownerid/password@SID buffer=100000 file=database_export_file.dmp⇒
 rows=Y indexes=Y log=logfile.txt ignore=Y full=Y

For instance, 

imp system/manager@HR buffer=100000 file=exp.dmp rows=Y indexes=Y⇒
 log=logfile.txt ignore=Y full=Y

Ensure that the import completes successfully with no errors before continuing.

Rebuilding PeopleSoft Temporary Tables

After you import the database, you must rebuild the temporary tables. To rebuild the temporary tables, sign into PeopleSoft Data Mover in user mode (non-bootstrap mode), and run the following command: 

CREATE_TEMP_TABLE *

Building Indexes

If you included the SET INDEXES OFF line of the Data Mover import, you need to build the indexes. Log into Application Designer and create a project called ALLRECORDS, and insert all record definitions into the project. Build the project to build the indexes defined for the records.

Click to jump to top of pageClick to jump to parent topicSpecifying the Unicode Database and Data Types in Your PeopleSoft System

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=2;

Click to jump to top of pageClick to jump to parent topicRunning GRANT.SQL

Before signing into the new PeopleSoft Unicode database, edit the GRANT.SQLscript (provided in your PeopleTools installation in the scripts directory) to include the connect ID you created previously. Then, run GRANT.SQL to set up the appropriate grants to the connect ID.

Sign into the Oracle SQL utility of your choice using the database administrator ID and password. Run the GRANT.SQL script.

You should now be able to configure your connection with Configuration Manager, and log in with Application Designer. It is recommended to check the database integrity by running the SQR audits DDDAUDIT and SYSAUDIT.

Click to jump to top of pageClick to jump to parent topicRerunning an Oracle Database Import

When the PSSTATUS table is imported from a non-Unicode export file, the UNICODE_ENABLED flag is 0.

Therefore, before re-running any PeopleSoft Data Mover imports, or any time PSSTATUS is re-imported from a non-Unicode export file, set the UNICODE_ENABLED flag to 1 and set the DATABASE_OPTIONS to 2.

Click to jump to parent topicRerunning Microsoft SQL Server and DB2/UDB Database Imports

When the PSSTATUS table is imported from a non-Unicode export file, the UNICODE_ENABLED flag is set to 0. Before re-running any PeopleSoft Data Mover imports, or any time PSSTATUS is re-imported for a non-Unicode export file, set the UNICODE_ENABLED flag to 1.

Click to jump to parent topicFinal Database Cleanup

After importing your data, run the Final Database Cleanup section from dbsetup.dms, generated by the Database Creation Wizard. Comment out the IMPORT line of the DMS script, since you have already imported all table structures and data. Run run the security steps and later lines from dbsetup.dms. Besides the security steps, the dbsetup.dms

script may contain these lines:

ENCRYPT_PASSWORD *;
                
CREATE_TRIGGER *;
                
REPLACE_VIEW *;
                
CREATE_TEMP_TABLE *;

The tables referenced by these lines are the ones in the structural export files, or full datamover export file, depending on which one you originally exported.

You should now be able to configure your connection with Configuration Manager, and log in with Application Designer. It is recommended to check the database integrity by running the SQR audits DDDAUDIT and SYSAUDIT.

Click to jump to parent topicConverting to Unicode on Microsoft SQL Server and DB2/UDB Databases

This section discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding Converting to Unicode on Microsoft SQL Server and DB2/UDB Databases

On Microsoft SQL Server and DB2/UDB 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.

Click to jump to top of pageClick to jump to parent topicExporting PeopleSoft Databases Using PeopleSoft Data Mover

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.

Click to jump to top of pageClick to jump to parent topicCreating New SQL Server or DB2/UDB PTSYS Unicode Databases

You will need to create a new SQL Server or DB2/UDB PTSYS 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 PeopleTools 8.52 Installation for Microsoft SQL Server, “Creating a Database Manually.”

See PeopleTools 8.52 Installation for DB2 UDB for Linux, UNIX, and Windows, “Creating a Database Manually.”

Click to jump to top of pageClick to jump to parent topicImporting Database Contents Using PeopleSoft Data Mover

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;
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.

Click to jump to top of pageClick to jump to parent topicSpecifying Unicode Databases in PeopleSoft Systems

Once the database has finished importing, run the following SQL statement either through PeopleSoft Data Mover, PeopleSoft Query Analyzer (ISQLW.EXE), or db2connect.

UPDATE PSSTATUS SET UNICODE_ENABLED=1

This sets a flag that specifies to PeopleSoft that it is operating against a Unicode database.

Click to jump to top of pageClick to jump to parent topicRerunning GRANT.SQL

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.