Administering Data

This chapter provides an overview of the PeopleSoft Application Designer data administration and the Build process and discusses how to:

Click to jump to parent topicUnderstanding 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 Structured Query Language (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. This build process is the centerpiece of the data administration features in PeopleSoft Application Designer. The Build process uses Data Definition Language (DDL) to construct a physical database component that is based on the associated record and field definitions that you created. With the build feature, you can create the following:

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 to:

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 has 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:

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.

See Also

Setting PeopleTools Permissions

Click to jump to parent topicRunning the Build Process

This section provides an overview of the Build menu and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding the Build Menu

Access all of the commands and options that are associated with building tables, views, indexes, triggers, and altering tables from the Build menu. When you choose to build an item from the Build menu, the tasks for creating or running SQL scripts that define the underlying database components are included in the Build dialog box.

Build dialog box

The settings for the Build process on the Build Settings dialog box enable you to configure various aspects of the process. Depending on which build option you are running, your settings vary. The Build Settings that you select are stored on your workstation so that they can be reused during future PeopleSoft Application Designer sessions. Access the Build settings by selecting Settings from the Build menu in PeopleSoft Application Designer or click theSettings button in the Build dialog box.

Build Settings dialog box

When you select the Create Tables, Create Views, or Create Indexes options in the Build dialog box, specify the appropriate options on the Create tab of the Build Settings dialog box such as if you need to specify whether an existing table, view, or index is skipped or dropped and re-created.

If you modify your record definitions, the tables in your production database should reflect those changes to maintain your data integrity. When you alter a table, select the appropriate options, such as the options in the Drop Column Options area on the Alter tab of the Build Settings dialog box.

You can monitor the Build process by reviewing the log files that it automatically generates. Keep in mind that the log file is entirely separate from the script file. The amount of information that the log file contains is up to you. You can set up logging so that all statuses (both good and bad) appear in the log, or you can specify that only errors or warnings appear in the log. Control the settings for the Build log file (such as the logging level and the location for the output log file) on the Logging tab of the Build Settings dialog box.

If you build a script file, then the Build process generates a script file that contains all of the CREATE and ALTER SQL statements so that you can review them before running the SQL through another SQL command processor. If the generated script file meets your requirements, a database administrator (DBA) can run the script later. The Build process can produce multiple scripts during a single run—one for each build option—depending on the script settings that you specify at runtime. For example, you can specify that the Build process generate a separate script for your tables, views, indexes, triggers, and alters, or you can have all of the SQL for each action contained in one script. Use the Scripts tab of the Build Settings dialog box to specify where the SQL script is written, whether you want multiple scripts generated for each definition type, and whether you want previous scripts overwritten.

Click to jump to top of pageClick to jump to parent topicSelecting the Build Scope

The Build feature includes three different scopes that determine the size of your build project. Depending on what you actually want to build—one record or an entire project—you can determine this by selecting one of the scopes described in the following sections.

Current Definition

Select Current Definition from the Build menu to build or alter the active record definition. Active definition means the definition that is currently active in the definition workspace.

Project

Select Project from the Build menu to build or alter all of the records, indexes, triggers, and views in the current project—that is, all of the records that appear in the project workspace. You typically find numerous record definitions in the Build Scope list box.

To build all definitions in a database, create a new project and insert all of the appropriate records. For example, insert records of a particular type, such as views. If you create another record after the all records project is built, you must manually add the new record to the project.

You may also build a project using the command line and specific parameters. The prerequisites for using the command line to build a project are:

Note. The Build Project process accesses every single record that is in the project, including work records, even if they are marked to be deleted.

Select Definitions Into Project

In some situations, you might want to build a subset of the records in the project that is currently open. If you do not want to build an entire project or even the current project, you can build only the definitions that you select in the project workspace by pressing the ctrl key and selecting multiple records in the project workspace. After you select the appropriate records, right-click and select Build from the pop-up menu. Only the definitions that are selected in the project workspace appear in the Build Scope list box.

Note. You must invoke the Build process from the pop-up menu; you cannot build selected definitions from an open project using the Build menu.

Click to jump to top of pageClick to jump to parent topicSelecting Build Options and Running the Build Process

Access the Build dialog box.

Build Scope

This list box displays all of the definitions that are included in the current Build process. The records that appear in this list box cannot be selected or cleared. If you must narrow the scope of your build, do so before accessing the Build dialog box.

Build

Click this button to start the Build process. While the Build process is running, the Build Progress dialog box appears, showing the current record being processed, the record number being processed in the series, and the total number of records that comprise the build. To stop a running Build process, click the Cancel button. When the Build process completes, the Cancel button is unavailable and the Close button is the only valid button.

Settings

Click this button to open the Build Settings dialog box, where you can view or change your options.

Note. After the Build process begins, there are no runtime prompts until the process is complete. All events are written to the Build log.

Build Options

The Build Options group box enables you to specify what action you want to occur: Create Tables, Create Indexes, Create Views, Alter Tables, or Create Trigger. If you select Create Tables or Alter Tables, Create Indexes is automatically selected by default. Also, if you select Alter Tables, Create Indexes and Create Trigger are automatically selected by default.

Note. You can select both Alter Tables and Create Tables to run concurrently, as long as the Skip table if it already exists option is enabled on the Create tab in the Build Settings dialog box. Otherwise, there is no way to determine whether you wanted to alter or re-create a table that already exists.

Build Execute Options

Build script file

Select this option to review and update the SQL before running the script. This is the safest method.

Execute SQL now (execute Structured Query Language now)

Select this option if you don’t want to invoke another program to run the SQL; the SQL runs as part of the Build process. The disadvantage with running the SQL immediately is that you have no opportunity to review the SQL before it is committed to the database.

Execute and build script

Select this option to review the SQL that the Build process just ran.

To run the SQL script file that the Build process generates, you must use a third-party SQL interpreter, such as Oracle SQL*Plus or Microsoft ISQL/W. Most database vendors include a native command processor that you can run on the client or the server.

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.

Click to jump to top of pageClick to jump to parent topicUsing Command Line Syntax to Build a Project

Use the same command line syntax as identified previously to build a project.

See Understanding PeopleSoft Application Designer Command Line Parameters.

Use the command line statement for the project build to:

Before running the build command line, verify that:

Available Parameters

This table lists all of the available parameters for the project build statement:

Parameter

Description

Error Handling

-HIDE

Use to hide the PeopleSoft Application Designer interface. Always use this parameter when performing a command line Copy process.

Required for all project command line processes.

-CT

Database type.

Enter the type of the database to which you are connecting (MICROSFT, ORACLE, SYBASE, and so on).

Required. If you omit this parameter, the last database type is taken from the registry. If it fails, the process stops and error messages are written to the log file.

-CS

Server name.

Enter the name of the source database server for the database to which you are connecting.

Required for some database types. If you omit this parameter, the process stops and error messages are written to the log file.

-CD

Database name.

Enter the name of the source database to which you are connecting, as you would enter it into the PeopleSoft Signon dialog box.

Required. If you omit this parameter, the process stops and error messages are written to the log file.

-CO

User ID.

Enter the PeopleSoft user ID that is needed to sign in to the source database.

Required. If you omit this parameter, the last database type is taken from the registry. If it fails, the process stops and error messages are written to the log file.

-CP

User password.

Enter the password for the specified user ID for the source database.

Required. If you omit this parameter, a PeopleSoft Signon dialog box appears for the user to enter a valid user password. If the password fails, the process stops and error messages are written to the log file.

-PJB

Project name.

Enter the name of the project to be built. This project should be available in the database before starting the command line project build.

Required. This is the main parameter and is used internally to decide whether the user can build a project. If you omit this parameter and if you supply all of the source database login parameters, the program only starts the application.

Example

For this example, assume that the:

For this example, you would enter the following at the command line:

PSIDE.EXE −CT MICROSFT −CD CJR810G −CO PTDMO −CP PTDMO −PJB CJR_PRJ

Click to jump to parent topicCreating Tables

This section provides an overview of the Create Table process and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding the Create Table Process

The Build process generates the appropriate SQL Create statements to build tables based on both your record definition and your platform. It prefaces each new application SQL table with PS_ to identify it as an application that was built using PeopleTools. For example:

PS_ABSENCE_HIST

Note. PeopleTools does not preface table names with PS_ if you specified a custom name in the Non-Standard SQL Table Name edit box located on the Type tab of the Record Properties dialog box.

The Build process also creates the appropriate indexes. 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.

The Create Table process creates a new application table based on parameters defined in the record definition. When a new table is created, the database management system updates the system catalog tables to reflect the attributes of the new table.

The Create Table process is illustrated by the following figure:

Create Table process

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. The following table lists the steps of the Create Table process and the associated record definition parameters:

Create Table Process

Record Definition Parameter

Usage

Drop the table if it already exists.

 NA

NA 

Create the application data table.

Record definition name.

Field names.

Field type.

Field length.

Table name (add PS_ prefix).

Column names.

Column type.

Column length.

Create indexes.

Key fields.

Alternate search key fields.

Custom indexes.

Unique index: (_)

Alternate index: 10 (0–9) per table.

Custom index: (A-M) per table.

The following procedure covers all of the high-level steps that you need to successfully complete the Create Table build process. Steps that involve a variety of options include links to the area where those options appear in the PeopleBooks.

Note. You can have a maximum of 500 columns per table.

Click to jump to top of pageClick to jump to parent topicCreating a Table

To create a table:

  1. Open the project for which you want to build SQL tables.

  2. Select Build, Project.

  3. From the Build menu, select the appropriate scope of your build.

    See Selecting the Build Scope.

    The Build dialog box appears.

  4. Select Create Tables in the Build Options group box.

    PeopleSoft automatically activates the Create Indexes option. If you’re creating tables, the indexes that are used to extract information from those tables must also be updated. Definitions to be built appear in the Build Scope list box, which does not allow you to edit, remove, or add definitions.

  5. Select one of the Build Execute options.

    The default option builds an SQL script file containing the commands to run the selected build options.

  6. Click the Settings button in the Build dialog box to set user-defined defaults.

    The Create tab in the Build Settings dialog box appears.

  7. Select the appropriate settings on the Create tab.

    See Specifying Create Options.

    Re-creating an existing table removes all data contained in the table as well as views or grants referencing that table.

  8. Select the Logging tab.

  9. Set the Logging Levels and associated options.

    See Specifying Logging Options.

  10. Select the Scripts tab.

  11. Specify the script file options.

    If you selected Execute SQL Now as your build option in the Build dialog box, you can skip the Scripts tab, because you will be running the SQL online. Settings on the Scripts tab are relevant only if you are building a SQL script to run later.

    See Specifying Script Options.

  12. Click OK to close the Build Settings dialog box.

  13. In the Build dialog box, click the Build button to run your build option.

    The length of a build process depends on the number of definitions that require building. Watch the Build Progress dialog box to monitor the Build process. When the process completes, check any errors listed in the log file.

Click to jump to top of pageClick to jump to parent topicSpecifying Create Options

Access the Build Settings dialog box. Select the Create tab.

Table Creation Options

These options determine when a table should and should not be created.

Recreate table if it already exists

Select to drop and re-create a table if it already exists. Select this option with extreme care because if data already exists in the table, it is also dropped. If you select this option, the Build process prompts you to confirm your intention before performing any destructive action. If you don't care about losing the data that resides in the table, then this option is faster than the Alter Tables option.

Skip table if it already exists

Select to create only those tables that are newly defined. If you want to preserve the data that already resides in existing tables or if you're just interested in creating the tables that do not already exist, select this option.

View Creation Options

Similar settings are available for creating views that you use for creating tables.

Recreate view if it already exists

Because views are just logical views of data, it is safe to select this option; you don’t run the risk of losing physical data. Using this option ensures that all views in the project are rebuilt, whether they need to be or not.

Skip view if it already exists

If you're concerned only with creating views that do not already exist in the database, select this option. This option is useful if you want to run Build Project on a large project and only a subset of the views in the project must be created. This consumes less time than re-creating all of the views in the project.

Index Creation Options

These options determine when an index should be re-created.

Recreate index if it already exists

Select to re-create the index for your tables, even if you already have one in place. It re-creates the index no matter what the situation.

Recreate index only if modified

Select to re-create the index only if the data is being modified in the indexes.

Click to jump to top of pageClick to jump to parent topicSpecifying Logging Options

Access the Build Settings dialog box. Select the Logging tab.

Logging Level

Specify the detail of information that you want in your Build log output.

Fatal errors only

Select this option if you want to see only the operations that failed.

Fatal errors and warnings

Select this option to add warnings to the fatal errors.

Fatal errors, warnings and informational messages

Select this option to see everything that processed successfully and all of the errors and warnings.

Note. These options are personal preferences and do not affect the actual build of your SQL tables, but they might help you track down potential failures. For example, if you're processing a large number of definitions, you'll want to see only the operations that failed. Whereas, if you're processing a small number of definitions, you'll want to see all of the successful operations too.

Logging Output

Specify where and in what form you want to view your log output.

Log to output window

Select to write the same information that appears in the log file to the PeopleSoft Application Designer output window. Always be aware of the level that you select. If there is a lot of detail (high logging level plus many records to process), it might be easier to copy the logging information to a file and print it later.

Log to file

Select to write the log to a file. If you select this check box, you can specify a location and name for your log file in the Log file name edit box. To search for a name, click the Browse button. Select a .LOG type file from the Build File Selection dialog box. If you do not select this check box, the edit box is unavailable for entry.

Log settings

Select to write all of the runtime options to the log. If you select this check box, it is not necessary to rerun a test when you have a problem. Should it be necessary to report a problem with the Build process or troubleshoot, it is important to know the settings that were active.

Note. PeopleSoft recommends selecting the Log to file check box and the Log to output window with a minimum logging level of fatal errors and warnings. In case you run into a problem, you already have the information that is needed to research the problem. Otherwise, you must run the process again with a higher logging level.

Example Log Results

The following are samples of the type of information that you can expect to see in your log output that corresponds to the selections that you make on the Logging tab:

Working With the Output Window

You can navigate from error messages in the PeopleSoft Application Designer output window to the corresponding record definition by selecting the appropriate line in the Build log and double-clicking anywhere on that line. PeopleSoft Application Designer opens the corresponding record in the definition workspace.

Also, you can double-click any line in the output window that corresponds to a file—not just a record. PeopleSoft Application Designer opens that file with whatever application that you defined to open files with a particular extension. For example, if you associated SQL files with your native SQL command utility, your SQL utility opens and loads your PSBUILD.SQL script when you double-click the line that reads:

Single script file name = C:\TEMP\PSBUILD.SQL

Note. PeopleSoft Application Designer uses the standard Microsoft Windows method for defining which programs are associated with particular file types. To modify the file associations, open My Computer, select Tools, Folder Options, and then select the File Types tab.

Click to jump to top of pageClick to jump to parent topicSpecifying Script Options

Access the Build Settings dialog box. Select the Scripts tab.

Comments

The Write Alter comments to script check box enables you to either include or suppress alter comments. The following example displays the types of comments that you see in your PSALTER.SQL script if you enable this option:

-- Alters for record PS_AE_RUN_CONTROL ; -- AE_THREAD - change ; -- OPRID - change ; -- Start the Transaction ; -- Data Conversion ; -- Drop Old Indexes ; DROP INDEX SYSADM.PS_AE_RUN_CONTROL / -- Add Columns ; ALTER TABLE PS_AE_RUN_CONTROL ADD Z_TMP_ALTER_1 DECIMAL(12,4) / ALTER TABLE PS_AE_RUN_CONTROL ADD Z_TMP_ALTER_2 CHAR(12) / UPDATE PS_AE_RUN_CONTROL SET Z_TMP_ALTER_1 = AE_THREAD, Z_TMP_ALTER_2 = OPRID /

If you do not view the alter comments, the script containing the same commands as the previous script looks like this:

DROP INDEX SYSADM.PS_AE_RUN_CONTROL / ALTER TABLE PS_AE_RUN_CONTROL ADD Z_TMP_ALTER_1 DECIMAL(12,4) / ALTER TABLE PS_AE_RUN_CONTROL ADD Z_TMP_ALTER_2 CHAR(12) / UPDATE PS_AE_RUN_CONTROL SET Z_TMP_ALTER_1 = AE_THREAD, Z_TMP_ALTER_2 = OPRID /

Script File

The default for the Save as Unicode check box is based on the UNICODE/ANSI database. This check box is available only when using MSSQLServer, Oracle, DB2/UDB for Linux, UNIX, and Windows, Sybase and DB2 UDB for OS/390 and z/OS databases. Only on Informix , the check box is permanently unavailable, because the script files are always ANSI.

The following table explains the default settings for the Save as Unicode check box:

Database Type

Default for Save as Unicode Check Box

Unicode

Selected

ANSI

Unavailable

The script file option, Save as Unicode, is stored in the registry with the other build settings. Therefore, the previous setting is always the default every time that the dialog box is opened.

The type of script file that is generated during the Build process is based on the Save as Unicode check box setting for the UNICODE/ANSI build and UNICODE/ANSI database.

The details are as follows:

Database Platform

Unicode

ANSI

MSSQL Server

UCS2

ANSI

Sybase

UTF8

ANSI

Oracle

UTF8

ANSI

DB2/UDB for Linux, UNIX, and Windows

UTF8

ANSI

DB2 UDB for OS/390 and z/OS

UTF8

ANSI

Script File Options

If you want all of your CREATE TABLE statements to be written to one SQL script file and your CREATE INDEX statements to another, then select the Output to separate files option. On the other hand, if you prefer to have all of your statements—CREATE, ALTER, and so on—contained in a single file, then select the Output to single file option.

Note. Script files can be quite large. To reduce the size of files so that they are easier to manage, select the Output to separate files option. Also, the system never appends script files; it overwrites them or you can terminate the process if you elected to be prompted before a build script file is overwritten.

Depending on which output option you select, the options in the Script File Names group box change to suit one file or multiple files.

File Overwrite Options

These options indicate whether you want to overwrite automatically or be prompted before script files are overwritten. Your personal preference determines which technique you employ.

Always overwrite

Select to overwrite your previous build scripts.

Prompt for each overwrite

Select to be alerted when a script is about to be overwritten.

To avoid the possibility of overwriting files altogether, you can use a naming convention that, in itself, prevents any overwrites. For example, you could specify a unique name for each build script, as shown:

  • C:\TEMP\PSINDEX1.SQL

  • C:\TEMP\PSINDEX2.SQL

Script File Names

The options available to select in this group box depend on your selection in the Script File Options group box.

Output to single file

When you select this option, an edit box appears in the Script File Names group box: All Output File Name. The default name for the generated script is PSBUILD.SQL.

Note. Script files can be quite large. To reduce the size of files so that they are easier to manage, select the Output to separate files option. Also, the system never appends script files; it overwrites them or you can terminate the process if you elected to be prompted before a build script file is overwritten.

Output to separate files

If you select this option, the following edit boxes appear—one for each build option:

  • Create Table File Name: The default name for this script is PSTABLE.SQL.

  • Create Index File Name: The default name for this script is PSINDEX.SQL.

  • Create Trigger File Name: The default name for this script is PSTRIGGER.SQL.

  • Create View File Name: The default name for this script is PSVIEW.SQL.

  • Alter Table File Name: The default name for this script is PSALTER.SQL.

Click to jump to top of pageClick to jump to parent topicConfirming a Table Build

If you are running SQL online, complete steps 1 and 3. Step 2 is not required.

To confirm a table build:

  1. Review the SQL script that was generated by the Build process.

    Use your native SQL command processor to open the SQL script. The script is located where you specified on the Scripts tab of the Build Settings dialog box.

  2. Run the script against your database.

  3. Confirm that the tables now exist.

    Use your query tool and SQL Select statements to confirm that the Create Table process created an application table that corresponds to your record definition and updated the system catalog tables. The SQL statement that you use to confirm depends on the table that you just created.

Click to jump to parent topicCreating Indexes

This section provides an overview of indexes and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding Indexes

Indexes are an important part of your physical database, because they affect the efficiency and speed with which your application can store and retrieve data. PeopleSoft application indexes are defined in two ways.

Some indexes are defined for you automatically, based on the key, search key, list box items, and alternate search keys that you specified on your record definition. These indexes are used by the application processor for quick access when you use the online system.

However, it is sometimes necessary to define additional indexes to improve the performance of demanding queries, reports, or batch programs. These additional indexes are defined and modified using the data administration tools in PeopleSoft Application Designer.

Note. You can only create indexes for record definition types that are SQL or temporary tables. You would not create indexes on views, such as a SQL view, because views are never physically created. The Indexes option in the Tools, Data Administration menu is disabled when a view is open.

When you use SQL Create or SQL Alter on a table, the system automatically builds database indexes to the SQL table that are based on the keys and list items that you specify in the record definition. Your database uses indexes to find definitions in the database the same way that you use indexes to find specific information in a book. If an index entry exists for a specific field on a table, the database can search and quickly find what it needs to complete your request; otherwise, it must search through the contents of the entire table.

Indexes enhance system performance by generating key lists that the application processor uses to extract the data that it uses to populate pages and search records. The system automatically creates:

For most database tables (those with unique keys), the key index defines unique keys for a table, but the system also uses it to access high-level keys and orders by all keys. The alternate search key indexes support the search record and field prompts. The purpose of these different indexes is to enable the system to respond to all system prompts through index-only SQL access, minimizing direct database table access.

Note. If you are using Create Table or Alter, the Create Index option is automatically selected. With the Create Table or Alter options, you can’t clear the Create Index option.

The alter process takes care of indexes regardless of the Alter Table Options you select on the Alter tab of the Build Settings dialog box. In the case of "Alter by Table Rename," the indexes are dropped automatically when the temp table is dropped. For the "Alter in Place" option, the index creation process goes through the "Recreate index only if modified" option on the Create tab.

When creating indexes with the Alter method, prior to altering the table, drop the statistics using "DROP STATISTICS" that were created explicitly on columns using "CREATE STATISTICS."

The following record definition modifications require the creation of new indexes:

Platform Support for Indexes

Most database platforms support indexes with an unlimited number of keys. However, Microsoft SQL Server 2000, DB2/UDB for Linux, UNIX, and Windows, and Informix support only indexes that have a maximum of 16 keys. PeopleSoft supports indexes for these databases by creating functional indexes. If a record definition has more than 16 keys, the Build process automatically alters the table to support functional indexes. The system adds a warning message to the log file indicating this. Sample code for functional indexes for each of these platforms can be found in Appendix A of this PeopleBook.

See Understanding Functional Indexes.

The following list offers more detail on functional indexes by database platform:

Click to jump to top of pageClick to jump to parent topicConfiguring Indexes

For performance tuning, you might want to change the order of your index keys. PeopleSoft Application Designer provides direct control over all of the indexes created by PeopleTools. You can inspect, add, or change all defined indexes or delete custom indexes for any record in the system through the Change Record Indexes dialog box.

Standard indexes are based on the key, duplicate order key, or alternate search key only. However, with the Change Record Indexes dialog box, you can modify those indexes and create additional indexes, which might be required if you do custom processing. Any of the indexes that you entered can be maintained for all database platforms or for a list of specific platforms. All indexes and keys created by PeopleTools are stored in PeopleSoft system tables.

To edit the index key order:

  1. Open the record definition for which you want to make the custom index.

  2. Select Tools, Data Administration, Indexes to access the Change Record Indexes dialog box.

    The Custom Order column indicates whether the index keys have been configured. The default selection is N, indicating that no configurations have been made.

  3. Double-click the N or Y in the Custom Order column for the index that you want to alter, or click Edit Index.

  4. Select the Custom Key Order check box.

  5. Click OK.

    The Custom Order entry changes from N to Y.

  6. Drag and drop the field that you want to move into the appropriate order.

    After configuring the key order, to reset the key order of the index to the original order as defined in the record definition, clear the Custom Order entry (Y to N), and click OK. The orders of the key fields reset to the original position in the Change Record Indexes dialog box.

  7. Check the key order in the Use Display mode of your record definition.

    The Num (number) column represents the original order of the keys in your record and the Ordr (order) column reflects the key order in the index.

  8. Create the indexes:

    1. Select Build, Current Project.

    2. Select Create Indexes in the Build Options group box.

      Make sure that you select the appropriate build settings.

  9. Run the Build process to either generate a script or run the SQL online.

  10. Confirm the construction of the new index with your native SQL command processor.

    Note. Users might also configure indexes for "parent" records that contain one or more subrecords without key fields, such as the subrecord ADDRESS_SBR. However, if the subrecords contain key fields, index key order changes are restricted. The Custom Key Order option is deactivated and cannot be changed from N to Y. Change the index key order for the parent before inserting the subrecord.

Click to jump to top of pageClick to jump to parent topicAdding a Custom Index

To add an index:

  1. Open the record.

  2. Select Tools, Data Administration, Indexes to access the Change Record Indexes dialog box.

  3. Click Add Index.

    The Add Index dialog box enables you to determine for which database platforms you want to define the index if you selected the Some option for Platform. The Custom Key Order check box has been selected by the system to ensure that the underlying record definition is not adversely impacted by the change to your index.

    For all user-defined indexes, you must enter a description of the index in the Comments section. It is useful to know the purpose of the index, the transactions and processes that use this index, and other relevant information. This information assists you when you want to remove unused indexes. An error message appears if you do not do so.

    Note. You cannot add more than 22 user indexes (A-V). The system disables the Add Index button if the record already contains 22 user indexes.

  4. Indicate the database platforms for which you want to create the index.

  5. Click the OK button to add a row to the Change Record Indexes dialog box and enable the arrow buttons.

  6. Move the field under the new index row:

    1. In the Record Fields list box, select the field that you want indexed.

    2. Click the arrow button, or double-click the field.

    3. Repeat this step for each field that you want to add.

  7. Click OK.

  8. Save the record definition.

  9. Select Build, Current Project.

  10. Select Create Indexes under Build Options.

    Make sure that you select the appropriate build settings.

  11. Run the Build process to either generate a script or run SQL online.

  12. Run the generated SQL script if you opted to generate one.

  13. Confirm the construction of the new index with your native SQL command processor.

Click to jump to top of pageClick to jump to parent topicEditing the DDL

In the Change Record Indexes dialog box, you can view the DDL for the index and override DDL parameters that are defined in the DDL model for this index.

To edit the DDL:

  1. In the Change Record Indexes dialog box, select the index that you want to edit.

  2. Click the Edit DDL button to access the Maintain Index DDL dialog box.

    The upper list box in this dialog box lists the platform and DDL parameter, and the lower list box shows the DDL templates for the various platforms. Your database might have fewer platforms enabled. The sizing set enables different collections of tables to have different model statements and parameters.

  3. Select the platform and sizing set.

  4. Click View DDL.

    The dialog box that appears shows the index DDL for the platform and sizing set that you selected. Review this DDL statement.

  5. Click the Close button when you’re finished.

  6. Select the parameter that you want to edit in the upper list box.

    The default value is set in the DDL Model Defaults dialog box. You can change this setting by selecting Go, PeopleTools, Utilities, Use, DDL Model Defaults in PeopleSoft Application Designer, or by selecting PeopleTools, Utilities, Use, DDL Model Defaults in the browser.

  7. Click the Edit Parm (edit parameter) button to access the Edit Override Parm Value (edit override parameter value) dialog box.

  8. Enter the override value for the parameter that you selected.

  9. Select OK for both open dialog boxes.

Click to jump to parent topicAltering Tables

This chapter provides an overview of the Alter process and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding the Alter Process

 

The Alter process enables you to make changes to your record definitions at any time without losing the application data that is already housed in the table. To ensure that the Alter process produces the appropriate results, be aware of when to alter an existing table and what types of conversions PeopleSoft Application Designer supports. To preserve data that is stored in tables and also reflect a recent change in the table definition, use the alter tables function to make the appropriate changes.

The alter function simplifies the process of modifying database tables when new data fields are added or when existing data fields are modified. It effectively eliminates the need to write SQL statements to perform the same function.

PeopleSoft specifically designed the alter process to automate the task of writing alter scripts and protect the integrity of your database. In essence, it protects your interests by ensuring that you always control data loss. The alter function performs tests that verify whether data will be lost during the conversion on each column to be altered. Data loss normally occurs when reducing the character width of a column, dropping a column that contains data, or converting a number field to a character field that is too small.

The generated statements for this method are sent to a SQL script file—the alter script. After the script has completed, you are responsible for reapplying any SQL table DDL that you applied outside of PeopleTools. Typically, this consists of GRANT and REVOKE statements controlling security.

Note. On Oracle databases, procedural language/Structured Query Language (PL/SQL) is required to use the PeopleSoft Application Designer Build process.

Alter Settings

The Alter tab in the Build Settings dialog box handles the most common types of data conversions, including:

The following sections describe the types of alter conversions that you can expect to occur during the Build process.

Data Conversions

When altering tables with existing field data, PeopleSoft Application Designer handles conversions as outlined in the following table. A No indicates that conversion cannot take place. A number in the cell following a Yes indicates that there are restrictions involved with the conversion. Refer to the corresponding numbered note following the table for additional information:

PSFT Old Field Type Format

PeopleSoft New Field Type Format

 

Char

Oracle– Long Char with Length < 2000

Long Char

Image

Long Char— Raw Binary

Number/Signed Number

where n = length and d = decimal

Date

Time

DateTime

SmallInt *(small integer)

Integer **

Decimal ***

Char

Yes (1)

Yes (1)

Yes (9)

No

No

No

No

No

No

No

No

Oracle— Long Char with Length < 2000

Yes (1)

Yes (1)

Yes

No

No

No

No

No

No

No

No

LongChar

Yes (2)(9)

Yes (2)

Yes (8)

No

No

No

No

No

No

No

No

Image

No

No

No

Yes (8)

Yes (8)

No

No

No

No

No

No

Long Char— RawBinary

No

No

No

Yes (8)

Yes (8)

No

No

No

No

No

No

SmallInt *

Yes (5)

Yes (5)

No

No

No

No Action

Yes (4)

Yes (4)

No

No

No

Integer **

Yes (5)

Yes (5)

No

No

No

Yes (4)

No Action

Yes (4)

No

No

No

Decimal ***

Yes (7)

Yes (7)

No

No

No

Yes (3)

Yes (3)

Yes (3)

No

No

No

Date

No

No

No

No

No

No

No

No

No Action

No

No

Time

No

No

No

No

No

No

No

No

No

No Action

No

DateTime

No

No

No

No

No

No

No

No

Yes (6)

Yes (6)

No Action

NOTES:

* n < 4, d = 0, n = 5, d = 0 RawBinary

** n > 4, d = 0, (or) n = 10, d = 0 RawBinary

*** n > 9, d > 0

1

If data fits or data truncation is allowed.

2

Not allowed in Oracle.

3

If data fits or data truncation is allowed (for decimals portion only).

4

If data fits. No data truncation can occur, even with the option to allow data truncation.

5

If data fits, except for Informix.

6

If data truncation is allowed by selecting theTruncate data if field too short option on the Alter tab of the Build Settings dialog box.

7

If data fits, except if data has decimal values or for Informix.

8

If image or long character change from Null to Not Null or Not Null to Null, otherwise, no action.

9

Allowed on all platforms except Informix (due to an Informix limitation).

Alter Process Tips

The following lists information to keep in mind as you create and run the alter scripts:

When to Alter Tables

The following record definition changes affect synchronization with the application table and require an alter process:

The Alter Table process is similar to the Create Table process, except that it does not drop existing application data tables and the data that they contain.

Note. The Build process automatically alters records with more than 16 keys to support functional indexes. A warning message indicating this is included in the log file for Microsoft SQL Server and DB2/UDB for Linux, UNIX, and Windows. For Informix, the warning message reads: "The record %s has more than 16 keys. Indexes will be re-created to support functional indexes."

Working With Alters on DB2 for z/OS

There are a variety of considerations and maintenance tasks that you need to be aware of when performing alters on DB2 UDB for z/OS. For example, you need to consider:

These topics are discussed in detail in the Data Management PeopleBook.

See Working with Alters on DB2 z/OS.

Click to jump to top of pageClick to jump to parent topicDetermining Tables to be Altered After a Field Change

To determine tables to be altered after a field change:

  1. Create a new project.

  2. Open the altered field.

  3. Select Edit, Find Definition References.

    The referenced definitions appear in the output window.

  4. Select all of the rows that appear in the output window.

  5. Right-click the selected rows, and select Insert Into Project from the pop-up menu.

Click to jump to top of pageClick to jump to parent topicAltering a Table

To alter a table:

  1. Open the project for which you must perform an alter.

  2. From the Build menu, select the appropriate scope of your build.

    The Build dialog box appears.

  3. Select Alter Tables in the Build Options group box.

    Selecting the Alter Tables option automatically selects and disables the Create Indexes and Create Trigger (only if triggers are needed) option. In the Build Execute Options group box, only the Build script file option is enabled.

  4. Click the Settings button in the Build dialog box to set user-defined defaults.

    Select the appropriate alter, logging, and scripts settings.

    Note. The alter process takes care of the indexes regardless of the Alter Table Options you select on the Alter tab of the Build Settings dialog box. In the case of "Alter by Table Rename," the indexes are dropped automatically when the temp table is dropped. For the "Alter in Place" option, the index creation process goes through the "Recreate index only if modified" option on the Create tab.

  5. In the Build dialog box, click Build to run the Alter Tables process.

    The length of a build process depends on the number of definitions that require building. Watch the Build Progress dialog box to monitor the Build process. When the process completes, check any errors listed in the log file.

Click to jump to top of pageClick to jump to parent topicSpecifying Alter Options

Access the Build Settings dialog box. Select the Alter tab.

Drop Column Options

These options are referenced whenever a field is deleted from a PeopleTools record definition where data exists in the database for that column.

Drop column if data present

Select to drop the column and data and write a warning to the build log.

Skip record if data present

Select to end the alter for that record, and write an error message to the log. Processing continues with the next record.

Note. Whenever you select Drop column if data present, you run the risk of losing data, and you are prompted at runtime to confirm your choice of this option.

Change Column Length Options

Use these options whenever the length of a character column is reduced in PeopleTools and an alter could result in data truncation.

Truncate data if field too short

Select to alter the record and write a warning message to the build log. Note that the system does not truncate data for numeric fields because of possible data loss.

Skip record if field too short

End the alter for that record and write an error message to the build log. Processing continues with the next record.

Note. Whenever you select Truncate data if field too short, you run the risk of losing data, and you are prompted at runtime to confirm your choice of this option. Also, numeric fields are not truncated to avoid any loss of data.

Alter Any

PeopleTools assumes that you want to perform alters for any modifications made to tables, so, by default, all of the check boxes in the Alter Any group box are selected. Alter Any allows for custom alter processing regarding adds, changes, renames, and deletes. For example, you have the flexibility to add, change, and rename fields, but not to delete any removed columns. When you complete any other custom external conversion processes, you can then enable the delete processing to drop columns that are no longer defined.

Normally, you select all of these options, but during upgrades or operations requiring data conversion steps, you might select a subset of actions. For example:

Alter even if no changes

Select this check box to force an alter, even if no changes are made to the tables. This check box is cleared by default. If selected, the Alter by Table Rename option in the Alter Table Options group box is automatically selected, and the Alter in Place option is disabled.

Alter Table Options

When altering tables, the alter process takes care of the indexes regardless of the Alter Table Options you select on the Alter tab of the Build Settings dialog box. In the case of Alter by Table Rename, the indexes are dropped automatically when the temp table is dropped. For the Alter in Place option, the index creation process goes through the Recreated index only if modified option on the Create tab.

See Working with Alters on DB2 z/OS.

Click to jump to top of pageClick to jump to parent topicConfirming Your Table Alter

To confirm your table alter:

  1. Review the SQL script that was generated by the Alter process.

    Use your native SQL command processor to open the SQL script. The script is located where you specified on the Scripts tab of the Build Settings dialog box.

  2. Run the script against your database.

  3. Use your query tool and SQL Select statements to confirm that the Alter Tables process has created an application table that corresponds to your record definition and has updated the system catalog tables.

Click to jump to parent topicCreating Triggers

A database trigger is a type of stored procedure that runs automatically when a user makes a specific data-modification statement (INSERT, UPDATE, or DELETE) on a specified table. Within PeopleTools, triggers are created in tables related to synchronization for mobile applications and for optimization servers. Triggers are not created for all records; they are created only for records where specific properties have been set.

For example, in a mobile component record, set the required properties for that record so that triggers are created during the Build process. Using the Build dialog box, create the trigger by selecting the Create Trigger check box in Build Options. Using the Build Execute options, you can either generate the SQL or run the build online. There are, however, usage and implementation restrictions.

Important! PeopleSoft Mobile Agent is a deprecated product. These features exist for backward compatibility only.

See Also

Creating and Building Optimization Records

Click to jump to parent topicCreating Views

This section provides an overview of online views and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding Views

Views in PeopleSoft applications are comparable to views used in traditional RDBMS technology. A view, in essence, is a virtual table. It is created from the results of a query run against one or more tables.

When you create views to use online, you must keep the views synchronized with the database. Like SQL tables, you must have a record definition for online views because the system checks the record definitions for online processing rules. You must build the view before you can use or reference it online.

If you use a view as the basis for a page, you select existing information to display on the page. Therefore, when you create the record definition for your view, you can clone an existing record definition, delete any fields that are not used in the view, and proceed to define the view. Query views are processed the same way as view text, which is defined as part of the record.

The order in which PeopleTools creates views is based on the build sequence number that you set on the Record Type tab while the record is open. The default build sequence number is 1. For the dependent views to be created first, the build sequence number should be 0. This way, all of the 0 views are created first, then the 1s, and finally the views that are greater than 1. Although the views are sorted in alphabetical order in the project, they are created based on the build sequence number.

Click to jump to top of pageClick to jump to parent topicCreating Views

If you’re already familiar with creating record definitions and writing SQL queries, creating online views is quite similar.

To create online views:

  1. Clone a record definition:

    1. Open an existing record definition.

    2. Select File, Save As.

    3. Specify the name of the view that you want to create.

  2. Delete and add the appropriate fields.

    All columns that are selected in your view should have a corresponding field in the record definition.

  3. Set the record type to SQL View:

    1. Select the Record Type tab for the record definition.

    2. Select SQL View in the Record Type group box.

  4. Click the Click to open SQL Editor button.

    The order in which you list the columns in your SQL SELECT statement should match the order that you specified in the record definition. A SQL statement can be given for each platform.

    See Creating SQL View and Dynamic View Select Statements.

  5. Save the record definition.

  6. Select Build, Current Definition to create the view in the database.

  7. Select the Create Views check box under Build Options.

  8. Click Build.

Click to jump to top of pageClick to jump to parent topicUsing Joins in Views

If you plan to use a join on a regular basis, you can save it as a SQL script, or you can create a view—a single virtual table—using the join as the logical representation of the data. After you create a view, the users never need to know that the data that they’re viewing is stored in multiple tables. To users, the relative complexity of your view is transparent. For example:

SELECT A.EMPLID, A.NAME, B.CONTACT_NAME, B.RELATIONSHIP FROM PS_PERSONAL_DATA A, PS_EMERGENCY_CNTCT B WHERE A.EMPLID = B.EMPLID;

Click to jump to top of pageClick to jump to parent topicUsing Cross-Platform Meta-SQL for Dynamic Views

PeopleTools includes functionality to produce meta-SQL constructs for dynamic views. This means that dynamic SQL views that are created using PeopleSoft Application Designer can be used on any platform that is supported by PeopleSoft. The PeopleSoft PeopleTools PeopleBook: PeopleCode Developer’s Guide contains a full list of the meta-SQL that is supported in the PeopleSoft Internet Architecture.

See Also

Meta-SQL Placement Considerations

Click to jump to parent topicAdministering 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:

Click to jump to top of pageClick to jump to parent topicUsing the Record DDL

Use the record DDL to define parameters and default values for the tables in your database. PeopleSoft provides templates for each database platform that PeopleSoft 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 Tools, Data Administration, Record DDL. The Data Administration menu option is only enabled if a record definition is open.

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 (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.

Click to jump to top of pageClick to jump to parent topicSetting the Tablespace

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 by selecting Tools, Data Administration, Set Tablespace. 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, a tablespace name must be allocated. For the Change Space dialog box to appear upon saving a new record definition, the Platform Compatibility Mode check box under PeopleTools, Utilities, Administration, PeopleTools Options must be selected.

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 have already been allocated to the space that you selected from the Available Space Name-DB Name drop-down list box. This field is display-only.

See Tablespace Utilities.

Click to jump to top of pageClick to jump to parent topicUsing Physical Data Storage

You have control over the physical storage of your data. The DDL for creating tables, indexes, and tablespaces can be viewed and edited in the browser.

Access the DDL Model Defaults page.

To view or edit the DDL parameters for creating tables, indexes, and tablespaces:

  1. From the homepage of your application, select PeopleTools, Utilities, Administration, DDL Model Defaults to access the DDL Model Defaults search page.

  2. Press enter to view platform names.

  3. 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:

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. In the preceding example, there is 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.

See Also

Using Administration Utilities