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

The alter process was specifically designed to automate the task of writing alter scripts and to 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 of 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 SQL table DDL consists of GRANT and REVOKE statements that control security.

Note:

In Oracle databases, procedural language/SQL (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:

  • Increasing the length of character fields.

  • Changing a character (CHAR) field to a long character (LONG VARCHAR) field.

  • Increasing the size of number or character fields.

  • Reducing the decimal positions in number fields.

    If any column of the table has data that will be lost when truncating, the system action is determined by the Change Column Length options.

  • Changing integer-only number fields to character fields, where the character field is formatted with leading zeros.

    If any column of the table has a number that does not fit in the new character field, the system action is determined by the Change Column Length options to prevent data from being lost.

  • Adding columns to SQL tables.

  • Dropping columns from SQL tables.

  • Renaming columns in SQL tables.

  • Renaming SQL tables.

  • Moving tables to a new tablespace.

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 value indicates that conversion cannot take place. A number in the cell following a Yes value indicates that restrictions are involved with the conversion. Refer to the corresponding numbered note following the table for additional information:

Previous Format New Format
— Char Oracle - Long Char * Long Char Image Long Char - Raw Binary Number/ Signed Number ** Date Time Datetime
SmallInt *** Integer **** Decimal *****

Char

Yes (1)

Yes (1)

Yes (9)

No

No

No

No

No

No

No

No

Oracle - Long Char *

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  

*

with length < 2000

**

where n = length and d = decimal. See notes ***, ****, and *****

***

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

****

n > 4, d = 0; 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 decimal portion only).

4

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

5

If data fits.

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.

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.

Alter Process Tips

As you create and run alter scripts, keep the following information in mind.

  • Temporary tables during alter:

    When you run the Alter process, the script might create a temporary table. Temporary tables are named PSY%tablename%, and they are created in the tablespace of the altered table, that is, the tablespace currently defined in the record definition.

  • Alter process on tables containing LONGS on Oracle:

    Oracle does not allow an INSERT or SELECT FROM command if a table contains a LONG VARCHAR or LONG RAW data type. Therefore, in Oracle databases, PeopleSoft Application Designer uses an Oracle PL/SQL script to perform alters on tables that contain any type of LONG data type.

  • Alter process on Oracle on records with CHAR fields greater than 1333 bytes but less than 2000 bytes:

    For Oracle, records with CHAR fields of length greater than 1333 characters and less than 2000 characters will appear in the alter SQL script as needing to be altered, even if the information has not changed. In Oracle Unicode databases, PeopleSoft applications triple the length of a field while creating a table with a VARCHAR2 column in the database to store all three bytes of the Unicode character. In Oracle databases, VARCHAR2 can store up to 4000 bytes, which means a restriction exists to store only 1333 characters (with three bytes each).

    For example, if a LONG CHAR field is designed with a length of 1999 characters, then PeopleSoft applications interpret the field as a VARCHAR2 and create the column with a size of 1333 characters. The field length of that LONG CHAR field will be stored as 1999 in the PSRECFIELD table, but in the database it will be created with a size of 1333. So, each time a record with a LONG CHAR field of 1999 (or any value > 1333), the alter process detects the difference between the 1999 character length in PSRECFIELD and the 1333 character length in the database and generates the alter script. In addition, PeopleSoft applications also create a constraint on the VARCHAR2.

  • View dependencies:

    PeopleSoft Application Designer does not keep track of view dependencies. When the structure of a table changes, you should recreate all views. Many database environments track this information, so you might be able to determine view dependencies by issuing a query directly against the system catalog. However, if you are not the DBA, you might not have the authority to run such a query successfully.

    Also, renaming or dropping a table, view, or column might invalidate view text, dynamic view text, and possibly other stored queries.

    This table describes what happens to views by database platform:

    Database Platform What Happens to the Dependent Views When a Table Is Dropped? Do the Inoperative Views Become Operative When the Table is Recreated? Comments

    Microsoft SQL Server

    Becomes inoperative.

    Yes

    The dependency reported by sp_depends is incorrect until you recreate the view.

    Oracle

    Becomes inoperative.

    Yes, but only after the first use of the dependent view.

    The VIEW object that became invalid remains invalid until its first use. The Oracle catalog table for objects marks the view as valid again.

    Db2 for z/OS

    Is dropped when the table is dropped.

    NA

    Views need to be recreated because the system drops them along with the table drop.

  • Alter script:

    After you invoke the Alter process, the system uses the default value that is defined in the record definition to populate the column in the altered table. The alter script that is generated contains detailed comments indicated by the (--) to assist you if you must modify the script manually.

    When you run the alter script in SQL Developer or SQL Plus, ensure that in client length settings you specify CHAR if the database is Unicode or BYTE if the database is non-Unicode.

  • Preserve storage parameters on Oracle databases:

    Alter by table rename preserves customized storage parameters for tables only (with some limitations) not for indexes, by taking DDL parameters from the system catalog.

When to Alter Tables

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

  • Add or delete a field in the record.

  • Modify the length of a field.

  • Change the required status of a date, time, Datetime, or long field.

  • Rename a field.

  • Rename a record.

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.

Working With Alters on Db2 for z/OS

You must be aware of a variety of considerations and maintenance tasks when performing alters on Db2 for z/OS. For example, you must consider:

  • Tablespace versioning.

  • Advisory Reorg Pending status.

  • Rebuild Pending status.

  • Avoiding SQL Code -4702.

These topics are discussed in detail in Data Management: Working with Alters on Db2 z/OS.