How to trace Application Engines from 8.4 onwards !!

How to trace Application Engines from 8.4?

SUMMARY:
PROCEDURE A: On the PIA *
PROCEDURE B: On the Process Scheduler *

NOTE 1: Tracing activated for one Application Engine or all Application Engines
On the PIA, you 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.

NOTE 2: Push button
If you want to trace an Application Engine triggered via a push button, follow procedure B but for the Application Server.

NOTE 3: PSJob
The procedures can also be used if the Application Engine you want to trace is run via a PSJob.
PROCEDURE A: On the PIA
A.1.Sign in the PIA.

A.2.Navigate to the ‘Processes’ component and select the Application Engine that you would like to get a trace on.

A.3.Go to the ‘Override Options’ tab, set the ‘Parameter List’ option to ‘Append’, and add the tracing flags as explained below.
You can turn on tracing for the Application Engine itself, for the SQL statements run or the PeopleCode run by the Application Engine, or a combination of the three.
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 Initiate the Application Engine Step trace
2 Initiate the Application Engine SQL trace
4 Trace dedicated Temp Table Allocation to AET file
128 Initiate the Statement Timings trace to file, which is similar to the COBOL timings trace to file
256 Initiate the PeopleCode Detail to the file for the Timings trace
1024 Initiate 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 Request 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:
-TOOLSTRACESQL 135
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:
-TOOLSTRACEPC 2012
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.

Combined tracing:
It is possible to combine the three types of trace above. Here are a few examples:
-TRACE 391 -TOOLSTRACESQL 135
-TRACE 391 -TOOLSTRACEPC 2012
-TRACE 391 -TOOLSTRACESQL 135 -TOOLSTRACEPC 2012


A.4.Go ahead and run the Application Engine that you would like to get a trace on.
A.5.Get the trace file.
In the ‘Process Requests’ page, click on the ‘Details’ hyperlink for your Process Instance.
In the ‘Process Detail’ page, click on the ‘View Log/Trace’ hyperlink.
A new window opens which looks like the following:
The ‘Application Engine Trace File’ is generated when using the -TRACE flag.
The ‘PeopleSoft Trace File’ is generated when using either the –TOOLSTRACESQL, the –TOOLSTRACEPC, or both flags.
NOTE to GSC analysts only: what to do if the hyperlink don’t work?
See file called ‘How to access PS_HOME on NT for GSC analysts only.doc’
Here is an example of an Application Engine trace file:
Here is an example of a SQL trace file:
Here is an example of a PeopleCode trace file:

A.6.DO NOT FORGET TO TURN TRACING OFF by setting the ‘Parameter List’ option to ‘None’ in the ‘Processes’ component – ‘Override Options’ tab (see step A.3) and saving.
PROCEDURE B: On the Process Scheduler
B.1.Stop the Process Scheduler (Note for GSC analysts only: can only be done by the LAB).

B.2.Open the ‘psprcs.cfg’ file with any text editor (Note for GSC analysts only: can only be done by the LAB).

B.3.You can turn on tracing for the Application Engine itself, for the SQL statements or the PeopleCode run by the Application Engine, or a combination of the three.

Application Engine tracing: look for the following section in the ‘psprcs.cfg’ file:
; AE Tracing Bitfield
;
; Bit Type of tracing
; --- ---------------
; 1 - Trace STEP execution sequence to AET file
; 2 - Trace Application SQL statements to AET file
; 4 - Trace Dedicated Temp Table Allocation to AET file
; 8 - not yet allocated
; 16 - not yet allocated
; 32 - not yet allocated
; 64 - not yet allocated
; 128 - Timings Report to AET file
; 256 - Method/BuiltIn detail instead of summary in AET Timings Report
; 512 - not yet allocated
; 1024 - Timings Report to tables, ignored if Process Instance is 0
; 2048 - DB optimizer trace to file
; 4096 - DB optimizer trace to tables
TraceAE=0
Sum the numbers of all the options you want to enable and set the ‘TraceAE’ parameter accordingly (Note for GSC analysts only: can only be done by the LAB). For example, you should set ‘TraceAE’ to 135 (1+2+4+128) to trace what is shown in bold above.

SQL tracing: look for the following section in the ‘psprcs.cfg’ file:
; SQL Tracing Bitfield
;
; Bit Type of tracing
; --- ---------------
; 1 - SQL statements
; 2 - SQL statement variables
; 4 - SQL connect, disconnect, commit and rollback
; 8 - Row Fetch (indicates that it occurred, not data)
; 16 - All other API calls except ssb
; 32 - Set Select Buffers (identifies the attributes of columns
; to be selected).
; 64 - Database API specific calls
; 128 - COBOL statement timings
; 256 - Sybase Bind information
; 512 - Sybase Fetch information
; 4096 - Manager information
; 8192 - Message Agent information
; Dynamic change allowed for TraceSql and TraceSqlMask
TraceSql=0
TraceSqlMask=12319
Sum the numbers of all the options you want to enable and set the ‘TraceSql’ parameter accordingly (Note for GSC analysts only: can only be done by the LAB). For example, you should set ‘TraceSql’ to 135 (1+2+4+128) to trace what is shown in bold above.
Make sure ‘TracePCMask’ is set to 1331 (Note for GSC analysts only: can only be done by the LAB). This parameter controls which of the SQL trace options requested by client machines will be written to the trace file. For example, if you set this value to 8 (Row Fetch), this option is the only one that can be traced from any client, no matter what you set for the ‘TraceSql’ parameter.

PeopleCode tracing: look for the following section in the ‘psprcs.cfg’ file:
; PeopleCode Tracing Bitfield
;
; Bit Type of tracing
; --- ---------------
; 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 value
; 2048 - Trace each statement in program
; Dynamic change allowed for TracePC and TracePCMask
TracePC=0
TracePCMask=4095
Sum the numbers of all the options you want to enable and set the ‘TracePC’ parameter accordingly. For example, you should set ‘TracePC’ to 2012 (4+8+16+64+128+256+512+1024) to trace what is shown in bold above.
Make sure ‘TracePCMask’ = 4095 (Note for GSC analysts only: can only be done by the LAB). This parameter controls which of the PeopleCode trace options requested by client machines will be written to the trace file. For example, if you set this value to 8 (Show fetched values), this option is the only one that can be traced from any client, no matter what you set for the ‘TracePC’ parameter.
B.4.Save your modifications and close the file (Note for GSC analysts only: can only be done by the LAB).

B.5.Reconfigure your Process Scheduler (select option 3 in ‘psadmin’ and answer ‘No’ to the question ‘making any changes?’) and restart it (Note for GSC analysts only: can only be done by the LAB).

B.6.Sign in the PIA and run the Application Engine that you would like to get a trace on.

B.7.Get the trace file.
In the ‘Process Requests’ page, click on the ‘Details’ hyperlink for your Process Instance.
In the ‘Process Detail’ page, click on the ‘View Log/Trace’ hyperlink.
A new window will open will look like the following:
The ‘Application Engine Trace File’ is generated when ‘TraceAE’ parameter is not equal to 0.
The ‘PeopleSoft Trace File’ is generated when ‘TraceSql’ and/or ‘TracePC’ parameters are not equal to 0.
NOTE to GSC analysts only: what to do if the hyperlink don’t work?
See file called ‘How to access PS_HOME on NT for GSC analysts only.doc’
Here is an example of an Application Engine trace file:
Here is an example of a SQL trace file:
Here is an example of a PeopleCode trace file:

B.8.DO NOT FORGET TO TURN TRACING OFF by signing out, stopping the Process Scheduler, resetting the ‘TraceAE’, ‘TraceSql’ and ‘TracePC’ parameters to 0 in the ‘appsrv.cfg’ file (see step B.3), reconfiguring the Process Scheduler, and restarting it (Note for GSC analysts only: can only be done by the LAB).

Some Important PeopleSoft Tools Tables

I have gathered the information from some other site. Hope it will help you !!

PeopleSoft Projects
PSPROJECTDEFN table stores information about projects created in Application Designer.
Try it out:
SELECT * FROM PSPROJECTDEFN
WHERE PROJECTNAME = 'Your_Project_name';
PSPROJECTITEM table stores objects inserted into your Application Designer project.
Try it out:
SELECT * FROM PSPROJECTITEM
WHERE PROJECTNAME = 'Your_Project_name';
Portal Structure
PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component.
PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.
XLAT Tables
XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4).
PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).
Record & Field Tables
PSRECDEFN: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table.
CASE RECTYPE
WHEN 0 THEN 'Table'
WHEN 1 THEN 'View'
WHEN 2 THEN 'Derived'
WHEN 3 THEN 'Sub Record'
WHEN 5 THEN 'Dynamic View'
WHEN 6 THEN 'Query View'
WHEN 7 THEN 'Temporary Table'
ELSE TO_CHAR(RECTYPE)
END CASE
PSRECFIELD: Stores records with all their fields (sub-records are not expanded)
PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)
PSINDEXDEFN: Contains 1 row per index defined for a table.
PSKEYDEFN: Containes 1 row per key field defined for an index.
PSDBFIELD: You got it, stores information about fields.
CASE FIELDTYPE
WHEN 0 THEN 'Character'
WHEN 1 THEN 'Long Character'
WHEN 2 THEN 'Number'
WHEN 3 THEN 'Signed Number'
WHEN 4 THEN 'Date'
WHEN 5 THEN 'Time'
WHEN 6 THEN 'DateTime'
WHEN 8 THEN 'Image'
WHEN 9 THEN 'Image Reference'
ELSE TO_CHAR(FIELDTYPE)
END CASE
PSDBFLDLABL: Stores field label information.
Process Definition Table(s)
PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine...), and the component name associated with the process definition.
PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.
Message Catalog Tables
PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
PSMSGCATLANG: language table.
-- Example
SELECT * FROM PSMSGCATDEFN
WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM <> TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM <>> PeopleTools >> Utilities >> Administration >> URLs
Application Classes
PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.
PeopleSoft Query Tables
PSQRYDEFN: Stores query related info.
PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).
PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.
PSQRYEXPR: Stores query expressions.
PSQRYBIND: Stores query bind variables.
PSQRYRECORD: Stores all records used in all aspects of query creation
PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.
PSQRYLINK: Stores the relationships to child queries.
PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)
PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).
SQL Objects
PSSQLDEFN: Stores SQL object definitions.
PSSQLDESCR: Stores SQL objects descriptions, and description long.
PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.
-- When SQL type is:
0 = Stand alone SQL objects
1 = Application engine SQL
2 = Views SQLs
Application Engines
PSAEAPPLDEFN: Table that stores Application Engine program definitions.
PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.
PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.
PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.
PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.
PSAESTEPDEFN: Steps in application engines are stored in this table.
PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.
PSAESTEPMSGDEFN: Application engine message action definition table.
AEREQUESTTBL: Application Engine request table behind the AE run control page.
AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.
PeopleCode Tables
PSPCMNAME: PeopleCode Reference table.
PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).
Process Request Tables
PSPRCSQUE: This record contains the process request information to run a process request.
PSPRCSRQST: This record contains the process request information to run a process request.
PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.
Other Useful Tables
PSSTATUS: Stores PeopleSoft information such as PS Tools release version and the UNICODE_ENABLED boolean flag where a value of 1 indicates the DB is to be treated by Tools as a UNICODE DB.
PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted."
PSMAPFIELD: Stores Field mapping of Activity
PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online

Data Types Used by PeopleSoft

I think it is helpfull to know what are the commonly used datatypes in PeopleSoft (Oracle only)


VARCHAR2
DATE
NUMBER
BLOB
CLOB
LONG RAW
LONG VARCHAR