Data Mover fails with Character Length Semantics is not enabled

Oracle 10.2.0.2.0 on Sun Solaris 10 and is installing CRM 9.0 + P 8.48.xx manually. Launched Data Mover in Bootstrap mode, navigated to \File\Database Setup, selected Unicode as Database Type and UTF-8 as Character Set, clicked on Next, selected Demo for the Database Type, added ‘PeopleSoft CRM Database – US English’ then clicked on Next. The following error message appeared then: Character Length Semantics (CLS) is not enabled.

Note: This issue did not occur when using HCM 8.9

The PeopleSoft Unicode implementation for PT 8.48 no longer triples the VARCHAR2 datatype columns. Instead it relies on an Oracle feature called Character Lenght Semantics. This parameter is not needed for non-unicode DBs or for unicode application databases prior to release 9.

This ”Character Length Semantics (CLS) is not enabled” error will occur if Data Mover detects that NLS_LENGTH_SEMANTICS = CHAR” is not set in the init.ora file, if not enabled then Data Mover will fail. CLS should be enabled when running the createdb.sql script. Check out that script and make sure you build your database with the CLS option set to ”NLS_LENGTH_SEMANTICS=CHAR” instead of ”’NLS_LENGTH_SEMANTICS=BYTE” which is the default setting.

Background information on changing Length Semantics:
In single-byte character sets, the number of bytes and the number of characters in a string are typically the same. In multibyte character sets, a character or code unit consists of one or more bytes. Calculating column lengths in bytes is called byte semantics, while measuring column lengths in characters is called character semantics. Oracle9i allows column maximum length constraints to be expressed in character semantics, offering this way an alternative to redefining the byte sizes of columns. Length semantics are useful for both handling and defining the storage requirements for multibyte strings of varying width characters. For example, you are migrating from an 8-bit Western European character set to a Unicode database (AL32UTF8). Suppose that you have existing VARCHAR2 columns that contain characters with diacritic marks. The scan indicates these columns would need to be resized to accommodate the expansion in the migration to a byte semantic multibyte database. Additionally you plan to add Asian data to your system. Using character semantics for your new migrated database may offer the best solution – particularly when the requirements for what type of information will be stored in these columns do not change. By changing a VARCHAR2(10 BYTE) column definition to a VARCHAR2(10 CHAR) definition you ensure that 10 characters will still fit in the column after migration even if they expand to more than 10 bytes of binary codes.

The NLS_LENGTH_SEMANTICS initialization parameter determines whether columns of character datatypes use byte or character semantics when they are created without an explicit qualifier. Byte semantics is the default for the database character set. Character length semantics is the default and the only allowable kind of length semantics for NCHAR datatypes. Length semantics can be explicitly specified in definitions of columns of CHAR datatypes by appending the BYTE or CHAR qualifier to the length value. The user cannot specify the qualifiers for NCHAR definitions. Like with column length adjustments, if the original copy of a database migrated through Export and Import utilities is not used after the migration, altering the length semantics can be done before the export step (but after taking a backup). This allows the Import utility to automatically create tables with correct column lengths and load data without truncation errors in a single run. Otherwise, tables have to be pre-created in the new database.

PeopleTools 8.48 and Unicode Error

Started: Thu Feb 08 17:24:41 2007
Data Mover Release: 8.48.07
Database: CRMDEMO (ENG)
Input file: D:\PT8.48\data\crgera.db (ENG)
Importing PSXLATITEMLANG
SQL error. Stmt #: 0 Error Position: 151 Return: 12899 – ORA-12899: value too large for column “SYSADM”.”PSXLATITEMLANG”.”XLATSHORTNAME” (actual: 11, maximum: 10)
INSERT INTO PSXLATITEMLANG (FIELDNAME, FIELDVALUE, EFFDT, LANGUAGE_CD, XLATLONGNAME, XLATSHORTNAME) VALUES (:1, :2, TO_DATE(:3,’YYYY-MM-DD’), :4, :5, :6)
Error: Unable to insert row 9
Error: SQL execute error for PSXLATITEMLANG
Ended: Thu Feb 08 17:27:08 2007
Unsuccessful completion

The storage of Unicode data has changed for the new Enterprise 9 applications on PeopleTools 8.48. In the pre-9 Unicode Applications the length of the VARCHAR2 field is tripled and a check constraint is added in order to enforce the length of the field defined in Application Designer.
The PeopleSoft Unicode implementation starting with PT 8.48 and Enterprise 9 applications no longer triples the VARCHAR2 datatype columns. Instead it relies on an Oracle feature called Character Length Semantics. This is not needed for non-unicode databases or for pre-release 9 Unicode application databases.

The following parameter has to be set in the init.ora file at the beginning of the installation:
NLS_LENGTH_SEMANTICS=CHAR

Setting the NLS_LENGTH_SEMANTICS = CHAR is a prerequisite that is enforced if loading the demo through the Database Configuration Wizard. If doing a manual creation of the Oracle database, that it’s a pre-requisite.

Here is how the definition of the table PSXLATITEMLANG should look like:
SQL> desc PSXLATITEMLANG;
Name Null? Type
—————————————– ——– ———————
FIELDNAME NOT NULL VARCHAR2(18 CHAR)
FIELDVALUE NOT NULL VARCHAR2(4 CHAR)
EFFDT DATE
LANGUAGE_CD NOT NULL VARCHAR2(3 CHAR)
XLATLONGNAME NOT NULL VARCHAR2(30 CHAR)
XLATSHORTNAME NOT NULL VARCHAR2(10 CHAR)

Trying to Install Demo Database for HRMS 8.9 MP on Redhat Linux

While trying to install Demo data base for HRMS 8.9 MP1 on RedHat Linux. Running Database Configuration Wizard fails with:

Initializing Data Mover … please wait

Error: unable to terminate process

Checking Data Mover log … please wait

ERROR: could not open /apps/psoft/scripts/psdemoora.dms

Validating PeopleTools Version…

——————————————————————————-
Warning!

Setup has detected that the PeopleTools version (8.48) does not match the
version in the installed database. The database may not be created
successfully. Please check the log file(s) located under the log directory for
details.

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

Check the PATH and LD_LIBRARY_PATH environment variables. Manually added the ORACLE_HOME /bin and /lib directories to these paths. psconfig.sh did not do this.

Export/Import Rule and Non-Rule Package using UNIX Process Scheduler

 Export/Import Rule & Non-Rule Package using UNIX Process Scheduler

1. Change the UNIX Process Scheduler configuration (in psprcs.cfg file for UNIX Process Scheduler):
[Data Mover]
InputDir = %PS_HOME%/data (This is an example)
OutputDir= %PS_HOME%/data –> this is where the create dms and dat files are created the default slashes are the wrong way around Process Scheduler > Process Types)

3. Change GP_EXP and GP_IMP Process Definition Server Names to PSUNX
(Peopletools > Process Scheduler > Processes > Process Definition Options)

4. Change the PSUNX Process Scheduler Server Definition – Data Mover needs to be added as a process type in order for Data Mover scripts to run through PSUNX. Scheduler should be bounced after this is done.
(Peopletools > Process Scheduler > Servers)

5. Check the process profile security – the user that runs GP_EXP and GP_IMP needs GLALL and HRALL

When directory is specified while exporting/importing Rule or Non-Rule packages specify the same path mentioned in Process scheduler Config file.

Difference between the creation of Demo and System Databases

A PeopleSoft Demo database contains all the data of the System database (tools and application) and demo data needed for testing or demonstration.

If you create the database manually, without using the Database Configuration Wizard, one of the main steps is to create the Data Mover script to import the system/demo data from the *.db files.

After you log in Data Mover (Bootstrap mode) and open the menu File > Database Setup, here are some things to consider:

1. In the second panel of the wizard, in the Database Type frame, you have to select what kind of application to create:
- choose Demo to have your core application along with demo data
- choose System if you don’t need the demo application data

2. Add the PeopleSoft application you want to create along with all the languages you need, no matter if you create a Demo or a System environment.

3. DO NOT ADD the PeopleTools System Database. This is used only if you want to create from scratch a custom application outside the delivered PeopleSoft applications.

4. When you finish this wizard, Data Mover will create a DMS file that will import data from the PS_HOME\data\*.db files according to your previous selections.

According to the PeopleSoft application, the .db files are named as follows:
hcengs.db, hcengl.db, etc. for HRMS
crengs.db, crengl.db, etc. for CRM
epengs.db, epengl.db, etc. for FSCM
lmengs.db, lmengl.db, etc. for ELM
etc.

Replace further xx with hc, cr, ep, lm, etc. according to your application.

The xxengs.db file will create all the Application and PeopleTools tables, and will import the system data. This file needs to be present in the dms script for both the Demo and System environments.

The xxengl.db file will import Application sample data in existing tables. This file needs to be present only in the script for the Demo environment.

The xxfraa.db, xxitaa.db, xxduta.db, etc. files will import language related data in existing tables. These files are needed for both the Demo and System environments.

The ptengs.db is used ONLY if you want to create from scratch a custom application outside the delivered PeopleSoft applications.

5. The logs of the Data Mover import will be found in the path set in Configuration Manager > Profile > Default > Edit > Common > Log Directory. They are in the format xxengs.log, xxengl.log, xxfraa.log, etc. and can be open to see what data has been imported from every *.db file.

Using Data Mover to move Data from One DB to Another DB

Specific for DB2:

MVPRDEXP/MVPRDIMP.DMS as an example for this discussion.

When you use Data Mover to move data from one database to another, you must specify the database-specific overrides which DataMover requires in order populate the objects (Tables) in the database, and also to create any new objects that are not present in the database.

If the overrides are not present then Data Mover will obtain the relevant details from the .DB or .DAT import file contains the details which relate to the database from which the data was exported — not a good idea when you are trying to import into a different database with different DBNAMES and Owner information.

At this point you need to provide certain override information for Datamover which is specific to your Database.
A situation analogous to this is when you setup your Demo Database from a .DB file which PeopleSoft provides. This DB file may have and probably was exported from a MS SQL Server Database with its own characteristics. That is why we provided the DBSETUP.EXE for you to run and enter all the relevant details for your site. (Refer Chapter 5 of the Install and Admin Guide – Setting up Database). You are instructed to enter the details and then click the “Create Scipt” button, which creates the HCDMODBO.DMS script in your file server PS_HOME\Scripts directory.

If you look at this script you will see several SET DDL override statements which are specific to your environment. These same overrides must be cut and pasted (and customised to suit the database into which you are importing) into your MVPRDIMP.DMS script. See below for an example — this should get you through any problems with invalid database names for objects which require to be created.

Example : HRMS Demo Database Setup script

REM – HCDMODBO.DMS
REM – Created by DBSETUP
/
REM – HRMS Demo Database HCDMO US English Database import
/
set log c:\temp\hcengs.log;
set input d:\hdmod1a\data\hcengs.db;
set execute_sql set current sqlid = ‘HCDMOD1A’;
set ddl table space * input stogroup as PSRTD1SG;
set ddl index * input stogroup as PSRTX1SG;
set ddl unique index * input stogroup as PSRTX1SG;
set ddl record * input dbname as HCDMOD1A;
set ddl record * input owner as HCDMOD1A.;
set ddl index * input owner as HCDMOD1A.;
set ddl unique index * input owner as HCDMOD1A.;
set ddl index * input owner2 as HCDMOD1A.;
set ddl unique index * input owner2 as HCDMOD1A.;
set no view;
set no space;
set no trace;
set no record;
import *;

*** The DMS Script overrides are ignored if the PSRECDDLPARM or PSIDXDDLPARM tables are populated for those records. If these tables are populated, delete the rows for your platformid BEFORE you Export the tables ***

If you currently have an exported DB or DAT file that you exported from a database which may have had data in the above tables you may want to check the DDL that Data Mover will create when you import into your new database. Follow these steps:
(For this example we will use MVPRDIMP.DMS but you may use your import DMS script)
1) Copy MVPRDIMP.DMS to MVPRDNEW.DMS
2) Apply the Overrides (SET DDL statements as required)
3) Add the following lines
SET EXTRACT OUTPUT C:\TEMP\TEST.DDL
SET EXTRACT DDL
IMPORT *
(Note: the IMPORT * should already be present. If the tables already exist use REPLACE_ALL command insted of IMPORT command)

This will create all the necessary DDL that will be required during the Import process and output it to a the TEST.DDL file without actually importing the data into your database.
4) Check the DDL output to ensure that the overrides did in fact work.
5) If the overrides were not used in the DDL output then the most likely problem is that the database from which the data was extracted had information in the PSRECDDLPARM or PSIDXDDLPARM tables.

WORKAROUND:
There are basically two workarounds at this time….

Workaround 1:
———————-
1) Backup PSRECDDLPARM and PSIDXDDLPARM from Source….using the Data Mover EXPORT command (against the Source database)
2) Delete entries from PSRECDDLPARM and PSIDXDDLPARM for PLATFORMID=1 (against Source)
3) Run the Export DMS scripts (against Source)
4) Apply the OVERRIDES to the Import DMS
5) Run the Import DMS (against the Target database)
6) Go back to the Source Database
7) Go to Data Mover and use REPLACE_DATA for PSRECDDLPARM and PSIDXDDLPARM from the backed up .DAT file (against the Source database)

Workaround 2:
———————-
1) Delete Entries from PSRECDDLPARM and PSIDXDDLPARM
2) Do the Export from the Source Database
3) Do the Import to the target with overrides
4) Run the SETDBNAM.SQR and SETINDX.SQR against source to get it back to the original state

*****
Here’s an update:
Db2 Os390 customer upgrading, in the Test the Move to Production step. As in the above steps, did deletes from psrecddlparm and psidxddlparm where platformid = 1 (db2). This allowed them to export from one environment and import into a different environment. However this failed due to space problems when importing one large table – the space parm failed as it was using a very low value.

workaround:
Before the export, instead of deleting from psrecddlparm where platformid = 1 and deleting from psidxddlparm where platform = 1

which wipes out all db2 parm settings, and caused the problem to begin with

do this delete:

delete from psrecddlparm where parm name = dbname
…so the import does not attempt to load the exported file back into the originating database

and

delete from psidxddlparm where parm name = stogroup
n

You aren’t in Bootstrap Mode

You receive the following error when you run a DMS – “Invalid Command in Bootstrap mode”.

You have signed into Datamover with your access ID rather than an user ID.

You can use PeopleSoft Data Mover in one of the following modes: user or bootstrap.

User (Regular) Mode
Most of the time you will sign onto PeopleSoft Data Mover in user mode. To do this, you simply enter your PeopleSoft user ID (like PS or VP1) and password at the signon screen.

Bootstrap Mode
At times, you need to signon on to PeopleSoft Data Mover in bootstrap mode, which means using the database access ID (like SYSADM or sa) and password at the signon screen. Typically, using bootstrap mode is necessary for database loading because there are no PeopleSoft security tables established yet. Bootstrap mode is also used for running some security commands, such as, ENCRYPT_PASSWORD.

In bootstrap mode, the following commands are not valid: EXPORT, RENAME, and REPLACE_VIEW.

Signon into Data Mover with your user ID ( “user mode”) unless specifically told to use your access ID (“bootstrap mode”).

HCM 9.0 RS Title Pages are in English when logged in using Spanish

All the title pages under Recruiting are in English, when logged in spanish & French languages. Application bundles are current. 

This problem is related to the missing data from table HR_SSTEXT_LANG.  Need to follow the instructions in the newly posted HRMS 9.0 ML Bundle #1 (Update ID: 747673) which includes the data for HR_SSTEXT_LANG.

 Download HRMS 9.0 ML Bundle #1(Update ID: 747673) and apply the same and the issue will be resolved.

Note: Please follow the instructions provided with this bundle before applying the same.

HCM 9.0 Job Code Table Introduction of the Subfunction field

With the release of 9.0, there are now some changes to the Job Code table and a new Subfunction field has been introduced.

The Subfunction field is a new field that was added to Release 9 that is basically an additional way to break down or further define the Job Function, if required. It has a child relationship to the current Job Function field. This is an optional field on the Job Code table. It is fully explained in PeopleBooks [PeopleSoft Enterprise HRMS 9.0 Application Fundamentals PeopleBook in the Setting Up Jobs section.

The Job Code can be used in the same way as always since the Subfunction field is an optional field.

Script to cleanup Duplicate Rows

The following SQL can be used to identify duplicate rows and the second SQL command will delete those rows:

select * from table_name
where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

delete from table_name where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);