Swapping the Base Language

This section provides an overview of base language swapping, lists prerequisites, and discusses steps related to it.

As delivered, the PeopleSoft database has a base language of English. The system has English data in its base language tables and, if you have installed the PeopleSoft-provided translations, non-English data in the related language tables. To choose a language other than English as your base language, you must move all English data to the related language tables (with an appropriate language code) and move all of the data in your new base language to the base tables. This process is called swapping the base language of your database. If you later decide to change base languages again, you must go through the same process.

There is a slight performance increase for the base language over the non-base languages, but there is extra administrative over head required when maintaining a swapped base environment. Take both of these into account if you are deciding whether to swap the base language.

PeopleSoft Data Mover provides the following command to automate this process: 

SWAP_BASE_LANGUAGE target_language;

To swap the base language of your database, you must identify the language to swap in as the new base language. PeopleSoft Data Mover finds all the affected tables and loads the appropriate language data into them.

This process handles all related language tables in the system: PeopleTools tables and application tables. Changes are committed after each table is swapped; a log file records the process of the swap and can help you troubleshoot any errors during the process.

You can check the existing base language by looking in the PeopleTools Options page. To navigate to the PeopleTools Options page, select PeopleTools > Utilities > Administration > PeopleTools Options. You can't change the base language setting in this page; it is for display purposes only. To change the base language, use the SWAP_BASE_LANGUAGE PeopleSoft Data Mover command.

Before swapping the base language of your database, you must have:

  • A clean audit report to ensure that there are no data integrity problems.

  • Sufficient space and resources to run the swapping process.

The swap audit report, SWPAUDIT.SQR, identifies data integrity problems that will cause errors during the swap process. This SQR program does not have a run control and, therefore, cannot be run with PeopleSoft Process Scheduler. For information on how to run SQR programs manually in your environment, refer to the section on creating a database in your PeopleTools installation documentation.

The swap audit report might list one or more of the following errors. Refer to the following instructions to correct these errors. Rerun the audit until it reports no errors before continuing with the swap process.

If the key structure is not valid between the base and RLR table, subsequent SWAP checks may fail. So, you may need to correct the key structure before rerunning the SQR to check the other SWAP checks.

Warning! Errors reported during the swap audit process indicate that the swap base language process will fail unless the errors are corrected.

Audit

Error Description

Resolution

SWAP-1

These are Related Language Records that are not valid records:

The records listed are defined as related language records for one or more base records, but they do not exist in your database.

Do one of the following:

  • Create the related language record, as appropriate, based on the keys and translatable fields of the base record.

  • Open the base language record and remove its association with the related language record in the Object Properties dialog box.

SWAP-2

The Field LANGUAGE_CD is not a key in the following Related Language Record(s):

A record is defined as a related language record, but it does not have LANGUAGE_CD as a component of its key. LANGUAGE_CD must be a key field on every related language record.

Make LANGUAGE_CD into a key field on each specified related language record.

SWAP-3

The following Related Language View(s) have the wrong structure defined:

A related language record must share all of the same keys as its base language record, plus an additional key field (LANGUAGE_CD). Also, any non-key fields must also be in the base language record.

Correct the field structure for the specified related language views to ensure that they conform to this requirement. Then, recreate the views in the database.

SWAP-4

The following Related Language Table(s) have the wrong key structure defined:

A related language record must share all of the same keys as its base language record, plus an additional key (LANGUAGE_CD).

Correct the key structure for the specified related language tables to ensure that they conform to this requirement. Then, alter the tables in the database to match the new key structure.

SWAP-5

The following Related Language Table(s) have the wrong structure defined:

A related language record must share all of the same keys as its base language record, plus an additional key field (LANGUAGE_CD). Also, any non-key fields must also be in the base language record.

Correct the field structure for the specified related language tables to ensure that they conform to this requirement. Then, alter the tables in the database to match the new field structure.

SWAP-6

The following Related Language Table(s) have the Orphan Row defined:

For each row on the related language record there must be a single row on the base table with matching keys. An orphan row is a row of data on the related language record that does not have a corresponding parent row on the base table. You must delete orphan rows from related language tables.

Run the PTIACLEANLNG application engine program to clean orphan rows or use your platform's query tool to select against the related language table using the fields listed in the report. For every row in the report, there is an orphan row in a related language table. Perform a SELECT command first to ensure that you are getting the same row count as the report, then use the DELETE command to delete the selected rows.

The following provides example code for an Oracle database. In this example, xxx is equal to the language code to which you are swapping. 

SELECT * FROM PSMSGCATLANG 
WHERE NOT EXISTS 
(SELECT 'X' FROM PSMSGCATDEFN A 
WHERE PSMSGCATLANG.MESSAGE_SET_NBR =
A.MESSAGE_SET_NBR AND PSMSGCATLANG.
MESSAGE_NBR = A.MESSAGE_NBR) 
AND LANGUAGE_CD = 'xxx'
 
DELETE FROM PSMSGCATLANG 
WHERE NOT EXISTS 
(SELECT 'X' FROM PSMSGCATDEFN A 
WHERE PSMSGCATLANG.MESSAGE_SET_NBR =
A.MESSAGE_SET_NBR AND PSMSGCATLANG.
MESSAGE_NBR = A.MESSAGE_NBR) 
AND LANGUAGE_CD = 'xxx'
 
SELECT * FROM Related_Language_Record A
WHERE NOT EXISTS
(SELECT 'X' FROM Base_Record B 
WHERE A.Field_Name = BField_Name ...for each field name listed)
AND LANGUAGE_CD = 'xxx'

SWAP-7

The following base records do not have a Unique Index:

A base record that has a corresponding related language record must have a unique index—that is, a key field.

Open each base record listed in the report and select a field that you want to make a key field. Then alter the tables in the database to match the new key structure.

SWAP-8

The following base records and the Related Language Records have different record types:

There is a mismatch between the record type of the base record definition and its related language record definition.

Create a new related language record definition and assign it (or reassign an existing related language definition) to the base record, making sure that both the base record and related language record definition are the same record type.

SWAP-9

The following translated BI Publisher template files are orphans:

An orphaned translated BI Publisher template file is a file that does not have a corresponding base template file. For each translated BI Publisher template file, there must be a base BI Publisher template file with matching keys. You must delete the row for each orphaned translated BI Publisher template file.

Use your platform’s query tool to select against the tables storing translated BI Publisher template file using the fields listed in the report. Perform a SELECT command first to ensure that you are getting the same row count as the report, then use the DELETE command to delete the selected rows.

The following provides example code for an Oracle database. In this example, xxx is equal to the language code to which you are swapping.

SELECT * FROM PSXPTMPLTRINFO A
WHERE NOT EXISTS 
(SELECT 1 FROM PSXPTMPLFILEDEF B
WHERE A.TMPLDEFN_ID = B.TMPLDEFN_ID
AND A.EFFDT = B.EFFDT)
AND A.TMPLLANGCD = 'xxx';

You must delete the orphan rows from PSFILEDEFN and PSFILEDATA before you delete the orphan rows from PSXPTMPLTRINFO.

DELETE FROM PSFILEDEFN WHERE FILEID IN 
(SELECT XLIFF_FILEID FROM 
PSXPTMPLTRINFO A
WHERE NOT EXISTS 
(SELECT 1 FROM PSXPTMPLFILEDEF B
WHERE A.TMPLDEFN_ID = B.TMPLDEFN_ID
AND A.EFFDT = B.EFFDT) 
AND TMPLLANGCD='xxx');

DELETE FROM PSFILEDATA WHERE FILEID IN 
(SELECT XLIFF_FILEID FROM 
PSXPTMPLTRINFO A
WHERE NOT EXISTS 
(SELECT 1 FROM PSXPTMPLFILEDEF B
WHERE A.TMPLDEFN_ID = B.TMPLDEFN_ID
AND A.EFFDT = B.EFFDT) 
AND TMPLLANGCD='xxx');

DELETE FROM PSXPTMPLTRINFO 
WHERE NOT EXISTS 
(SELECT 1 FROM PSXPTMPLFILEDEF B
WHERE PSXPTMPLTRINFO.TMPLDEFN_ID = 
B.TMPLDEFN_ID
AND PSXPTMPLTRINFO.EFFDT = B.EFFDT)
AND TMPLLANGCD='xxx';

Before running the SWAP_BASE_LANGUAGE command, be sure that your database has sufficient space and resources to perform the swap process. Although the swap base language process commits after each table is successfully swapped, you need sufficient log space, or rollback space in the case of Oracle databases, to hold the contents of the largest table to be swapped.

On a newly installed PeopleSoft database, the PSPNLFIELD table is typically the largest table to be swapped, and this can be used as a benchmark for sizing your log space. Plan on at least 50 to 75 MB log or rollback segment space before running the swap process.

Once you are ready to swap the base language, start PeopleSoft Data Mover in non-bootstrap mode by logging in using a regular PeopleSoft user ID (not the access ID). Once PeopleSoft Data Mover begins, type and run the SWAP_BASE_LANGUAGE command: 

SWAP_BASE_LANGUAGE target_language;

PeopleSoft Data Mover provides feedback during the swap process, including the name of the record currently being swapped and the number of records that remain to be swapped.

Even when SWPAUDIT completes with no errors, the swap base language process may fail. Typically, environmental issues, such as lack of database space, log space, or rollback segment space, causes any errors during this stage of the process. If a failure occurs, note the database-specific error message issued and take the appropriate action according to your database platform documentation.

PeopleSoft Data Mover stops when errors are encountered. Once you have corrected the problems that caused the failure in the swap base language process, you can restart the process without having to restore your database or remember where the first error occurred. To restart the swap process, rerun the SWAP_BASE_LANGUAGE command. PeopleSoft Data Mover recognizes the tables whose data has already been swapped and does not attempt to swap the data in those tables again; it will report that zero rows were swapped for those tables.

It is safe to rerun this command as many times as needed, correcting errors between runs, until the log file reports no errors.

In some situations, you may want to swap a specific record. Typically, you would swap a single record only if errors occurred during the swap base language process and you want to verify that the swap will succeed without having to re-swap all the records in the database or trace or troubleshoot the swap process.

To swap a specific table, use the following PeopleSoft Data Mover commands:

SET BASE_LANGUAGE target_language;
SWAP_BASE_LANGUAGE recname;

Note: You should swap individual tables only when there has been an error with system-wide swapping.

If you want PeopleSoft Data Mover to not stop when errors are encountered and continue the swap process, set the following:

SET IGNORE_ERRORS;
SWAP_BASE_LANGUAGE LANGUAGE_CD;

If you chose to ignore errors during the swap, after the swap process is complete carefully review the log file to ensure that no errors occurred. If errors occur, fix the errors and then re-swap each table using the SWAP_BASE_LANGUAGE command in individual table mode for each table that failed.

After you have upgraded to a new release of PeopleTools, you can apply patches to a swapped database using the usual upgrade tools such as the Change Assistant, Application Designer Project Copy, or Data Mover. These tools will make the transformation between the non-swapped source tables and the swapped target tables. If the patch involves direct SQL statements, the SQL statements should account for swapped and non-swapped environments. If you are writing or running your own custom SQL or querying tables directly, keep in mind that if a table has a related language table associated with it, after swapping the base language you may need to adjust your SQL to work with the correct table.

To apply patches for multi language files, the steps are listed within each upgrade or install guide for your database platform.

See the product documentation for PeopleSoft 9.2 Application Installation for your database platform, “Working with Multilingual Databases”.

The following table compares the two patching methods, for applying patches to applications:

Using the Classic method for applying patches

Using the PeopleSoft Upgrade Manager (PUM) for applying patches

Used for Applications 9.1 and earlier.

Used for Applications 9.2 and later.

Change Assistant loads patches and bundles in a predefined upgrade package. Change Assistant will call Data Mover or Project Copy utilities.

PeopleSoft Update Manager generates a custom upgrade package, then uses Change Assistant to call Data Mover or Project Copy utilities.

Translations ship separately from English language, sometimes at a later release date than English. The translations are applied after applying English.

Translations ship in the patch image along with English language, and each language is loaded if the target database has it enabled. More information on PUM is at PeopleSoft Update Manager (PUM) Home Page (Doc ID 1464619.1).

The following table compares the two patching methods, for applying patches to PeopleTools:

Using the Manual method for applying patches

Using the Change Assistant method for applying patches

Used for Tools 8.53 and earlier.

Used for Tools 8.54 and later.

Manually calls Data Mover or Project Copy utilities.

Change Assistant calls Data Mover or Project copy utilities.

Translations ship in the project and data directories, along with English.

Translations ship in the project and data directories, along with English, and each language is loaded if the target database has it enabled.

Also see,