Using PeopleSoft Data Mover

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

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.

Click to jump to parent topicUnderstanding PeopleSoft Data Mover

This section discusses:

Click to jump to top of pageClick to jump to parent topicPeopleSoft Data Mover Overview

PeopleSoft Data Mover enables you to:

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.

Click to jump to top of pageClick to jump to parent topicPeopleSoft Data Mover Environment

There are two ways to run PeopleSoft Data Mover:

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

Click to jump to top of pageClick to jump to parent topicPeopleSoft Data Mover Operating Modes

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

Click to jump to parent topicUnderstanding Data Mover Scripts

This section discusses:

Click to jump to top of pageClick to jump to parent topicData 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:

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

See Also

Using PeopleSoft Data Mover Commands

Using PeopleSoft Data Mover Command Modifiers

Using SET Parameters

Click to jump to top of pageClick to jump to parent topicSupported 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:

Standard SQL Commands with DMS Scripts

PeopleSoft Data Mover supports the following standard SQL commands:

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.

Click to jump to top of pageClick to jump to parent topicData 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.

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

--

Click to jump to top of pageClick to jump to parent topicPeopleSoft 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.

Click to jump to parent topicUsing the Development Environment

This section discusses how to:

Click to jump to top of pageClick to jump to parent topicSigning 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.

Click to jump to top of pageClick to jump to parent topicNavigating 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:

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:

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

Click to jump to parent topicCreating and Running PeopleSoft Data Mover Scripts

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

Click to jump to top of pageClick to jump to parent topicUnderstanding Command Types

A PeopleSoft Data Mover script can contain two types of commands:

Click to jump to top of pageClick to jump to parent topicUnderstanding 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:

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;

Click to jump to top of pageClick to jump to parent topicCreating 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

Click to jump to top of pageClick to jump to parent topicPreparing 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 SYAUD01.SQR and fix any errors.

  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.52 PeopleBook: Application Designer Developer's Guide

Click to jump to top of pageClick to jump to parent topicRunning Scripts

Through PeopleSoft Data Mover, you can run DDL, DML, and SQL scripts created with the following tools:

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:

To run a script:

  1. Select File, Open.

  2. Select one of the following types of script to run.

  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.

Click to jump to parent topicUsing the Database Setup Utility

This section discusses how to:

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.

Click to jump to top of pageClick to jump to parent topicAccessing 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.

Click to jump to top of pageClick to jump to parent topicWorking with the Database Setup Utility

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

Database Setup

Select Target Database

Select the RDBMS against which to run the database setup script. For instance, if the database that you are creating will run on an Oracle server, select Oracle.

Database Type

PeopleSoft supports non-Unicode (ANSI) and Unicode database types. Select the appropriate type for the system. For some RDBMS types, Unicode is not available.

Select Character Set

Select a character sets. Your choices vary depending on the database type that you selected.

Select PeopleSoft Application

PeopleSoft Application

Only the applications that you have licensed appear. Select the applications for which you want to create PeopleSoft Data Mover scripts. To add applications selectively, use the Add button. To add all applications available, use the Add All button.

Data Mover Scripts to Create

Use theRemove button to remove a single application, or use the Remove All button to clear the list box.

Database Type

Specify what the result of running the script should be. There are two database codes: PT for PeopleTools and EP for PeopleSoft applications. Options are:

  • Demo: Select to create a demonstration database.

  • System: Select to create a system database.

  • Add New Language: Select to add support of new languages to an existing database.

  • Add New Product: Select to add a new PeopleSoft product to the current system. With this option selected, only non-PT database codes appear.

Database Parameters

Database Name

Enter the name of the database against which to run the script. The database name that appears is the database to which you are currently signed on. If the script that you are creating will be run against another database, specify the appropriate name here. If you generate a script for a database other than the current database, the system uses a default database using the following convention: XXDMO for demonstration databases and XXSYS for system databases. The XX represents the product code, such as HR.

Symbolic ID

Enter the ID used as the key to retrieve the access ID and access password. For initial installation, set it equal to the database name.

Access ID

This ID is the RDBMS ID with which PeopleSoft applications are ultimately connected to the database once the PeopleSoft system validates the user or connect ID. It typically has all the RDBMS privileges necessary to access and manipulate data for an entire PeopleSoft application.

Access Password

Enter the password associated with the access ID.

Connect ID

This ID is used for the initial connection to the database. Any two-tier connection requires a connect ID. A connect ID is a valid user ID, that when used during logon, sign-in takes the place of PeopleSoft user IDs for the sign-in process.

Table Owner

(DB2 UDB for z/OS) This field populates the CREATOR field in the system catalog table SYSIBM.SYSTABLES. You determine the name of the table owner ID during the initial installation.

Index Storage Group

(DB2 UDB for z/OS) Enter the storage group where the index spaces are created.

Table Space Storage Group

(DB2 UDB for z/OS) Enter the storage group for tablespaces. This value must be the same as that used in the XXDDL.SQL script when you create tablespaces during the installation.

Click to jump to top of pageClick to jump to parent topicChecking 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:

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

Click to jump to parent topicUsing the PeopleSoft Data Mover Command-Line Interface

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

Click to jump to top of pageClick to jump to parent topicUnderstanding 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.

Click to jump to top of pageClick to jump to parent topicSetting 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.

Statement

Description

$DM_HOME=$HOME/PS_DM

Data Mover output, log file and trace file path. This environment setting needs to point to a writable location in case of a secure, read-only PS_HOME environment.

The default setting is $HOME/PS_DM

PS_DM_DATA_IN=$PS_HOME/data

Specifies the directory where PeopleSoft Data Mover searches for input data (.DAT) files. The default setting is $PS_HOME/data.

PS_DM_DATA_OUT=$DM_HOME/data

Specifies the directory where PeopleSoft Data Mover writes the output data (.DAT) files. The default setting is $DM_HOME/data.

PS_DM_SCRIPT=script_path;export PS_DM_SCRIPT

$PS_DM_SCRIPT specifies the location of the PeopleSoft Data Mover script files. The default setting is $PS_HOME/scripts.

PS_DM_LOG=log_path;export PS_DM_LOG

$PS_DM_LOG specifies the location of PeopleSoft Data Mover log files. The default is $DM_HOME/log, as in as in $HOME/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.

Note. If you have installed your PeopleSoft application outside of PS_HOME, make sure to consider the PS_APP_HOME location.

See Setting Up Tracing.

Click to jump to top of pageClick to jump to parent topicSetting 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:

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:

Bit Value

Type of Tracing

1

SQL statements.

2

SQL statement variables.

4

SQL connect, disconnect, commit and rollback.

8

Row Fetch (indicates that it occurred, not data).

16

All other API calls except ssb.

32

Set Select Buffers (identifies the attributes of columns to be selected).

64

Database API specific calls.

128

COBOL statement timings.

256

Sybase Bind information.

512

Sybase Fetch information.

4096

Manager information.

8192

Mapcore information.

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

Click to jump to top of pageClick to jump to parent topicRunning Data Mover Scripts from the Command Line

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

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:

Parameter

Argument

Example

–CT

Specify the database type. Valid values are DB2ODBC, DB2UNIX, INFORMIX, MICROSFT, ORACLE, and SYBASE.

Note. Notice the spelling of MICROSFT.

DB2ODBC is the database type for DB2 z/OS.

-CT ORACLE

–CS

(Optional) Specify the name of the database server for the database to which you're connecting.

Note. This parameter is required only if you specify INFORMIX or SYBASE as the database type.

-CS pt-sun05

–CD

Specify the name of the database to connect to, as you would when signing in to PeopleSoft.

-CD HR844DMO

–CO

Specify the PeopleSoft user ID you're using to sign in.

-CO JPHAM2

–CP

Specify the user password for the PeopleSoft user ID you specified.

-CP MYPASS

–CI

(Optional) Specify the connect ID used to connect to the database server.

Note. This parameter is required only if you're running PeopleSoft Data Mover in regular mode.

-CI people

–CW

(Optional) Specify the password for the Connect ID you specified.

Note. This parameter is required only if you're running PeopleSoft Data Mover in regular mode.

-CW peop1e

–I

(Optional) Specify the Process Scheduler process instance.

Note. This parameter is required only if you're running PeopleSoft Data Mover from PeopleSoft Process Scheduler. You generally enter the predefined meta-string %%INSTANCE%% in the process type definition, and PeopleSoft Process Scheduler inserts the correct value at runtime.

See Pages Used to Define Process Type Definitions.

-I %%INSTANCE%%

–FP

Specify the file name and path of the PeopleSoft Data Mover script to run.

-FP $PS_HOME/scripts/ test.dms

/help

No value required.

psdmtx /help

Note. When running scripts, be sure to consider your PS_APP_HOME location if you've installed your PeopleSoft application outside of PS_HOME.

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.

Click to jump to parent topicUsing 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.

Click to jump to top of pageClick to jump to parent topicCHANGE_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:

Parameters

LOG and NO TRACE

Click to jump to top of pageClick to jump to parent topicCREATE_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.

Click to jump to top of pageClick to jump to parent topicCREATE_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.

Click to jump to top of pageClick to jump to parent topicENCRYPT_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 *;

Click to jump to top of pageClick to jump to parent topicEXPORT

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

Click to jump to top of pageClick to jump to parent topicIMPORT

Syntax

IMPORT {record | *} [IGNORE_DUPS] [AS new_table_name] [WHERE conditions];

Description

The IMPORT command:

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:

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:

See Also

REPLACE_ALL

REPLACE_DATA

Click to jump to top of pageClick to jump to parent topicREM, 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.

Click to jump to top of pageClick to jump to parent topicRENAME

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.

  4. Select Build, Project.

  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.

  2. Select Build, Project.

  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;

Click to jump to top of pageClick to jump to parent topicREPLACE_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.

Click to jump to top of pageClick to jump to parent topicREPLACE_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.

Click to jump to top of pageClick to jump to parent topicREPLACE_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.

Click to jump to top of pageClick to jump to parent topicRUN

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.

Click to jump to top of pageClick to jump to parent topicSET

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.

Click to jump to top of pageClick to jump to parent topicSET 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.

Click to jump to top of pageClick to jump to parent topicSET 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

Click to jump to top of pageClick to jump to parent topicSET 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;

Click to jump to top of pageClick to jump to parent topicSWAP_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.

Click to jump to parent topicUsing 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.

Click to jump to top of pageClick to jump to parent topicAS

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:

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.

Click to jump to top of pageClick to jump to parent topicIGNORE_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

Click to jump to top of pageClick to jump to parent topicUPDATE_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.

Click to jump to top of pageClick to jump to parent topicWHERE

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;

Click to jump to parent topicUsing SET Parameters

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

Click to jump to top of pageClick to jump to parent topicCOMMIT

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.

Click to jump to top of pageClick to jump to parent topicCREATE_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.

Click to jump to top of pageClick to jump to parent topicDBSPACE

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

Click to jump to top of pageClick to jump to parent topicDDL

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;

Click to jump to top of pageClick to jump to parent topicEXECUTE_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.

Click to jump to top of pageClick to jump to parent topicEXTRACT

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.

Click to jump to top of pageClick to jump to parent topicIGNORE_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.

Click to jump to top of pageClick to jump to parent topicINPUT

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:

Parameters

IMPORT, REPLACE_ALL, and REPLACE_DATA.

Click to jump to top of pageClick to jump to parent topicINSERT_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.

Click to jump to top of pageClick to jump to parent topicLOG

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.

Click to jump to top of pageClick to jump to parent topicNO 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.

Click to jump to top of pageClick to jump to parent topicNO INDEX

Syntax

SET NO INDEX;

Description

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

Parameters

IMPORT and REPLACE_ALL.

Click to jump to top of pageClick to jump to parent topicNO RECORD

Syntax

SET NO RECORD;

Description

Prevents records from being created during an import

Parameters

IMPORT and REPLACE_ALL.

Click to jump to top of pageClick to jump to parent topicNO 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.

Click to jump to top of pageClick to jump to parent topicNO 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.

Click to jump to top of pageClick to jump to parent topicNO VIEW

Syntax

SET NO VIEW;

Description

Prevents views from being created.

Parameters

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

Click to jump to top of pageClick to jump to parent topicOUTPUT

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:

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.

Click to jump to top of pageClick to jump to parent topicSIZING 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.

Click to jump to top of pageClick to jump to parent topicSPACE

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;

Click to jump to top of pageClick to jump to parent topicSTART

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.

Click to jump to top of pageClick to jump to parent topicSTATISTICS

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.

Click to jump to top of pageClick to jump to parent topicUNICODE

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.

Click to jump to top of pageClick to jump to parent topicVERSION

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.

Click to jump to parent topicUsing 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.

Click to jump to top of pageClick to jump to parent topicExporting Databases

Description

This example shows how to export a database.

Example

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

Click to jump to top of pageClick to jump to parent topicBuilding 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 *;

Click to jump to top of pageClick to jump to parent topicRecreating All Views

Description

This example shows how to recreate all views.

Example

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

Click to jump to top of pageClick to jump to parent topicImporting 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;

Click to jump to top of pageClick to jump to parent topicCombining 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 *;