Hello, did some searches on this and did not turn up anything. I am calling a DB2 stored procedure using a synonym. I want to see the exact syntax of the DB2 call that is being generated by WebFOCUS and sent to the database, but none of the SQL tracing shows it. Anyone know how to see the stored proc call in the same way you can see generated SQL against a table synonym? Any help is appreciated, thank you.

My code

-*-INCLUDE IBFS:/WFC/Repository/SalesConnect/SQL_Trace.fex
-SET ECHO=ALL;
-*** 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 = 132
 
-*** Set trace to output file
-*SET TRACEUSER=C:\ibi\apps\baseapp\my_sql_trace.txt
 
-*** Activate SQL tracing
SET TRACEUSER = ON
 
-RUN

TABLE FILE REPORT_FIRM_PERSISTENCY
PRINT
REPORT_FIRM_PERSISTENCY.ANSWERSET1.FIRM_ID NOPRINT
REPORT_FIRM_PERSISTENCY.ANSWERSET1.FIRM_LNG_NM AS 'DESCRIPTION_NAME'
REPORT_FIRM_PERSISTENCY.ANSWERSET1.PERSISTENCY_END_DATE
REPORT_FIRM_PERSISTENCY.INPUT.BEGIN_LOOK
REPORT_FIRM_PERSISTENCY.INPUT.END_LOOK
REPORT_FIRM_PERSISTENCY.INPUT.INTERVAL
REPORT_FIRM_PERSISTENCY.INPUT.PRODUCTS
REPORT_FIRM_PERSISTENCY.INPUT.FIRMS
REPORT_FIRM_PERSISTENCY.INPUT.SOURCE_TYPES
REPORT_FIRM_PERSISTENCY.INPUT.DISCOUNT_CATEGORIES
REPORT_FIRM_PERSISTENCY.INPUT.SOCIAL_CODES
REPORT_FIRM_PERSISTENCY.INPUT.DEALER_CONTROL_LEVELS
REPORT_FIRM_PERSISTENCY.ANSWERSET1.FIRM_LNG_NM
WHERE REPORT_FIRM_PERSISTENCY.INPUT.BEGIN_LOOK            EQ '01-01-2011';
WHERE REPORT_FIRM_PERSISTENCY.INPUT.END_LOOK              EQ '12-31-2011';
WHERE REPORT_FIRM_PERSISTENCY.INPUT.PERSISTENCY_END_DATE  EQ '07-18-2012';
WHERE REPORT_FIRM_PERSISTENCY.INPUT.FIRMS                 EQ '''00000000000000001021'',''00000000000000001018''';
WHERE REPORT_FIRM_PERSISTENCY.INPUT.INTERVAL              EQ 'M';
WHERE REPORT_FIRM_PERSISTENCY.INPUT.PRODUCTS              EQ 'NULL';
WHERE REPORT_FIRM_PERSISTENCY.INPUT.SOURCE_TYPES          EQ 'NULL';
WHERE REPORT_FIRM_PERSISTENCY.INPUT.DISCOUNT_CATEGORIES   EQ 'NULL';
WHERE REPORT_FIRM_PERSISTENCY.INPUT.SOCIAL_CODES          EQ 'NULL';
WHERE REPORT_FIRM_PERSISTENCY.INPUT.DEALER_CONTROL_LEVELS EQ 'NULL';
-*ON TABLE HOLD AS FOCCACHE/FIRMDATA
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

Result (sorry a little jumbled, but notice no SQL at the end):

-*** Set up SQL tracing-*** Deactivate SQL tracingSET 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 TranslatorSET TRACEON = SQLTRANS-*** Show SQL statementsSET TRACEON = STMTRACE//CLIENT-*** Show Optimization informationSET 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 charactersSET TRACEWRAP = 132-*** Set trace to output file-*SET TRACEUSER=C:\ibi\apps\baseapp\my_sql_trace.txt-*** Activate SQL tracingSET TRACEUSER = ON-RUNTABLE FILE REPORT_FIRM_PERSISTENCYPRINTREPORT_FIRM_PERSISTENCY.ANSWERSET1.FIRM_ID NOPRINTREPORT_FIRM_PERSISTENCY.ANSWERSET1.FIRM_LNG_NM AS 'DESCRIPTION_NAME'REPORT_FIRM_PERSISTENCY.ANSWERSET1.PERSISTENCY_END_DATEREPORT_FIRM_PERSISTENCY.INPUT.BEGIN_LOOKREPORT_FIRM_PERSISTENCY.INPUT.END_LOOKREPORT_FIRM_PERSISTENCY.INPUT.INTERVALREPORT_FIRM_PERSISTENCY.INPUT.PRODUCTSREPORT_FIRM_PERSISTENCY.INPUT.FIRMSREPORT_FIRM_PERSISTENCY.INPUT.SOURCE_TYPESREPORT_FIRM_PERSISTENCY.INPUT.DISCOUNT_CATEGORIESREPORT_FIRM_PERSISTENCY.INPUT.SOCIAL_CODESREPORT_FIRM_PERSISTENCY.INPUT.DEALER_CONTROL_LEVELSREPORT_FIRM_PERSISTENCY.ANSWERSET1.FIRM_LNG_NMWHERE REPORT_FIRM_PERSISTENCY.INPUT.BEGIN_LOOK EQ '01-01-2011';WHERE REPORT_FIRM_PERSISTENCY.INPUT.END_LOOK EQ '12-31-2011';WHERE REPORT_FIRM_PERSISTENCY.INPUT.PERSISTENCY_END_DATE EQ '07-18-2012';WHERE REPORT_FIRM_PERSISTENCY.INPUT.FIRMS EQ '''00000000000000001021'',''00000000000000001018''';WHERE REPORT_FIRM_PERSISTENCY.INPUT.INTERVAL EQ 'M';WHERE REPORT_FIRM_PERSISTENCY.INPUT.PRODUCTS EQ 'NULL';WHERE REPORT_FIRM_PERSISTENCY.INPUT.SOURCE_TYPES EQ 'NULL';WHERE REPORT_FIRM_PERSISTENCY.INPUT.DISCOUNT_CATEGORIES EQ 'NULL';WHERE REPORT_FIRM_PERSISTENCY.INPUT.SOCIAL_CODES EQ 'NULL';WHERE REPORT_FIRM_PERSISTENCY.INPUT.DEALER_CONTROL_LEVELS EQ 'NULL';-*ON TABLE HOLD AS FOCCACHE/FIRMDATAON TABLE SET PAGE-NUM NOLEADON TABLE NOTOTALON TABLE PCHOLD FORMAT HTMLON TABLE SET HTMLCSS ONON TABLE SET STYLE *-INCLUDE _EDAHOME/ETC/endeflt.

***a bunch of style tracing text removed***

END0 NUMBER OF RECORDS IN TABLE= 16 LINES= 160 HOLDING HTML FILE ON PC DISK ...WebFOCUS Version 8.0.06 compiled and linked on Sun Dec 8 20:30:48 EST 2013 (Gen branch8006:50)

This message has been edited. Last edited by: <Kathryn Henning>,

Hi All,

FYI, Ryan has a case open with Support for this question, and it is being researched.

Cheers!

Kathryn