[SOLVED]SQL Trace statements for SQL Server DB

June 10, 2013, 09:19 AM

Hi all,
I am trying to trace the SQL script in my report. The following statements work only for Oracle databases. What are the equivalent SQL trace statements for reports running against SQL Server databases?

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON

Thanks in advance,

Seyed

This message has been edited. Last edited by: SeyedG,

June 10, 2013, 10:10 AM

Francis Mariani

These should work for all RDMS. The complete set of commands:

-DEFAULTH &IBIMR_user = 'unknown';

-*** Deactivate SQL tracing
SET TRACEOFF=ALL

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

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

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

-*** Enable Trace for the SQL Translator
SET TRACEON = SQLTRANS

-*** Show SQL statements in HTML page
SET TRACEON = STMTRACE//CLIENT

-*** Show Optimization information in HTML page
SET TRACEON = SQLAGGR//CLIENT

-*** Show SQL generated statement trace in HTML page
SET TRACEON = STMTRACE/1/CLIENT

-*** Show SQL generated sub-statement trace in HTML page
SET TRACEON = STMTRACE/2/CLIENT

-*** Use these settings only when redirecting trace to a file ----------------------------------------
-SET &TRACEFILE = 'D:\ibi\apps\test\sqltrace_' || &IBIMR_user || '_' || &YYMD || '_' || &TOD || '.trc';
-*-SET &TRACEFILE = 'sqltrace.trc';

-*** Show SQL statements in trace file
-*SET TRACEON=STMTRACE/1/FSTRACE

-*** Show Optimization information in trace file
-*SET TRACEON=SQLAGGR/1/FSTRACE

-*** Show SQL generated statement trace
-*SET TRACEON = STMTRACE/1/FSTRACE

-*** Show SQL generated sub-statement trace
-*SET TRACEON = STMTRACE/2/FSTRACE

-*** Set SQL tracing output file
-*SET TRACEUSER = &TRACEFILE
-*** Use these settings when redirecting trace to a file - end -------------------------------------

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

This message has been edited. Last edited by: Francis Mariani,

June 10, 2013, 10:11 AM

Francis Mariani

Be careful with

quote:
SET TRACEON = STMTRACE/2/CLIENT

This one can generate a lot of lines.

June 10, 2013, 04:07 PM

SeyedG

quote:

Originally posted by Francis Mariani:
These should work for all RDMS. The complete set of commands:

-*** 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

-*** Enable Trace for the SQL Translator *******************
SET TRACEON = SQLTRANS

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

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

-*** Show SQL generated statement trace ********************
-*SET TRACEON = STMTRACE/1/CLIENT

-*** Show SQL generated sub-statement trace ****************
-*SET TRACEON = STMTRACE/2/CLIENT

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

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

-*** Write SQL tracing to a file ***************************
-*SET TRACEUSER=C:\ibi\apps\baseapp\ifds_insight_trace.trc
-RUN

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

Good afternoon Francis,
Thanks as always for the valuable information you provide. The only way that I could get the SQL statement generated is when I run the report in WebFocus with the ‘Message Viewer’ option set to ‘ON’. The report that I am working on consists of two TABLE FILEs. First one is an Oracle table, and the second TABLE FILE is in SQL Server.

Here is what I added to the top of the FEX:

SET TRACEOFF=ALL
SET TRACEUSER=CLIENT
SET TRACEON=SQLAGGR//CLIENT
SET TRACEON=STMTRACE//CLIENT

SET TRACEON = STMTRACE/2/CLIENT
SET TRACEON = SQLTRANS
-RUN

Thanks again,

Seyed

June 10, 2013, 04:58 PM

Francis Mariani

Seyed,

I can't see why the SQL trace will not work when accessing tables in two different database connections. A couple of things I would try:

- Add the same TRACE commands right before each of the two table requests.
- Try redirecting the SQL to an external file - see my updated SQL trace commands in my original reply.

June 10, 2013, 05:04 PM

Francis Mariani

-*** Set SQL tracing output file
-SET &TRACEFILE = 'D:\ibi\apps\test\sqltrace_' || &IBIMR_user || '_' || &YYMD || '_' || &TOD || '.trc';

SET TRACEUSER = &TRACEFILE

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

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

-*** Show Optimization information
SET TRACEON=SQLAGGR/1/FSTRACE

-*** Show SQL statements
SET TRACEON=STMTRACE/1/FSTRACE

-*** Activate SQL tracing
SET TRACEUSER=ON

June 14, 2013, 12:25 PM

SeyedG

Hi Francis,
Thank you very much for the information. I am sorry; I just realized that I had forgotten to change my FocalPoint profile to show zLinux. As a result, it was showing Windows for the Operating System. Your solution for setting trace file was based on Windows. What is the syntax for defining a trace file for a zLinux environment?

By the way, with help from our WebFocus Administrator, I created a physical hold file using &&ENVPATH:

FILEDEF TESTME DISK &&ENVPATH|userrpts/testfile.txt
SET TRACEUSER = &TESTME
SET &ECHO=ALL
SET TRACEOFF=ALL
SET TRACEUSER=CLIENT
SET TRACEON=SQLAGGR//CLIENT
SET TRACEON=STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT

TABLE FILE AUTH_SITES
PRINT AUTH_URL
ON TABLE HOLD AS TESTME
END

This code created a physical hold file called ‘testfile.txt’ and saved it to ‘other’ folder in the ‘userrpts’ domain in the ‘Data Servers’.

Thanks as always,

Seyed