Using the Application Engine, we create application that performs background SQL processing against the Data. AE is used for batch processing where we have large amount of data that needs to be processed without user intervention.
We can use AE for straight, row-by-row processing, but the most AE programs are written to perform set-based processing.
Application Engines execution component is written in C++ and is part of and maintained by – PeopleTools. This executable program is called PSAE.exe.
Application Engine Advantages:
1. Graphical Developer Interface
2. Encapsulation
3. Data Dictionary Integration
4. Enhanced SQL/Meta-SQL Support
5. Effective-Dating
6. Platform Flexibility
7. Reuse Business Logic
8. Upgrade Support
9. Built-in Restart Logic
1. Graphical Developer Interface: with PeopleSoft 8, AE is fully integrated with People Tools. This means that we can use the Application Designer, an intuitive graphical interface to create Application Engine Programs.
2. Encapsulation: An AE program resides completely within the DB. With Application Engine, we do not have to compiler our programs, there are no statements to store, and there is no need to directly interact with the operating environment we use.
3. Data Dictionary Integration: one of the cornerstones of PeopleSoft functionality is the Application Designer. Because of the way it works, most field attributes, such as type, length and scale, only need to be specified once, and that change will be reflected globally. If the same field appears on more than one record, we know that the same attributes reside in each record definition.
Because AE works in harmony within the PeopleSoft system and references the Data Dictionary for object definitions, changes to meta-data in the database have no impact on AE programs.
4. Enhanced SQL/Meta-SQL support: we can write SQL within AE, or we can copy SQL statements into AE from any SQL utility with few, if any changes.
RDBMS platforms have many differing syntax rules-especially in regard to Date, time and other numeric calculations. For the most part, we can work around this problem using PeopleSoft meta-SQL, which AE supports. This language is designed to replace RDBMS-specific SQL syntax with a standard syntax.
For example, if we reference a date field called AE_DATE in a SELECT clause, we specify it as %DATEOUT(AE_DATE). This meta-string will be resolved to the appropriate value as required by the current RDBMS platform. Meta-SQL also allows to dynamically generating portions of SQL statements. This new meta-SQL is not supported in Cobol or SQR.
Fox example, if we wanted to join 2 tables on their common keys, we could use the following code:
%Join(COMMON_KEYS, PSAESECTDEFN ABC, PSAESTEPDEFN XYZ)
at run time, the function would be expanded into the following
ABC.AE_APPLID = XYZ.AE_APPLID
AND ABC.AE_SECTION = XYZ.AE_SECTION
AND ABC.DBTYPE = XYZ.DBTYPE
AND ABC.EFFDT = XYZ.EFFDT
5. Effective-Dating: Application Sections are effective-dated, meaning we can activate/deactivate a Section as of a particular date. This enables us to archive Sections as we modify them, instead of destroying them. In the future if we decide to revert to a previous incarnation of a section, we can simply reactive it.
6. Platform Flexibility: in AE we have the ability to call different versions of a Section for different platforms, as needed.
Default : This is the common denominator or global version.
Supported RDBMS platform: DB2, Oracle, Informix, DB2/Unix, Sybase, MS SQL Server.
Most Sections will only have the default version and even in cases where we refine a particular Section for a specific RDBMS, a default version of that Section will also exist. When AE executes a Section, it will use the version that’s specific to the current DB platform if a specific version exists. If there is no REBMS-specific version, AE uses the (default) version.
Within platform-specific Sections, we also have the ability to call base portions of SQL statements by using the %SQL object. This means we can write our generic SQL portions just once, and then can reference them from different platform versions.
7. Reuse Business Logic: AE programs can now invoke PeopleCode. This means that we can call common PeopleCode functions from AE. Conversely, PeopleCode can now invoke an AE program Synchronously. AE Libraries are used to store common routines, stored as callable sections. Multiple programs can then call these sections.
8. Upgrade Support: AE program definitions are defined in and stored as Application Designer object, then join the growing ranks of PeopleTools components that we can easily upgrade using the Application Designer. We can upgrade AE definitions by project, program, or section.
9. Built-in Restart Logic: within each AE program, we must define how frequently our program will issue a COMMIT. After doing so, each COMMIT becomes a checkpoint that AE uses to locate where within a program to restart after an abend.
The restarted AE program locates the last checkpoint, or the last successfully executed and committed step, and continues with the processing from that point. This type of built-in logic does not exist with COBOL or SQR.
Key Points
1. The AE executable is PSAE.exe and is written in C++
2. AE is generated into the Application Designer to give you a graphical developer tool
3. AE programs are stored in PeopleSoft DB.
4. Applications can dynamically adjust for changes in record and field definitions through integration into the PeopleSoft Data Dictionary and meta-SQL, a situation requiring changes and recompilation of COBOL.
5. Using meta-SQL our programs can be written to be DB platform independent.
6. Portions of our program are effective-dated so we can activate/deactivate a section as of a particular date.
7. Our programs can take advantage of existing business logic in the form of shared PeopleCode
8. You have Upgrade integration for migrating programs to other instances of our DB as well as future release upgrade support.
9. AE has built in restart capability.
Application Engine programs are created and modified in the Application Designer. The Application Designer offers AE developers the following benefits
· Ability to open multiple AE programs concurrently.
· Ability to work on entire AE program at once. Users of previous versions will know that this is a change from only being allowed to work on a Section at a time.
· Easy access to PeopleCode and SQL editiors.
· 2 Views of our program
1. The Definition view is where we create and modify our programs.
2. The Program Flow view shows a graphical representation of our program execution.
Application Engine Program Structure
An AE program is made up of several key components.
1. Program
An Application Engine program identifies the set of processes to execute a given task. A program must contain at least one section. The execution of the program always starts with the Section defined as ‘MAIN’
2. Sections
An AE Section is comprised of 1 or more Steps and is equivalent to a cobol paragraph or an SQR procedure. All AE programs must contain at least 1 Section entitled ‘MAIN’
Execution precedence: A section is unique based on the program, section names, RDBMS platforms, effective date and market. When we execute and AE program, it executes Sections based on the following order
· If a Section for the current Market exists, execute it.
· If a Section for the current Platform or RDBMS exists, execute it.
· If a Section for the current Effective Date exists, execute it.
If a ‘Unique’ section does not exist, AE just executes the ‘base’ Section. The default Market value is GBL for Global, which is similar to the Platform value of default.
3. Steps
A Step is the smallest unit of work that can be committed within a program. Although, we can use a Step to execute a PeopleCode command or log a message, typically, we will use a Step to execute a SQL statement or to call another Section. The SQL or PeopleCode that a step executes are the Actions with the step.
Each section contains 1 or more steps. The steps are executed in the order that they are displayed in the Application Designer. The steps contain the logical groupings or actions to be performed.
Control
Description
Step Name
Consecutively numbered based on the last step name
Step Description
Enter a descriptive name for the step
Commit After
Determines when commit will be executed:
· Default – uses value supplied for the section
· Later – don not commit now
· After step - commit when step completes
Frequency
Only available in loops
On Error
Determines the action to take when an error occurs
· Abort – program will write message to message log and terminate
· Ignore – program will write message to log and continue
· Suppress – no message will be written, but program will continue.
Comment
Add any additional comments to document your program
4. Actions
There are multiple types of Actions that we can specify to include within a step. A single step can have multiple actions.
Actions are the reason for our program. They have the particular executable action we wish to take. The primary type of action for the AE program is a SQL statement we whish to have executed against the DB. But we also have the ability to log messages, execute PeopleCode, and call other Sections. Moreover, we can execute other sections conditionally and iteratively. The following action types are available
Do Select
Do While
Do Until
Do When
PeopleCode
SQL
Call Section
Log Message
We can note that there are 7 actions out of the 8, because the SQL and Call Section Actions are mutually exclusive.
Note: we must save our program before we can change the name of a Section. The only Section we cannot rename is MAIN.
One of the advantages of AE is that we can code a program to execute differently based on the market code. Based on the market selected, the program will execute the appropriate Sections.
Key Points
1. Applications are structured in a hierarchy of Sections, Steps and Actions.
2. we must determine the data sources and structures for our AE program.
3. We must generate the SQL required for our program. We can use the PeopleSoft Query tool or relational DB SQL tool.
4. Application Designer is used to build individual AE programs.
5. FASTTEST can be used from the AE Request Page to test our AE programs.
State Records:
When we create AE programs, we will need to pass variables; a State Record is the method by which we allocate variables in our AE program, and it also is the method by which we pass the values to subsequent program steps. An AE program may have 0, 1 or many State Records, but only one State Record will be the default.
The State Record can be a Derived Work Record or an SQL Table. The only difference is that Derived State records cannot have their values saved at commit time, there fore the values would be lost during the restart.
The State Record is used as a worktable for:
· Initial values
· Values to be passed to program Steps from prior Sections, Steps and Actions.
· Results of SQL Selects
· Values for SQL Updates, Inserts, Deletes
· Computed values or counters
· Program range or scope values (Business Unit, Pay Groups, Departments)
· Program working values
· Programmable switches or flags
ü A State Record can be a Database Table of Derived Work Record
ü Name must end with _AET identifier
ü PROCESS_INSTANCE must be a key field
ü Date fields cannot be defined as required fields
ü %SELECT (meta-SQL) stores data into the State Record.
ü %BIND (meta-SQL) gets data out of the State Record.
ü Can have 1,nore or multiple State Records
ü In pre-release, it was called as ‘Cache’ Record.
ü No PeopleCode files on an AE State Record.
Values are inserted into the State Record, either by using the %Select statement or the Run Control.
Once a value is inserted into the State Record, it can be access ed using the %Bind(fieldname) to execute the SQL.
When the AE program completes successfully, the row of data in the State Record is deleted.
SQL Action Properties:
1. ReUse Statement: ReUse is an option we can enable to optimize the SQL
components of our batch program. ReUse converts any %BIND references to State Record Fields into real bind variables(:1, :2 and so on)
ReUse has the following options
a. Bulk Insert: This option turns on ReUse, and in addition, it holds all the data in a buffer and only performs an insert after a large volume of rows has gathered in the buffer.
b. No: This option disable the ReUse, with ReUse off, the AE runtime Process recompiles the SQL statement every time the loop executes. By default ReUse will be disabled.
c. Yes: Select this option to enable basic ReUse functionality.
2. No Rows: In the event that the SQL(Insert, Update, Delete) associated with the SQL Action does not return any rows, we need to specify what our AE program should do. The following list contains the options that we have when no rows are returned:
a. Abort – The Program terminates.
b. Section Break – AE exits the current section immediately, and control returns to the calling Step.
c. Continue – The program continues processing.
d. Skip Step – AE exits the current Step immediately and moves on to the next step. When using Skip Step the following in mind:
I. AE ignores the commit for the current step at runtime.
II. If the current Step contains only one Action, only use Skip Step to bypass the commit.
Note: In the Message Catalog, variables are entered using %1 and consecutively increasing the number for each variable.
Meta-SQL Alternative
When we use %SQL in a statement, AE replaces it with the specified SQL object. This allows commonly used SQL text to be shared among AE and PeopleCode programs alike. In AE the variables in the %SQL can be
· %BIND(fieldname)
· Literal
In PeopleCode SQL, we can use: Record.Field or :1
Update PS_CUSTOMER_PRD
Set CUSTOMER_ID = %BIND(TO_CUSTOMER_ID)
Where CUSTOMER_ID = %BIND(FROM_CUSTOMER_ID)
To rewrite this using %SQL it would be the following:
%sql(UPD_CUST_ID, PS_CUSTOMER_PRD, %BIND(TO_CUSTOMER_ID), %BIND(FROM_CUSTOMER_ID))
Key Points
1. A State Record is the method by which we allocate variables for AE program.
2. The developer creates the State Record
3. A State Record must be defined with the following criteria:
· The name of the record must end with AET
· The record contains only 1 key field that must be PROCESS_INSTANCE
· All other fields on the State Record are designed to meet the need of the program.
4. %SELECT is used to store data in the State Record.
5. %BIND is used to retrieve data from the State Record.
6. We can have 0,1 or many state records associated with an AE program.
7. The State Record can be either physical table or Derived Work Record.
8. State Records are associated with an AE program in the Object properties.
Testing & Debugging
Initially, the details of the failures are presented in the Process Monitor. To capture the details of the execution of our application, we have a variety of trace facilities including the Application Engine Step Trace and Application Engine SQL Trace.
To monitor the application and control its execution while it is running, we have Application Engine Debugger. If we have PeopleCode in our AE program, we can use the PeopleCode Debugger with or without the AE Debugger.
1. The Process Monitor:
The Process Monitor has a message log that will tell you if the program executed successfully or not. With a failure you will need to invoke other tools.
2. The Application Engine Trace File
The Application Engine at your request generates trace. You can specify Steps to see a sequence of messages tracking the Step execution of your application execution. We can also trace the more detailed execution of our programs SQL within the Steps. We can also have performance information logged. We can find and analyze the trace in
%temp%\ps\
3. The Application Engine Interactive Debugger
The Application Engine Debugger is an online, interactive, execution-monitoring tool. We can run our application step by step and look at the results of the execution in the State Record. We can dynamically modify the State Record. We can set Break Points and Watch Fields to see if our application executes portions of the application. We can control the execution of the program and output is dynamically presented to you in a windows command window.
4. The PeopleCode Debugger
We can use the PeopleCode Debugger if errors are occurring the our Application Engine PeopleCode.
Application Engine Trace:
For processes running on a Windows workstation, we can set our trace options in the Configuration Manager. This procedure is only valid when we are running the Application Engine program from the development environment or through the command line on the client.
Application Engine Trace files use the naming convention:
Application Engine Debugger:
The Application Engine’s internal debugger can be used while running on the client or when running directly on the Server- outside of the Process Scheduler. When we run our application, we will enter an interactive state in which we can set break points, issue COMMITs, look at the State record, etc.
Turning On the AE Debugger:
The AE debugger is turned on in the Configuration Manager. Open the Configuration Manager => First click on the Trace Tab and turn off the Statement Timings => Click on the Profile Tab => click on the Edit Push button => on the Process Scheduler page, we can find the Application Engine Options.
Debug –Turning this option ON will cause AE to run in debug mode every time we invoke AE in 2-tier.
Disable Restart -this Option will disable restart for all AE programs run in 2-Tier.
Process Monitor:
Programs run through the Process Scheduler, the Process Monitor keeps track of the status of the programs during and after execution. It is one of the first thing that should be inspected to determine if our program terminated correctly and if not, to see if any clues as to the problem recorded.
Key Points
1. To determine what the execution flow of our AE was, we have the trace facility.
2. to drill down during execution of our AE application we have a debug facility to run in step mode, view the State Record as it changes and set breakpoints to permit us to determine exactly what our application is doing.
3. The Restart ability allows us to both debug an application during development and restart a failing production run.
4. Knowing the execution flow of the AE can help you refine the cause of failures.
Do Select Action
The Do Select Action is a SELECT statement that executes subsequent Actions in the Step once for every row of data that the Do Select produces. In other words, the subsequent Actions within the Step are executed in a loop based on whether the SELECT statement in the Do Select Action has returned a row. This is equivalent to a FOR loop.
The Do Select will store the row in the State Record as specified in the Do Select SQL.
%select(Course, Effdt, Eff_status, Tools_Rel)
Select Course, Effdt, Eff_status, Tools_Rel
From PS_PSU_COURSE_TBL
Where Eff_Status=’I’
Each row of data in the database meeting the criteria is loaded into the state record and processed within a loop. When there are no more rows meeting the criteria, the program will move to the next step.
When the Do Select executes, it sets a cursor in the DB for all of the rows matching the criteria in the select statement. The first row is loaded into the State Record then the program proceeds to next action in the step.
Do Select Types:
The type of Do Select determines the specific looping rules. There are 3 Select Types:
a. Select/Fetch
b. Reselect
c. Restartable
a. Select/Fetch
The Select/Fetch type
· Opens the cursor to the database.
· Selects the first row that meets the SQL condition in the Do Select.
· Executes the actions in the step.
Once the subsequent actions, are completed the program returns to the open cursor form the Do Select, retrieves the next row and continues again with the rest of the actions.
Commits:
The Select and Fetch type will ignore all COMMITS placed anywhere in your program that are executed within the Do Select loop, unless we have disabled restart. Disabling restart is a simple way of telling the application that we are freeing it up of the responsibilities of knowing where to restart. That responsibility is now ours via a SWITCH field or an ORDER BY clause
Write ‘COMMIT IGNORED’ in the trace file for any commits within a Do Select loop.
b. Reselect
The Reselect type opens the cursor and closes the cursor on each iteration of the loop.
· Opens the cursor to the database
· Selects the first row that meets the SQL condition in the Do Select
· Closes the cursor
· Executes the following Actions in the step
· Once the subsequent Actions, are completed the program returns to the SQL in the Do Select and opens a cursor again.
Commits:
Commits are not ignored and will be committed in a Reselect Loop. Because the rows being selected are being changed by your code, provided you COMMIT those changes, the program will in fact have a way to know where in the loop it left off, making a restart possible.
We need to: Identify the rows to be processed, orders the data values, place commits in the appropriate sections or steps.
There is a column on the PS_AERUNCONTROL table called AE_STEP_DONE. This column will be populated with a value of ‘Y’ once the entire step, all loops complete. If the program abends in the middle of the loop, the column AE_STEP_DONE will be set to ‘N’.
c. Restartable
The Resatrtable type acts precisely like the Select and Fetch. It opens the cursor and leaves it open. This causes each iteration of our loop to select the next row that meets the condition without the need to change the status of that row as we do with the Reselect option.
Commits:
The major difference with Restartable is that it will COMMIT inside the loop thus allowing a checkpoint to the PS_AERUNCONTROL table.
If the program abends, the information in the AE_RUNCONTROL record and the State Record is used to restart the program from that point.
Steps to Application Engine Development
1. Analyze and Design the Application.
· Determine your data sources
· Develop and process logic pseudo-code and program source
· Does the program need to be restartable
2. Develop and Build your Application
2A. Create and test your SQL statements
· Write and test your SQL Statements
Ø SQL Tool / PS-Query
· Performance-tune your SQL statements
Ø An iterative process
Ø Optimizers
2B. Build Related Definitions
· State Records
· Messages
· Methods for use the user execute the program
Ø Process Definitions (Process Scheduler Manager)
Ø Push Buttons (Application Designer)
Ø Batch Files (PC Text Editor)
2C. Build the Application Engine Program
Create the program in the Application Designer
3. Test and validate the program
· Design your tests
· Initialize your data
· Test and Debug
· Validate success
· Test for restart capability, if applicable.
Key Points
1. The Do Select Action is a SELECT statement that executes subsequent Actions once for every row of data that the Do Select produces.
2. The type of Do Select determines the specific looping rules.
3. Select/Fetch – opens the cursor and retrieves the first row that meets the SQL criteria performs the subsequent actions, the fetches the next row meeting the criteria. This process continues until there are no more rows meeting the criteria, at which time the cursor is closed and the program continues to the next step.
4. Reselect – opens and closes the cursor on each iteration of the loop
5. Restartable – acts the same as the Select/Fetch, except that it will COMMIT inside the loop thus allowing a checkpoint.
6. We must code our program restartability.
Conditional Processing with Do Actions
When a section is called, its steps are executed sequentially. An AE program begins by executing for the first step of the section MAIN and ends after the last step. Any programming language needs to have the capability to perform conditional logic to control the looping.
The 4 Do Actions – Do Select, Do When, Do While, Do Until, act as SQL condition for the execution of the other 4 actions – PeopleCode, SQL, Call Section and Log Message.
Do When Action
The Do When Action is a SELECT statement that allows subsequent actions to be executed if any rows of data are returned. The Do When statement is first in the execution order within a step. The Do When statement will contain SQL that tests for a condition. As long as the condition is true and a row of data is returned, the subsequent actions within the step will be performed. If there is now row of data returned the entire Step would terminate. If there are no more steps to execute, the program will end.
This Do When option is similar to a COBOL ‘IF’ statement. A Do When statement runs before any other actions in the step. If the Do When statement returns any rows, the next Action will be executed. If the Do When conditions are not met, the remaining Actions within that Step are not executed. A Do When Action is executed just once when a step is executed.
***Executes the action once if the condition is TRUE.
DO WHEN counter=1000
Grant Award
Do While Action
The Do While Action is a SELECT statement that runs before subsequent Actions of the Step. If the Do While returns any rows of data, the subsequent actions in the step will be executed. You use a Do While Action to control the execution of the following actions. The Do While Action runs each time before the next Action is repeated. If the Do While returns any rows of data, all the Actions in the Step will be repeated. If no rows are returned, AE will move on to the next Step or Section.
The Do While is identical to the Cobol ‘While’ function. In other words, the subsequent Actions within the Step are executed in a loop as long as the SELECT statement returns at the least one row for the Do while Action.
***Loops as long as the condition is true. In short, if the Do While does not return any rows, the Step is complete.
DO WHILE counter<1000
Counte=counter+1
Do Until Action
A Do Until Action is a SELECT statement that runs after each Action, when a Step completes. If the SELECT returns any rows of data, the Step terminates.
Use a Do Until if you want the ‘processing actions’ to execute at least once, and to execute over and over until a certain condition is true, as in until a SELECT returns some rows.
We can also use a Do Until to stop a Do Select prematurely. For example of the SELECT for the Do Until does not return any rows, then the Actions in the Step are repeated (except if a Do When appears in the Step). Normally, a Do Select continues until no rows are returned. If any rows of data are returned, the Do Select stops and the actions in the Step are not repeated.
***Loops as long as the condition is FALSE.
DO UNTIL counter=1000
Counter=counter+1
Test Condition and the State Record:
All SQL SELECT statements in an AE Action must be preceded by a %SELECT(), causing the value to be written to the State Record. We generally use the TEMP_FLD to write the value to, which acts as a container for the ‘X’ retrieved from the DB if our condition is true. Once the ‘X’ is written to the field, there is no need for it. A subsequent ‘X’ statement in our AE program will overwrite it, and generally the character is not used in our processing.
%SELECT(TEMP_FLD)
SELECT ‘X’
FROM PS_INSTALLATION
WHERE %BIND(COUNTER) >10
Meta-SQL to write ‘X’ to State Record
If ‘X’ is returned this equals TRUE
Fills the requirement for a FROM table
Condition to be tested.
When we add an Action it automatically takes its place within the hierarchy. This way, while we are developing our program we can visualize the sequence in which our logic will execute.
The following shows an example of the Action Execution Hierarchy:
Do When
Do While
Do Select
PeopleCode
SQL / Call Section
Log Message
Do Until
Application Library
Most Application Engine programs are defined to be executable. An executable program has a main entry point in its definition: the Section MAIN. This defines the entry point so whatever method you choose to invoke the program, such as command line or Process Scheduler, can initiate the program. However, in some cases, we may want a program to only contain a collection, or “library” of common routines (in the form of “callable” Sections) that we do not want to run as standalone program. When sections are defined as “Public”, other programs can call the Sections, or routines, that exist in the “library” at runtime. Because this type of program is not designed to run as a stand-alone program, it does not require the MAIN section, or initial entry point. Setting the Application Library option renames or removes any existing MAIN section.
Application Libraries are the appropriate sport to store a collection of shared Application Engine program Sections. It is not intended to store a specific SQL Action within a Section. To share common SQL, use the SQL repository.
Key Points
1. The Application Engine has the ability toe make logical decisions to control the execution of Sections, Steps or Actions.
2. Do Flow Actions are:
· Do When Action allows subsequent Actions to be executed if any rows of data are returned in a SQL Select before the execution of the step.
· Do While Action allows subsequent Actions to be executed if any rows or data are returned in a SQL select
· Do Select will execute subsequent Actions once for every row of data returned in a SQL Select
· Do Until Action allows the Step to be return after the Step has run and before it loops again.
Do When => IF Condition logic
Do While => While Loop Logic
Do Select => FOR Loop Logic
Do Until => Repeat Until Logic
PeopleCode and Application Engine
In PeopleSoft 8, the ability to use PeopleCode in an Application Engine program was introduced. Keep in mind that AE is a development tool that allows to develop programs that execute SQL, in batch and online mode, against our data in a procedural and dynamic structure.
PeopleCode can be used for:
· Setting IF, THEN, ELSE logic constructs.
· Performing data ‘preparation’ tasks
· Building dynamic portions of SQL, while still relying on SQL to complete the bulk of the actual program processing.
· Reusing online logic that’s already developed
· Leveraging the new technologies – Business Interlinks, File Objects, Component Interfaces, Application Messaging.
PeopleCode in Application Engine Program
Inserting PeopleCode within AE programs provides an opportunity for us to reuse common function libraries and improve performance. It also can simplify development in some instances, as well. In many cases, a small PeopleCode program used instead of a Do When Action, for example, can replace a more complicated SQL statement.
There are few restrictions in terms of how we take advantage of PeopleCode in our AE program, however, as with most software development tools, there is an ‘intended’ use for a given tool. AE PeopleCode is an excellent way to build dynamic SQL, perform simple IF/THEN/ELSE edits, set defaults, and perform other tasks that don’t require a trip to the DB.
ü We do not need to concern yourself with formatting your code. When you save or validate the PeopleCode program, the system will automatically format the code for you. This holds true for anything except quoted literals, which are not formatted. Also PeopleCode is case-insensitive.
PeopleCode Basics:
There are 3 types of variables in PeopleCode, differentiated by the life span of the variable.
Variable Type
Life Span
Notes
Local PeopleCode Variables
PeopleCode Program
These variables are only available for the duration of the program in which they are declared.
Global PeopleCode Variables
AE Program
These variables are available while the AE program is running. They are saved at commits and checkpoints, so they can be used for restarts.
Component PeopleCode Variables
AE Program
Same as Global PeopleCode Variables for AE.
Variables are denoted with an ‘&’ prefix
Example: &MyRow, &Course
Local Variables:
Local variables do not need to be declared. They can take on the appropriate datatype when a value is assigned to the variable. However, Global and component variables must be declared.
LOCAL
Where LOCAL can be replace with GLOBAL or COMPONENT depending on the variable type. This declaration must appear before all other PeopleCode in our program.
Standard Types: Boolean, Date, DateTime, Time, Number, Object, String,
Any
Definition Types: Field, Record, Row, RowSet, AESection, Array, Component, File, BusinessInterlink, Message, Opertor, ProcessRequest, SQL.
Referencing Fields:
There are significant differences between Online and Batch modes. AE programs run in Batch mode so, our PeopleCode cannot access pages or controls as it can while running in Online mode. In short, any Peoplecode operations that manipulate pages will not run successfully. Even if you invoke your AE program “Online” from a Record or a Page using the CallAppEngine PeopleCode Function, the Application Engine PeopleCode still does not have direct access to the Page buffers.
Any RECORD.FIELD references that appear in a PeopleCode Action can only refer to fields that exist on an Application Engine State Records. Page buffers, controls, and so on are inaccessible even if you define the page records as State Records on the Program Properties dialog. An Application Engine program can only access State Records or other Definitions you create in PeopleCode.
There are multiple methods used to reference fields in PeopleCode. Of the various methods, there are only 2 that apply to Application Engine.
First, we can refer to the field using the Syntax
Second, we can use dot notation. For example, we can assign the employee’s name to a variable using the following syntax:
&Name=GetRecord(RECORD.Employee).GetField(FIELD.Name);
All PeopleCode statements should end with a semicolon – ‘;’. The PeopleCode editor will strip away any excess spaces or line breaks when the program is saved.
Date and Time operations
Operation
Result
Time + Number (no of seconds)
Time
Date + Number (no of days)
Date
Date – Date
Number (difference in days)
Time – Time
Number (difference in seconds)
Date + Time
DateTime
If
else
end-if
*****************
Exit Statement
In the Application Engine, the IF-THEN statement is used to control flow of execution with the EXIT statement. The setting of the EXIT can cause the next Application Engine step to conditionally be skipped.
If &Test = 10 Then
Exit(0);
Else
Exit(1);
End-if
The parameter in the Exit Function controls the path the AE program will take
1 – indicates that the instruction to be done On Return will be done.
0 – tells the program to ignore the On Return Action of the PeopleCode Action.
The On Return options are:
Abort The program issues an error and exits immediately
Break The program exits the current Step and Section, and control returns to the calling Step
Skip Step The program exits that current step, and continues processing at the next Step in the Section. If this is the last Step in the Section, the calling Step resumes control of the processing.
Sequence Numbering
We can use the meta-SQL constructs %NEXT and %PREVIOUS when performing sequence numbering. Using these constructs may help performance in both PeopleCode and SQL calls.
Dynamic Calls
Rather than calling one specific Section, we can take advantage of the AE_APPLID and AE_SECTION fields in the State Record to execute different Sections depending on the conditions a program encounters during runtime. This is called Dynamic Call.
If we will only be using the Section within one AE program, then only the AE_SECTION field must be on the State Record. If we want to be able to call a section in other Application Engine Program, we will need to define both AE_APPLID and AE_SECTION on our State Record.
We enable a Dynamic Call by first having our program store different Section names in the AE_SECTION field, and different program names in AE_APPLID field. The values you insert into these fields needs to be based on various conditions met within our program. We then create a Call Section Action that call whatever Section name appears in the State Record field by selecting the Dynamic checkbox.
Check Points
1. The AE has the ability to use PeopleCode to make logical decisions to control the execution of Sections, Steps or Actions.
2. We can use the PeopleCode Action type with If-Then logic to control the flow of our AE program.
3. We can use PeopleCode in AE programs to perform computational functions.
4. PeopleCode can be used to control processing and to save time and overhead by not going to the database to make test.
5. This is mainly due to the fact that AE is most powerful when it executes SQL-based processing against your database.
6. PeopleCode is an interpreted language, so there is an inherent extra performance overhead when computed to compiled language.
Set Processing
Set Processing uses SQL to process groups, or sets, of rows at one time rather than processing each row individually. With Set Processing, you’d only select, or isolate, those rows that meet the filtering criteria and then run the rule once against all the effected rows.
In most cases, Set Processing is more efficient than row-by-row processing, especially when you enter the realm of high volume processing. Since Application Engine is designed specifically to process our high volume updates, Set Processing is vital to the development of the most efficient Application Engine Programs.
Set Processing allows applying our business rule directly on the data in the DB using an UPDATE or INSERT/SELECT statement. The bulk of the performance gain lies in the fact that the processing occurs in the DB as opposed to
· Pulling the data across the network, to our application program
· Processing it
· Transmitting results back across the network to be put into the DB tables.
Note: the only SELECT statements used in Set Processing appear in sub-queries of an INSERT or UPDATE statement.
Set Processing, Temporary Tables, and Application Engine program
Most Application Engine program require 1 or more Temporary Tables to contain transient data. This is especially true of programs that take advantage of Set Processing techniques. Although temporary tables are not necessarily required for Set Processing.
Creating temporary tables allow you to achieve one of the main objective involved with set based processing – the processing remains on the RDBMS Server. By storing transient data in temporary tables, you avoid the situation where the batch program fetches the data, row-by-row, and runs the business rule, processes the data and then passes the updated data back to the database. If the program were running on the client, you’d be taking performance hits due to the network roundtrip and the diminished processing speed of a client.
Temporary tables should be designed to accomplish the following:
· Hold transaction data for the current run or iteration of your program.
· Contain only those rows of data affected by the business rule
· Present key information in a denormalized or ‘flattened’ form, which provides the most efficient processing.
· Switch the keys for rows coming from the master tables if needed. A transaction may use a different key than what appears on the master tables.
Identify Temporary Tables
Most applications will require 1 or more temporary tables to contain transient data. This is especially true of applications that take advantage of Set Processing techniques. Normally, we would name temporary table something that ends with “_TMP”.
AE has Checkpoint/Restart capabilities, these tables should not really be considered temporary. To protect the overall integrity of the batabase, give these tables name that end with “_TAO”.
There is a special record type “Temporary Table” that is used with AE programs for parallel processing. If you only need one instance of a temporary table, and SQL table could be used. For this activity, we will use a record type of “SQL Table”.
Key Points
1. Application solutions designed to take advantage of Set Processing improve the performance of an AE application
2. Set Processing can yield:
· Improved performance
· Minimized (PeopleTools) SQL overhead
· Easier maintenance
· Leveraged power from the RDBMS
Temporary Tables
The ability for multiple instances of the same program to run simultaneously is called Parallel or Concurrent processing.
Since AE programs run in Batch, it is very likely that multiple instances of the same program can be running in parallel. When batch programs being running simultaneously, they introduce a significant risk of data connection and dedlocks on common tables and/or temporary tables.
For AE programs, PeopleTools provides a feature that enables you to drastically reduce the risk of table contention by dedicating specific instances of temporary tables for each program run. With this feature, we have a pool of temporary table instances, and within that pool some tables are dedicated to particular programs, and some instances are undedicated, meaning they are shared.
Parallel Processing – Why?
For batch process there can be potential performance improvements by spilitting the data to be processed into groups and simultaneously running multiple instances of our program to deal with different groups of data.
For example if we have an inventory of 1,000,000 items you need to process, consider the following time lines to run the following 3 cases
1. All 1,000,000 with one execution of our program
-------------t--à------------------------------------
2. 500,000 each in 2 executions of our program, serially
--------t-à------------------------t-à-------------
3. 500,000 each in 2 executions of our program parallel
--------t-à--------------
--------t-à--------------
If you have a program that uses a Temporary Table and is invoked multiple times, that single Temporary Table could be used concurrently in multiple executions of the code.
This could create unpredictable results since the different instances of the code would be issuing Deletes, Inserts, and/or Updates unsynchronized with each other.
We could solve the problem by creating multiple temporary tables as a pool of tables. Each invocation of our program would have to allocate an unused temporary table, mark it as ‘in use’ use it and release it back to the pool when you are through with it (for each AE program we write)
Implementing Parallel Processing
There is no simple switch or checkbox that enables you to turn parallel processing on and off. To implement parallel processing, you need to complete a set of tasks in the order that they appear in the following list
1. Define your Temporary Tables by defining and saving your Temporary Table records as type Temporary Table.
2. Build the Temporary Table Pool. This will build the basic Temporary Table Pool based on the PeopleTools options specifications.
3. Assign Temporary Tables to your AE Program in its Program Properties, setting the appropriate number of instance counts and Runtime options.
4. Build/Rebuild your Temporary Table record. This will build the necessary Batch temporary tables into that record’s Temporary Table pool for use at execution time.
5. Code %Table meta-SQL as references to Temporary Tables in your AE program, so that AE can resolve table references to the assigned Temporary Table instance dynamically at runtime.
The Temporary Table Instance (Total) specified on the PeopleTools Options page.
Plus the sum of all instance count values specified in all of the AE programs that use that Temporary table.
Application Designer only creates a maximum of 99 Temporary table instances.
There are 2 fields that are used in connection with the temporary table instances.
Temp Table Instances(Total)
Temp Table Instances(Online)
These 2 values should always be the same except for EPM
EPM Managed Temporary tables
The difference between the Temp Table Instances(Total) and Temp Table Instances(Online) will be the EPM Managed Temporary Tables
The naming convention for the Temporary Table instances is as follows:
nn
where nn is a number between 1 and 99.
PeopleTools Options:
Temp Table Instances (Total):5
Temp Table Instances (Online):3
Application Designer Build
OnlineTEMP_TA05
TotalTEMP_TA04
EPM ManagedTEMP_TA03
TEMP_TA02
Base TableTEMP_TA01
TEMP_TA0
Reference Temporary Table (%Table)
To reference a temporary table (Online or Batch), we need to use:
%Table(record)
We can reference any table with %Table, but only those records defined as Temporary Tables get replaced by Application Engine with an numbered instance of a Temporary Table from the Temporary Table pool.
Clear Temporary Table (%TruncateTable)
We do not need to delete data from a Temporary Table manually. The Temporary Tables are truncated automatically when they are assigned to our program. If the shared base table has been allocated because no batch/dedicated instances were available. Then AE performs a delete by process instance instead of performing a truncate. In such case, the PROCESS_INSTANCE is required as a high-level key.
We can perform additional deletes of Temporary Table results during the run, but you will need to include your own SQL Action that does a %TruncateTable.
Run Time Allocation
Depending on how we invoke a program the manner in which it allocates a Temporary Table is different. The distinction between invocation methods fall into 2 categories:
1. Online
2. Batch
1. Online Processes
Online processes have their own set of dedicated Temporary tables, defined globally on PeopleTools Options.
When you invoke a process Online, PeopleTools randomly allocates a single Temporary Table instance number to ProgramX for all its dedicated temp table names. While programX runs, no other program can use that instance number assigned to programX until programX has run to completion. Any other Online process that happens to get the same instance value as programX will wait for the look to be released when programX completes and the instance number is unlocked.
2. Batch Processes
For Batch processes, PeopleTools allocates the batch/dedicated table instance numbers on a record-by-record basis. PeopleTools begins with the lowest instance number available for each Temporary Table until all of the Temporary Table instances are in use. If there are not any temporary table available and you indicated Continue for the If non-shared Tables cannot be assigned, then the base table will be used with the process instance number as a key.
When a program ends normally or is cancelled by way of Process Monitor, the system automatically releases the assigned instances.
Application Engine Execution
We can invoke AE programs 3 modes:
1. Batch
2. Online
3. Manual
1. Batch
This is the most typical mode of execution. We invoke programs that run in this mode using Process Scheduler or the AE Process Request page. Batch mode is also referred to as asynchronous execution meaning that it runs independently in the background.
2. Online
AE programs that execute online, typically get executed from a page with the CallAppEngine PropleCode function. Such Online processes are synchronous meaning that subsequent processes wait on the results. For instance, a page may ‘frozen’ until the Online process returns the necessary results. When the program is called synchronously, no COMMITs are issued until the program completes.
3. Manual
To execute an AE program in manual mode, we would use the command line. Usually, we only use this technique during testing or if you need to manually restart the program.
1. Batch Mode 2.Online Mode 3.Manual Mode
Asynchronous Process Synchronous Process
Process Scheduler CallAppEngine Command Line
PeopleCode
Process Scheduler
Request Push Button Bat File
AE Process
Request Page
PSAE.exe
Key Points
1. We can execute AE programs in a number of different ways appropriate to the type of user that is initiating the program.
2. we can initiate AE program from pages with Push Button set for Synchronous or Asynchronous execution.
3. We can run AE program in or out of the Process Scheduler.
Application Engine Process Definition
We use Process Scheduler Manager to set and create Process Types, Process Definitions, and Job Definitions to integrate Process Scheduler in PeopleSoft applications from our browser. For each AE program that we want to process via the process scheduler, we need to create Process Definition and a component designed to control the runtime parameters. PeopleSoft provides standard records and pages from which to clone.
PeopleTools => Process Scheduler Manager => Use => Process Type Definitions
Command Line: points to the executable program PSAE.exe, a PeopleSoft API program to run AE programs.
Enclose local environment (Client side) strings within a single pair of percent signs:%TOOLBIN%.
Enclose all Server environment (Server side) strings with a double pair percent signs: %%TOOLBIN%%
Parameter List: contains the string command line variable passes to the executable. The parameter list follows the same definitions as we saw for the command line. The variable are denoted by the %% and are retrieved from the system at run time.
Parameter
Variables (meta-strings)
Description
-CT
%%DBTYPE%%
Database Type
-CD
%%DBNAME%%
Database Name
-CO
%%OPRID%%
User ID
-CP
%%OPRPSWD%%
User Password
-R
%%RUNCNTLID%%
Run Control ID
-I
%%INSTANCE%%
Process Instance
-AI
%%PRCSNAME%%
Process Definition
Process Scheduler uses a number of variables during the run time. These are in the form of meta-strings, in-line bind variables, or client/server variables.
% - Client side Variable
%% - Server side Variable
C:\PT812\CLIENT\WINX86\PSAE –CT MICROSOFT –CD
Steps to Add Appliction Engine Process
1. Design
2. Identify initial Bind Variables
3. Determine Run Control Record
4. Build the Record
5. Determine the Run Control Page
6. Place the Run Control Page on a Component
7. Place the Component on a Menu
8. Create a Process Definition
9. Grant Access to the Component
10. Test
Run Control Record
The most common way to request an AE program through the Process Scheduler is from a Run Control component. To build a component, we must start with a record.
PropleSoft provides a standard Run Control Record for processes with no user input: PRCSRUNCNTL. This record contains 4 fields.
1. OPRID Key Field
2. RUN_CNTL_ID Key Field
3. LANGUAGE_CD
4. LANGUAGE_OPTION
The Fields OPRID and RUN_CNTL_ID contain the following PeopleCode:
OPRID - RowInit - OPRID=%OperatorId
RUN_CNTL_ID- Save Edit - Ensures no blanks in value
Key Points
1. A process type definition is used to define the command line, parameter list, working directory, and other general parameters for a process that can be run through the Process Scheduler.
2. AE Generic Test Type can be used to run an AE program without creating the process definition (for testing).
3. A Process Definition defines the AE program to the Process Scheduler.
4. PeopleSoft supplies some standard run control records and pages that can be used or cloned.
5. The steps to add a new AE program to the Process Scheduler are:
· Design
· Identify initial bind variables – these will need to be included on the Run Control Page
· Determine the Run Control Record
· Build the Record
· Determine the Run Control Page
· Place page on a component
· Place component on a Menu
· Add a Process Definition
· Grant access to the Component
· Test the application
Appliction Enginer Meta-SQL
Meta-SQL
Description
%Bind
%Select
%SQL
%Table
%TruncateTable
%ExecuteEdits
%InsertSelect
%Join
%List
%SelectInit
%UpdateStatus
Application Engine Macros
Meta-SQL
Description
%ClearCursor
%Execute
%Next
%Previous
%RoundCurrency
Application Engine System (Meta) Variables
Meta-SQL
Description
%AeProgram
%AeSection
%AeStep
%JobInstance
%ProcessInstance
%ReturnCode
%RunControl
%AsOfDate
%SQLRows
%LeftParen
%LeftParen
%Space
%Comma
Meta-SQL Date & Time Functions
Meta-SQL
Description
%CurrentDateIn
%CurrentDateOut
%DateIn(dt)
%DateOut(dt)
%DateTime(dtt)
%DateTimeOut(datetime_col)
%TimeIn(tm)
%TimeOut(tm)
