Stored Outlines and SQL Profiles do not work well with PeopleSoft. Bind variables in Application Engines often become literals before the SQL statement is submitted to the database, unless the ReUse Statement feature has been enabled, which it is not by default.
I wrote about this in PeopleSoft for the Oracle DBA, it contains a script called stmtid.sql (listing 11-23, page 280) that adds an identifying comment to each of the stored statements used by PeopleSoft COBOL programs.
Now, I have produced a similar script for Application Engine programs. The script aeid.sql adds identification comments containing the name of object. It directly modifies the PeopleTools tables for Application Engine steps and PeopleSoft SQL objects. These comments will be seen in database tools and traces. So now when your database tools find a problem statement, it is easy to find the source.
Below, is part of a screen shot from Oracle Enterprise Manager. You can see the identifying comment in the SQL, which was added by this script
The comment identifies the name of the Application Engine program, section, step and step type. In this example, the SQL is from an Application Engine ID GPGB_EDI_PRC, in section called EDI_PRC, in a SQL type step called Step01.For example, if you need adjust this SQL statement, perhaps add a hint to it, you don't have to spend time working out where it came from. It is not uncommon to find many similar SQL statements in a program. Also, where dynamic code is used to generate the SQL statement, it can be very tricky to find the exact source.
Using aeid.sql
- You will need to edit the script each time, to specify which Application Engine programs and SQL objects are to be commented. The script is not perfect. It does not handle some very long statements, so you cannot simply instrument the entire system in one go.
- The script is designed to run in SQL*Plus.
- It produces a report of statements that it has altered.
- The script does not commit. You should either commit or rollback the update depending upon whether you are satisfied with the output.
- I suggest that the script only be run in pre-production environments. The comments will of course be migrated along with any other changes.
@go-faster.co.uk on BlueSky
Posts
5 comments :
Rather interesting place you've got here. Thanks for it. I like such topics and everything that is connected to this matter. I definitely want to read more soon.
Does this work with tools 8.52. So far i have not been able to get it to work. Is there only 2 places that i need to edit with the app engine job name?
s.ae_applid = 'my_app_job_name'
t.sqlid = 'my_app_job_name'
Thanks
This strategy is pretty much obsolete from PeopleTools 8.52 because Application Engine is instrumented. That is to say that the application engine name and step are set in the session's Module and Action attributes (see http://blog.psftdba.com/2012/11/peopletools-852-application-engine-sets.html), although there are some problems with DO SELECT and DO WHILE loops.
Thank you for the quick response. I am creating oracle trace files for some app engine jobs by setting the oracle trace with the following in an AE sql step
SET EVENTS '10046 trace name context forever, level 8';
Do you know of any way of getting the
app engine step into the oracle trace
sql stements? Thank you again for
all of your help. Greatly appreciated.
If you know the module and/or action that you are interested in, then there are various ways to extract trace for just that module/action.
• You can enable trace using dbms_monitor.serv_mod_act_trace_enable (see https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_monitor.htm#CFAGCHJC). It is rather like a watch-point in a debugger. You will get a trace file for the sessions where module and/or action match the specified values. So you don’t need to alter code. You probably also want a trigger on PSPRCSRQST to set tracefile_identifier so the trace file name is meaningful.
• The Oracle trcsess utility (https://docs.oracle.com/cd/E29505_01/server.1111/e16638/sqltrace.htm#i20110 ) will filter statements that match a particular module and action, and then you can profile the output from that.
However, if you have a trace file for several AE steps or a whole AE process and you want to know which SQL is which AE step then you need a trace profiler that will show the module for the statement. Have a look at Christian Atognini’s profiler TVD$XTAT (TriVaDis eXtended Tracefile Analysis Tool) http://antognini.ch/downloads/top2/chapter03/tvdxtat_40beta10_20140630.zip. It is in the software distribution for his book Troubleshooting Oracle Performance (http://antognini.ch/top/).
Post a Comment