Creating and Running PeopleSoft Data Mover Scripts
This section provides overviews of command types and syntax rules in PeopleSoft data mover scripts.
A PeopleSoft Data Mover script can contain two types of commands:
Data Mover commands.
Use these commands to export and import database information and to otherwise modify the database. PeopleSoft Data Mover commands also control script execution, call other PeopleSoft Data Mover files, and indicate comments.
SQL commands.
You can use both standard and nonstandard SQL commands that modify the database.
To create or edit PeopleSoft Data Mover scripts, follow these syntax rules to ensure that the commands run successfully.
Delimiters
With the exception of double-hyphen (--) comment statements, every command statement must be followed by a delimiter.
Valid delimiters are:
Semicolon (;)
A semicolon can appear on the same line as the command itself, or by itself on the line immediately following a command statement. For example, the following two examples of the semicolon delimiter are valid:
SET OUTPUT c:\temp\abc.dat; SET LOG c:\temp\new.log ;Forward slash (/)
This delimiter can be used only on a line by itself, in column 1, on a line immediately following a command statement. For example:
IMPORT * /
Multiline Statements
With the exception of double-hyphen (--) comment statements, statements can span multiple lines. For example:
EXPORT absence_hist
WHERE absence_type = 'A';Multiline Comments
A double-hyphen (--) comment statement does not require a delimiter termination. However, each statement can't span more than one line. Be sure to add a space after the double hyphen before you start the comment. For example:
Correct:
-- This script imports the information stored in
-- the ABC.DAT file.Incorrect:
--This script imports the information stored in
the ABC.DAT file.White Space
Command statements can contain any amount of white space between items.
Case Sensitivity
Statement text is not case-sensitive. For example,
IMPORT * is equivalent to
import *String Constants
String constants are
case-sensitive and must be surrounded by single quotation marks. For
example, 'ABC' is treated differently than 'Abc' or 'abc'.
Record Names and Table Names
In PeopleSoft Data Mover, when a record name needs to be specified as one of the elements in the command statement syntax, as in an IMPORT statement, you can specify either the record name or the corresponding table name. For example, the following IMPORT statements are equivalent:
Correct:
IMPORT job;Correct:
IMPORT ps_job;However, when a table name is required for one of the elements in the command statement syntax, you must use the table name, not the record name. For example:
Correct:
IMPORT job AS ps_process;Incorrect:
IMPORT job AS process;When you use PeopleSoft Data Mover to manipulate the information in a database, you can either write a new script or open and edit an existing script that is similar to the one that you want to create.
The default file extension for scripts is .DMS, which stands for Data Mover script.
Creating a New Script
To create a new script:
Select File, New.
When you first launch PeopleSoft Data Mover, a new file appears automatically. .
Enter the script text (that is, the code) in the input pane, which appears on top.
Using proper Data Mover syntax, enter the command statements that you want the script to run.
Save the script.
Select File, Save. In the Save As dialog box, select the Save as Unicode check box (if appropriate) and click Save.
Editing an Existing Script
To edit an existing PeopleSoft Data Mover script:
Select File, Open.
Select the file and click OK.
By default, you view only .DMS files. You can select All Files from the Files of type drop-down list box to view all file types. After you open a script, it appears in the PeopleSoft Data Mover input pane.
Modify the script.
If the file that you opened is not a .DMS file, verify that it conforms to the required syntax rules and that it doesn't contain unsupported SQL commands.
Save the script with a new name.
Select File, Save As.
In the Save As dialog box, enter a file name, select the Save as Unicode check box (if appropriate) and click Save.
If the script is edited in Unicode format, then the default save is Unicode. However, if the file is opened in ASCII format, then the default is ASCII.
Before running a PeopleSoft Data Mover export script, you must first prepare the database.
To prepare for an export:
Load DDL model information by running all DDL*.DMS files through PeopleSoft Data Mover.
To change the DDL model information, use the DDL Model Defaults utility in PeopleTools Utilities.
Run DDDAUDIT.SQR and fix any errors that it finds.
Run SYSAUDIT.SQR and SYAUD01.SQR and fix any errors.
Use the SQL Alter function in Application Designer to alter all tables.
Either let the files alter in place or run the script that it generates to alter any tables that it marks as out of synchronization.
Use the SQL Create function in Application Designer to create all records, using the If table exists ... Never recreate option.
Through PeopleSoft Data Mover, you can run DDL, DML, and SQL scripts created with the following tools:
PeopleSoft Data Mover (DMS scripts).
Build SQL functionality in Application Designer (SQL scripts).
Platform-specific SQL utilities (SQL scripts).
Note: You can also schedule PeopleSoft Data Mover scripts using PeopleSoft Process Scheduler. This can be useful in scheduling audit routines or extracting data from the PeopleSoft database. Additionally, logs and data files generated by PeopleSoft Data Mover can be posted to the report repository in PeopleSoft Process Scheduler so that they can be viewed either through Process Monitor or Report Manager.
When running scripts through PeopleSoft Data Mover, keep the following items in mind:
Turn off the SQL Trace utility to run PeopleSoft Data Mover scripts.
If SQL Trace is enabled, disable it on the Trace tab in PeopleSoft Configuration Manager before you run the script. You can also enter the SET NO TRACE statement within scripts. This disables SQL Trace for the DMS script even if it is enabled in PeopleSoft Configuration Manager.
Records defined using the PeopleSoft Data Mover EXPORT and IMPORT commands can have a maximum of 500 columns, and they can have 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.
On DB2 UDB platforms, locks can occur on system catalogs.
Do not run unattended PeopleSoft Data Mover sessions. Close the session as soon as all scripts terminate.
To run a SQL script, you must open it by selecting File, Open so that the SQL runs properly.
Do not copy and paste SQL from another source into PeopleSoft Data Mover.
Note: If you plan to import or export files greater than 2 gigabytes (GB) on UNIX, you must enable large file support at the operating system level.
To run a script:
Select File, Open.
Select one of the following types of script to run.
PeopleSoft Data Mover files (.DMS).
These are the files created using PeopleSoft Data Mover.
Query files (.SQL).
These are the files created using the Build SQL functionality in Application Designer or using a query tool specific to a relational database management system (RDBMS), such as PL/SQL on Oracle.
All files.
Select to view all available files in a directory. Only .DMS and .SQL files are valid file types for PeopleSoft Data Mover.
Note: SELECT commands are not supported. When performing upgrades, use the SQL utility for the platforms to run .SQL scripts, not PeopleSoft Data Mover.
Select File, Run.
You can monitor the script's progress in the output pane, which reveals any error messages and displays the message Script Completed when processing has ended.