Using PeopleSoft Data Mover Commands

This section provides the details of syntax and use for each of the PeopleSoft Data Mover commands. This section also discusses PeopleSoft Data Mover command modifiers, such as AS, WHERE, and IGNORE_DUPS, which can be used to modify certain commands.

Syntax

CHANGE_ACCESS_PASSWORD SymbolicID newAccessPswd

Description

Use this command to reset the access password and make it transparent to users.

The CHANGE_ACCESS_PASSWORD command performs the following operations:

  • Selects the access password field from the PSACCESSPROFILE table for the specified symbolic ID.

  • Changes the access ID's database password to the new access password that you specify (for Oracle and Microsoft SQL Server only).

  • Updates the PSACCESSPROFILE table for the specified symbolic ID with the new access password.

Parameters

LOG and NO TRACE

Syntax

CREATE_TEMP_TABLE
{record | *}

Description

Creates temporary table images for use with PeopleSoft Application Engine programs. To customize the number of temporary tables, you need to modify the PeopleTools Options page or updated the PSOPTIONS table using the following SQL:

UPDATE PSOPTIONS SET TEMPTBLINSTANCES = number

You also need to review the number of temporary tables allotted for PeopleSoft Application Engine programs

Note: For security reasons, this command is disabled for z/OS, DMS scripts generated by the Database Setup utility.

Syntax

CREATE_TRIGGER
{* | recordname}

Description

Creates database triggers on the specified table.

Note: If you use CREATE_TRIGGER in bootstrap mode, the system automatically activates SET IGNORE ERROR. This enables PeopleSoft Data Mover to continue processing until all of the view definitions have been processed, and all errors have been written to the current .LOG file (or an error log file). This is similar to the REPLACE_VIEW behavior.

Syntax

ENCRYPT_PASSWORD
{userID | *};

Description

Encrypts one or all user passwords (user passwords and access passwords). When encrypting a single user's password, the user ID must be present in PSOPRDEFN. You can use an asterisk instead of a name to encrypt all passwords in PSOPRDEFN.

Parameters

LOG, NO COMMIT, and NO TRACE.

Example

Here's an example of how to encrypt a single user password (FS) already listed in PSOPRDEFN:

ENCRYPT_PASSWORD FS;

To encrypt all user passwords in PSOPRDEFN, enter:

ENCRYPT_PASSWORD *;

Syntax

EXPORT {record | *} [WHERE conditions];

Description

Creates a single export file containing the specified database contents. The result set can contain any of the following: a single PeopleSoft record, a group of records, or the entire database. You can use the export file as input for the PeopleSoft Data Mover IMPORT command to migrate the data within the platform or to another platform.

Note: This command is not available in bootstrap mode.

Records exported using EXPORT can have a maximum of 500 total columns and multiple long columns within the limitations for long columns set by the database platform. Check with the database vendor for restrictions on the number of long columns allowed for the platform.

When you export all records using EXPORT*, PeopleSoft Data Mover orders the records alphabetically (with the exception of PSLOCK, which is the last record exported). After each record, PeopleSoft Data Mover indicates how many records remain. After all the tables are exported, then the views are exported.

Warning! The WHERE clause, when used in this command, supports only US-ASCII (seven-bit ASCII) values. Characters beyond this range can produce errors in the export file.

Parameters

LOG, NO COMMIT, NO DATA, NO TRACE, NO VIEW, and OUTPUT.

Note: SET NO VIEW is only valid with EXPORT *.

Note: If SET OUTPUT is not used, PeopleSoft Data Mover writes to the default file name, DATAMOVE.DAT.

Example

To export a single record, use an EXPORT command for the specific record. For example:

EXPORT PS_JOB;

Note: When specifying a particular record in the EXPORT command (as shown in the previous example), the specified record must be a table, not a view.

To export all PeopleSoft records, including views, enter 

EXPORT *;

Syntax

IMPORT {record | *} [IGNORE_DUPS]
  [AS new_table_name] [WHERE conditions];

Description

The IMPORT command:

  • creates database spaces.

  • creates nonexisting tables and indexes.

  • appends non-duplicate rows to records.

  • creates views if the export file was created using EXPORT * and imported using IMPORT *.

Warning! All duplicate row-checking depends on the existence of a unique index. If no unique indexes are created before loading the data, there is a potential for duplicate data.

In the IMPORT statement, the AS clause is only valid if you specify a table name.

Using * with AS or WHERE is allowed in cases where the imported data file consists only of a single table. For example,

IMPORT * AS table_name

If the data file consists of the more than one table when using * AS or WHERE, the system returns the following error message:

Error: The Input File file_name contains number_of_records records.

The table name that you specify immediately after the AS command modifier must not exceed 18 characters (including the ps_ prefix). If you do specify a table_name that exceeds 18 characters, the following error appears: Error: Unable to process create statement.

See WHERE.

Records defined using IMPORT can have a maximum of 500 columns with multiple long columns. The number of long columns allowed is determined by the limitations for long columns set by the database platform. Refer to your database vendor documentation for restrictions on the number of long columns allowed for the platform.

There are two variations of IMPORT that you can use:

  • REPLACE_ALL

  • REPLACE_DATA

Parameters

All except OUTPUT.

INPUT is a required parameter.

Note: IGNORE_DUPS is only valid in bootstrap mode.

Example

To import a single record from an export file, use an IMPORT command for that record. For example:

SET INPUT file_name;
IMPORT PS_JOB;

To import all PeopleSoft records from an export file, including views, enter:

SET INPUT file_name;
IMPORT *;

Globalization Considerations

PeopleSoft Data Mover offers a base-language-independent method for moving application data between databases. PeopleSoft Data Mover loads a single DAT file, detects the target database base language, and inserts the data into the correct base or related language table.

If Oracle provides a software fix, you don't need to swap the base language before importing it into a database with a different base language. For example, suppose that a fix is sent with the base language English (ENG) and the related language Japanese (JAP). In this case, you can import this file directly into a database where the base language is JAP and the related language is ENG.

Upon EXPORT, the system adds the LANGUAGE_CD (language code) to the generated DAT file. For example: 

SET BASE_LANGUAGE ENG

Then, when you use the IMPORT command to import the generated DAT file, the system detects the LANGUAGE_CD in the DAT file and compares it with the LANGUAGE_CD in the target database to determine how to swap the base language and related language tables.

Note: Base language is the database base language. It can be any PeopleSoft-supported language.

Consider the following points when running the IMPORT command:

  • This feature is enabled whenever you import a DAT file.

  • Running the IMPORT command may have an unavoidable adverse affect on performance.

Syntax

REM comments; REMARK comments; -- comments

Description

Each of these three command variations indicates explanatory text in a PeopleSoft Data Mover script.

Example

Here are three examples demonstrating the use of each:

REM  This example demonstrates the use of the REM command to set off script
 comments.
 These statements can span multiple lines and must be terminated with a valid
 delimiter;
REMARK  The REMARK command variation has the same restrictions as REM
/
--  This example demonstrates the use of two dashes to denote script
--  comments.  No delimiters are required, but statements can not 
--  exceed one line without using another double-dash.

When using a double hyphen (--), as in the third example, you need at least one space after the double hyphen, before the start of the actual text of the comment. Otherwise, you receive a syntax error.

When used in conjunction with a comment prefixed by REM or REMARK, the forward-slash delimiter (/) should be by itself on the last line of that comment. In such cases, instead of using a forward-slash (/), you can also use a semicolon (;) by itself on this last line. The forward slash (/) can also be used by itself without a REM or REMARK statement, in lieu of blank lines, which are also allowed in a script.

Syntax

RENAME {RECORD record | FIELD
{field | record.field}}  AS new_name;

Description

Renames a PeopleSoft record, a field in one record, or a field in all records.

Note: This command is not available in bootstrap mode.

Warning! Using RENAME only modifies a definition in the PeopleSoft tables. To write the record and field change to the system tables, you must use Application Designer to modify the affected definitions.

To rename a record field, you must qualify the original name of the field with the record name. If you don't qualify the record name, PeopleSoft Data Mover attempts to globally change the field name in all records.

Renaming a record field is only possible through PeopleSoft Data Mover.

To rename a record field:

  1. Perform the rename in PeopleSoft Data Mover.

    For example:

    RENAME FIELD RECORD.FIELD AS NEWFIELD; COMMIT;

  2. In Application Designer, create a project that includes the record that contains the field that you renamed, and save the project.

    In the case of a subrecord field rename, the subrecord along with all tables that contain that subrecord must be inserted into the project.

  3. Select Build, Settings.

    • Select the Alter tab.

    • Select Adds and Renames.

    • Clear Changes and Deletes.

      Note: Drop column and change column length do not apply.

    • Select the Scripts tab and select output settings.

    • Specify an output file and click OK.

  4. Select Build, Project.

    • Select Alter Tables (Create Indexes is selected by default).

    • Click Build.

    • Click Yes to continue the build process.

  5. Run the generated SQL script using the query tool.

    This adds the new field to the tables within the project.

    Note: For subrecord field renames only, data is not automatically migrated from the old field to the new field. You will need to migrate this data manually.

To remove the old field from the tables:

  1. In Application Designer, open the project that you created using the preceding steps.

    • Select Build > Settings.

    • Select the Alter tab.

    • Select Drop column if data present.

    • Select Deletes.

    • Clear Adds and Renames.

    • Select the Scripts tab.

    • Give the output file a different name and click OK.

  2. Select Build, Project.

    • Select Alter Tables (Create Indexes is automatically selected).

    • Click Build.

    • Click Yes to continue the build process.

  3. Run the generated SQL script using the query tool.

    The old field should no longer appear on the tables included in the project.

Parameters

LOG, NO COMMIT, and NO TRACE.

Example

Here's an example of how to rename a record:

RENAME RECORD absence_hist AS absent_hist;

Here's an example of how to globally rename a field:

RENAME FIELD effdt AS currdate;

Here's an example of how to rename a recfield:

RENAME FIELD course_tbl.duration_days AS duration_d;

Syntax

REPLACE_ALL {record | *} 
 [AS new_table_name];

Description

This is a variation of the IMPORT command. If a table already exists, use this command to drop the table and its indexes from the database. It then:

  1. creates the table.

  2. creates any triggers.

  3. inserts data.

  4. creates indexes.

In the REPLACE_ALL statement, the AS clause is only valid if you specify a particular record. It is not valid and should not be used with REPLACE_ALL *.

The table name that you specify after the AS command modifier should not have more than 18 characters (including the ps_ prefix). Specifying a table name that is greater than 18 characters invokes the following error message: Error: Unable to process create statement.

Note: Records defined using REPLACE_ALL can have a maximum of 500 total columns and multiple long columns within the limitations for long columns set by the database platform. Check with the database vendor for restrictions on the number of long columns allowed for the platform.

Parameters

All except IGNORE_DUPS and OUTPUT. INPUT is a required parameter.

Syntax

REPLACE_DATA
{record | *};

Description

This command is a variation of the IMPORT command. Use it to delete data in existing tables and insert the corresponding data from the export file.

Parameters

COMMIT, EXECUTE_SQL, EXTRACT, INPUT, INSERT_DATA_ONCE, LOG, NO COMMIT, NO TRACE, NO VIEW, SIZING_SET, SPACE, START, and VERSION. INPUT is a required parameter.

Syntax

REPLACE_VIEW
{view | *};

Description

Recreates one or all specified views in the database.

Parameters

LOG, NO COMMIT, NO TRACE, and START.

Note: If you use REPLACE_VIEW in bootstrap mode, the system automatically activates SET IGNORE ERROR. This enables PeopleSoft Data Mover to continue processing until all of the view definitions have been processed, and all errors have been written to the current .LOG file.

Syntax

RUN dms_file_name;

Description

Runs a DMS file from within a script. The specified file can contain any supported SQL commands, PeopleSoft Data Mover commands, or SET statements, but it cannot contain any RUN commands.

The RUN command cannot contain a directory path. The RUN command uses the same directory as the current PeopleSoft Data Mover script in which RUN is used.

Syntax

SET parameter_1;
SET parameter_2; ... SET parameter_n;

Description

The SET command, when combined with valid SET parameters, creates statements that establish the conditions under which PeopleSoft Data Mover runs a script.

A SET statement controls the processing environment for the commands in a script until another SET statement intervenes between commands. At that point, all SET parameters are reset to their default values.

Example

SET LOG c:\temp\new.log
SET OUTPUT c:\temp\new.dat;
/
EXPORT absence_hist;
EXPORT employee_tbl
/
SET NO DATA 
/
REMARK  All other SET parameters will be reset to defaults at this point;
EXPORT bank_branch_tbl;

In the previous script, the specified log and output files (NEW.LOG and NEW.DAT) are used for the first two EXPORT commands. Then, because SET NO DATA interrupts the script commands, all other SET parameters are reset to their default values. So, for the third EXPORT and any subsequent PeopleSoft Data Mover or SQL commands, the log file used is the default log file, DATAMOVE.LOG, and the output file used is the default output file, DATAMOVE.DAT.

See Using SET Parameters.

Syntax

SET BASE_LANGUAGE current_language_code; SWAP_BASE_LANGUAGE recordname;

Description

Use only when there is an error with any of the tables after the SWAP_BASE_LANGUAGE new_language_code command.

Note: Never run SET BASE_LANGUAGE current_language_code and SWAP_BASE_LANGUAGE recordname commands before SWAP_BASE_LANGUAGE new_language_code.

Syntax

SET IGNORE_ERRORS;
SWAP_BASE_LANGUAGE language_code;

Description

Use this command in conjunction with the SWAP_BASE_LANGUAGE command.

Example

Here's an example of how to swap one table (without the SET IGNORE_ERRORS command, it stops on error):

SWAP_BASE_LANGUAGE DUT;

Here's an example of how to ignore all errors and swap all tables: 

SET IGNORE_ERRORS;
SWAP_BASE_LANGUAGE JPN;

When the SWAP_BASE_LANGUAGE command is run after SET IGNORE_ERRORS, the PSOPTIONS SET LANGUAGE_CD is automatically updated with new base language, even if errors were recorded.

When the command has run, you should then examine the log and swap the individual record names that failed using SWAP_BASE_LANGUAGE recordname command

Syntax

Set COMMIT level;

Description

Sets the commit level for inserting rows and not for DDL statements. If the level is set to 0, commits are only done when all rows for a record are inserted. Due to the expense of recompiling and rebinding after a commit, the default is 0.

Note: There are performance implications associated with the SET COMMIT command. For a large database with millions of rows, there is significant degradation in performance. However, for a small database, performance slows down somewhat. Run the SET COMMIT command only as necessary.

Parameters

The default is to commit at the end of the record.

Example

The following examples demonstrate how to use SET COMMIT in conjunction with SWAP BASE_LANGUAGE:

Set COMMIT 2;
SWAP BASE_LANGUAGE FRA;

or

Set COMMIT 2;
SET BASE_LANGUAGE ENG;
SWAP_BASE_LANGUAGE MY_RECORD;

Syntax

SWAP_BASE_LANGUAGE new_language_code;

or

SET BASE_LANGUAGE current_language_code; SWAP_BASE_LANGUAGE recordname;

Description

Installs any language other than English.

The command swaps all the language tables from PSRECDEFN. It gets all table names that contain related tables, and it swaps one table at a time. It copies the base table into the related table, updates the related record into the base table, and then deletes the related record from the related table.

If successful, the command updates PSOPTIONS SET LANGUAGE_CD to the new base language.

Swapping an individual table (SET BASE_LANGUAGE current_language_code and SWAP_BASE_LANGUAGE recordname) is used only when there is an error with any of the tables after the SWAP_BASE_LANGUAGE new_language_code command has been run.

Note: Never run a combination of SET BASE_LANGUAGE current_language_code and SWAP_BASE_LANGUAGE recordname command before SWAP_BASE_LANGUAGE new_language_code.

Example

To swap English for Canadian French, enter the following:

SWAP_BASE_LANGUAGE CFR

CFR is the new language code.

Note: During the initial installation, the Database Setup utility generates a script that automatically swaps the base language if, while in the Database Setup interface, you select a base language other than English.