Understanding Data Mover Scripts

This section discusses the commands and statements used in Data Mover Scripts.

This section discusses the valid PeopleSoft Data Mover commands that you can include in PeopleSoft Data Mover scripts.

PeopleSoft Data Mover commands are platform-independent and are unique to PeopleSoft Data Mover. You can use PeopleSoft Data Mover commands for importing, exporting, and other tasks, such as controlling the run environment, renaming fields and records, administering database security, and denoting comments.

The following table describes the PeopleSoft Data Mover commands and the ways that you can indicate comments:

Command

Description

ENCRYPT_PASSWORD

Encrypt one or all user passwords (operator and access) defined in PSOPRDEFN for users.

EXPORT

Select record information and data from records and store the result set in a file. You can use the generated export file as input for migrating to another platform. This file is portable between ASCII and EBCDIC character sets, and also supports double-byte characters.

IMPORT

Insert data into tables using the information in an export file. If a tablespace or table does not exist, this command creates tablespace, table, and indexes for the record, using the information in the export file, and inserts the data.

REM, REMARK, and --

Indicate comment statements.

RENAME

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

REPLACE_ALL

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 does the following:

  1. creates the table.

  2. creates any triggers.

  3. inserts the data.

  4. creates indexes.

REPLACE_DATA

This is a variation of the IMPORT command. Delete data in existing tables and insert the corresponding data from the export file.

REPLACE_VIEW

Recreate specified views found in the database.

RUN

Run a specified .DMS file from within a PeopleSoft Data Mover script. The file cannot contain nested RUN commands.

SET

When a command is followed by valid SET parameters, it forms a statement that establishes the conditions under which PeopleSoft Data Mover runs the PeopleSoft Data Mover and SQL commands that follow.

SET_IGNORE_IMPORT_ERRORS

(Optional) If this command is set, then all errors produced by IMPORT, REPLACE_DATA, and REPLACE_ALL will be ignored and processing will continue until the end of the data file or last import command.

Any failed records will be shown in the Data Mover log if it is run from the Data Mover GUI or the import output if it is run from the command line.

SET IGNORE_ERRORS

(Optional) If this command is set, then all errors produced by the SWAP_BASE_LANGUAGE command are ignored. Otherwise, the system stops on errors.

SET BASE_LANGUAGE

Swap individual language tables. You should swap individual tables only when there is an error with any of the tables after the SWAP_BASE_LANGUAGE command.

SWAP_BASE_LANGUAGE

Swap all the language tables from PSRECDEFN.

With PeopleSoft Data Mover, you can use supported SQL commands in scripts on any supported database platform. Except as noted in the following discussion regarding standard SQL commands, you can use all of the supported SQL commands with the following Data Mover SET statements:

  • SET LOG

  • SET NO COMMIT

  • SET NO TRACE

Standard SQL Commands with DMS Scripts

PeopleSoft Data Mover supports the following standard SQL commands:

  • ALTER

  • COMMIT

  • CREATE

  • DELETE

  • DROP

    Note: With DROP commands, any drop errors are ignored. The script continues, but the errors are reported in the log.

  • GRANT

  • INSERT

    Important! INSERT cannot be used with SET NO COMMIT or SET NO TRACE.

  • ROLLBACK

  • UPDATE

  • TRUNCATE

Warning! PeopleSoft Data Mover does not support SELECT statements, because they require a SQL FETCH function.

Standard SQL Commands with SQL Files

PeopleSoft Data Mover supports all SQL commands and other database-specific function calls that are supported by the database engine.

Note: PeopleSoft Data Mover runs only files with the extension .SQL.

Nonstandard SQL Commands

With PeopleSoft Data Mover, you can also use the following nonstandard SQL commands created by PeopleSoft: STORE and ERASE. Use the commands to change COBOL SQL statements in PS_SQLSTMT_TBL.

The STORE command first deletes the existing stored statement from PS_SQLSTMT_TBL, and then inserts the new statement using the following syntax: 

STORE progname_type_stmtname

For example:

STORE PTPEMAIN_S_MSGSEQ
SELECT MAX (MESSAGE_SEQ), PROCESS_INSTANCE
  FROM PS_MESSAGE_LOG
  WHERE PROCESS_INSTANCE = :1
  GROUP BY PROCESS_INSTANCE
;

The ERASE command deletes one or all stored statements from PS_SQLSTMT_TBL. When deleting a single statement, you use the progname_type_stmtname format as shown for STORE. For example:

ERASE PTPEMAIN_S_MSGSEQ;

When deleting all SQL statements for a particular program, you include only the program name in the command line format. For example:

ERASE PTPEMAIN;

Expressing Dates and Time in SQL Used in Data Mover

When you need to express dates and time in Data Mover SQL statements, use PeopleSoft meta-SQL date and time constructs, such as %CURRENTDATEOUT, %CURRENTTIMEOUT, %CURRENTDATETIMEOUT, %DATEIN, %TIMEIN, and so on.

See Understanding Meta-SQL.

Truncating Tables

Occasionally, it is necessary to delete all the rows in a table from Data Mover. Data Mover supports the use of the TRUNCATE command. When used, Data Mover resolves the command to the current database syntax using PeopleTools meta-SQL constructs.

For best performance, using the TRUNCATE command is recommended, rather than performing a mass DELETE operation, which can encounter performance issues where large tables and triggers are involved.

See %TruncateTable.

The following table shows the relationship between SQL and PeopleSoft Data Mover commands. DDL refers to data definition commands, which define the structure of a database. DML refers to data manipulation commands which define the contents of a database.

Function

Command Type

Supported SQL Commands

Data Mover Commands

Create tables, tablespaces, and indexes.

DDL

CREATE

IMPORT

REPLACE_ALL

Create views.

DDL

CREATE

REPLACE_VIEW

Drop tables.

DDL

DROP

REPLACE_ALL

Modify tables.

DDL

ALTER

None

Modify PeopleSoft records.

DDL

None

RENAME

Delete all rows.

DDL

TRUNCATE

None

Insert rows.

DML

INSERT

STORE

IMPORT

REPLACE_ALL

REPLACE_DATA

Delete rows.

DML

DELETE

ERASE

REPLACE_DATA

Update rows.

DML

UPDATE

None

Encrypt rows.

DML

None

ENCRYPT_PASSWORD

Select rows.

Query

None

EXPORT

Save or don't save changes.

Transaction

COMMIT

ROLLBACK

SET (when used with COMMIT or NO COMMIT)

Control or run other PeopleSoft Data Mover commands.

Environment

None

SET

RUN

Denote an explanatory statement.

Comment

None

REM

REMARK

--

PeopleSoft Data Mover issues COMMIT statements after most successful SQL commands, except for EXPORT and IMPORT. For EXPORT and IMPORT, PeopleSoft Data Mover issues a COMMIT after each record. With IMPORT, a SET COMMIT n command performs a COMMIT after the system inserts every n rows.

If you are executing native SQL in PeopleSoft Data Mover, and no COMMIT statements exist in the SQL script, PeopleSoft Data Mover issues a COMMIT after each successful SQL statement. For example, if you run a PeopleSoft Data Mover script that contains three update commands, and the third command fails, the first and second update commands are committed, but the third command is not.