Using PeopleSoft Data Mover

This chapter provides overviews of PeopleSoft Data Mover and Data Mover scripts, and discusses how to:

• Use the development environment.

• Create and run PeopleSoft Data Mover scripts.

• Use the Database Setup utility.

• Use the PeopleSoft Data Mover command-line interface.

• Use PeopleSoft Data Mover commands.

• Use PeopleSoft Data Mover command modifiers.

• Use SET parameters.

• Use script examples.

Note. PeopleSoft supports a number of versions of UNIX and Linux in addition to Microsoft Windows. Throughout this chapter, we make reference to operating system configuration requirements. Where necessary, this chapter refers to specific operating systems by name. However, for simplicity the word UNIX refers to all UNIX-like operating systems, including Linux.

Understanding PeopleSoft Data Mover

This section discusses:

• PeopleSoft Data Mover overview.

• PeopleSoft Data Mover environment.

• PeopleSoft Data Mover operating modes.

PeopleSoft Data Mover Overview

PeopleSoft Data Mover enables you to:

• Transfer application data between PeopleSoft databases.

• Move PeopleSoft databases across operating systems and database platforms.

• Execute Structured Query Language (SQL) statements against any PeopleSoft database, regardless of the underlying operating system or database platform.

• Control database security and access.

• Create, edit, and run scripts.

  These scripts may include any combination of SQL commands and PeopleSoft Data Mover commands for exporting and importing data.

Note. Data in PeopleSoft databases generally can't be directly transferred between major releases using PeopleSoft Data Mover. For example, you can't import data from a PeopleTools 7.x database into a PeopleTools 8.x database.

PeopleSoft Data Mover Environment

There are two ways to run PeopleSoft Data Mover:

• Using the Data Mover development environment.

  This a graphical user interface (GUI), which runs only in Microsoft Windows. Use the Data Mover shortcut in the PeopleSoft program group. Select Start, Programs, your_PSFT_program_group, Data Mover.

• Using the Data Mover command-line interface.

  The command-line interface is intended mainly for UNIX servers. You run PeopleSoft Data Mover from a console in Microsoft Windows and from a telnet session in UNIX.

Note. PeopleSoft Data Mover runs in two-tier mode only. You must sign in to the database directly, not through an application server.

You set Data Mover environment variables in PeopleSoft Configuration Manager on Windows and in the psconfig.sh for UNIX.

See Also

Setting Up UNIX to Run PeopleSoft Data Mover

Data Mover Directories

PeopleSoft Data Mover Operating Modes

Operating modes determine how you are connected to the database. PeopleSoft Data Mover operating modes are:

• Regular mode.

  Most of the time, you use regular mode. To sign in to regular mode, enter your PeopleSoft user ID and password during sign-in. In regular mode, all commands are valid.

• Bootstrap mode.

  In bootstrap mode, you use a database access ID and password when signing in. Typically, you use bootstrap mode for database loading, because no PeopleSoft security tables are established yet. You also use bootstrap mode for running some security commands, such as ENCRYPT_PASSWORD.

  Note. In bootstrap mode, the following script commands are not valid: EXPORT and RENAME.

Understanding Data Mover Scripts

This section discusses:

• Data Mover script commands.

• Supported SQL commands.

• Data Mover commands compared to SQL commands.

• Data Mover COMMIT statements.

Data Mover Script Commands

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:

See Also

Using PeopleSoft Data Mover Commands

Using PeopleSoft Data Mover Command Modifiers

Using SET Parameters

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.

• 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 Meta-SQL Elements.

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.

Data Mover Commands Compared to SQL Commands

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.

PeopleSoft Data Mover COMMIT Statements

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.

Using the Development Environment

This section discusses how to:

• Sign in to the development environment.

• Navigate the Data Mover window.

Signing In to the Development Environment

To start PeopleSoft Data Mover in the Windows development environment:

1. Select Start, Programs, PeopleSoft Group, Data Mover.

   If you don't have a PeopleSoft Data Mover shortcut, you can add one to the desktop. The executable to launch is: PS_HOME\bin\client\winx86\psdmt.exe

2. Sign in using the appropriate ID and password.

   In regular mode, use a user ID and password. In bootstrap mode, use a system access ID and access password, such as SYSADM.

Navigating the Data Mover Window

The PeopleSoft Data Mover interface consists of two horizontal panes: an input pane and an output pane. The input pane is on top.

The status bar at the bottom of the window provides the following information:

• Database name (for example, QEDMO, PT850HR, and so on).

• Database type (for example, Oracle, Sybase, and so on).

• Operating mode (regular or bootstrap).

• Trace status (on or off).

The input pane displays the script that you open. In this pane, you view and edit PeopleSoft Data Mover scripts.

The output window displays the results after running a script. If you encounter any errors, the output window shows where the script failed. In a multidatabase environment, always check the information at the top of the output to ensure that you run the script against the appropriate database. Specifically, verify the information on the Database line.

Note. By default, the results in the output window are saved to the file DATAMOVE.LOG, which is written to the default log directory as specified in PeopleSoft Configuration Manager (on the Edit Profile, Common tab). You can specify a different file name.

The status of the SQL Trace utility appears on the right-hand end of the status bar. Use PeopleSoft Data Mover with tracing turned off. There are several ways to disable the SQL Trace utility (for the Microsoft Windows environment) before starting PeopleSoft Data Mover. You can use:

• PeopleSoft Configuration Manager.

• PeopleTools options.

• A Data Mover command (NO TRACE).

The operating mode on the status bar indicates either regular mode or bootstrap mode. If you connect to the database in regular mode, the operating mode status is blank. The operating mode is bootstrap if you sign in using the access ID and password.

Note. Verify the mode that you are using. Most commands require regular mode to run successfully.

See Also

Using SET Parameters

NO TRACE

PeopleSoft Data Mover Operating Modes

Creating and Running PeopleSoft Data Mover Scripts

This section provides overviews of command types and syntax rules and discusses how to:

• Create and edit scripts.

• Prepare to run export scripts.

• Run scripts.

Understanding Command Types

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.

Understanding Syntax Rules

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;

Creating and Editing Scripts

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:

1. Select File, New.

   When you first launch PeopleSoft Data Mover, a new file appears automatically. .

2. 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.

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

1. Select File, Open.

2. 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.

3. 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.

4. 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.

See Also

Understanding Data Mover Scripts

Understanding Syntax Rules

Preparing to Run Export Scripts

Before running a PeopleSoft Data Mover export script, you must first prepare the database.

To prepare for an export:

1. Load DDL model information by running all DDL*.DMS files through PeopleSoft Data Mover.

2. To change the DDL model information, use the DDL Model Defaults utility in PeopleTools Utilities.

3. Run DDDAUDIT.SQR and fix any errors that it finds.

4. Run SYSAUDIT.SQR and fix any errors that it finds.

5. 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.

6. Use the SQL Create function in Application Designer to create all records, using the If table exists ... Never recreate option.

See Also

Using PeopleTools Utilities

Ensuring Data Integrity

PeopleTools 8.51 PeopleBook: Application Designer Developer's Guide

Running Scripts

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:

1. Select File, Open.

2. 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.

3. 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.

Using the Database Setup Utility

This section discusses how to:

• Access the Database Setup utility.

• Work with the Database Setup utility.

• Check the generated script.

Typically, you use the Database Setup utility during PeopleSoft installations and upgrades, not on a daily basis. You use this utility to create PeopleSoft Data Mover import scripts that load data into a PeopleSoft database.

Note. If you are performing an installation, use the documentation included in your PeopleSoft installation guide, which provide specific details regarding your applications, languages, and RDBMS. This section provides a general overview and is not specific to the installation procedure.

Accessing the Database Setup Utility

To access the Database Setup utility:

1. Sign in to PeopleSoft Data Mover in bootstrap mode.

   Use the access ID and password rather than your PeopleSoft user ID and password.

2. Select File, Database Setup.

   Note. If you sign in to PeopleSoft Data Mover using regular mode, not bootstrap mode, the Database Setup menu item is not available.

Working with the Database Setup Utility

This section discusses the dialog boxes that make up the utility.

Database Setup

Select PeopleSoft Application

Database Parameters

Checking the Generated Script

After running the Database Setup utility, check the output directory for the generated script. Some commands are added that call other scripts and perform various functions. These commands are added to reduce the number of scripts and commands that you must run manually. For example, note that the following commands appear at the end of the script:

• REPLACE_VIEW

  This command creates views for the new database.

• CREATE_TEMP_TABLE

  This command creates any necessary temporary table images. The number of temporary tables is determined by the value for the Temp Table Instances setting in PeopleTools options (Utilities, Administration, PeopleTools Options) plus the number of PeopleSoft Application Engine temporary tables.

• SWAP_BASE_LANGUAGE

  If you selected a base language other than English, this command modifies the system to recognize that language as the base language. The default PeopleTools language is English if the PSSTATUS table is not available.

• RUN

  This command runs the CURRXXX.DMS script to load the system with the appropriate currency information, and it runs MSGTLXXX.DMS to load the system with the appropriate PeopleTools messages (error and informational messages). The XXX represents the language code, such as FRA for French. The system runs these scripts only if you have selected a base language other than English.

Note. After each DDL create table, import data, and DDL create indexes command, PeopleSoft Data Mover issues an UPDATE STATISTICS command (except on z/OS), which improves the performance of subsequent commands, such as the REPLACE_VIEW command.

See Also

CREATE_TEMP_TABLE

SWAP_BASE_LANGUAGE

Using the PeopleSoft Data Mover Command-Line Interface

This section provides an overview of the PeopleSoft Data Mover command-line interface and discusses how to:

• Set up UNIX to run PeopleSoft Data Mover.

• Set up tracing.

• Run Data Mover scripts from the command line.

Understanding the PeopleSoft Data Mover Command-Line Interface

The PeopleSoft Data Mover command-line interface enables you to run PeopleSoft Data Mover scripts from the command line in UNIX and Microsoft Windows environments. The command-line interface is designed only for running scripts, not creating and editing scripts. In Microsoft Windows, you create and edit scripts using the PeopleSoft Data Mover development environment. In UNIX, you can use any supported text editor.

When using the command-line interface, the results of the script run appear in the command-line window, much like the contents of the output pane in the PeopleSoft Data Mover GUI. The system also writes this information to the log file.

The PeopleSoft Data Mover command line supports the environment variable $PS_HOME on UNIX and %PS_HOME% on Windows.

Note. Although the command-line interface also runs on Windows machines, this documentation primarily discusses UNIX.

Important! The PeopleSoft Data Mover command line on UNIX is intended to increase performance with large database loads during installation. Use the PeopleSoft Data Mover Windows interface for other types of scripts.

Setting Up UNIX to Run PeopleSoft Data Mover

Before running the PeopleSoft Data Mover command-line interface on UNIX, verify that Tuxedo is installed. Tuxedo is required for PeopleSoft Data Mover to run on UNIX.

Next, configure the psconfig.sh shell script to set the UNIX and PeopleTools environment variables properly for Data Mover, then run the script. You must run it from the PS_HOME directory.

Note. The UNIX environment requires certain platform-specific environment variables. These variables are set automatically, but you can reconfigure the psconfig.sh script file to change their values.

UNIX Environment Variables

Data Mover environment variables for UNIX are stored in the psconfig.sh script. When you run psconfig.sh, several environment variables are automatically set to default values that reflect a standard PeopleSoft Data Mover install.

To modify them, you must edit psconfig.sh or manually change the environment.

$DM_HOME=$HOME/PS_DM

PS_DM_DATA_IN=$PS_HOME/data

PS_DM_DATA_OUT=$DM_HOME/data

PS_DM_SCRIPT=script_path;export 
PS_DM_SCRIPT

PS_DM_LOG=log_path;export PS_DM_LOG

Note. $DM_HOME/log is an environment variable for UNIX specifying a location to which Data Mover has write access in the case of a read-only PS_HOME configuration.

Note. If you want to perform tracing under UNIX, you must set additional environment variables.

See Setting Up Tracing.

Setting Up Tracing

To enable tracing for PeopleSoft Data Mover, you must set the PS_SERVER_CFG environment variable to point to the Data Mover configuration file, which contains parameters for tracing and character set:

• In Windows, you can set PS_SERVER_CFG in the System control panel, or issue the following command in a batch file or at a command prompt:

  set PS_SERVER_CFG=%PS_HOME%\setup\psdmtx.cfg

• In UNIX, edit psconfig.sh to include the following statement:

  PS_SERVER_CFG=$PS_HOME/setup/psdmtx.cfg;export PS_SERVER_CFG

To configure tracing for PeopleSoft Data Mover, you must edit the psdmtx.cfg file to specify the appropriate tracing behavior. Use the TraceSql bitfield parameter to set the level of the SQL trace by adding together the numeric values that represent each degree of tracing required. The values are defined as follows:

For example, if you want to trace Sybase bind and fetch information, enter:

TraceSql=768

After running PeopleSoft Data Mover, look for the generated trace log file in PS_CFG_HOME\log\APPSRV.LOG.

For UNIX, the TraceFile environment variable enables you to configure the trace file path and name. The system only uses the TraceFile value when TraceSql is set to a value greater than 0. The default value of TraceFile is DM_HOME/datamover.trc. For example,

TraceFile=%DM_HOME%/datamover.trc

Running Data Mover Scripts from the Command Line

The PeopleSoft Data Mover command line program is located as follows:

• Microsoft Windows: PS_HOME\bin\client\winx86

• UNIX: PS_HOME/bin

At a command prompt, change to the program directory and issue the psdmtx command with the appropriate parameters.

Standard Command Line Syntax

Use the following standard syntax to run most Data Mover scripts:

psdmtx -CT ​dbtype [-CS ​server] -CD ​database_name -CO ​user_ID -CP ​user_password
 [-CI ​connect_ID -CW ​connect_password] [-I ​process_instance] 
-FP ​dms_filepath

The value of each parameter follows the parameter name, separated by zero or more spaces. It doesn’t need to have quotation marks around it, even if it has internal spaces — the system treats all text following the parameter name as part of the value, up to the next parameter or the end of the command line.

The -CS server parameter is required only for the Informix and Sybase database platforms.

Note. You must enclose a value in quotation marks only when it includes a hyphen or forward slash, or to include leading or trailing spaces. If the value itself includes a quotation mark character, precede the double quote with a backslash (\).

To display a listing of all the command-line parameters and their arguments at the command prompt, enter:

psdmtx /help

Standard Command Line Parameters

The following table lists the standard command-line parameters and arguments for running the psdmtx command:

-CT ORACLE

-CS pt-sun05

-CD HR844DMO

-CO JPHAM2

-CP MYPASS

-CI people

-CW peop1e

-I %%INSTANCE%%

-FP $PS_HOME/scripts/
test.dms

psdmtx /help

Following is an example of a standard psdmtx command line on a UNIX system:

psdmtx -CT DB2UNIX -CD FS845A1 -CO PSOFT -CP PSOFT
 -CI people -CW peop1e -FP fs845a1dbo.dms

Using a Parameter File

Rather than submitting parameters manually on the command line, you can have PeopleSoft Data Mover read a file that contains appropriate parameters. Create a text file that contains a complete set of parameters as you would enter them on the command line.

If you submit a parameter file name and path to PeopleSoft Data Mover as the first parameter in the command line, PeopleSoft Data Mover reads the contents of the file and interprets them as parameters entered on the command line. For example:

psdmtx c:\dms\myparmfile.txt

Note. You must enter the full path to the parameter file.

Warning! For security reasons, after PeopleSoft Data Mover interprets the contents, it immediately deletes the parameter file.

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.

CHANGE_ACCESS_PASSWORD

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 ACCESSPSWD field from PSACCESSPRFL for the specified symbolic ID.

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

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

Parameters

LOG and NO TRACE

CREATE_TEMP_TABLE

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.

CREATE_TRIGGER

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.

ENCRYPT_PASSWORD

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 *;

EXPORT

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 *;

See Also

Using PeopleSoft Data Mover Command Modifiers, WHERE

IMPORT

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.

See Also

REPLACE_ALL

REPLACE_DATA

REM, REMARK, and – –

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.

RENAME

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;

REPLACE_ALL

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.

REPLACE_DATA

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.

REPLACE_VIEW

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.

RUN

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.

SET

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.

SET BASE_LANGUAGE

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.

SET IGNORE_ERRORS

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

SET COMMIT

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;

SWAP_BASE_LANGUAGE

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.

Using PeopleSoft Data Mover Command Modifiers

The following commands enable you to modify a PeopleSoft Data Mover command to limit its scope, rename the item being processed, or control error messaging.

AS

Syntax

{IMPORT | REPLACE_ALL} record
  AS table_name;

Description

Changes the name of a record and then imports it. When using this modifier, keep the following points in mind:

• If used with an IMPORT, the record is not imported if the table name specified in the IMPORT command already exists in the database.

• When using the AS command modifier, you can specify either the record or table name for the record or table specified preceding the AS.

  However, you must always specify the table name (not the record name) for the record or table specified following the AS. The name specified following the AS is the actual name that is used for the table to be created.

• This modifier is not supported for records containing trigger definitions.

Parameters

IMPORT and REPLACE_ALL

Example

The following example imports a new record or table originally named PS_JOB and creates it as PS_PROCESS:

IMPORT job
  AS ps_process;

Also correct:

IMPORT ps_job
  AS ps_process;

Incorrect:

IMPORT ps_job
  AS process;

Incorrect:

IMPORT job
  AS process;

The last two examples are incorrect because process is specified, instead of ps_process. This means that the table created is named PROCESS, but it should be named PS_PROCESS to comply with the convention that all non-PeopleTools tables have the prefix PS_.

The table name that you specify following 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.

When you import a record in this way, it is only created in the system tables, not in the PeopleSoft tables. You must also create the record in the PeopleSoft tables, such as PSRECDEFN.

To create a table after running the IMPORT command:

1. Launch Application Designer.

2. Create or clone the new record.

   Using the job and process example from the previous discussion, you would open JOB and then select File, Save As and rename the record to PROCESS.

   Note. The PS_ prefix does not appear in Application Designer.

3. Select Build, Current Object.

4. In the Build dialog box, select Create Tables under Build Options.

   You may also want to make sure that all the appropriate options are set on the Build Settings tabs.

IGNORE_DUPS

Syntax

SET IGNORE_DUPS;
IMPORT {record | *};

Description

Ignores duplicate-row error messages from the database. The IMPORT process continues despite any duplicate-rows errors in the output window and log file. When IGNORE_DUPS is set, bulk loading, the ability to load more than one row at a time, is turned off. By default, bulk loading is on and inserts up to 100 rows into a table at a time. Because turning off bulk loading slows performance, you should use this feature only when required.

Note. SET IGNORE_DUPS is only valid in bootstrap mode.

Parameters

IMPORT.

UPDATE_DUPS

Syntax

SET UPDATE_DUPS;
IMPORT {record | *};

Description

On command, PeopleSoft Data Mover imports a new row and updates an existing row.

Note. This command is valid for both bootstrap mode and regular mode. In regular mode, if the table is identified as a language table, the system automatically resolves and swaps the base and related language tables.

See IMPORT.

Parameters

IMPORT.

WHERE

Syntax

Data Mover Command {record | *} WHERE
  condition(s)[;var#1_type,_var#1_value,var#2_type,
var#2_value,...var#n_type,var#n_value];

Note. In an EXPORT statement, the WHERE modifier must be on the same line as the EXPORT command.

Description

Retrieves a partial set of rows from the data source. The data source could be a data file or the database. The syntax and conditions of a Data Mover WHERE clause are similar to a WHERE clause in SQL. You can write the WHERE clause with comparison operands in-line or as bind variables. You can also use nested SELECT statements.

Warning! When comparing string or character values, use only US-ASCII (seven-bit ASCII) values. Characters beyond this range can produce errors in the export file.

Parameters

EXPORT

IMPORT

Example

Here's an example of a WHERE clause using both an inline operand and bind variables in an EXPORT script:

EXPORT JOB WHERE
  EFFDT > :1 AND 
  HOURLY_RT > :2 
  AND GRADE = 'ADV';DATE,1994-01-01,NUMBER,100;

There are no single or double quotation marks around the bind data, as they are not necessary, and dates are formatted as YYYY-MM-DD. The valid data types for binding are CHAR, NUMBER, DATE, TIME, DATETIME, LONG, and IMAGE. Not all database platforms support LONG or IMAGE data types in the WHERE clause, so you should not use WHERE clauses with these data types.

The following operators are supported in an import WHERE clause: =, < >, <, >,< =, > =, and simple uses of AND and OR. For example, in the following formula, if A, B, and C are true, or if D is true, or if E is true, then the whole statement is true

WHERE 
  A = :1 AND B = :2 AND C = :3 
  OR D = :4 
  OR E = :5;NUMBER,10,NUMBER,20,NUMBER,30,NUMBER,0,NUMBER,1;

Using SET Parameters

The following parameters can be appended to a SET command to create a valid SET statement.

COMMIT

Syntax

SET COMMIT #of_rows;

Description

Sets the commit level only 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.

Parameters

IMPORT, REPLACE_ALL, and REPLACE_DATA.

CREATE_INDEX_BEFORE_DATA

Syntax

SET CREATE_INDEX_BEFORE_DATA;

Description

Creates the index before inserting rows into a record. The default method is to insert rows into a record and then create the index.

Parameters

IMPORT and REPLACE_ALL.

DBSPACE

Syntax

SET DBSPACE {old_dbname.old_spacename} AS {new_dbname.new_spacename};

Description

The DBSPACE command is similar to the SPACE command, but it is designed to handle the combination of DBNAME.DDLSPACENAME. On DB2 UDB, the DBNAME or DDLSPACENAME alone is not necessarily unique. However, the combination of the two (DBNAME.DDLSPACENAME) provides a unique relationship. For example, DBSPACE would be needed in the following scenario:

PSFSDMO.HRAPP
PSHRDMO.HRAPP
PSPTDMO.HRAPP

Note. This command is supported only on DB2 UDB for z/OS. You use this command in place of the SPACE command used on other platforms.

Parameters

IMPORTand REPLACE_ALL.

Example

The wildcard (*) character is permitted for the database name and space name parameters to apply to all values being processed for the specific parameter in which the wildcard character is used. The following are examples of using this command to achieve one of the following:

To change a specific DBNAME/DDLSPACENAME combination to a single new combination:

SET DBSPACE ​old_dbname.old_spacename AS ​new_dbname.new_spacename

To keep the current database name the same but change the specific space name to a new name:

SET DBSPACE *.old_spacename AS *.new_spacename

To keep the current space name the same, but change the specific database name to a new name:

SET DBSPACE ​old_dbname.* AS ​new_dbname.*

Warning! Because of the large number of objects delivered in the PeopleSoft logical databases, do not override all old database name or space name values to a single new database name or space name value when building a SYS or DMO database. However, this feature may be useful in working with smaller data files that contain a smaller number of objects.

For large databases, do not use the following commands:

SET DBSPACE *.* AS ​new_dbname.new_spacename
                
SET DBSPACE *.* AS *.new_spacename
                
SET DBSPACE *.* AS ​new_dbname.*

You can use multiple SET DBSPACE statements to override the space name in the .DAT file. This enables you to override multiple databases in the same section of the script. For example:

SET DBSPACE PSFSDMO.* AS MYFSDMO1.*;
SET DBSPACE PSFSDMOF.* AS MYFSDMO2.*;
SET DBSPACE PSFSDMOD.* AS MYFSDMO3.*;
SET DBSPACE PSFSDMOM.* AS MYFSDMO4.*;

DDL

Syntax

SET DDL {RECORD | INDEX | UNIQUE INDEX | SPACE} {object_name | *} 
  INPUT parm AS value;

Note. The object_name is only available for the SPACE option, not the RECORD, INDEX, and UNIQUE INDEX. The RECORD, INDEX, and UNIQUE INDEX are available for the *, not the object_name.

Description

Substitutes values for the parameters specified in the DDL template commands. Substitute the parm and value placeholders for an actual parameter and its value. If an asterisk is used instead of an object name, a SQL update on PSDDLDEFPARMS is performed on the parameter and value upon successful completion of the IMPORT or REPLACE_ALL command that corresponds to the SET DDL statement.

Parameters

IMPORTand REPLACE_ALL.

Example

Below are some examples of DDL template SET commands from a DB2 UDB import script:

SET DDL RECORD   *  INPUT dbname   AS ps910dg0;
SET DDL INDEX   *  INPUT stogroup   AS wps04sg;
SET DDL SPACE   *  INPUT stogroup   AS wps04sg;

EXECUTE_SQL

Syntax

SET EXECUTE_SQL [AFTER] sql_statement;

Description

Performs the SQL statement specified at the beginning of a transaction. Typically, this command is used to set up a specific cursor environment before PeopleSoft Data Mover begins processing. For example, in DB2 UDB, use this command to set the current setID, or for Oracle, use this command to designate a specific rollback segment.

This command doesn't run for DDL SQL statements. For example, in DB2 UDB, you cannot set the current setID before creating spaces, tables, indexes, or views.

Parameters

IMPORT, REPLACE_ALL, and REPLACE_DATA.

EXTRACT

Syntax

SET EXTRACT {COMMAND | DDL | INPUT | SPACE | OUTPUT file_name};

Description

Extracts various types of information from an export file (the DAT file specified in the corresponding SET INPUT command that precedes the IMPORT or REPLACE ALL command) and writes this information to the user-defined output file specified in the SET EXTRACT OUTPUT file_name statement.

Note. You must use SET EXTRACT OUPUT before issuing any other SET EXTRACT statements.

EXTRACT INPUT writes out any statements from the DAT file that are associated with the tables being imported. EXTRACT DDL writes out any CREATE TABLE, CREATE INDEX, or CREATE UNIQUE INDEX statements from the DAT file. EXTRACT COMMAND writes out the EXPORT statements from the DAT file.

When EXTRACT statements are issued, no SQL CREATE or INSERT statements are executed. The associated IMPORT or REPLACE_ALL command is not actually executed, so no import is performed.

Parameters

IMPORTand REPLACE_ALL.

IGNORE_DUPS

Syntax

SET IGNORE_DUPS;

Description

Ignores duplicate-row error messages from the database; the IMPORT process continues despite any duplicate-row errors displayed in the output window and log file. You can set this command for the entire import script or by record, using IGNORE_DUPS as a command modifier.

When IGNORE_DUPS is set, bulk loading, the ability to load more than one row at a time, is turned off (to allow checking for duplicates, so that duplicate rows can be ignored or bypassed). By default, bulk loading is on and inserts many (100) rows into a table at a time. Because turning off bulk loading slows performance, use this feature only when required or by record.

See IMPORT.

See IGNORE_DUPS.

Parameters

IMPORT.

Note. The command SET IGNORE_DUPS is only valid in bootstrap mode. This prevents the loss of data during a PeopleSoft Data Mover import of a language table in regular mode.

INPUT

Syntax

SET INPUT file;

Description

Specifies the name of the exported file to import; typically this file has a .DAT extension, though this is not a requirement. Because this statement is required to do an import, there is no default file.

If you don't specify a path for this file, PeopleSoft Data Mover searches for the file in the following locations in the order presented:

• It searches the Data Mover input directory as defined in PeopleSoft Configuration Manager on the Edit Profile, Common tab.

• If the input directory setting is blank (not set) on the Edit Profile, Common tab, PeopleSoft Data Mover searches the C:\TEMP directory.

Parameters

IMPORT, REPLACE_ALL, and REPLACE_DATA.

INSERT_DATA_ONCE

Syntax

SET INSERT_DATA_ONCE record;

Description

Skips (that is, bypasses importing) the specified record if there is already one or more rows in the table corresponding to that record. If the table is empty, only a single row is inserted.

Parameters

IMPORT, REPLACE_ALL, and REPLACE_DATA.

LOG

Syntax

SET LOG file;

Note. You must specify a file name for the SET LOG statement or else a log file is not created. If you do not want to specify a log file name, omit the SET LOG statement completely.

Description

Specifies a user-defined file name for the log file that is created when running a PeopleSoft Data Mover script or command. If the SET LOG statement is omitted completely, a default log file is created with the name DATAMOVE.LOG. PeopleSoft Data Mover writes this DATAMOVE.LOG file to the default log directory, which is DM_HOME\log.

The system uses the PeopleSoft Data Mover log directory specified on the Edit Profile, Common tab in PeopleSoft Configuration Manager. If the preceding setting is blank, the log file is written to C:\TEMP.

Note. If you use the SET LOG statement but do not specify a file name and path, PeopleSoft Data Mover writes the user-defined log file to the default log directory according to the same rule.

When checking the DATAMOVE.LOG file in a multidatabase environment, make sure you are examining the correct log file. At the top of the output file, verify the date and the database name.

Logging status in C:\TEMP\datamove.log
Started: Fri Mar 17 13:47:15 2001
Data Mover Release: 8.4
Database: HR702U40
... 
Ended: Fri Mar 17 13:47:20 2001
Successful completion

Parameters

All.

NO DATA

Syntax

SET NO DATA;

Description

During an export, the NO DATA command prevents data from being exported. In an import, this command prevents data from being inserted.

Parameters

EXPORT, IMPORT, and REPLACE_ALL.

NO INDEX

Syntax

SET NO INDEX;

Description

Prevents indexes from being created during an IMPORT or a REPLACE_ALL command.

Parameters

IMPORT and REPLACE_ALL.

NO RECORD

Syntax

SET NO RECORD;

Description

Prevents records from being created during an import

Parameters

IMPORT and REPLACE_ALL.

NO SPACE

Syntax

SET NO SPACE;

Description

Prevents tablespaces from being created. This is the default setting. You can use this statement to reset the default after executing a SET SPACE statement.

Parameters

IMPORT and REPLACE_ALL.

NO TRACE

Syntax

SET NO TRACE;

Description

Sets the PeopleSoft trace flag (TraceSQL) in PeopleSoft Configuration Manager to Off for the commands that follow, until the next SET statement. This is the recommended method of executing commands. If SET NO TRACE is specified, then no trace file is created, even if you specify a trace file in PeopleSoft Configuration Manager on the Trace tab. Commands that you run without specifying SET NO TRACE do trace SQL, if SQL tracing is enabled in PeopleSoft Configuration Manager.

By default, the trace file is written to DM_HOME\trace. The default trace file name is datamover.trc.

Note. This statement cannot be used with an INSERT command.

Parameters

All.

NO VIEW

Syntax

SET NO VIEW;

Description

Prevents views from being created.

Parameters

EXPORT * only, IMPORT * only, REPLACE_ALL * only, and REPLACE_DATA * only.

OUTPUT

Syntax

SET OUTPUT file;

Note. You must specify a file name for the SET OUTPUT statement or else an output file is not created. If you do not want to specify an output file name, omit the SET OUTPUT statement completely.

Description

Specifies a user-defined file name for the output file that is created by the corresponding EXPORT statement. If the SET OUTPUT statement is omitted completely, a default output file with the name DATAMOVE.DAT is created. The location that the output file is created is determined by the following:

• The system uses the PeopleSoft Data Mover output directory specified on the Edit Profiles, Common tab in PeopleSoft Configuration Manager.

• If the previous setting is blank, the output file is created in the C:\TEMP directory.

Note. If you use the SET OUTPUT statement but do not specify a file name and path, PeopleSoft Data Mover writes the user-defined output file to the default output directory.

Parameters

EXPORT.

SIZING SET

Syntax

SET SIZING_SET n;

Description

Specifies the sizing set number as defined on the DDL Model Defaults page. The default is 0. To use this parameter, the specified sizing set must be defined in the export file.

See Using PeopleTools Utilities.

Parameters

IMPORT and REPLACE_ALL.

SPACE

Syntax

SET SPACE old spcname AS new_spcname;

Description

Use for all operating systems other than z/OS.

Renames the default space names to customized space names. To name all record default space names to a single space name, substitute * for a space name.

Parameters

IMPORT and REPLACE_ALL.

Example

SET SPACE * AS PS;

START

Syntax

SET START [AFTER] record;

Description

Designates where in the export file to start the import process. The default is to start at the beginning of the file. To start immediately after a particular PeopleSoft record in the file, use SET START AFTER. This SET statement is useful for restarting a script after an error.

If the AFTER parameter is omitted, the import process starts at the record that is specified in the SET START statement. If the AFTER parameter is specified, the import process starts after the record specified in the SET START statement.

Note. If the same record name appears multiple times in the same DAT file, the SET START AFTER command begins after the last occurrence of the record name in the DAT file.

When you use the SET START command with REPLACE_VIEW and no DAT file specified, you designate at which (or after which) view in the database to start. Views are created in alphabetical order.

Parameters

IMPORT, REPLACE_ALL, REPLACE_DATA and REPLACE_VIEW.

STATISTICS

Syntax

SET STATISTICS { ON | OFF };

Description

Sets UPDATE STATISTICS to on or off. The default value is on. Set the value to off if you do not want to update statistics after an IMPORT. This command works only in bootstrap mode.

Parameters

IMPORT and REPLACE_ALL.

UNICODE

Syntax

SET UNICODE { ON | OFF }

Description

This command is recommended for use in bootstrap mode for an initial database load. It specifies whether the database is Unicode or non-Unicode.

Warning! If the database is already fully loaded, DO NOT use this command because it could result in the wrong value ENABLE_UNICODE flag being set on the PSSTATUS table.

Parameters

IMPORT and REPLACE_ALL.

VERSION

Syntax

SET VERSION sql_table.column condition;

Description

Verifies the version of the database for importing.

Parameters

IMPORT, REPLACE_ALL and REPLACE_DATA.

Example

Suppose that you state the following:

 SET VERSION PSLOCK.TOOLSREL="8.4"

PeopleSoft Data Mover verifies that the TOOLSREL column in PSLOCK equals 8.4. This avoids importing an export file into the wrong database. Use the SQL table name to indicate which PeopleSoft record to check.

Using Script Examples

This section provides several example script files. Review these scripts to see how you can use PeopleSoft Data Mover to accomplish various tasks.

Exporting Databases

Description

This example shows how to export a database.

Example

SET OUTPUT c:\temp\pt.dat;
SET LOG c:\temp\pt.log;
EXPORT *;

Building Databases

Description

This example shows how to build a database.

Example

set log c:\temp\hcengd.log;
set input c:\HRDMO\data\hcengd.db;
set no view;
set no space;
set no trace;
import *;
update PSLOCK set OWNERID = 'ownerid';
update PSOPRDEFN set ACCESSID = 'accessid', ACCESSPSWD = 'accesspw',
 OPERPSWD = '0000000000000000' where OPRTYPE = 0;
update PSACCESSPRFL set ACCESSID = 'accessid', ACCESSPSWD = 'accesspw',
 VERSION = 0, ENCRYPTED = 0;
set log c:\temp\grant.log;
encrypt_password *;

Recreating All Views

Description

This example shows how to recreate all views.

Example

SET LOG c:\temp\view.log;
REPLACE_VIEW *;

Importing with REPLACE_ALL with a Commit Level

Description

This example shows how to import with REPLACE_ALL with a commit level.

Example

SET INPUT c:\ptdvl\bin\exp2.dat;
SET LOG c:\ptdvl\bin\exp2.log;
SET COMMIT 2;
REPLACE_ALL employee_review;
REPLACE_ALL course_tbl 
   WHERE days_duration = :1 AND course_type > :2;number,1,char,C;
REPLACE_ALL absence_hist 
   WHERE return_dt > :1;date,1988-01-01;

Combining SQL Commands and IMPORT

Description

This example shows how to combine SQL commands and IMPORT.

Example

SET INPUT c:\ptdvl\bin\exp2.dat;
SET COMMIT 10;
SET START AFTER course_tbl;
SET IGNORE_DUPS;
DELETE FROM ps_absence_hist WHERE emplid = '8001';
IMPORT *;