Working with the Alter Repository
This section discusses how to work with the alter repository.
The alter repository consists of a set of tables designed to store the results of the PTANALYZER program run, as well as some static information to be used for reference. Once the PTANALYZER program runs, the alter repository will contain information for each definition where metadata attributes differ between the source and target databases. Use a SQL editor or reporting application to query the repository and extract the information you require. This will require a working knowledge of the Alter Repository table structure.
The alter repository consists of these tables:
PS_PTUALTRECDATA
PS_PTUALTRECFLDDAT
PS_PTUALTRECFLDDEF
The structure of the tables is described in the following sections.
The following table describes the fields in the PS_PTUALTRECDATA table.
|
Column Name |
Description |
|---|---|
|
PTUPG_SRECNAME |
Source Record Name. |
|
PTUPG_TRECNAME |
Target Record Name. |
|
PTUPG_STABNAME |
Source Table Name. |
|
PTUPG_TTABNAME |
Target Table Name. |
|
PTUPG_SRECTYPE |
Source Record’s Type. |
|
PTUPG_TRECTYPE |
Target Record’s Type. |
|
PTUPG_STMPTBLINST |
Temp Table Instances count for Source temp table. (Applies only to temporary tables). Default value is 0. |
|
PTUPG_TTMPTBLINST |
Temp Table Instances count for Target temp table. (Applies only to temporary tables’). Default value is 0. |
|
PTUPG_ALTACTION |
Indicates the Alter action: A: Add Record (New) D: Delete Record RR: Rename Record CR: Record Added. DR: Record Deleted RR: Record Renamed AR: Alter Record with Field changes, Index changes, and Trigger changes. NA: Not applicable, Record with only Trigger, Index, or both changes. TC: Record type changed between source and target. Source table is a non-table (SQL View, Dynamic View, Derived/Work table, and so on) and in the Target the table is an actual SQL table (SQL Table, Temporary Table). TD: Record type changed between source and target. |
|
PTUPG_IDXONLY |
Indicates whether changes are only to indexes. 0: Default 1: Only Index Changes |
|
PTUPG_TRGONLY |
Indicates whether changes are only to triggers. 0: Default 1: Only Trigger Changes |
The following tables describes the fields in the PS_PTUALTRECFLDDAT table.
|
Column |
Description |
|---|---|
|
PTUPG_SRECNAME |
Source Record Name. |
|
PTUPG_SRECTYPE |
Source Record Type. |
|
PTUPG_SFLDNAME |
Source Record.Field Name. |
|
PTUPG_SFLDTYPE |
Source Record.Field Name and Type. (Platform independent-PeopleTools field type). |
|
PTUPG_SFLDLEN |
Source Record.Field Name, Type, and .Length. |
|
PTUPG_SFLDDECPOS |
Source Record.Field Nam, Type, and Decimal Position. |
|
PTUPG_SFLDNOTNULL |
Source Record.Field Name, and Null ability. 0: Null 1: Not Null |
|
PTUPG_SFLDDEFREC |
Default Record Name. |
|
PTUPG_SFLDDEFFLD |
Constant (or) Default Record Name. Field Name. |
|
PTUPG_SENCRYPT |
(Applies only to Oracle databases using TDE.) Source Record.Field, Encryption. 0: No Encryption 1: Encryption |
|
PTUPG_TRECNAME |
Target Record Name. |
|
PTUPG_TRECTYPE |
Target Record, Record Type. |
|
PTUPG_TFLDNAME |
Target Record.Field Name |
|
PTUPG_TFLDTYPE |
Target Record.Field Name, and Type. (Platform independent-PeopleTools field type) |
|
PTUPG_TFLDLEN |
Target Record.Field Name, Type, and Length. |
|
PTUPG_TFLDDECPOS |
Source Record.Field Name, Type, and Decimal Position. |
|
PTUPG_TFLDNOTNULL |
Target Record.Field Name, and Null ability. 0: Null 1: Not Null |
|
PTUPG_TFLDDEFREC |
Default Record Name. |
|
PTUPG_TFLDDEFFLD |
Constant (or) Default Record Name. Field Name. |
|
PTUPG_TENCRYPT |
(Applies to Oracle databases using TDE.) Target Record Field .Encryption. 0: No Encryption 1: Encryption |
|
PTUPG_ALTACT_A |
Indicates whether the field is newly added or not. 0: Not an Added Field 1: Added Field (New) |
|
PTUPG_ALTACT_D |
Indicates whether the field is deleted or not. 0: Not a Deleted Field 1: Deleted Field |
|
PTUPG_ALTACT_CT |
Indicates whether the field type has changed. 0: No Change in Field Type 1: Change in Field Type |
|
PTUPG_ALTACT_CL |
Indicates whether the field length has changed. 0: No Change in Field Length 1: Change in Field Length |
|
PTUPG_ALTACT_CD |
Indicates whether the field decimal position has changed. 0: No Change in Field Decimal Position. 1: Change in Field Decimal Position. |
|
PTUPG_ALTACT_CN |
Indicates whether the field null ability has changed (Null/Not Null). 0: No Change in Null ability 1: Change in Null ability |
|
PTUPG_ALTACT_CDR |
Indicates whether the default record name has changed. 0: No Change in Default Record Name 1: Change in Default Record Name |
|
PTUPG_ALTACT_CDF |
Indicates whether the default field name has changed. 0: No Change in Default Field Name 1: Change in Default Field Name |
|
PTUPG_ALTACT_CEN |
(Applies only to Oracle databases using TDE.) Indicates a change in the field encryption. 0: No Change in Encryption 1: Change in Encryption |
|
PTUPG_ALTACT_FR |
Indicates whether a field has been renamed. 0: No Field Rename 1: Field Renamed |
This table is provided for reference purposes only. It displays the PeopleSoft field types and their default values per database platform. It is a static table, not used or updated by Alter Analyzer.
|
Column Name |
Description |
|---|---|
|
PTUPG_FIELDTYPE |
PeopleSoft field type. |
|
PTUPG_FLDTYPENAME |
PeopleSoft field type name. |
|
PTUPG_PLTFRM_ORA |
Default value for Oracle databases. |
|
PTUPG_PLTFRM_MSS |
Default value for Microsoft SQL Server databases. |
|
PTUPG_PLTFRM_DBX |
Default value for DB2 LUW databases. |
|
PTUPG_PLTFRM_DB2 |
Default value for DB2 z/OS databases. |
This section provides these examples to illustrate the type of information you can gather from the contents of the alter repository.
Example: Fields Altered For Target Records
Example: Source Records Deleted From Target
Example: Source Records Renamed In Target
Example: Fields Altered For Target Records
This sample query illustrates how to determine which fields on a particular target record will be altered why.
SELECT
PTUPG_TRECNAME,
PTUPG_TFLDNAME,
PTUPG_ALTACT_A,
PTUPG_ALTACT_D,
PTUPG_ALTACT_CT,
PTUPG_ALTACT_CL,
PTUPG_ALTACT_CD,
PTUPG_ALTACT_CN,
PTUPG_ALTACT_CDR,
PTUPG_ALTACT_CDF,
PTUPG_ALTACT_CEN,
PTUPG_ALTACT_FR
FROM PS_PTUALTRECFLDDAT
WHERE PTUPG_TRECNAME LIKE 'JOB_CD_TBL'
Example: Source Records Deleted From Target
This sample query illustrates how to determine which records in the source database will be deleted from the target.
SELECT
PTUPG_SRECNAME,
PTUPG_STABNAME
FROM PS_PTUALTRECDATA
WHERE PTUPG_ALTACTION = 'DR'
Example: Source Records Renamed In Target
This sample query illustrates how to determine which records in the source database are being renamed in the target.
select
PTUPG_SRECNAME,
PTUPG_STABNAME,
PTUPG_TRECNAME,
PTUPG_TTABNAME
FROM PS_PTUALTRECDATA
WHERE PTUPG_ALTACTION = 'RR'