Understanding Data Conversion Utilities

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

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.

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.

PS_PTIA_DTLIDSQLS

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

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.

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.

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.

PS_PTIA_ SECDEPEND

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

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.

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.

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.

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