Unable to retrieve Application Engine step

Problem:
    Unable to retrieve Application Engine step

Solution:
    State record field that is used in program is not present in the state record.

Exit(0) And Exit(1) Function in Application Engine.

Exit(0) – Terminates the current PeopleCode Action in application engine.

Exit(1) – Terminates the current Step in application engine.

Avoid rowset processing in an Application Engine program.

Avoid rowset processing in an Application Engine program. Loading data into a rowset can use a significant amount of memory, approximated by the following formula:

mem = nrows * (row overhead + nrecords * ( rec overhead + nfields * ( field overhead) + average cumulative fielddata))

where

· mem is the amount of memory required to store the rowset.

· nrows is the number of rows.

· row overhead is the overhead per row.

· nrecords is the number of records per row.

· rec overhead is the record overhead (approximately 40 bytes).

· nfields is the number of fields in the record.

· field overhead is the overhead per field (approximately 80 bytes).

· average cumulative fielddata is the average amount of data per field.

Using this formula, a rowset containing 500,000 rows with one record per row, 50 fields, and 200 bytes per field would require approximately 2.3 gigabytes of memory.

Trace Application Engines

Application Engines can be traced on two ways.

1. On The PIA.

2. On the Process Scheduler and Application Server.

Trace Application Engines On The PIA:

On the PIA, we can activate tracing for a particular Application Engine.

When activating tracing on the Client or on the Process Scheduler, any Application Engine run will generate a trace file.

Suggested Process Scheduler Trace Setting:

-TRACE 391- TOOLSTRACESQL 143 -TOOLSTRACEPC 1996

Go to the ‘Override Options’ tab. Set the ‘Parameter List’ option to ‘Append’ and add the tracing flags as explained below.

Tracing Options:

Application Engine tracing:

Add the sum of all the options you want to use at the end of the parameter list and precede the number by ‘–TRACE + a space’. For example:

-TRACE 391

Here is the list of options and associated numbers for each option:

1 Initiates the Application Engine Step trace

2 Initiates the Application Engine SQL trace

4 Trace dedicated Temp Table Allocation to AET file

128 Initiates the Statement Timings trace to file, which is similar to the COBOL timings trace to file

256 Initiates the PeopleCode Detail to the file for the Timings trace

1024 Initiates the Statement Timings trace, but, instead of writing to the trace file, this trace stores the results in the following tables: PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL

2048 Adding this value requests a database optimizer trace file

4096 Requests a database optimizer to be inserted in the Explain Plan Table of the current database

8192 This value sets a trace for Integration Broker transform programs

To turn traces on, sum all the options you want to use and enter the results at the end of the parameter list and precede the number by ‘–TRACE + a space’ as shown above. For example, you should use ‘391’ (1+2+4+128+256) to trace what is shown in bold above.

SQL tracing:

Add the sum of all the options you want to use at the end of the parameter list and precede the number by ‘–TOOLSTRACESQL + a space’. For example:

-TRACE 391 -TOOLSTRACESQL 143

Here is the list of options and associated numbers for each option:

1 Trace SQL statements

2 Trace SQL statement variables

4 Trace SQL connect, disconnect, commit and rollback

8 Show fetched rows (indicates that it occurred, not data)

16 Show all other API calls except ssb

32 Set Select Buffers (identifies the attributes of columns to be selected).

64 Show database API specific calls

128 Show COBOL statement timings

256 Show Sybase bind information

512 Show Sybase fetch information

4096 Show manager information

8192 Show Mapcore information

To turn traces on, sum all the options you want to use and enter the results at the end of the parameter list and precede the number by ‘–TOOLSTRACESQL + a space’ as shown above. For example, you should use ‘135’ (1+2+4+128) to trace what is shown in bold above.

PeopleCode tracing:

Add the sum of all the options you want to use at the end of the parameter list and precede the number by ‘–TOOLSTRACEPC + a space’. For example:

-TRACE 391 -TOOLSTRACEPC 1996

or

-TRACE 391 -TOOLSTRACESQL 135 -TOOLSTRACEPC 1996

Here is the list of options and associated numbers for each option:

1 Trace instructions

2 List the program

4 Show assignments to variables

8 Show fetched values

16 Show stack

64 Trace start of programs

128 Trace external function calls

256 Trace internal function calls

512 Show parameter values

1024 Show function return values

2048 Trace each statement in program

To turn traces on, sum all the options you want to use and enter the results at the end of the parameter list and precede the number by ‘–TOOLSTRACEPC + a space’ as shown above. For example, you should use ‘2012’ (4+8+16+64+128+256+512+1024) to trace what is shown in bold above.

NOTE: DO NOT FORGET TO TURN TRACING OFF by setting the ‘Parameter List’ option to ‘None’ in the ‘Processes’ component – ‘Override Options’ tab.

Run Process from hyperlink without code

The following steps help to run process from hyperlink or from push button without any code.

 

Code to Schedule App Engine through PeopleCode

The following code can be used to schedule App Engine Through PeopleCode.

Local ProcessRequest &MYRQST;
&MyAppName = "TEST_AE";
&MYRQST = CreateProcessRequest("Application Engine", &MyAppName);
&MYRQST.RunControlID = "TEST";
&MYRQST.RunLocation = "PSUNX";
&MYRQST.Schedule();
If &MYRQST.Status = 0 Then /* if Schedule status is success */
End-If;

Reuse in App Engines

One of the easiest tricks to improve performance of some App Engines is the ‘ReUse’ setting. It’s useful when you have a SQL object that is called multiple times, for instance when you have a DoSelect that runs a SQL Step repeatedly, substituting different Bind variables in each time.
 
The default setting for ReUse is ‘No’, which means that each time the SQL Step is called it is recompiled and new execution plan generated, bad news if the SQL has to run a large number of times. If the ReUse setting is ‘Yes’ then any %Binds in the SQL statement are converted into ‘:1’, ‘:2’ etc. This means the statement can just be compiled once and the same plan used each time, resulting in really quite dramatic improvements in processing time.
In addition to the performance gain from the above, some databases also have an area of memory cache where recently used SQL is stored. Oracle definitely has this cache, I’m not sure about other databases. As each SQL statement would contain Binds rather than literals it would be an exact match for the previous SQL Statement enabling the database to use the cached SQL and thus giving a further performance boost.
As always, there are some exceptions. If the %Bind is either part of either a record or column name (using ‘NOQUOTES’) then it shouldn’t be used. If this is the case the record and/or field is changing each time and as a result a new plan would be needed, therefore PeopleSoft converts the Binds to statics.
The performance gains that this gives will vary depending upon your SQL and the data, but a recent opportunity I had to set the ReUse flag to Yes dropped processing time for an AE Section from 15 mins to just under 2. Well worth considering next time you write an App Engine.

Important MetaSQL of SQL- Action in AE

%ProcessInstance    - To get the PROCESS_INSTANCE
%OperatorId           - To get the Operator Id i.e UserId
%RUNCONTROL - To get the RuncontrolId