Supported SQL Commands

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.

    DB2 UTS customers can use the DROP table command.

  • 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 PeopleCode Language Reference: 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 PeopleCode Language Reference: %TruncateTable meta-SQL element.