Application Application Engine Program Composed of one or more sections

Section Callable block of logic. Composed of one or more steps. Specified by: Market. Effective date. Effective status.

Step Action Logical grouping Lines of code. of actions. Composed of one Used to: or more actions. Retrieve or Smallest amount ofmanipulate data. work that can be committed. Control program flow.

First section MAIN 12- character name for application

Platform. Executed only when Executed from top Must be unique called, except for to bottom within within step. MAIN. the section. Executed in specific order.

Adding Markets and Filters to Application Engine Program (continued)

Sections These rules apply to when a section is executed: If a section exists for the current market, execute it. If a section exists for the current platform or RDBMS execute it. If a section for exists for the current effective date, execute it. If a there is no unique section, execute the default section. Within the called section, steps are executed in order of sequence number Within those steps, actions execute from top down

Testing Application Engine Programs

1

Application Engine Testing Tools There are several common Application Engine testing tools available to you: The Process Monitor. The Application Engine Trace file. The Application Engine interactive debugger. The PeopleCode Debugger.

Using Process Monitor (Continued)

Other Links Other links on the Process Monitor, Process, Process Detail page include: View Locks Batch Timings View Log/Trace

Using Application Engine Traces

Trace Options For processes running on Windows, trace options are set in the Configuration Manager. These settings are only valid when the Application Engine program is run on the client from Application Designer or from the command line.

Using the Application Engine Debugger

To start the Application Engine Debugger: 1. 2. Launch Configuration Manager. Select the Profile tab.
2

3. 4. 5. 6.

Highlight the Default profile. Click the Edit button. Select the Process Scheduler tab. In the Application Engine group box, select the Debug checkbox.

Commit Settings When using Application Engine Restart, there are several program level commit settings that can be selected to make a program restartable: Commit after each step in that section. Require commits after a specific step. Commit frequency within a step or n iterations for looping actions.

In this lesson you learned that: You can test Application Engine programs using Process Monitor, the Application Engine trace file, the Application Engine debugger, and the PeopleCode debugger. Process Monitor allows you to check Application Engine Program Status, access run time information and re-start programs initiated in the Process Scheduler. You start an Application Engine trace from Configuration Manager, on the Trace tab.

The Application Engine Debug tool allows you to run a program in step mode, view the State Record as it changes and set breakpoints to permit you to determine exactly what your application is doing. Restart allows you to both debug an application during development and restart a failed production run.

Do Select Do Select: Is the equivalent of a For loop in an Application Engine program. Is driven by a %Select statement SQL. Populates a state record with values to be used by other actions. Controls execution of subsequent actions in a step.

Do Select Types The three Do Select types are: Select/Fetch Reselect Restartable

The Do Select types re differentiated by: Cursor handling. Checkpointing and commit handling.

Cursors A cursor is a temporary workspace used by the RDBMS to exchange information with a program. A cursor is created and opened whenever a SQL statement is sent to the RDBMS. A cursors memory buffer can contain multiple rows, retrieved in turn individually by a Fetch. Work done on rows in a cursor is conditional until a Commit is carried out. Rollback discards work done in a cursor back to the last Commit.

An Application Engine may have many cursors open simultaneously, and many may be opened and closed during the course of program execution.

Select/Fetch Flow This flow chart illustrates the flow for Select/Fetch:

Using Select and Fetch (continued)

Commit Processing Programs with a Do Select type of Select/Fetch are not restartable from within the loop. No checkpoints are performed within the loop. Commits will be issued if the program is not restartable. Select/Fetch commit processing follows these rules:
5

 If restart is enabled (the default) all Commits carried out within Do Select loops are ignored. For example, Commits on steps in called sections. A COMMIT setting ignored message is written to the trace file for any commits within a Do Select loop. If Restart is disabled, Commits are not ignored

Using Reselect
This flow chart shows the Reselect flow if restart and commits are enabled:

Go to Slide

Using Restartable (continued)

Do Select Loop with Restart Enabled This flow chart illustrates a Do Select loop with Restart enabled.

Using Restartable (continued)

Ensuring that Rows are not Processed Twice You need to have some conditions that reduce the answer set returned by the Do Select to ensure that the program does not process rows again if restart is necessary. Options include: Delete processed rows. Add a switch to the selected table. Add an order by clause.

Using Restartable (continued)

7

AE_STEP_DONE The AE_STEP_DONE column on PS_AERUNCONTROL table tracks step processing. Set to Y once the entire step, all loops, is complete. Set to N if the program terminates in the middle of a step.

Developing an Application Engine Program Using Do Select

Step 1: Design Here are the steps to develop an Application Engine program: 1. Analyze and design the application. Determine your data sources. Application Designer - Find Object References. Data Models. Record Cross Reference Page. Develop and process logic pseudo-code and program source. Decide whether the program needs to be restartable.

Step 2: Develop and Build your Application 2. Develop and Build your Application. 2A. Create and Test your SQL Statements. Write and test your SQL statements. SQL Tool. PeopleSoft Query. Performance-tune your SQL statements.
8

Tuning is an iterative process. Work with a database administrator (DBA) using Application Engine statistics and traces. User Optimizers (rule-based or cost-based).

2B. Build Related Definitions. State records (Application Designer). Messages (Message Catalog). Method for the user to run the program: Process Definitions (Process Scheduler). Push buttons (Application Designer). Batch files (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 (script for retesting). Test and debug. Validate success. Test for restart capability, if applicable.

Build the Application Engine program.

Section MAIN

* INSERT *

Step Action Type Statement STEPM1 Do Select %SELECT(COURSE, EFF_STATUS, Type: DESCR,LENGTH_DAYS, ) Select/ Fetch SELECT A.COURSE, A.EFF_STATUS, A.DESCR * Call Section INSERT STEPI1 SQL INSERT INTO PS_PSU_COURSE_TBL VALUES (%BIND(COURSE), %BIND(EFFDT), STEPI2 Log Message Set = 30,000 Message Number = 4 Message Param. = %BIND(COURSE), %BIND(EFFDT)

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. Reselect opens and closes the cursor on each iteration of the loop.

Restartable acts the same as the select and fetch, except that it will COMMIT inside the loop thus allowing a checkpoint.

SQL State Record. The following table shows the interaction between the SQL test condition and the state record. SQL %SELECT(TEMP_FLD) SELECT X FROM PS_INSTALLATION WHERE %BIND(COUNTER) > 10 Result Meta-SQL to write X to State Record If X is returned this equals TRUE Fills the SQL Syntax requirement for a FROM table Condition to be tested, value (%BIND) from the state record.

10

Designing a Program Using Conditional Processing (continued)

Step 1: Application Design In Step 1 you will: Design a program that will increment a counter if the number of students enrolled in a course session exceeds the maximum enrollment for that session. A message should be issued if the counter reaches a certain number.

Step 2A: Create and Test SQL Steps In Step 2A you will: Validate your Selects with what is in the table. Validate your Count with what is in the table. Enroll Table (Where Course=1001 and Session Nbr = 1) Session Table (Where count(*) >= MAX_ENROLL)

Step 2B. Build Related Definitions In Step 2B you will: Create new fields. Define state records Create messages in the Message Catalog.

Step 2C. Write the application. Create the WAITLIST Application Engine program.
11

Designing a Program Using Conditional Processing (continued)

Step 3. Test the Application In step 3 you will: Trace statement timings Run from Application Designer Save the trace to compare with others generated later. Activate and test using the Debugger.

Application Engine Library An Application Engine library: Is a collection of shared Application Engine program sections Contains sections defined as Public. May not be defined to run standalone. Does not require a MAIN Section. Is not intended to store a specific SQL action within a section.

The steps of program construction are: 1. Application Design

2A. Create and Test SQL Steps 2B. Build Related Definitions 2C. Write Application 3. Test Application
12

Inserting PeopleCode into an Application Engine Program (continued)

The If-Then Statement The parameter in the Exit function controls the path the Application Engine program will take. 1 indicates that the On Return instruction will be executed. 0 tells the program to ignore the On Return action.

For example: If &Test = 10 Then Exit(0); /* or can use Exit 0; */ Else Exit(1); /* or can use Exit 1; */ End-If; On Return The On Return options are: Abort Break The program issues an error and exits immediately. The program exits and control returns to the calling step.

Skip StepThe program exits the current step, and continues with the next step.

Exit Code The following diagram illustrates how the Exit code controls program flow.

13

Enabling Dynamic Calls (continued)

Enabling a Dynamic Call To enable a dynamic call: 1. Add a PeopleCode action.

2. In the action, based on a condition test, store a program name and a section name in the state record. The two fields used with dynamic calls are: AE_APPLIDfor the Application Engine program name. AE_SECTION for the Application Engine section name.

The fields must be populated, typically using PeopleCode. The fields must exist in the default state record.

3. Following the PeopleCode action, add a Call Section action with the Dynamic Call checkbox selected.

Temporary Tables Set processing can make use of temporary tables to preselect data that can then be handled via a single SQL statement.
14

Temporary tables are designed to: 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 form. Switch the keys for rows coming from the master tables if needed.

Modifying a Program to use Set Processing (continued)

CRS_FULL_TMP Data Model The following diagram illustrates temporary table relations for the WAIT_SET temporary table, CRS_FULL_TMP.

Parallel Processing Parallel processing is the simultaneous execution of multiple instances of a single program. Uses of parallel processing include:
15

Concurrent execution of a program by multiple users.

Reduction of processing time by partitioning high volumes of data into batches to be processed by separate instances of a program.

Online and Batch Methods When Application Engine programs are run in parallel, they are run in one of two methods: Online (synchronously) implemented through a code callappengine() function.

Batch (asynchronously) implemented through a process definition initiated via the Process Scheduler. Due to the differences in execution of these two methods the temporary tables used must be segregated.

This table summarizes PeopleSoft temporary table solutions: Online Invoked by CallAppEngine from PeopleCode. Run quickly, synchronously, and at random times. Potential for simultaneous execution. Uses the online temporary table pool. Not restartable Batch Invoked through the process scheduler. Run for longer amounts of time, asynchronously, and at scheduled times. Potential for simultaneous execution and can be designed for parallel execution for performance. Uses the batch/dedicated temporary table pool. Restartable

Implementing Parallel Processing

Steps to Implement Parallel Processing The steps to implement parallel processing are: 1. Define your temporary tables.
16

2. Set or check the temporary table online pool. This is a global setting and is usually established at installation time. 3. Assign temporary tables to be dedicated to each Application Engine program.

4. Set the number of dedicated temporary tables to form the number of temporary tables to be created for the process to be run in parallel. 5. Build or rebuild your temporary table record to create the appropriate number of temporary table instances assigned to the Application Engine parallel process. 6. Adjust your program meta-SQL (i.e. %TABLE) to resolve the data set to the appropriate application engine process being run in parallel..

Implementing Parallel Processing (continued)

Defining your Temporary Tables 1. Define your temporary tables
1. a. Create a new record. PeopleSoft recommends that you make Process Instance a key field. The process instance is used to restart the process should it abnormally end. 2. b. Set the Record Type to Temporary Table. 3. c. Save the record.

Temporary Table Online Pool 1. Set or check the temporary table online pool. On the PeopleTools Options page, specify: Temp Table Instances (Total) Temp Table Instances (Online) used by callappengine() function Total temporary table instances that will be SQL created is:
17

The value in Temporary Table Instance (Total).

The sum ofinstance count values specified in all of the Application Engine Programs that use that temporary table. A maximum of 99 temporary table instances can be created.

EPM Temporary Tables This diagram illustrates which temporary tables are reserved.

Assigning Temporary Tables 1. Assign Temporary Tables to the Application Engine program. Use the Temp Tables tab on the Program Properties dialog box to assign temporary tables.

Also on this tab assign Instance Count and specify Continue or Abort if non-shared tables cannot be assigned.

Setting the Temporary Table Batch Pool 1. Set the Temporary Table Batch pool
18

 If the program will be run in batch, you will need to establish the number of temporary table instance that will be used for your program. Use the Instance Count field on the Temp Tables tab of the Program Properties dialog box.

If the program will only be run in batch mode, select the Batch Only checkbox on the Program Properties: Advanced tab.

1.

Build or rebuild your temporary table record.

This diagram shows how temporary tables are allocated based on Instance Count in the program properties:

Application Engine Temporary Table Use, Running in Parallel

19

This diagram shows an example with three Application Engine programs using a variety of temporary tables with different instance counts.

Application Engine Temporary Table Use, Running Serially This diagram shows an example with three Application Engine programs using a variety of temporary tables with different instance counts:

Adjusting Meta-SQL 1. Adjust your program Meta-SQL to resolve the application engine program to the temporary table assigned to its process at run time.
20

Table references must be set up using %Table. To add rows to temporary tables, you must provide the process instance number using either: %ProcessInstance %Bind(PROCESS_INSTANCE)

While assigned Temporary Tables are cleared at the start of processing, no automatic deletes happen during or at the end of the program. You may want to consider using an action containing SQL with %TruncateTable for additional deletes during or at the end of the run.

Using Meta-SQL with Parallel Processing (continued)

Single Temporary Table Assignment with %Table With no batch or dedicated tables assigned, this statement: Select PROCESS_INSTANCE,TEMP_COL1,TEMP_COL2 from %Table(MYAPPLTMP) is resolved as shown in this diagram.

Multiple Temporary Table Assignment with %Table Where multiple batch or dedicated tables are assigned, this statement:
21

Select PROCESS_INSTANCE,TEMP_COL1,TEMP_COL2 from %Table(MYAPPLTMP) is resolved to an available instance of the temporary table, as shown in this diagram.

Batch Processes Batch/dedicated table instance numbers are allocated on a record-by-record basis. Numbers start with the lowest instance number until all numbers are in use. If all instances are used and you indicated Continue for the If non-shared Tables cannot be assigned, additional programs use the base table with Process Instance as a key. When a program ends normally or is cancelled in Process Monitor, assigned instances are automatically released.

Batch Processes Batch/dedicated table instance numbers are allocated on a record-by-record basis. Numbers start with the lowest instance number until all numbers are in use. If all instances are used and you indicated Continue for the If non-shared Tables cannot be assigned, additional programs use the base table with Process Instance as a key. When a program ends normally or is cancelled in Process Monitor, assigned instances are automatically released.

22

Batch Processes Batch/dedicated table instance numbers are allocated on a record-by-record basis. Numbers start with the lowest instance number until all numbers are in use. If all instances are used and you indicated Continue for the If non-shared Tables cannot be assigned, additional programs use the base table with Process Instance as a key. When a program ends normally or is cancelled in Process Monitor, assigned instances are automatically released.

This table gives a summary of differences between batch and online execution: Online Execution Started by CallAppEngine, executed as from PeopleCode. Intended for programs that run quickly, synchronously, and at random times. Potential for simultaneous execution. Uses the online temporary table pool. Batch/Manual Execution Executes PSAE through Process Scheduler or the command line Intended for programs that run for longer periods of time, asynchronously, and at scheduled times. Can be designed for parallel execution for performance. Uses the Batch/Dedicated temporary table pool.

Executing an Application Engine Program Using a Push Button (continued)

Synchronous Execution Use synchronous execution when: Processing should be of short duration. Processing requires some task to complete before further processing can occur. Application Engine performs a SQL task more efficiently than some other methods. A developer will be able to take advantage of existing Application Engine resources.
23

Setting up a Synchronous Call to Application Engine To set up a synchronous call to Application Engine: 1. 2. 3. 4. 5. Create a record and field for the push button. Add FieldChange PeopleCode containing CallAppEngine to call the program. Add a push button to the page you wish to use to launch the process. In the push button properties, select PeopleCode Command for the Destination. Enter the record and field containing the PeopleCode into the push button properties.

Command Line Execution Use Command Line execution of an Application Engine Program (PSAE.EXE) for: Restarting a program. Development/Testing. Debugging.

Parameters This table lists the minimum parameters that are required in addition to the path where PSAE.exe resides: Description Database platform (e.g. Microsoft, Oracle, DB2, etc.) Database name Logon ID Password Run Control Application Engine program Parameter -CT <dbtype> -CD <database name> -CO <oprid> -CP <oprpswd> -R <run control id> -AI <application id>
24

Creating Application Engine Process Definitions

Purposes of Process Definitions A process definition serves four primary purposes: Identify the type of process to be run. Identify the program name to be run. Identify from where the program is to be run. Identify who has security access to run the program.

Implementing an Application Engine Program with No User Inputs

Adding an Application Engine Process To add an Application Engine process: 1. 2. 3. Design the Program. Identify fields for user input. Determine a Run Control record. You may have to create one if input parameters are required. 4. 5. Build the record. Determine Run Control page.

Implementing an Application Engine Program with No User Inputs (continued)

Adding an Application Engine Process
25

The steps to add an Application Engine process are (continued): 1. 2. 3. 4. 5. Place page on a component. Register the component, place on menu and apply security for the permission list. Add a Process Definition. Make Application Engine Program Modifications (if required) to read the input parameters. Test

Implementing an Application Engine Program With User Inputs

Input Parameters Many Application Engine programs require the user to input parameters. Developing to include user input parameters requires: A run control page with fields to enter the input parameters. A run control record with fields to store the input parameters. A state record with fields to store input parameters. Modifications to the Application Engine program to retrieve the input parameters.

1 2 3 4 5 6 7 8 9 10

Design Identify fields for user input to be included on the run control record. Determine Run Control record Build the record Determine Run Control page Place page on a component Register the component and set security with the registration wizard Add a Process Definition Add the necessary actions to your application engine program Test
26

Daemon Programs A daemon program is an Application Engine program of daemon type that checks for an event. When an event occurs, the daemon will trigger the process to handle the event. Daemons allow processes to be driven based on events rather than a schedule. Events can be internal or external to the PeopleSoft application database.

Daemon Groups A collection of daemon programs for a Process Scheduler server definition is called a daemon group. Unlike scheduled processes, the daemon group is re-initialized when a batch server restarts. Grouped programs are run sequentially. Each Process Scheduler server can have only one daemon group assigned to it.

27

Daemon Groups PSDAEMON programs are run by assigning them to a daemon group and associating that group with a Process Scheduler server definition that is used by a Process Scheduler server. This diagram shows how PSDAEMON is tied to a Process Scheduler server.

Go to Slide < Prev | Home | Next > Slide 226

Describing Traces
Three Traces You will look at three traces: Application Engine Trace.
28

SQL Trace. PeopleCode Trace.

Describing Traces (continued)

PSAE.EXE Command-Line Parameters This table summarizes PSAE.EXE command-line parameters for tracing. Trace Name Application Engine Trace SQL Trace PeopleCode Trace Trace Description Application Engine Trace File Tools SQL Trace Tools PeopleCode Trace Parameter -TRACE <tracevalue> -TOOLSTRACESQL <tracevalue> -TOOLSTRACEPC <tracevalue> Typical Trace Value 384 31 2048

Describing Traces (continued)

Trace Values Here are 4 of the 8 trace values available to you for the -TRACE parameter: Value 1 2 128 256 Description Step Trace SQL Trace Timings Trace PeopleCode Detail Timings

If you want Timings Trace Option (128) and PeopleCode Trace Option (256), add their values together to yield a trace value of 384.
29

Two-Tier Trace Settings Use the Configuration Manager trace tab to select two-tier trace settings for: Application Engine Trace. SQL Trace. PeopleCode Trace.

Passing Trace Values to PSAE.EXE (continued)

Process Scheduler Trace If your program is being run through Process Scheduler, you can pass the additional trace parameters to the Process Definition, on the Override Options page.

Passing Trace Values to PSAE.EXE (continued)

Command Line Trace To pass the additional tracing variables when executing your program from a command line, add on any additional parameters that you need with the trace value you want.

CallAppEngine Trace File Settings Application Engine trace (-TRACE) settings are turned on in the Application Server configuration file using PSADMIN. Trace settings made here affect all users and all online activity. The SQL and PeopleCode traces may be activated for individual users via PIA trace options if available at logon. The trace files will be sent to the Application Servers log file directory.

30

Application Engine Trace File The Application Engine trace file contains: Columns for Compile, Execute, and Fetch counts and time. A Total Time column. A summary of PeopleCode actions. Timing for PeopleCode built-in functions and methods. A summary section.

TOOLSTRACESQL and -TOOLSTRACEPC Use -TOOLSTRACESQL and -TOOLSTRACEPC only when: The level of detail in the -TRACE parameter is not enough or You required more specific details about the activities surrounding your programs execution.

The PeopleTools Trace file will have the combined data collected for the PeopleCode trace and the SQL trace.

Interpreting the PeopleTools Trace (continued)

Column Definitions

31

This diagram shows the SQL trace information in the PeopleTools trace file:

SQL Statement Compilation and Execution And Fetch This segment of the PeopleTools trace file shows SQL statement compilation and execution and fetch:

Interpreting the PeopleTools Trace (continued)

TOOLSTRACEPC Parameter This segment of the PeopleTools trace file shows information generated by the TOOLSTRACEPC parameter:

32

Advanced Trace Options Two advanced trace options are: DB Optimizer (file). DB Optimizer (table).

In this lesson you learned that: When improving performance on any system, it is important to take into account all areas of your system and understand the big picture. The information gathered in a trace points to where to focus your tuning efforts. At the Application Level, three trace parameters are key to gathering trace data: -TRACE, -TOOLSTRACESQL, -TOOLSTRACEPC The following trace settings are the most useful: -TRACE 384, -TOOLSTRACESQL 31, -TOOLSTRACEPC 2048 -TRACE is your Application Engine Trace; -TOOLSTRACESQL and -TOOLSTRACEPC are PeopleTools traces.
33

Advanced trace options trace the performance of SQL statements on the database.

Five aspects we will discuss in this section are: How traces can affect performance. Too many trace settings will skew the results. How the ReUse option can reduce processing time. How Commits and Checkpoints can affect performance. Coding Bulk Insert SQL. SQL vs. PeopleCode.

Identifying Application Level Tuning Opportunities (continued)

Tracing Tracing can affect performance. Gather the minimum trace information you need. For batch timings, trace 384 (SQL and PeopleCode timings) is recommended. It is highly recommended you do not have trace turned on in the production environment. ReUse ReUse Option: Is generally most effective when looping. Replaces %Bind variables with real bind variables. Causes the program to reuse an execution plan.

When Not to Use ReUse Do not use ReUse:

34

When limited by database platform functionality. When SQL changes dynamically in the program.

Unnecessary Commits Unnecessary commits can make a significant difference in your programs run time. Commits (and corresponding checkpoints) are set: At the section level: Auto Commit After Step checkbox. At the step level: Commit After dropdown and Frequency field.

Every commit with restart enabled causes an update to PS_AERUNCONTROL.

Bulk Insert Bulk Insert takes advantage of your databases ability to place data in a memory buffer before the table insert. To use this option your SQL must follow a specific structure to work properly. Incorrect syntax for Bulk Insert: INSERT INTO PS_SOME_TBL VALUES (%BIND(FIELDX), %BIND(FIELDY), %BIND(FIELDZ)) Correct syntax for Bulk Insert: INSERT INTO PS_SOME_TBL (FIELD1, FIELD2, FIELD3) VALUES (%BIND(FIELDX), %BIND(FIELDY), %BIND(FIELDZ))

PeopleCode Actions PeopleCode actions in Application Engine are meant to be limited to the following:
35

Testing for conditions (If-Then-Else). Performing computations. Building dynamic portions of SQL. Sharing complex business rules between online and batch processing. Accessing PeopleTools integration technologies.

In this lesson you learned that: Many factors tied to your database affect performance. Manipulating these factors can affect the performance of other systems. Application level performance tuning is by far the best place to start your Application Engine tuning. The ReUse option is an excellent way to easily reduce the amount of work your program is doing to execute the SQL in your Application Engine program. Executing SQL within a PeopleCode action is far less efficient than using the other Application Engine actions. The Bulk Insert option can speed up your SQL Inserts within a loop. Commits and checkpoints should be structured strategically.

36