Altering Tables

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:

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/UDB for Linux, UNIX, and Windows	Becomes inoperative.	No	Views need to be recreated to make them operative.
  DB2 UDB 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.

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

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 UDB 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 inthe product documentation for PeopleTools: Data Management.

See Working with Alters on DB2 z/OS.

Drop Column Options

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

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.

Alter Table In Memory

If you have selected any of the Oracle In Memory options for your record or record fields, you need to select Alter Table In Memory for those options to take effect and alter the table.

See the Oracle In Memory option for records description here:Setting Use Properties.

See the Oracle In Memory option for record fields description here: Setting Record Field Use Properties.

The following example illustrates the build script generated for a table with All Fields selected on the record definition Oracle In Memory settings, and all record fields contained in that record definition are supported for IM column store processing.

ALTER TABLE PS_QE_JOBCODE_TBL INMEMORY MEMCOMPRESS FOR QUERY PRIORITY
 LOW
/
SELECT COUNT(*) FROM PS_QE_JOBCODE_TBL
/

The following example illustrates the build script generated for a table with All Fields selected on the record definition Oracle In Memory settings, and all record fields contained in that record definition are not supported for IM column store processing.

Alter in memory ALL qe book selective

ALTER TABLE PS_QE_BOOK INMEMORY MEMCOMPRESS FOR QUERY PRIORITY LOW NO
 INMEMORY (QE_BOOK_DESCR, QE_BOOK_COVER_IMG)
/
SELECT COUNT(*) FROM PS_QE_BOOK

Note that the system adds the NO INMEMORY (QE_BOOK_DESCR, QE_BOOK_COVER_IMG) statement automatically, including the fields that are not supported for IM column store processing. These fields are of type Long Character and Image, respectively.

The following example illustrates the build script generated for a table Selective Fields selected on the record definition Oracle In Memory settings, and all record fields contained in that record definition are not supported for IM column store processing. In this case, the NO INMEMORY statement will include automatically the record fields that are not supported for IM column store processing and the record fields not selected (manually) for IM column store processing.

ALTER TABLE PS_QE_BOOK INMEMORY MEMCOMPRESS FOR QUERY PRIORITY LOW NO
 INMEMORY (QE_ADDED_DT, QE_BOOK_DESCR, QE_BOOK_COVER_IMG, PSIMAGEVER)
/
SELECT COUNT(*) FROM PS_QE_BOOK
/

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. The Alter Any group box enables you to customize alter processing for 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:

• Perform alter with Adds and Changes selected.

• Perform data conversion routines to populate the new and changed columns (perhaps from columns that are ultimately to be dropped).

• Perform alter with Rename and Delete selected.

Alter even if no changes

Select this check box to force an alter, even if no changes were made to the tables. This check box is deselected 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 temporary 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.

• Alter in Place

  Selected by default. For database platforms in which Alter in Place is not supported, alter is automatically done by Alter by Table Rename (even if that option is not selected).

  Database platforms vary in the capabilities for Alter in Place table operations. This table displays the limitations for each operation by platform:

  Database Platform	Add Column	Change Column (data type, size, precision, scale, and null/not null property change)	Rename Table	Delete Column
  Microsoft SQL Server	Yes *	Yes *	Yes *	Yes *
  Oracle	Yes *	No	Yes	Yes *
  DB2/UDB for z/OS	Yes *	Yes *	Yes *	No
  DB2/UDB for Linux, UNIX, and Windows	Yes *	No	No	No
  * Certain restrictions might apply to this operation. Refer to the documentation for your specific database platform for more information.

• Alter by Table Rename

  Select to create a temporary table (with changes made to the original table or its fields); the data from the original table is imported into the temporary table. The original table is then dropped and the temporary table is renamed to the original table.

  Note: For Oracle databases only, Alter by Table Rename preserves customized storage parameters for tables (with some limitations), by taking DDL parameters from the system catalog.

  If a table is renamed from the old name to a new name, the indexes that were created for the old table are moved to the new table, but the index names remain in the name of the old table. With Alter by Table Rename selected, the indexes of the old table are dropped before being renamed to the new table name, and the indexes are recreated after the table is renamed to the new name. This way, the index is created in the name of the new table.

See Working with Alters on DB2 z/OS.