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 OUTPUT 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
IMPORT and 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 multi database 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.
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.