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:
|
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_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.
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.