Using SET Parameters

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

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.

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.

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

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

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

IMPORT and 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;

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.

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.

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.

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.

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.

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.

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.

Syntax

SET NO INDEX;

Description

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

Parameters

IMPORT and REPLACE_ALL.

Syntax

SET NO RECORD;

Description

Prevents records from being created during an import.

Parameters

IMPORT and REPLACE_ALL.

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.

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.

Syntax

SET NO VIEW;

Description

Prevents views from being created.

Parameters

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

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.

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 System and Server Administration.

Parameters

IMPORT and REPLACE_ALL.

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;

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.

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.

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.

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.