I am aware that when the following code is posted in your fex, the SQL code will be displayed.

SET TRACEOFF=ALL
SET TRACEON=STMTRACE//CLIENT
SET TRACEON=SQLAGGR//CLIENT
SET TRACEUSER=ON
SET XRETERIVAL=OFF

In addition to displaying the SQL code, does the above code result in the creation of huge trace files on the server?

The procedure I have been running has been bombing out (apparently due to huge temporary files) and I was wondering if the above code was contributing to the problem.

I would like my fex to display the SQL code, but I do not want the above code to create addional overhead and space requirements on the server.

I have tried to research the above commands, but all I have been able to find out is that the above code displays the SQL code.

Does anyone know more about the above code?

quote:

SET TRACEOFF=ALL
SET TRACEON=STMTRACE//CLIENT
SET TRACEON=SQLAGGR//CLIENT
SET TRACEUSER=ON
SET XRETERIVAL=OFF

The option you have on XRETerival will result in no returned data, assuming it's spelled correctly.

Leah:

I changed my code to SET XRETRIEVAL=ON

I have not been able to find out documentation on what these commands do. I am aware that they display the SQL code (which is what I want), but I do not know what else it does. I was wondering if in addition to displaying the SQL code, is this code also causing the creation of a trace file (that could be huge) on the server, and if it is, won't this create a burden on the server which could result in my fex bombing, and reducing response time for everyone else using the server.

JohnK :
Here are couple of previous threads that dealt with this topic -

https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/3571095091

https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/5201067331

At our site (z/OS & DB2), we use the following snippet to spit out the SQL to a dataset, that can inturn be used to run an EXPLAIN on -

DYNAM FREE DD FSTRACE FSTRACE3 FSTRACE4
DYNAM ALLOC DD FSTRACE DSN ABCFLQ.FSTRACE4.DATA SHR REU
SET TRACEON = STMTRACE
SET TRACESTAMP = RIGHT
SET TRACEWRAP = 132
-RUN

Hope that helps,
Sandeep Mamidenna.

BlueZone thankyou for your post. I found the following information:

-******* Turn Off All Tracing
SET TRACEOFF=ALL
-******* Turn on SQL Trace and have it sent back to the browser
SET TRACEON=STMTRACE//CLIENT
-******* Trun on SQL Aggration Trace and have it sent back to the browser
SET TRACEON=SQLAGGR//CLIENT
-******* Turn on User Tracing
SET TRACEUSER=ON
-******* Do Not Send SQL to DB2
SET XRETRIEVAL = OFF

I have come the conclusion that when the above code is included into your fex, the SQL statements will be displayed in your browser (via display source).

I will go under the assumption that no other "trace" files will be created on the server (which would slow down processing). Since no other "trace" files are being created, including these statements should not affect the time required to run a request. I'm glad to learn that there is no performance penalty for using the above statements while running your fex.

If anyone believes that there is a performance penalty to doing a SQL trace then let me know.

Usually it is true that no other trace files are created, we use these commands all the time. However, while the procedure is still running, the trace (which will eventually come back to the browser), appears to be held on the server.
The trace should be small with these options. However, I have seen cases (with XRETRIEVAL ON) where the trace on the server is tracking more options than these! And these are for sure the only trace options in effect. In those cases it is a huge file.

-*-- Set up SQL tracing --------------------------------------------------------

-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL

-*-- Show Commands and data exchange between the -----------
-*-- physical and the logical layers of the data adapter
-*SET TRACEON = SQLCALL

-*-- Show ??? ----------------------------------------------
SET TRACEON = SQLTRANS

-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT

-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT

-*-- Show ??? ----------------------------------------------
-*SET TRACEON = STMTRACE/1/CLIENT

-*-- Show ??? ----------------------------------------------
-*SET TRACEON = STMTRACE/2/CLIENT

-*-- Hide the trace stamp (Date/Time etc) ------------------
SET TRACESTAMP = OFF

-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78

-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

This is what I use. I've commented the STMTRACE/1 and STMTRACE/2 lines as these two generate traces based on each row retrieved - potentially thousands of lines generated.

I have not one single proper document that describes each of these commands, I've found them in bits and bobs in different documents. There are a couple of other trace commands that I do not use.

I don't know how I missed this. I've been looking for this for a long time. Back when we were using FOCUS for HP/UX this was available in FSTRACE3 and FSTRACE4. I have a programmer that is relatively new to FOCUS/WebFOCUS and he done an excellent job on a new app, but it run very slow at times. I'm almost sure it is the SQL being genereated. This will tell me for sure.

Do these SQL Trace commands work in 7.6.5 also?
I can't find my trace via the echo, so where do they go?

Thanks,

Carol

Aha, thank you Tom, I had a typo in my Trace command.

Carol,

You can see the trace if the output is HTML by doing a view source on the report page. The trace will be at the bottom.

For 7.6.x, in Dev Studio for any output type, you can click the little black down arrow next to the run button and choose Message Viewer On. When you run your report, you will see a horizontal bar and below it will be the trace command output.

Trace commands do write files to the server in EDATEMP. It is recommended that you only use them for testing and comment them out in production programs. It is recommended that you (or your admin) erase the trace files in EDATEMP on a recurring basis.