Understanding Data Administration and the Build Process

Using PeopleSoft Application Designer, you create several kinds of definitions that represent database components. For instance, field definitions represent table columns, and record definitions represent tables and views. From these field and record definitions, you can create tables, views, and indexes. The important concept to remember is that the definitions are just the blueprints for the actual, physical database components with which they are associated. In the context of the entire application development cycle, use the Build process to create SQL tables, views, triggers, and indexes after you define new fields and create the record definitions. After you build the SQL tables, you begin creating page definitions.

After you create and define your field and record definitions, you must create or build the underlying SQL tables that house the application data that your users enter online in your production environment. The Build process is the centerpiece of the data administration features in PeopleSoft Application Designer. The Build process uses DDL to construct a physical database component that is based on the associated record and field definitions that you created. Using the Build feature, you can create:

  • Tables.

  • Indexes.

  • Views.

  • Triggers.

You can also use the Build feature to alter existing tables if you change the record definition after the table already exists. Altering a table is useful, because it enables you to make changes without losing the application data that is already housed in the table. In general, the results of the build operation are written to a script file that a database administrator can run later. On some database platforms, you can run the SQL online, if you prefer, so that your changes are immediately reflected in the physical database. However, if you run the SQL immediately, you cannot review it to make sure that the table that was built truly meets all of your requirements.

Before you begin using the data administration tools and running a build process, make sure you:

  • Review what DDL means for PeopleSoft applications.

  • Grant build authority.

Reviewing DDL

DDL is the part of SQL that pertains to the creation of tables, indexes, views, triggers, and tablespaces. DDL is also the part of SQL that differs most between the various relational database platforms. Each database vendor provides different syntax and configuration options for creating and organizing tables and for optimizing performance. Because PeopleTools supports multiple database platforms, PeopleSoft developers designed a flexible way of specifying DDL that enables you to take advantage of each vendor’s features. 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.

  • Record DDL: 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 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.