Skip to Main Content
Return to Navigation

Understanding Data Conversion Utilities

The Upgrade Data Conversion Application Engine Programs are organized into a series of Drivers or Groups that guide the flow and order of execution at runtime for a particular upgrade path.

This topic contains information regarding People Tools Impact Analysis (PTIA) and the PS_PTIA_DCAEPGMS table. The PTIA process consists of two Application Engine programs and is intended to optimize the data conversion process by analyzing Source and Target tables and state records to determine actual dependencies between Application Engine sections. This allows you to run your data conversion process during your PeopleSoft application upgrade with optimal performance.

Using the PTIA Data Conversion Process

This section discusses:

  • Understanding the PTIA Data Conversion Process

  • Reviewing PTIA Initial Analysis

  • Reviewing Dependency Analysis

  • Reviewing Runtime for PTIADATACONV

  • Reviewing PTIA Reporting

Understanding the PTIA Data Conversion Process

The PeopleTools Impact Analysis (PTIA) process uses many pieces of the previous style data conversion delivered in PeopleSoft 9.1 applications and lower. For example, PTIA uses the Application Engine section grouping and sequencing in the PS_PTIA_DCAEPGMS table for its dependency modeling. The PTIA process also uses the terminology – root or top section. A root or top section is an Application Engine section defined in PS_PTIA_DCAEPGMS. We use root or top section to distinguish between sections being called from the data conversion program as opposed to sections being called from an Application Engine call section step.

The PTIA process includes analyzing the insert, update, and delete SQL steps in your data conversion to determine the Source and Target tables and state records that are used. This includes analyzing dynamic SQL, App Classes, SQLExec’s, and platform-specific code.

The Application Engine program gathers a list of Application Engine sections required for data conversion from a given upgrade path. These sections are analyzed and SQL statements are extracted and stored in the Application Engine Analyzer repository. Each SQL statement is analyzed to derive a list of tables that are manipulated or queried during the execution of that SQL. Once all the SQL is analyzed, the information is used to derive section dependency information, which is then saved in the Application Engine Analyzer repository. PTIA allows the Application Engine data conversion to run out of the box on a number of threads.

There are two types of analysis for PTIA: initial and dependency. This section will describe both analysis types in detail.

Reviewing PTIA Initial Analysis

This section discusses:

  • Understanding Initial Analysis

  • Reviewing Data Conversion Query Parsing

  • Reviewing Custom Data Conversion Code

  • Reviewing Table Usage Information

  • Reviewing Non Parsable SQL

  • Reviewing the Data Conversion Repositories

Understanding Initial Analysis

The first part of the PTIA process is the PTIAANALYSIS Application Engine, also known as the Application Engine Analyzer. PTIAANALYSIS accepts one parameter for the upgrade path, and then queries PS_PTIA_DCAEPGMS to retrieve all the groups and sections for that upgrade path, ordering by group and sequence. Starting with the first group and first sequence, PTIAANALYSIS parses each Application Engine section definition following the flow from step to step and through any nested call sections. As it follows the flow, it inserts rows into the PS_PTIA_ANALYSIS table for each Application Engine Section, Step, and Action it comes across. PTIAANALYSIS maintains a counter as it goes and increments the counter as it writes each Action to the PS_PTIA_ANALYSIS table. By the end of this first task, the PS_PTIA_ANALYSIS table will describe the entire upgrade from top to bottom, from the first Application Engine section in the first Upgrade Group to the last section in the last Upgrade Group. By querying the PS_PTIA_ANALYSIS table and ordering by PTIA_AESTMTSEQ, the whole will be described, including any nested call sections.

It is important to note that the PS_PTIA_ANALYSIS table contains every actual Step in the chosen upgrade path. During the data conversion runtime phase, it is likely that not all these steps will be executed because specific data composition and various application options will prevent some sections or steps from running. With the PTIA process, data composition can affect the data conversion runtime flow, which makes it impossible to predetermine the exact runtime flow the conversion will follow.

The PTIAANALYSIS Application Engine reads the data conversion code for your defined upgrade path (where the path is defined in the PS_PTIA_DCAEPGMS table with PTIA_UPG_CONV_TYPE= “MAIN”).

The Application Engine Analyzer program leverages two PeopleCode functions included with PeopleSoft PeopleTools 8.50 or higher. The two PeopleCode functions are:

  • GetProgText: A function that retrieves a PeopleCode program as text.

  • ResolveMetaSQL: A function that returns a string of SQL text that has had its metasql resolved.

Reviewing Data Conversion Query Parsing

After PTIAANALYSIS determines the upgrade path flow, it traverses the flow again looking at all the different Step Actions to determine which SQL is being executed by that Step. Most action types are straightforward; SQL, Do Select. PeopleCode is the most complicated action type. A Java program parses the PeopleCode and pulls all the SQL executed in the PeopleCode. The results of the action type analysis end up in a table called PS_PTIA_DTLIDSQLS, which stores a reference to PS_PTIA_ANALYSIS, along with the SQL statements associated with each Step Action. In the case of PeopleCode, there may be many rows in the PS_PTIA_DTLIDSQLS table for each PeopleCode reference in PS_PTIA_ANALYSIS. In addition, a second shadow table, called PS_PTIA_DTLIDSQLSR, is also populated during action type analysis. The only difference between PS_PTIA_DTLIDSQLS and PS_PTIA_DTLIDSQLSR is that PS_PTIA_DTLIDSQLSR contains the fully resolved SQL statements. For example, if the original SQL in a Step was:

UPDATE PS_BEN_DEFN_COST SET RATE_TBL_ID = %Substring(%Sql(UPG_HC_221,RATE_TBL_ID),1,4) %Concat ’-2’ WHERE RATE_TYPE=’2’ AND RATE_TBL_ID IN ( SELECT RATE_TBL_ID FROM PS_UPG_BN_RATES WHERE RATE_TYPE=’2’)

Then this would be resolved to platform-specific SQL. In the case of SQL server it would be:

UPDATE PS_BEN_DEFN_COST SET RATE_TBL_ID = SUBSTRING(RTRIM(RATE_TBL_ID),1,4) + ’-2’ WHERE RATE_TYPE=’2’ AND RATE_TBL_ID IN (SELECT RATE_TBL_ID FROM PS_UPG_BN_RATES WHERE RATE_TYPE=’2’)

Each of these SQL statements is further parsed to determine the tables that participate in the query. The results are stored in the PS_PTIA_DTLIDTBLS table. A query can have zero or one target tables. If the query is an INSERT, UPDATE, DELETE, etc, then there will be one target. If the query is a select statement, then there will be no target table. For the previously stated query, you would expect to see 2 rows in the PS_PTIA_DTLIDTBLS table. The first row would be for the PS_BEN_DEFN_COST table with an PTIA_TABLEUSAGE value of T because it is the target table of the query. The second row would be for the PS_UPG_BN_RATES table with an PTIA_TABLEUSAGE value of S because it is a source table in the query.

At this point we have gathered all the information we need about the specific upgrade path to build a dependency model. The dependency model is solely based on which tables are affected by which steps and follows some very simple rules. Most of these rules are inherent in the Upgrade Group model.

Reviewing Custom Data Conversion Code

You can include custom data conversion code in the Initial Analysis and subsequent steps in the PTIA process by adding a row (or rows) to the PS_PTIA_DCAEPGMS table for each custom Application Engine section that is to be executed, where a row is defined as PTIA_UPG_PATH, PTIA_UPG_GROUP, SEQ_NUM, AE_APPLID, AE_SECTION, ACTIVE_FLAG, PTIA_UPG_CONV_TYPE, PTIA_UPG_GROUP_LVL.

Reviewing Table Usage Information

The data conversion analysis process attempts not only to identify the tables that are used in a given Application Engine step, but also how the tables are being used in the context of each step.

This information is stored in the analysis tables and documented in the Table Usage and Action columns of delivered PTIA reports, such as PTIA0001.SQR.

Valid values for the Table Usage column are:

  • S for Data Source

  • T for Data Target

  • X for Unknown

Note: An X value in the Table Usage column for the PS_PTIA_DUAL, PS_PTIA_COMMON_AET, PS_PTIA_NORECNAME, or PS_PTIA_STATE_AE tables is expected and does not impact the subsequent Dependency Analysis Process.

See Reviewing Dependency Analysis

Valid values for the Action column are:

  • CREATE

  • DELETE

  • DROP

  • INSERT

  • SELECT

  • TRUNCATE

  • UPDATE

  • UPDSTATS

  • UNKNOWN

  • OTHER

A valid value for the action “Unknown” is only applicable to PeopleCode steps and only occurs in instances when the parser encounters syntax such as GETRECORD, GETROWSET, CREATERECORD, or CREATEROWSET, and cannot determine which actions were being done against the variable.

A valid value for the action “Other” occurs in instances when the parser encounters syntax such as the “Invalid SQL Override” or other non-SQL statements such as application function calls.

Reviewing Non Parsable SQL

The data conversion analysis process may mark certain SQL statements as non parsable. This designation refers to SQL statements that the Application Engine Analysis process could not correctly process. When a SQL statement is marked non parsable, there are three options that you can use:

  • Modify the SQL so that the Application Engine Analyzer can process the statement. The following table compares sample non parsable and parsable SQL statements:

    NON PARSABLE SQL

    PARSABLE SQL

    UPDATE %Table(%BIND(RECNAME)) SET RELATIONSHIP = ’C’ WHERE RELATIONSHIP IN (’S’, ’D’)

    • UPDATE %TABLE(BN_834_MEMBER) SET RELATIONSHIP = ’C’ WHERE RELATIONSHIP IN (’S’, ’D’)

    • UPDATE %TABLE(DEP_BEN_EFF) SET RELATIONSHIP = ’C’ WHERE RELATIONSHIP IN (’S’, ’D’)

    • UPDATE %Table(EMERGENCY_CNTCT) SET RELATIONSHIP = ’C’ WHERE RELATIONSHIP IN (’S’, ’D’)

  • For non parsable SQL statements in PeopleCode, add an override line directly above the non parsable SQL to manually document the Source and Target tables that are in use.

    Note: There is no override option for Application Engine SQL steps that are marked as non parsable.

    Note: Entering inaccurate or incomplete information in the override statement may result in data conversion sections being run in the incorrect dependent order, which can produce incorrect conversion results, such as data errors.

    Note: Tables defined in the override statement require the PS_ prefix.

    Correct = PS_JOB

    Incorrect = JOB

    The following table gives sample override lines for various situations:

    Syntax

    Sample Override Lines

    When Source and Target tables are explicitly known and static

    For example:

    • REMSQLANALYSIS:T:<Tgt Table>,<Tgt Table>:S:<SRC Table>,<SRC Table>;

    • REMSQLANALYSIS:T::S:<SRC Table>,<SRC Table>;

    • REMSQLANALYSIS:T:<Tgt Table>,<Tgt Table>:S:;

    When Source and/or Target Tables are determined based on a query

    For example:

    • REM SQLANALYSIS:T:%SQL(SQLid [, paramlist]):S:[table name];

    • REM SQLANALYSIS:T:<Tgt Table>,<Tgt Table>:S:%SQL(SQLid [, paramlist]);

    • REM SQLANALYSIS:T:%SQL(SQLid [, paramlist]):S: %SQL(SQLid [, paramlist]);

    • REM SQLANALYSIS:T::S:%SQL(SQLid [, paramlist]);

    • REM SQLANALYSIS:T:%SQL(SQLid [, paramlist]):S:;

    Where:

    SQLid: Specify the name of an existing SQL definition.

    paramlist: Specify a list of arguments for dynamic substitutions at runtime. The first argument replaces all occurrences of %P(1) in the referenced SQL definition, the second argument replaces %P(2), and so forth.

    Note: The paramlist arguments must be static values. Variable values in the parmlist are not permitted.

    Note: The Query is resolved at the time the Data Conversion Analysis is executed. It is NOT resolved during the Data Conversion Runtime.

    Note: The Query must return one or more valid RECNAME values. No other return results are permitted.

    Where there is no Source or Target table to be defined an/or the non parsable SQL is to be excluded from the table and dependency analysis.

    REMSQLANALYSIS:T::S:PS_PTIA_NORECNAME;

    Note: The “REMSQLANALYSIS:T::S:;” syntax is not a valid override and will be marked as “Invalid” by the PTIAANALYSIS Program.

  • Leave the SQL as it is. This results in the non parsable SQL being marked as “dependent” on all steps that exist prior to it, and all steps subsequent to the non parsable SQL become dependent on it.

    Note: This will likely result in slowing the runtime of data conversion and is not recommended.

Reviewing the Data Conversion Repositories

The tables in the Data Conversion Analysis repository hold the following data:

  • Step actions stored in execution order.

  • SQL clauses extracted from step actions.

  • Tables featured in SQL clause.

  • Bind variables used in SQL.

Analysis information is stored in the following tables:

  • PS_PTIA_DCAEPGMS

  • PS_PTIA_ANALYSIS

  • PS_PTIA_ANALYSISTX

  • PS_PTIA_DATACONV

  • PS_PTIA_DTLIDSQLS

  • PS_PTIA_DTLIDSQLSR

  • PS_PTIA_DTLIDTBLS

  • PS_PTIA_RUNDEPEND

  • PS_PTIA_SECDEPEND

  • PS_PTIA_SECLISTTMP

  • PS_PTIA_STEPDEPEND

The following Analysis tables make up the PTIA process:

PS_PTIA_DATACONV

The PS_PTIA_DATACONV table is based on the table definition for PS_PTIA_DCAEPGMS. It stores the upgrade Application Engine sections for the chosen upgrade path.

COLUMN

DESCRIPTION

PTIA_UPG_PATH

Upgrade Path copied from PS_PTIA_DCAEPGMS

PTIA_DBTYPE

Stores the text equivalent of the standard DBTYPE codes

PTIA_UPG_GROUP

Upgrade Group

PTIA_UPG_GROUP_LVL

Upgrade Group Level

PTIA_UPG_CONV_TYPE

Conversion type: MAIN or DDL

SEQ_NUM

Upgrade Sequence Copied from PS_PTIA_DCAEPGMS

AE_APPLID

Upgrade Application Engine Copied from PS_PTIA_DCAEPGMS

AE_SECTION

Upgrade Application Engine Section Copied from PS_PTIA_DCAEPGMS

ACTIVE_FLAG

Active Flag Copied from PS_PTIA_DCAEPGMS

PTIA_RUNDURATION

Elapsed time for this section to run during data conversion

PTIA_RUNSTATUSFLAG

Run Status Flag (Y-complete, N-not run yet, R-Running, F-Failed)

PTIA_GUID

GUID generated by the Data Conversion runtime engine

PS_PTIA_ANALYSIS

This is the main analysis table. The Application Engine Analyzer (PTIAANALYSIS) writes a row to this table for every Action in each Root Section of the specified upgrade path.

COLUMN

DESCRIPTION

PTIA_UPG_PATH

Upgrade Path copied from PS_PTIA_DCAEPGMS

PTIA_DBTYPE

Stores the text equivalent of the standard DBTYPE codes

PTIA_UPG_GROUP

Upgrade Group

PTIA_UPG_GROUP_LVL

Upgrade Group Level

PTIA_UPG_CONV_TYPE

Conversion type: MAIN or DDL

SEQ_NUM

Upgrade Sequence copied from PS_PTIA_DCAEPGMS

PTIA_TOPAEAPPLID

Upgrade Application Engine copied from PS_PTIA_DCAEPGMS

PTIA_TOPAESECTN

Upgrade Application Engine Section copied from PS_PTIA_DCAEPGMS

PTIA_TOPAESTEP

Upgrade Section Step

PTIA_TOPAESEQNUM

Upgrade Section Sequence Number

PTIA_AELEVEL

Nesting level for Call Section

AE_APPLID

Actual Application Engine Program (same as PTIA_TOPAEAPPLID if PTIA_AELEVEL is 1)

AE_SECTION

Actual Section (same as PTIA_TOPAESECTN if PTIA_AELEVEL is 1)

AE_STEP

Actual Step (same as PTIA_TOPAESTEP if PTIA_AELEVEL is 1)

AE_SEQ_NUM

Actual Seq Num (same as PTIA_TOPAESEQNUM if PTIA_AELEVEL is 1)

MARKET

Market

DBTYPE

DBTYPE

AE_DO_SECTION

If Step Action is Call Section, then this is the section to be called

AE_DO_APPL_ID

If Step Action is Call Section, then this is the program to be called

AE_DYNAMIC_DO

Indicates the Call Section is a dynamic call section

STEP_DESCR

Step Description

AE_STMT_TYPE

Action Type e.g. S-SQL, P-PeopleCode, D-DoSelect, H-DoWhen etc

PTIA_STMTTYPENUM

Numeric identified for AE_STMT_TYPE (used for ordering step actions)

PTIA_AESTMTSEQ

Sequence used to order the steps actions for the whole upgrade

AE_REUSE_STMT

Standard Application Engine Reuse Statement flag

AE_DO_SELECT_TYPE

Standard Application Engine Do Select Type

DETAIL_ID

Section.Step.Action identifier used as a key to most PTIA tables

PTIA_INFO1

Extra Information mostly related to FUNCLIB calls

PTIA_INFO2

Extra Information mostly related to FUNCLIB calls

PTIA_INFO3

Extra Information mostly related to FUNCLIB calls

PTIA_INFO4

Extra Information mostly related to FUNCLIB calls

PTIA_INFO5

Extra Information mostly related to FUNCLIB calls

SQLID

For SQL step, the SQLID of the SQL this step action executes

PTIA_STMTDESCR

Description copied from Application Engine Step Description

PTIA_HASPARENTS

This Step has dependencies on other one or more other Steps

PTIA_HASCHILDREN

One or more other Steps have a dependency on this step

PTIA_HASWHERE

The SQL has a where clause – Mostly used by PeopleSoft Development

PS_PTIA_DTLIDSQLS

This table holds a reference to every SQL in the conversion code for the specified upgrade path.

COLUMN

DESCRIPTION

PTIA_GUID

GUID generated by the Data Conversion runtime engine

DETAIL_ID

Section.Step.Action identifier used as a key to most PTIA tables

AE_APPLID

Actual Application Engine Program

DBTYPE

DBTYPE

PTIA_UPG_PATH

Upgrade Path copied from PS_PTIA_DCAEPGMS

PTIA_DBTYPE

Text equivalent of the standard DBTYPE codes

PTIA_UPG_CONV_TYPE

Conversion type: MAIN or DDL

PTIA_SQLNUM

SQL Number, for peoplecode there may be many SQL statements

PTIA_AESTMTLEN

Length of the text of the SQL statement

PTIA_OBJ_TYPE

S-SQL or P-PeopleCode

TABLE_NAME

Main Table in the SQL Statement, Blank if SQL is SELECT with many tables

PTIA_DMLACTION

INSERT, UPDATE, DELETE, SELECT etc

PTIA_LINENUM

Refers to the PeopleCode line number where the SQL is defined

PTIA_SQLPASSDPARSE

Indicates whether SQL parser was able to successfully parse the SQL statement

DESCR254

Description column

PTIA_PARAMCLAUSE

Bind variable used in the SQL

PTIA_INFO1

Extra Information mostly related to FUNCLIB calls

PTIA_INFO2

Extra Information mostly related to FUNCLIB calls

PTIA_INFO3

Extra Information mostly related to FUNCLIB calls

PTIA_INFO4

Extra Information mostly related to FUNCLIB calls

PTIA_INFO5

Extra Information mostly related to FUNCLIB calls

PTIA_CHUNKSEQ

Statement Chunk Sequence

PTIA_TEXTCHUNK

Statement executed by this Step

PS_PTIA_DTLIDSQLSR

This table differs slightly from the PS_PTIA_DTLIDSQLS table in that the SQL statement has been fully resolved into platform-specific SQL. This makes it much easier to see what is happening in the SQL.

COLUMN

DESCRIPTION

PTIA_GUID

GUID generated by the Data Conversion runtime engine

DETAIL_ID

Section.Step.Action identifier used as a key to most PTIA tables

AE_APPLID

Actual Application Engine Program

DBTYPE

DBTYPE

PTIA_UPG_PATH

Upgrade Path copied from PS_PTIA_DCAEPGMS

PTIA_DBTYPE

Text equivalent of the standard DBTYPE codes

PTIA_UPG_CONV_TYPE

Conversion type: MAIN or DDL

PTIA_SQLNUM

SQL Number, for PeopleCode there may be many SQL statements

PTIA_CHUNKSEQ

Statement Chunk Sequence

PTIA_TEXTCHUNK

Statement executed by this Step

PS_PTIA_DTLIDTBLS

This table holds a reference to every SQL in the conversion code for the specified upgrade path and which Tables or Records are in use for each piece of SQL.

COLUMN

DESCRIPTION

PTIA_GUID

GUID generated by the Data Conversion runtime engine

DETAIL_ID

Section.Step.Action identifier used as a key to most PTIA tables

AE_APPLID

Actual Application Engine Program

PTIA_UPG_PATH

Upgrade Path copied from PS_PTIA_DCAEPGMS

PTIA_DBTYPE

Text equivalent of the standard DBTYPE codes

PTIA_UPG_CONV_TYPE

Conversion type: MAIN or DDL

PTIA_SQLNUM

SQL Number, for peoplecode there may be many SQL statements

RECNAME

Record Name

TABLE_NAME

Associated Table Name

PTIA_TABLEUSAGE

T-Target, S-Source

PTIA_TABLETYPE

R-Record, S-State Record, U-Upgrade Table, V-View, T-TempTable

PTIA_INFO1

Extra Information mostly related to FUNCLIB calls

PTIA_INFO2

Extra Information mostly related to FUNCLIB calls

PTIA_INFO3

Extra Information mostly related to FUNCLIB calls

PTIA_INFO4

Extra Information mostly related to FUNCLIB calls

PTIA_INFO5

Extra Information mostly related to FUNCLIB calls

PS_PTIA_STEPDEPEND

By querying PS_PYIA_DTLIDTBLS and PS_PTIA_ANALYSIS, it is possible to determine which steps have dependencies and what those dependencies are.

COLUMN

DESCRIPTION

PTIA_UPG_PATH

Upgrade Path copied from PS_PTIA_DCAEPGMS

PTIA_DBTYPE

Text equivalent of the standard DBTYPE codes

PTIA_UPG_CONV_TYPE

Conversion type: MAIN or DDL

PTIA_P_UPG_GROUP

Parent Data Conversion Group

PTIA_P_UPGGRPLVL

Parent Data Conversion Group Level

PTIA_P_SEQNUM

Parent Application Engine Section Sequence Number

PTIA_P_TOPAEAPPLID

Parent Data Conversion Application Engine Program

PTIA_P_TOPAESECTN

Parent Data Conversion Application Engine Section

PTIA_P_TOPAESTEP

Parent Data Conversion Application Engine Step

PTIA_P_TOPAESEQNUM

Parent Data Conversion Application Engine Step Sequence

PTIA_P_AEAPPLID

Parent Application Engine Program

PTIA_P_AESECTION

Parent Application Engine Section

PTIA_P_AESTEP

Parent Application Engine Step

PTIA_P_AESEQNUM

Parent Application Engine Step Sequence within the Section

PTIA_P_AESTMTSEQ

Parent Application Engine Step Sequence across whole upgrade

PTIA_P_DETAILID

Parent Application Engine Step Detail ID

PTIA_P_SQLNUM

Parent Application Engine Detail ID SQL Sequence

PTIA_C_UPG_GROUP

Child Data Conversion Group

PTIA_C_UPGGRPLVL

Child Data Conversion Group Level

PTIA_C_SEQNUM

Child Application Engine Section Sequence Number

PTIA_C_TOPAEAPPLID

Child Data Conversion Application Engine Program

PTIA_C_TOPAESECTN

Child Data Conversion Application Engine Section

PTIA_C_TOPAESTEP

Child Data Conversion Application Engine Step

PTIA_C_TOPAESEQNUM

Child Data Conversion Application Engine Step Sequence

PTIA_C_AEAPPLID

Child Application Engine Program

PTIA_C_AESECTION

Child Application Engine Section

PTIA_C_AESTEP

Child Application Engine Step

PTIA_C_AESEQNUM

Child Application Engine Step Sequence within the Section

PTIA_C_AESTMTSEQ

Child Application Engine Step Sequence across whole upgrade

PTIA_C_DETAILID

Child Application Engine Step Detail ID

PTIA_C_SQLNUM

Child Application Engine Detail ID SQL Sequence

PTIA_TABLENAME

Common table referenced by the parent and child step

PTIA_P_TABLEUSAGE

Parent table usage T-Target, S-Source

PTIA_C_TABLEUSAGE

Child table usage T-Target, S-Source

PS_PTIA_ SECDEPEND

This table is an aggregation of PS_PTIA_STEPDEPEND to the Section level.

COLUMN

DESCRIPTION

PTIA_UPG_PATH

Upgrade Path copied from PS_PTIA_DCAEPGMS

PTIA_DBTYPE

Text equivalent of the standard DBTYPE codes

PTIA_UPG_CONV_TYPE

Conversion type: MAIN or DDL

PTIA_P_UPG_GROUP

Parent Data Conversion Group

PTIA_P_UPGGRPLVL

Parent Data Conversion Group Level

PTIA_P_TOPSEQNUM

Parent Application Engine Section Sequence Number

PTIA_P_TOPAEAPPLID

Parent Data Conversion Application Engine Program

PTIA_P_TOPAESECTN

Parent Data Conversion Application Engine Section

PTIA_P_AESTMTSEQ

Parent Application Engine Step Sequence across whole upgrade

PTIA_C_UPG_GROUP

Child Data Conversion Group

PTIA_C_UPGGRPLVL

Child Data Conversion Group Level

PTIA_C_TOPSEQNUM

Child Application Engine Section Sequence Number

PTIA_C_TOPAEAPPLID

Child Data Conversion Application Engine Program

PTIA_C_TOPAESECTN

Child Data Conversion Application Engine Section

PTIA_C_AESTMTSEQ

Child Application Engine Step Sequence across whole upgrade

PTIA_DEPENDSOURCE

Dependency Rule

PTIA_DEPENDRULE

DEPENDENT or INDEPENDENT

PTIA_EXCLUDEFLAG

Indicates whether this dependency should be excluded from the runtime dependency calculation

PS_PTIA_RUNDEPEND

This table represents the section dependency model. You can query this table for any given data conversion Application Engine Section to determine what it depends on and what depends on it. The runtime data conversion Application Engine (PTIADATACONV) uses this table to determine which sections are eligible to run.

COLUMN

DESCRIPTION

PTIA_UPG_PATH

Upgrade Path copied from PS_PTIA_DCAEPGMS

PTIA_DBTYPE

Text equivalent of the standard DBTYPE codes

PTIA_P_UPG_GROUP

Parent Data Conversion Group

PTIA_P_TOPSEQNUM

Parent AE Section Sequence Number

PTIA_P_TOPAEAPPLID

Parent Data Conversion AE Program

PTIA_P_TOPAESECTN

Parent Data Conversion AE Section

PTIA_C_UPG_GROUP

Child Data Conversion Group

PTIA_C_TOPSEQNUM

Child AE Section Sequence Number

PTIA_C_TOPAEAPPLID

Child Data Conversion AE Program

PTIA_C_TOPAESECTN

Child Data Conversion AE Section

PTIA_DEPTH

Dependency Nesting

Reviewing Dependency Analysis

This section discusses:

  • Understanding Dependency Analysis

  • Reviewing Data Conversion Runtime Rules

Understanding Dependency Analysis

The table usage information identified in the Initial Analysis is subsequently used to determine the dependencies between AE Steps. The Step Dependency Information is then aggregated to the “Root Section” level where a Root Section is defined as a row in the PS_PTIA_DCAEPGMS table (PTIA_UPG_PATH, PTIA_UPG_GROUP, SEQ_NUM, AE_APPLID, AE_SECTION, ACTIVE_FLAG, PTIA_CONV_TYPE, PTIA_UPG_GROUP_LVL).

Reviewing Data Conversion Runtime Rules

The following rules are the data conversion runtime rules:

  • Dependencies are derived from tables referenced in SQL or PeopleCode actions in Upgrade Sections.

  • Dependencies follow the Upgrade Group sequencing. If Section ABC in Upgrade Group 1 updates a given table, then any Section assigned a higher sequence than ABC that updates or queries that same table cannot run until Section ABC is complete.

  • Upgrade groups are further grouped into levels. For example, objects defined in Level 2 are dependent on objects defined in Level 1. Additionally, objects defined in Level 3 are dependent on objects defined in Level 1 and Level 2.

  • Upgrade groups within the same level do not depend on each other. If Section QWE in Upgrade Group 2 updates table FFF and Section ASD in Upgrade Group 3 also updates table FFF and Upgrade Groups 2 and 3 are at the same level, there is no dependency created.

  • Upgrade groups create dependencies on sections within their own upgrade group and upgrade groups of lower levels. If Section ABC in Upgrade Group 1 updates table FFF and Section QWE in Upgrade Group 2 also updates table FFF, and Upgrade Group 1 is in a lower level than Upgrade Group 2, then Section QWE becomes dependent on Section ABC.

  • Tables as sources do not create dependencies. If Section ZXC in Upgrade Group 1 selects from table FFF, and then Section BNM in Upgrade Group 1 also selects from table FFF, no dependency is created.

  • If a Section has a SQL statement that PTIAANALYSIS cannot understand, the SQL is flagged as non parsable from the parser point of view (the Data Conversion will still run fine) and a hard dependency is created. This means for every Section with a query that cannot be parsed, it becomes dependent on every Section sequentially above it in its Upgrade Group, and on every Section in Upgrade Group 1. Furthermore, every Section sequentially afterward becomes dependent on it.

  • Usage of the PS_PTIA_DUAL, PS_PTIA_COMMON_AET, or PS_PTIA_NORECNAME tables never results in a dependency.

Example of Dependency Rules Calculation

In the following example, the highlighted row (with values in italics) would be dependent on itself and all items in Level’s MAIN-1 and MAIN-2, but not the other items in Level MAIN-3 nor items in Level DDL-1.

PATH

GROUP

SEQ_NUM

AE_APPLID

AE_SECTION

PTIA_CONV_TYPE

GROUP_LEVEL

HC89

1

260

UPG_HR89

HCHRS01

MAIN

1

HC89

1

265

UPG_FG89

FGHCS01

MAIN

1

HC89

3

230

UPG_BN89

HCBNS06

MAIN

2

HC89

4

165

UPG_GPBR90

HCBRP040

MAIN

2

HC89

7

40

UPG_EP89

HCEPS25

MAIN

2

HC89

7

50

UPG_EP89

HCEPS30

MAIN

2

HC89

10

20

UPG_TL89

HCTLK01

MAIN

2

HC89

80

140

UPG_TL90

HCTLP04

MAIN

3

HC89

80

160

UPG_TL90

HCTLP06

MAIN

3

HC89

85

170

UPG_PY90

HCPYM01

MAIN

3

HC89

85

180

UPG_PY90

HCPYP01

MAIN

3

HC89

10

200

UPG_PY90

HCPYP09

DDL

1

HC89

10

210

UPG_PY90

HCPYP10

DDL

1

Reviewing Runtime for PTIADATACONV

This section discusses:

  • Understanding Runtime for PTIADATACONV

  • Querying the PTIA Tables

Understanding Runtime for PTIADATACONV

All runtime information for PTIADATACONV is stored in the following tables:

  • PS_PTIA_DATACONV

  • PS_PTIA_RUNSTATUS

  • PS_PTIA_RUNDETAIL

  • PS_PTIA_RUNCOUNT

The PTIADATACONV Application Engine leverages the Dependency Analysis to optimize the runtime of the data conversion. The runtime of the data conversion is improved in the new PeopleSoft release by running multiple instances of PTIADATACONV in parallel, executing against a single set of dependency information. The optimal number of instances to be initiated will vary.

PTIADATACONV determines which “Root Sections” are able to run and executes them. A Root Section is able to run when all Root Sections that are dependent on it have completed successfully.

In the event that multiple root sections are able to run at the same time, steps that have the largest number of dependent Root Sections and/or Root Sections that have the longest runtime (in a previous run), are given priority.

In the event of failure, the instance of PTIADATACONV that encountered the error will mark the step as “Failed” and stop. All other instances of PTIADATACONV will continue to run. Steps that are dependent on a “Failed” step will be marked as “Blocked” and will not be executed as part of the current run. Upon restarting the process, the “Failed” section and any “Blocked” sections will be executed.

The following list describes the PTIADATACONV program flow:

  • The run is initialized.

    This initial phase determines if this is a brand new run or if it is a restart of a previously failed run. If it is a new run, then PTIADATACONV sets up a thread in PS_PTIA_RUNSTATUS.

  • PTIADATACONV performs a simple test to verify that there is work to do.

    If there is work to do, then PTIADATACONV runs Data Conversion Application Engine Sections that have not already run. This is a fairly simple Do While loop that counts eligible sections left to run. If there are no more sections left to run, processing stops. The work inside the loop consists of executing a process to check the status of any other thread that is running. If a thread dies, it cannot clean itself up, so one of the other threads has to perform the cleanup. The cleanup mostly consists of setting the status flag in PS_PTIA_DATACONV to “F” for the AE Section that failed.

  • SQLs run to look for work to do.

    The SQL object PTIA_FINDSECTIONTORUN finds the next eligible section to run. If the query returns nothing, we execute another SQL object called PTIA_COUNTSECTIONSNOTDONE to count how many Sections are left to run. If PTIA_FINDSECTIONTORUN returns no work to do and PTIA_COUNTSECTIONSNOTDONE returns Sections still need to be run, then there must be a Section already running that must complete before anything else can run. If there is no work to do, the loop issues a pause before the loop completes and executes the next loop.

  • PTIADATACONV performs more housekeeping to reset statuses on successful completion of all Data Conversion Application Engine Sections.

  • A completion message is written to the log file.

Reviewing PTIA Reporting

This section discusses:

  • Understanding PTIA Reporting

  • Reviewing the Tables Referenced Report

  • Reviewing the Customization Impacts Report

  • Reviewing Execution Report by Section – Duration

  • Reviewing Execution Report by Section – Start Time

  • Reviewing the Execution Report by Step

  • Reviewing the Execution by Thread Report

  • Reviewing the Thread Duration Report

  • Reviewing the Execution Comparison Report

  • Reviewing the Table Analysis Report

  • Reviewing the Data Conversion Report

Understanding PTIA Reporting

You can query all tables populated and leveraged by PTIA (as identified previously) through the various platform specific query tools or psquery. You can gather information in the PTIA tables to identify the following:

  • Tables referenced in the data conversion code.

  • Steps impacted by customizations (prior to the initial data conversion run).

  • Performance issues (after the initial data conversion run).

  • Impact of changes (run to run timing comparisons).

Oracle has delivered a series of standard reports to address the most commonly accessed information in the PTIA repository.

Reviewing the Tables Referenced Report

PTIA0001.SQR lists all tables referenced within the Application Engine data conversion programs. For each table listed, the report displays the section and step in which it is used, whether it is a data source or data target table, and the type of SQL statement in which it is referenced. This report is sorted by table name. Data for this report comes from the PS_PTIA_ANALYSIS, PS_PTIA_DTLIDSQLS, and PS_PTIA_DTLIDTBLS tables. This report can be run anytime after the PTIAANALYSIS Application Engine program has run and populated the PTIA tables used by this SQR.

Reviewing the Customization Impacts Report

PTIA0002.SQR shows the section/steps within the Application Engine data conversion programs that referenced tables with custom added fields. This report is sourced from the PS_PTIA_ANALYSIS table and the PSPROJECTITEM table. This report must be run after the customizations project has been compared against the New Release Demo database.

Reviewing Execution Report by Section – Duration

PTIA0003.SQR shows the duration or execution time for each Application Engine section. Since this report is at a section level, the information is sourced from the PS_PTIA_RUNDETAIL table. The report is ordered by execution time with the poorest performing steps at the top. This report can be run anytime after the PS_PTIA_RUNDETAIL table has been populated for the data conversion run on which you want to report.

Reviewing Execution Report by Section – Start Time

PTIA0004.SQR shows the duration or execution time for each section. Since this report is at a section level, the information will be sourced from the PS_PTIA_RUNDETAIL table. The report would be ordered by start time so that you can see the order in which the sections were executed. This report can be run anytime after the PS_PTIA_RUNDETAIL table has been populated for the data conversion run on which you want to report.

Reviewing the Execution Report by Step

PTIA0005.SQR shows the execution time for each section and the associated steps that were run.

This report requires a trace of 16,384 or higher.

Since this report is at a step level, it assumes that a trace of 16,384 or higher has been run so that the step information could be obtained from the PS_PTIA_TIMINGS_DT table. If the appropriate trace has not been run, then a report is not created and output files will be produced. The report will be ordered by execution time with the poorest performing steps at the top.

Reviewing the Execution by Thread Report

PTIA0006.SQR shows the execution timing of each Application Engine section run as part of the data conversion process. This report is sorted so that you can see which sections were executed by each thread. This report is sourced from the PS_PTIA_RUNDETAIL table.

Reviewing the Thread Duration Report

PTIA0007.SQR shows the total duration time for each thread used during the data conversion process. This report is sourced from the PS_PTIA_RUNDETAIL table. It can be run anytime after the PS_PTIA_RUNDETAIL table has been populated from the data conversion run on which you want to report.

Reviewing the Execution Comparison Report

PTIA0008.SQR shows the execution duration from the current run of data conversion as compared to the execution duration from the previous run of data conversion. This report is sourced from the PS_PTIA_RUNDETAIL table. This report can be run anytime after the PS_PTIA_RUNDETAIL table has been populated for the data conversion runs on which you want to report.

Reviewing the Table Analysis Report

PTIA0009.SQR indicates how a particular application table is impacted by the create/alter scripts as well as the data conversion process during the PeopleSoft upgrade. This report is sourced from the PS_PTUALTRECDATA, PS_PTUALTRECFLDDAT, PS_PTIA_ALTRECDATA, PS_PTIA_ANALYSIS, and PS_PTIA_DTLIDTBLS tables. This report can be run after the Alter Analyzer and the AE Analyzer processes have successfully completed. This report is designed to be run against the initial pass database as the data stored in the tables during the Move to Production will differ.

Reviewing the Data Conversion Report

Each of the upgrade data conversion sections contains comments that describe the processing performed by the section. Oracle delivered an SQR (PTIA0010.sqr) to list all of these comments by the group and sequence numbers that determine how they run. The name of this report is PTIA0010.

To run PTIA0010:

  1. Using SQRW, run SQR PTIA0010 on your copy of Production database.

  2. When prompted for upgrade path, enter (for example):

    HC90

    CR90

    CR91