image
Appendix
B
Data Mover Commands and Syntax
image his appendix contains a listing of common Data Mover commands and command syntax. In the syntax and example sections, the combination {|} represent a choice. For example, {record_name | *} means either record_name or *, but not both. Text inside [] represents an optional parameter. For example, [WHERE SQL] means the WHERE parameter is optional.
CHANGE_ACCESS_PASSWORD
Change the password associated with a Symbolic ID. For Oracle, Microsoft SQL Server, and Sybase, this command updates the Symbolic ID’s database password. All other databases require you to change the database password from within your database’s management tool. For all databases, this command updates the password in PeopleSoft’s metadata (the PSACCESSPRFL record).
Syntax
image
Parameters
■   symbolic_id   The symbolic ID you want to update.
■   new_password   The new password value for the database symbolic ID.
CREATE_TEMP_TABLE
This command creates temporary table instances for an Application Engine temporary table based on the number of instances requested in the Application Engine’s properties as well as the number of instances specified in the PeopleTools Options page.
Syntax
image
Parameter
■   record_name   Either an Application Engine record name (usually suffixed with TAO) to build temporary table instances for a single record definition or the wildcard * to build all temporary Application Engine tables.
CREATE_TRIGGER
This command creates database triggers.
Syntax
image
Parameter
■   record_name   Either the name of a record definition or the wildcard * to build triggers for all record definitions.
ENCRYPT_PASSWORD
This command encrypts the password for a user (or all users) within the PeopleSoft user profile (PSOPRDEFN).
Syntax
image
Parameter
■   OPRID   The PeopleSoft operator ID whose password will be encrypted. Specify the wildcard * to encrypt all PSOPRDEFN passwords.
EXPORT
This command exports the contents of a table (or all tables) to a file. Prior to using the EXPORT command, use the SET OUTPUT command to specify a destination file.
Syntax
image
Parameters
■   record_name   Either a record definition name or SQL table name to export a single table, or the wildcard * to export all tables.
■   WHERE SQL   Optional selection criteria identifying which rows to export from record_name. This parameter is not valid when using the wildcard * to export all tables.
IMPORT
This command imports data contained in a previously created export file. The target tables do not have to exist, because the IMPORT command will create missing tables and indexes based on metadata stored in the export file. When you’re copying data between two databases, however, the record definitions must be identical.
Syntax
image
Parameters
■   record_name   The name of a specific record contained in the export file or the wildcard * to import all record definitions contained in the export file.
■   IGNORE_DUPS   Optional modifier that tells Data Mover to ignore error messages when inserting duplicate rows as identified by a record’s unique index.
■   AS new_table_name   Optional parameter that requests Data Mover to import data into a table with a different name than the original table name. This parameter cannot be used with the wildcard *.
■   WHERE SQL   Optional parameter to import a subset of the rows contained in the export file. This parameter cannot be used with the wildcard *.
RENAME
This command renames a record or a field within the PeopleSoft metadata tables. It does not change the database structure. After using Data Mover to change the definition’s name, use Application Designer to rebuild the definition and all related dependencies.
Syntax
image
Parameters
■   RECORD record_name   The name of a record to rename.
■   FIELD field_name   The name of a field to rename, causing an update to all records that use this field.
■   FIELD record_name.field_name   Rename the field and update only one record definition.
■   AS new_name   The new name for the record or field.
REPLACE_ALL
This command is a variant of IMPORT that drops the target table before performing the import. The REPLACE_ALL command will re-create the target table and its indexes.
Syntax
image
Parameters
■   record_name   The name of a specific record contained in the input file or the wildcard * to import all record definitions contained in the input file.
■   AS new_table_name   Optional parameter that requests that Data Mover import data into a table with a name different from the original table name. This parameter cannot be used with the wildcard *.
REPLACE_DATA
This command is a variant of the IMPORT command. It deletes all data from the target table prior to import. It differs from the REPLACE_ALL command in that it does not change the structure of the target table.
Syntax
image
Parameter
■   record_name   The name of a specific record contained in the input file or the wildcard * to import all record definitions contained in the input file.
REPLACE_VIEW
This command drops and re-creates one or all views defined in the PeopleSoft record metadata.
Syntax
image
Parameter
■   record_name   The name of a specific view record or the wildcard * to re-create all views.
RUN
This command runs another Data Mover script from within the current Data Mover script. The requested Data Mover script must reside in the same file directory as the currently running Data Mover script.
Syntax
image
Parameter
■   data_mover_file_name.dms   The name of the Data Mover file to execute.
SWAP_BASE_LANGUAGE
This command changes the base language and updates the appropriate PeopleSoft metadata.
Syntax
image
Parameter
■   language_cd   Three-letter target language code.
SET Statements
The SET statement alters Data Mover’s default behavior and provides Data Mover with additional information where necessary. SET statements grouped together before a command alter the way Data Mover executes the following commands. A SET statement following a command invalidates all prior SET statements.
SET BASE_LANGUAGE
Use this SET statement prior to the command SWAP_BASE_LANGUAGE record_name to swap the base language of a specific record definition only if a standard SWAP_BASE_LANGUAGE command failed.
Example
image
SET COMMIT
Use this SET statement to specify the commit interval when inserting rows into the database. Managing the commit interval is important for performance. A commit interval that is too large may cause large updates to fail due to temporary space constraints. A commit interval that is too small may reduce performance. A commit interval of 0 will commit after processing all of the rows for a record definition. Be aware that some database platforms will perform an implicit commit when executing data definition language (DDL) statements.
Example
image
SET CREATE_INDEX_BEFORE_DATA
When importing data using the REPLACE_ALL command, Data Mover’s default is to import data and then create indexes. This SET statement directs Data Mover to create indexes before importing data. Creating indexes first may reduce performance as the database alters index metadata for each row.
Example
image
SET DDL
This SET statement allows you to import definitions into containers that differ from the input file. For example, use this SET statement to import all indexes from the input file into a different tablespace.
Example
image
SET EXECUTE_SQL
Use this SET statement to execute an SQL statement prior to performing an IMPORT command. This statement was used with Oracle versions prior to 10g to manage rollback segments. It is not necessary with 10g and later versions when using undo segments.
Example
image
SET EXTRACT
Use this SET statement to extract DDL information from an input file.
Example
image
SET IGNORE_DUPS
This SET statement tells Data Mover to ignore duplicate row warnings. Duplicate row errors are thrown by the database on insert when a row has a unique index and a Data Mover import attempts to insert a new row that violates the unique index constraint. This statement turns off bulk imports, causing a row-by-row insert.
Example
image
SET IGNORE_ERRORS
Use this SET statement with the SWAP_BASE_LANGUAGE command to allow the command to continue even if it encounters errors. If you use this SET statement, review the log, resolve errors, and then run the SET BASE_ LANGUAGE; SWAP_BASE_LANGUAGE record_name; combination.
Example
image
SET INPUT
Use this SET statement to specify the name of an export file for IMPORT commands or EXTRACT statements.
Example
image
SET INSERT_DATA_ONCE
This SET statement tells Data Mover to execute an IMPORT only if the target table is empty.
Example
image
SET LOG
Use this SET statement to specify the name and location of the Data Mover log file.
Example
image
SET NO DATA
Use this SET statement to prevent data from being imported or exported.
Example
image
SET NO INDEX
Use this SET statement to prevent Data Mover from creating indexes during an IMPORT operation.
Example
image
SET NO RECORD
Use this SET statement to tell Data Mover to skip creating records when importing data.
Example
image
SET NO SPACE
Use this SET statement to prevent Data Mover from creating tablespaces when importing data.
Example
image
SET NO TRACE
Use this SET statement to turn off tracing options that were previously set in Configuration Manager.
Example
image
SET NO VIEW
Use this SET statement to keep Data Mover from creating views.
Example
image
SET OUTPUT
Use this SET statement to identify the output file for EXPORT commands. You need to use this SET statement only once. Multiple definitions can be added to the same output file.
Example
image
SET SPACE/DBSPACE
Use this SET command to import data into a different tablespace than what was specified in the input file. Use the DBSPACE statement with DB2 for z/OS. Use the SPACE statement for all other database platforms.
Example
image
SET START
Use this SET statement to restart an import when a prior import failed. This statement tells Data Mover where to restart the import. This statement may also be used with the REPLACE_VIEW command to restart a failed REPLACE_VIEW command.
Example
image
SET STATISTICS
When running an IMPORT command in bootstrap mode, use this SET statement to tell Data Mover whether or not to update database statistics.
Example
image
SET UNICODE
Use this SET statement in bootstrap mode for the initial database load.
Example
image
SET UPDATE_DUPS
Use this SET statement to cause Data Mover to import new rows only, updating rows that already exist.
Example
image
Standard SQL Commands
You can use the following standard SQL commands within any Data Mover file:
 
■   ALTER
■   COMMIT
■   CREATE
■   DELETE
■   DROP
■   GRANT
■   INSERT
■   ROLLBACK
■   TRUNCATE
■   UPDATE
Nonstandard SQL Commands
PeopleSoft uses the following nonstandard SQL commands to manipulate COBOL SQL statements.
STORE
The STORE command inserts COBOL SQL statements into the PS_SQLSTMT_ TBL table, deleting duplicates prior to insertion.
Syntax
image
The parameter is a concatenation of the program name, SQL type, and the statement name.
Example
image
ERASE
Use the ERASE command to delete a stored COBOL SQL statement or to delete all stored SQL statements for a specific COBOL program.
Syntax
image
Example
image
Meta-SQL Statements
Data Mover supports the following Meta-SQL statements.
CURRENTDATEOUT
Use this Meta-SQL statement anywhere that you require the database date as an output value—for example, in a SELECT list or column UPDATE statement.
Example
image
CURRENTTIMEOUT
Use this Meta-SQL statement anywhere that you require the database time as an output value—for example, in a SELECT list or column UPDATE statement.
Example
image
CURRENTDATETIMEOUT
Use this Meta-SQL statement anywhere that you require the database date/time as an output value—for example, in a SELECT list or column UPDATE statement.
Example
image
DATEIN
Use this Meta-SQL statement in a WHERE clause when specifying a plain text date.
Example
image
TIMEIN
Use this Meta-SQL statement in a WHERE clause when specifying a plain text time.
Example
image
Conclusion
In this appendix, you learned how to use Data Mover’s commands and statements to write Data Mover scripts.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset