Enabling and understanding SQL trace in PeopleSoft

Enabling SQL Trace

One of the most powerful trouble-shooting tools provided in all versions of PeopleSoft (PS) applications is SQL trace. This tool provides a programmer with a file containing all of the SQL statements executed by the application during an individual user's PS session. The file can be used to trouble-shoot unique constraint violations, buffer errors, missing prompt table entries, and other common PS issues. The trace file is also frequently requested by the PeopleSoft help desk when you log cases with them, so you definitely want to know how to create and use this file.

To enable SQL trace in versions 8.0 and higher, the user simply needs to click on the "To set trace flags, click here" link, which leads to a sign in page that includes the SQL trace settings. An alternate way to enable trace if the link is not provided is to add "&trace=y" to the sign in URL. To enable trace in earlier versions of PS, the user needs to navigate to the configuration manager (navigation: Edit-Preferences-Configuration from an applications panel) and click on the "Trace" tab. There are numerous trace settings that appear as checkbox items; the two that I find most useful are "SQL statements" and "SQL statement variables". In versions 8.0 and above, checking these two boxes and then logging in as usual enables trace. In earlier versions of PS, there are a couple of additional steps. First, the name of the trace file must be entered in the "Online Trace File" box at the bottom of the "Trace" panel. Second, after the trace settings have been entered, the user must then logout and log back in for them to take effect. Please note: in any version of PS, using SQL trace significantly retards application performance for the individual user, so it should be used only when other trouble-shooting methods have failed.

Finding the trace file

OK, now that the user is logged in with trace enabled, what needs to happen next? The user should navigate to the component or page that is causing the problem, but should not yet replicate the error. Why? Because the trace file can be rather voluminous at this point, so it is best to delete it and get rid of all the unnecessary SQL executed to that point and make your trouble-shooting task a little easier. Where do you go to delete the trace? In versions prior to 8.0, you know where the file is because you've entered the path for it (usually something like c:\temp\sqltrace.log, which can be on the client machine or the server, depending upon whether you're logged on in two- or three-tier mode). In versions 8.0 or higher, the trace file is created on the application server in the {PS_HOME}\appserv\{DBNAME}\LOGS directory. The name is normally a combination of the operator ID used to log in to the session followed by the machine name or IPAddress and ".tracesql" (e.g., JSMITH_PSSERV.tracesql).

Once the file has been deleted, the user should replicate the error and then stop, sign out, and sign back in without trace enabled and continue doing his or her normal work. You can then pull the file off the server and begin your trouble-shooting activities. If you're lucky, you'll be able to scroll to the bottom of the file and the last statement executed will be the one that you need to solve the problem. In some cases, though, you may have to enter a number of the statements into a SQL tool (e.g., Query Analyzer, SQL Plus, TOAD) before you can diagnose and solve the problem.

Understanding the file

Here's a sample line from a SQL trace file that I created while inquiring on my vendor processing authority preferences in the "User Preferences" component of a PS financials instance:

PSAPPSRV.1088 1-344 14.38.04 0.000 Cur#1.A84XXXX RC=0 Dur=0.000 COM

Stmt=SELECT OPRID, AUTH_VNDR_UNCERTFY, AUTH_VNDR_CERTFY, AUTH_VNDR_INACT_ST FROM

PS_OPR_DEF_TBL_VND WHERE OPRID=:1 ORDER BY OPRID

PSAPPSRV.1088 1-345 14.38.04 0.000 Cur#1.A84XXXX RC=0 Dur=0.000 Bind-1

type=2 length=4 value=ROBR

The "1-344" is a sequential line counter for the process that you're currently executing. The next column contains "14.38.04", which is a time stamp from the machine on which PeopleSoft is running. The third column, "0.000", is the amount of time that has elapsed since the previous line was written to the trace file. The "Cur#1" indicates the number of the cursor for the statement and the fifth column ("A84XXXX") indicates the name of the instance in which the statement was executed, which is followed by the return code (RC=0) and duration (Dur=0.000) of the statement. Finally, the last column displays the SQL statement and any bind variables; the values of the binds can be found in the next line of the trace file (value =ROBR for bind variable :1).

Common uses for the SQL Trace file

What kinds of problems can you solve with the trace file? One common issue is the "matching buffer not found error", in which an orphan record can be preventing a page from building successfully. Another common use for the trace file is to solve problems with search records not returning values that the user expects to see, such as voucher IDs, employee numbers, expense reports to be approved, or other items. These can normally be found by navigating to the component, determining the search record and querying the search record, but there are numerous cases where the search record is named dynamically via PeopleCode and the trace file saves you a lot of time reading through that PeopleCode to determine the name of the table. You can also solve dreaded unique constraint violations using the trace file, because you'll have the exact insert statement that the system was executing at the time that the error occurred. The trace file is also great for SQL tuning purposes because it provides the timing for each statement. In any case, now that you've got the trace file you can run the relevant statements in a SQL tool and solve the problem that the user is experiencing.

Ref:http://it.toolbox.com/blogs/robs-peoplesoft-blog/enabling-and-understanding-sql-trace-in-peoplesoft-11093

SHARE

peoplesoft

• 
• 
• 
• 
•