Administering DDL
Select Data Administration from the Tools menu in PeopleSoft Application Designer to access critical dialog boxes that enable you to define the record location and structure and other guidelines for PeopleTools to extract information from your selected database.
This section discusses how to:
Use the record DDL.
Set the tablespace.
Use physical data storage.
Manage partition DDL.
Use the record DDL to define parameters and default values for the tables in your database. PeopleSoft applications provide templates for each database platform that PeopleTools supports. The templates contain the typical parameters for each database platform. The mechanics of editing and viewing record, index, and unique index DDL are the same as for space DDL.
Access the Maintain Record DDL dialog box from The Data Administration menu option is only enabled if a record definition is open.
Image: Maintain Record DDL dialog box
This example illustrates the fields and controls on the Maintain Record DDL dialog box. You can find definitions for the fields and controls later on this page.
|
Field or Control |
Definition |
|---|---|
| View DDL |
Select a Platform row in the Maintain Record DDL dialog box to enable the View DDL button. This button opens the DDL Statements dialog box, which displays the CREATE TABLE DDL for the current record on each of the database platforms. |
| Edit Parm (edit parameter) |
Select a Parameter row in the Maintain Record DDL dialog box to enable the Edit Parm button. This button opens the Edit Override Parm Value dialog box, where you can enter a new value to override the Default Value listed in the table. The new value is stored in the PSRECDDLPARM override table. |
The Change Space dialog box is an editing tool with which you can select the space name and view the records attributed to the named space in the database.
Access this dialog box by selecting This dialog box also appears automatically when you attempt to save a new record definition. For each new record definition that is created in PeopleSoft Application Designer, you must allocate a tablespace name. For the Change Space dialog box to appear upon saving a new record definition, you must select the Platform Compatibility Mode check box under .
Image: Change Space dialog box
This example illustrates the fields and controls on the Change Space dialog box. You can find definitions for the fields and controls later on this page.
|
Field or Control |
Definition |
|---|---|
| Record Name |
Displays the name of the current record definition. This field is display-only. |
| Platform |
Select from the available database platforms to which you can assign the designated space name. |
| Space Name.DB Name (tablespace name.database name) |
Displays the available space name that is assigned to the current record. This field is display-only. |
| Available Space Name-DB Name (available tablespace name-database name) |
Select the space name and database name to assign to the record. |
| Records Allocated to this Space |
Displays all of the records that are already allocated to the space that you selected from the Available Space Name-DB Name drop-down list box. This field is display-only. |
You have control over the physical storage of your data. You can view and edit the DDL for creating tables, indexes, and tablespaces in the browser.
Access the DDL Model Defaults page (.)
Image: DDL Model Defaults page
This example illustrates the fields and controls on the DDL Model Defaults page. You can find definitions for the fields and controls later on this page.
To view or edit the DDL parameters for creating tables, indexes, and tablespaces:
Open the component.
Press the Enter key to view platform names.
Select a platform name.
The DDL Model Defaults page appears.
The Model SQL edit box shows the DDL template for the specified platform and sizing set. The items in square brackets are special parameters that are populated when instances of SQL are generated. For example, the preceding example shows an index model statement for ALLBASE. In this example:
[TBNAME] is the name of the table.
[TBCOLLIST] is replaced by the columns that are specified in the index definition.
The model statements also contain parameter names enclosed in pairs of asterisks. A parameter name is replaced by a value when instances of the SQL are generated. The preceding example has one parameter, **FILESET**. When DDL is generated using this model, the FILESET parameter is replaced by FILE unless it is overridden for the specific record or tablespace. The other text in the model statement is copied to the generated SQL.
On Oracle databases, you can implement Oracle Partitioning.
Partitioning enables you to subdivide tables and indexes into smaller pieces, such that the database system can access and manage the partitioned objects at a finer level of granularity. This provides for more efficiency for administration with faster backups, for example, and for better transaction performance with queries being able to isolate the relevant data more quickly through partitions.
To mange partition DDL, in Application Designer select
Image: Maintain Partitioning DDL dialog box
This example illustrates the use of the Maintain Partitioning DDL dialog box for managing partitioning DDL. Descriptions of the fields and controls follow the example.
Important! PeopleTools performs no validation on SQL entered in the Table or Index partitioning DDL edit boxes. You must validate the syntax on your own.
Details for implementing this feature are covered in the product documentation for PeopleTools: Data Management.