Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]SQL Trace statements for SQL Server DB

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]SQL Trace statements for SQL Server DB
 Login/Join
 
SeyedG
Gold member
posted
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,


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
 
Posts: 90 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
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,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Be careful with
quote:
SET TRACEON = STMTRACE/2/CLIENT
This one can generate a lot of lines.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
SeyedG
Gold member
posted Hide Post
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


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
 
Posts: 90 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
-*** 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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
SeyedG
Gold member
posted Hide Post
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


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
 
Posts: 90 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Report This Post
SeyedG
Gold member
posted Hide Post
I believe, Francis' solution for setting traces works for both Windows and zLinux environments.

Thank you so much Francis.

Seyed
 
Posts: 90 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]SQL Trace statements for SQL Server DB

Copyright © 1996-2020 Information Builders