July 20, 2009

PS Technical: Administering Data

• Use the Build menu.
• Create tables.
• Create indexes.
• Alter tables.
• Create triggers.
• Create views.
• Administer DDL.

1. Understanding Data Administration and the Build Process

Reviewing DDL
The basic components of the PeopleSoft DDL support include the following:
  • DDL model definition: A complete set of the supported DDL statements for each database platform. Statements include Create Table, Create Tablespace, and Create Index. Each DDL model statement has substitution parameters that can be specified at the database level or overridden for individual records.
  • RecordDDL: Specify the DDL model substitution parameters for an individual record.
  • Index DDL: Specify the DDL model substitution parameters for an index.
  • Sizing sets: A way to maintain multiple versions of your DDL model statements for a database platform. For example, you can use one sizing set during a development phase, when tables have only test data, and another during production, when tables have much more data.
Granting Build Authority
PeopleSoft Security enables you to specify which users can build scripts, run scripts (Execute SQL now), maintain DDL, and so on. Access Security from the PeopleTools link in the menu.

Note: The Execute SQL now option is unavailable if you use the Alter Tables Build option. However, you can use the Execute and build script option when using Alter Tables. Selecting this option builds a script with the entire alter SQL. This script may help if errors are encountered during the alter process. However, if there are multiple tables in an alter process and an error occurs, the errors are reported to a log file and the process continues to the next table.

Using Command Line Syntax to Build a Project
  • Connect the project to the source database.
  • Use the build settings from the Windows registry to generate the SQL script for the create or alter processes.
  • Before running the build command line, verify that, the project’s system components are created and loaded and the build options are set in the Windows registry.
  • Available Parameters: HIDE (used to hide the PeopleSoft Application Designer interface), CT (Database type), CS (Server name), CD (Database name), CO (User ID), CP (User password), and PJB ( Project name).
  • Example: PSIDE.EXE −CT MICROSFT −CD CJR810G −CO PTDMO −CP PTDMO −PJB CJR_PRJ
2. Creating Tables
  • Because long character fields operate more efficiently in some database environments when they’re located at the end of the table, the Build process automatically positions long character fields at the end of the table for each database environment.
  • After you build an updated SQL build script and run it, the PeopleTools tables and the system catalog tables are synchronized; the record definition and the application data table are synchronized.
  • Create Indexes: Key fields -> Unique index: (_); Alternate search key fields -> Alternate index: 10 (0-9) per table; Custom indexes -> Custom index: (A-M) per table
  • Note: You can have a maximum of 500 columns per table.
3. Creating Indexes
  • Configure indexes.
  • Add a custom index.
  • Edit the DDL.
4. Altering Tables

to be continued.............

No comments:

Post a Comment