Different
types of variables in the Application Engine people code
Local
variable – these are available for the duration of the program in which they
are declared.
Global /
Component – These variables are available while the Application Engine program
is running. They are saved at commits and checkpoints, So they can be used for
restarts. Component variables are same as Global incase of the AE.
Different
ways pass data between the steps
1) State
records – One row can be passed and can have many state records.
2)
Component/Global People code variables
3)
Temporary records – Multiple rows of data can be passed.
Prerequisite
for the State record
1) Should
be either Derived work record Or SQL Table.
2) Name of
the record must end in AET; this is how the system identifies the record as a
state record.
3) PROCESS_INSTANCE
must be a key.
4) Any data
types except character or numeric must require not be fields.
Note: - No
People code fires on an Application Engine state record.
No
Validation of translate values or Prompt tables are done.
Read the
runctrl parameters in AE
1) SQL: - %Select (EMPLID)
Select EMPLID from PS_AERUNCONTROL where PROCESS_INSTANCE = %PROCESS_INSTANCE
and ORPID = %oprid
2) People
code: - &SQL = “Select EMPLID from
PS_AERUNCONTROL Where PROCESS_INSTACNE =” | PS_TEST_AET.PROCESS_INSTANCE | “and
OPRID =” | %oprid.
%PROCESS_INSTANCE or %Bind
(PROCESS_INSTANCE) can
be used.
%PROCESS_INSTANCE is more efficient and faster
Execute
Application Engine through Push Button
1) CreateProcessRequest
() , Schedule ()
2) CallAppEngine.
CallAppEngine
Syntax
CallAppEngine
(applid [, state record]);
3 trace
parameters you can pass to your psae.exe?
- TRACE
- TOOLSTRACESQL
- TOOLSTRACEPC
Best
Trace option
- TRACE 384
– mostly used trace value.
Difference
between a Trace value and Trace parameters
A Trace
parameter determines which type of trace is turned on.
A Trace
value determines what type of data is recorded in your trace files (s).
Where does the trace file is written
to?
The trace file is written to the server directory when you’ve specified
the trace on the client. If the client has %Temp% set to a drive or directory that does not exist on
the server, PeopleSoft Application Engine does not generate a trace file.
For an Application Engine program running on a server, PeopleTools writes
the generic PeopleTools trace for SQL and PeopleCode trace files to the same
directories as the AET traces. The prefix of the trace file name is also the
same, and the suffix is trace. On the Windows workstation, the trace is written
to the “People Tools Trace File” specified in the Trace folder of PeopleSoft
Configuration Manager.
3 common ways to pass a trace
parameter and value to your program psae.exe
Configuration
manager, Process Definition, Command prompt
Different
types of Application Engine?
·
Standard:
Which is a Standard or normal entry-point program.
·
Upgrade
Only: Used by PeopleSoft Upgrade utilities only.
·
Import
Only: Used by PeopleSoft Import utilities only
·
Daemon
Only: Use for daemon type programs.
·
Transform
Only: Support for Extensible Stylesheet Language Transformations (XSLT).
PSDAEMON process
One
PSDAEMON process can run for each row in the PS_SERVERDEFN table. The
PS_SERVERDEFN.DAEMONENABLED field must be set to 1.
System
automatically modifies all static references in other programs to the renamed
program
The system automatically modifies all static references in other programs
to the renamed program. For instance, if you call the renamed program from
another Application Engine program, the Call Section action in the calling
program is modified to reflect the new program name. All sections and steps are
saved under the new name. Only one occurrence of a program name can exist for a
given database.
PeopleSoft Application Engine recompiles
a reused statement
%Clear Cursor function
Restart AE
program
Program
Level
·
State
Record
One of the
state record needs to SQL Table, Since All Derived work record will be
re-initializing on commit.
·
Program
Properties
On the
Advanced tab in the program properties dialog box, make sure that disable
restart is not checked.
·
Configuration
manager
In the
configuration manager, sure that Disable restart is not selected on the process
scheduler tab.
Section
Level
·
Section
type
The option
for section type are prepare only and critical updates
If the
section is preparing data i,e select data, Populating temporary tables, or
updating temporary tables then the section should be prepare only.
If the
section is updating the permanent application tables in the database, you
should select critical update.
Step
Level
·
Add
an order by clause
%Select
Field Select Field1 from PS_SOME_RECORD Where FIELD1 > %Bind (FIELD1) Order
by FIELD1.
·
Add
a switch to the selected table
·
Delete
processed rows.
The only
restriction for batch runs occurs when you have restart enabled, and you are
inside a Do Select that is of the Select/Fetch type (instead of
"Re-select" or "Restartable"). with select/Fetch, all
commits inside the loop are ignored, including the commit frequency if it's
set.
Why
Sections are reordered alphabetically by name?
Sections
are always reordered alphabetically by name at save time to make it easier to
locate a given section. However, order of execution is dependent on internal
call section references, and is therefore independent of the order that
sections are inserted and displayed.
What
does On Error property do?
The On Error property does not apply to compile errors (for example,
specifying erroneous SQL statements). It checks only for execution-type errors.
If your program has a syntax error, the program terminates.
What is to be intend to include
multiple SQL statements within a single action?
Use the
meta-SQL construct %EXECUTE. The previous sample SQL statement
sample contains bind variables from a previous Application Engine action.
Entries in
the AEREQUESTTBL table do not have any effect on
Application Engine programs called from PeopleCode using the CallAppEngine
function.
The
Application Engine process request page inserts values into the following
tables:
·
AEREQUESTTBL.
Contains
all of the values that appear on the page except those in the Parameters group.
·
AEREQUESTPARM.
Includes
only initial state record values specified in the Parameters group, if needed.
Invoke an Application Engine program
through the command line
·
Restarting.
When a
program abends, a system administrator might restart the program using the
command line. If needed, you can locate all of the specific program and process
information from Process Monitor on the Process Request Detail dialog box.
Normally, users (or system administrators) perform a restart from Process
Monitor.
·
Development
or Testing.
Many
developers include the command line in a batch file to launch a program they
are developing or testing. This way, they can quickly execute the batch file as
needed. This also enables separation of development of the application program
from its associated pages.
·
Debugging.
To debug a
program running on the server, you can sign into the server (using telnet, for
example) and invoke the program from the command line.
Bulk Inserts
Bulk Insert
is also ignored when all three of the following conditions are true: the
database platform is Oracle, the record contains and EFFDT field (effective
date), and the record contains a mobile trigger. This is required because an
Oracle database does not allow the reading of mutating tables in a row trigger.
Populate PS_MESSAGE_LOG instead of
Log Message action
You can
also use MessageBox PeopleCode to populate PS_MESSAGE_LOG instead of using the
Log Message action. This enables you to easily record errors encountered within
Application Engine PeopleCode programs.
Application
Engine program is not set up for restart ability
When an Application Engine program is not set up for restart ability,
then commits are not controlled, monitored, or restricted by PeopleSoft
Application Engine. When Restart is disabled, commits are controlled by the
program.
PeopleSoft
Application Engine does not commit a step containing a Do Select action with
the Select/Fetch option enabled until the entire step completes successfully,
regardless of the other options you have selected.
Set Based
Processing
Set
Processing uses SQL to process groups, or sets, of rows at one time rather than
processing each row individually. With row-by-row processing you following a
repetitive loop that selects a row, determines if it meets a given criteria, if
so, apply rule x to row Update row, commit. With set processing, you only
select those rows that meet the filtering criteria and then run the rule once
again all the affected rows.
Advantages
of Set Processing
Improved
Performance: Our internal testing has revealed that, in an overwhelming
majority of cases, set processing performs significantly better than it is
-by-row counterpart for “reasonable” batch processing volumes.
Minimized
SQL Overhead: It tends to use fewer Application Engine SQL statements that
each processed more data than the statements executed in row-by-row processing.
Easy
Maintenance: - If need to make a fix or add an enhancement to SQL, it’s just a
matter of modifying the SQL or inserting the new “Chunk”.
Leveraging
the RDBMS: With
Set – based processing, you take advantage of the SQL processing engine on the
database rather than placing the processing burden and overhead on the
application executable.
Testing
and debugging application engine
Application
Engine Trace file-You can track the step execution of your application
execution.
Application
Engine Interactive Debugger – First click on the trace tab and turn off the
statement timings. Select Profile
-> Edit Profile-> Process Scheduler Tab -> Application section Select
Debug check box.
(You can
dynamically modify the state record run application engine step by step
People Code
Debugger for the Application Engine.)
AE Trace
files: - AE__.AET
Without the
process instance: - AE__.AET
Order
and flow of action types
1) Do When
2) Do While
3) Do
Select
4)
Peoplecode
5) SQL
6) Call
Section
7) Message
Log
8) XSLT
9) Do until
How
running AE program as Batch differs from running it online
Application
Engine programs are designed for two types of execution and each has its own
pool of Temporary Tables
Online:
·
Invoked
by CallAppEngine from People code
·
Run
quickly, synchronously, and at random times.
·
Potential
for simultaneous executions
·
Uses
the online Temporary Table pool.
·
Not
restart able.
·
Psae.exe
randomly assigns an instance number from the number range on your online temp
tables.
·
If
the instance number is in use psae.exe puts the program in Queue until the
assigned instance becomes free.
·
Unlock
on completion, on Crash free from Manage Abends.
Batch:
·
Invoked
through the Process Scheduler.
·
Run
for longer amounts of time, asynchronously, and at scheduled times.
·
Can
be designed for parallel execution for performance.
·
Uses
the Batch/Dedicated Temporary table.
·
Restart
able, If not Re-startable on Program completion.
·
It
allocates instance number based on the availability on a record by record basis
and psae.exe begins with the lowest instance numbers. If the properties are set
continue - Base table is used with Process instance as key.
·
If
Re-starable – Locked across Restarts until completes successfully.
Access
property in the Section
Basically
they have two options, public and private if section declared as public then it
be access from other program. Private we cannot call from other program.
Which
actions are mutually exclusive and why they are mutually exclusive?
Sql and
call Section.
Error
handling or Exception handling in Application Engine
Step
properties
On Error
Abort –
Write message to message log and terminate.
Ignore -
Write message to message log and continue.
Suppress –
No message will be written, but program will continue.
People
code Action
On
return options can used to handle run time errors.
Abort: -
Exits immediately – Not recommended.
Break:-
Exits the current step and section and control returns to the calling step.
Skip Step:-
The program exits the 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.
SQL Action
properties
No Rows:-
When the Sql doesn’t return any rows, you can tell what application engine
program should do.
Abort: -
Program terminates
Section
Break: - Application Engine exits the current section immediately, and
control returns to the calling step.
Continue: -
The program continues processing
Skip Step:
- Application Engine exits the current step immediately and moves on to the
next step. When using skip step keep the following in mind:
1)
Application Engine ignores the commit for the current step at runtime
2) If the
current step contains only one Action, only use skip step at by-pass the
commit.
What are
different types Do Select
1) Select/Fetch
2) Reselect
3) Restartable
·
Select/Fetch
Opens the
cursor only at the first time and retrieve rows one at loop.
Commits
inside the step (commits in the Called Section) are ignored if AE is Restart
enabled.
·
Reselect
It opens
the cursor and closes the cursor on each iteration of the loop.
It will
reselect the same row of data.
Logic in
Actions of the step should be such that it will be changing the status of the
rows in the table the do select is selecting.
Commits are
not ignored and will be committed in a reselect loop when the restart is
enabled.
·
Restartable
similar to
select/Fetch but it WILL COMMIT inside the loop thus allowing the checkpoint to
the PS_AERUNCONTROL table.
Difference between exit (0), exit
(1) when we are using this functions in AE
Exit (1)
causes immediate termination of a PeopleCode program. Use this parameter to
rollback database changes.
Exit (0)
caused immediate termination of a Peoplecode Program but don’t make rollback in
the database.
What is
the Mandatory property of App.engine?
Main-->Step-->Action
Is the Mandatory property of App.engine
Is the Mandatory property of App.engine
·
Main
is the required section in Application Engine.
·
There
can be multiple steps in single application engine but at least one step should
be part of AE.
·
Similarly
you can have multiple actions in AE but you should have minimum 1 action part
of step.
·
Save
as AE
Temp Tables
They are used to store the immediate values during the processing of the AE.
They help in batch processing by creating multiple instances and there by facilitating parallel processing. If used properly they can greatly improve the performance.
They are used to store the immediate values during the processing of the AE.
They help in batch processing by creating multiple instances and there by facilitating parallel processing. If used properly they can greatly improve the performance.
State Records
They are used to pass on values from one section/step/prog to the other.
There can be a max of 200 state records that can be used in a single AE but only one of them can be default. state record name must end with _AET.
They are used to pass on values from one section/step/prog to the other.
There can be a max of 200 state records that can be used in a single AE but only one of them can be default. state record name must end with _AET.
Differences
between State and temporary records
1. State
record at any point of time has only single line information as it's driven by
the Process_Instance as key field. While temp table can have multiple rows.
2. State record had only single instance while the temp table can have maximum of 99 instance.
3. State record is used for restart logic.
4. State record can be used for dynamically calling AE section by adding AE_SECTION AE_APPLID as fields. Temp table cannot be used.
2. State record had only single instance while the temp table can have maximum of 99 instance.
3. State record is used for restart logic.
4. State record can be used for dynamically calling AE section by adding AE_SECTION AE_APPLID as fields. Temp table cannot be used.
Why
temporary records are needed?
Defines the
record definition as a temporary table. Temporary images of the table can be created
specified by the PeopleTools Options Page. Temporary tables are used for running
Application Engine batch processes. Temporary tables can store specific data to
update without risking your main application table.
Prerequisite
for the Temporary record
1) Should
have process Instance as key
2) Name
should end with _TAO
Different
types of Temporary tables
·
Dedicated
·
Shared
Parallel Processing
Potential performance improvements by splitting the data to be processed into groups and simultaneously running multiple instance of your program to deal with different groups of data.
If you have a one 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 delete, Inserts and/or updates unsynchronized with each other.
You could solve the problem by creating multiple temporary tables as a pool of tables. Each invocation of your 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 through with it (for each Application Engine program you write).
What are the important steps for implementing the parallel processing
1. Define you Temporary Tables.
2. Set the Temporary Tables Online pool.
3. Assign Temporary Tables to your Application Engine program in it program.
4. Set Temporary Table Batch Pool – Instance count in the AE.
5. Build / Rebuild your Temporary Table record.
6. Code %Table Meta – SQL as reference to Temporary Tables in your Application Engine program, so that Application Engine can table references to the assigned Temporary Table instance dynamically at runtime.
What happens when all the instance of the temporary table are in use?
It behavior can control by AE developer. If the runtime options are set to continue “People Tools will insert rows into the base table using the PROCESS_INSTANCE as a key”. If temp table doesn't contain PROCESS_INSTANCE as a key field in a Temporary table, you should change the Temp table runtime options to “Abort” in the appropriate Application Engine programs.
Divide the data to be processed by different instance of the program to perform parallel program
Run control parameter passed to each instance of the AE program enable it to identify which input rows “belong” to it, and each program instance inserts the rows from the source table into its assigned temporary table instance using %Table.
Difference between Application
Engine Do-while, Do-When and Do-select
In DoSelect
we don't need to increase the counter. Pointer to the next row in the selected
data will be moved automatically.
In DoWhen we need to write the select query to skip the processed row.
Do-while keeps running till the condition runs true.. And do-select runs
for each row and moves to the next row.
for each row and moves to the next row.
like if you have statement:
SELECT EMPLID from PS_EMPLOYEMENT and PS_ EMPLOYEMENT has 2000 rows then do-while keeps running forever and selecting the same row every time but do-select will run for 2000 times fetching the next EMPLID each time.
SELECT EMPLID from PS_EMPLOYEMENT and PS_ EMPLOYEMENT has 2000 rows then do-while keeps running forever and selecting the same row every time but do-select will run for 2000 times fetching the next EMPLID each time.
Difference in using SQL directly and
calling SQL from PeopleCode in Application Engine
If you use
SQL directly then it will go to database directly to fetch rows of data. But if
u write SQL'S in PeopleCode then the trip will be from application server to
database because PeopleCode resides in app server.
One should avoid writing SQL'S in App engine PeopleCode as it would degrade the performance.
One should avoid writing SQL'S in App engine PeopleCode as it would degrade the performance.
Run an
Application Engine program in different ways
1. Running
from Application Designer.
2. By
calling People Code function.
3. Running
from DOS Environment (Debugging).
4. Running
from Application Engine People Tool.
5. Running
from PeopleSoft Application.
Why
can’t the SQL and call Section Actions be present simultaneously in a single
step?
Call
section and SQL actions are mutually exclusive so they cannot be called at a
time. There is chance to get into an infinite loop if they are appeared in the
same step.
Test an
application engine program
One can
test Application in two ways:
1. Running
Application engine from 2-Tier
2. Running
Application engine from 3-Tier.
1. Running
Application Engine from 2-Tier: Open Application Designer Then open desired
Application engine. Then on top middle tool bar you can see 'Run Program
button' click on to that button give vales to parameter like Process Instance
Run Cntrl id etc and click. This will run application engine and generate a
trace file to your local system. After completion of Application engine you can
see if you got desired results or not.
2. Running
Application engine from 3-Tier: Open 3-Tier link Go to peopleTools-> Process Schedular -> Process Add you Application engine process
here in 'Process Defination Options' give 'PRCSMULTI' in COMPONENT and 'TLSALL'
in Process Group and then Save the page.
After
defining Application engine Go to
peopleTools-> Process Schedular -> System Process Request then add a RUN CONTROL ID After
adding run control id click on the RUN button on the page this will lead you to
page where all the process will be listed Select you process name (Application
engine Name) and clik on ok. This will run you Application engine.
Application Engine event in
PeopleCode
Application
engine PeopleCode event is On Execute.
Debug your Application Engine
Go to
configuration manager of 2 tier (App Designer). Then navigate thru Profile > Edit > Process Scheduler > Application
Engine check the Debug
option.
2. Open the App Engine program needs to debug and click the traffic light run program button.
3. Enter the run control id & debug the AE.
2. Open the App Engine program needs to debug and click the traffic light run program button.
3. Enter the run control id & debug the AE.
Difference between AE and SQR
1. AE is
very systematic as it is structured as Section step and Action. While SQR is
not systematic.
2. AE flow is easy to read and understand while SQR is not that easy.
3. AE is easy to debug as you can do line by line debugging. With SQR it’s not easily possible.
4. By using SQR you can create and maintain different reports which are not possible in AE's.
2. AE flow is easy to read and understand while SQR is not that easy.
3. AE is easy to debug as you can do line by line debugging. With SQR it’s not easily possible.
4. By using SQR you can create and maintain different reports which are not possible in AE's.
Difference between sql statements
and metasql statements
SQL statements:
The SQL
statements are used to fetch/retrieve update insert delete data from the
database. In PeopleSoft we use the sql stmts using sqlexec or createsql.
Differnet sql statements are: Select
Insert Update Delete.
Meta Sqls:
Different
RDBMS have difference date and time formats. Though the component processor
takes care of the automatic conversion but at the time of using the sqlexec the
automatic conversion does not take place. So in order to understand this the
meta sql concept has come. Which we can use in diff sql stmts. for example
datetimein date time currentdatetime, datetimeout etc.
dateIn: This is used in the where clause
of Select and update
dateout: This is used while selecting the
data.
Inbound
and outbound Process
Import data
by using a file layout requires the
following sequence of activities:
1.
Provide
the import data in a properly formatted flat file.
Each record
in the file must correspond to a record with the same name in the PeopleSoft
database, and its fields must have the same names and data formats as the
database record’s fields. Each record in the file must end with a new line
character.
2.
Create
a file layout definition to match the record and field structure of the data.
Insert the
appropriate record definitions into the file layout, then reposition the file
records and file fields to match the record and field positions in the file.
3.
Preview
and troubleshoot the input data format and content.
4.
Generate
the data import Application Engine program and PeopleCode.
5.
Run
the Application Engine program to import the data.
Export Data
To export
data to a flat file, you create a file layout definition, and then write
PeopleCode to transfer the data to a file. The PeopleCode can be initiated from
Application Engine or from any event. It should populate text strings, rowset
objects, or record objects, and it should apply the WriteRecord or WriteRowset
method from the File class to transfer the data to the file by using the file
layout definition to position the records and fields as required by the target
application.
Read More
