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     [CLOSED] SQL translator

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] SQL translator
 Login/Join
 
EWoerle
Gold member
posted
Does anyone know where to locate any commands that would help optimize the way webFOCUS generates the SQL that is passed to DB2?

When I run these commands : 
  SET TRACEOFF=ALL
SET TRACEUSER = ON
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP=OFF
SET XRETRIEVAL=OFF


I often find webFOCUS sending using subselects when not necessary, not translating IN statements properly, among other things. Also I'd like to have the group by statements done on the FOCUS side of things instead of the DB2 side of things.

Also I know that IB changed the way that sql was generated between versions 7.1.4 and 7.6.7. Is there maybe a command that I can use that will utilize the old translations? (which appears to be more efficient in some cases) over the new translations

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


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
quote:
Also I'd like to have the group by statements done on the FOCUS side of things instead of the DB2 side of things.

I'm not sure why you want to do this. It is more efficient for the relational engine to do this.

But where I would have you look would be on the IBI site for anything related to relational efficiencies, Data Administration manuals, sql translator, etc.

Your branch may be able to provide some presentations on relational efficiencies as relates to joins, aggregation, and selection. If you can't find any, private post to me with your email address and I'd be happy to forward what I have.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
EWoerle
Gold member
posted Hide Post
I know it seems strange to want to do my group by's on the reporting server, but our reporting system is much faster then our DB2 system (due to not having to share with other company resources). Also our users are running into resource limit issues. So if we can get the SQL more efficient and do less work in DB2, then we should be able to speed up the queries of our users while utilizing the excess resources we have at our reporting server.


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
Then don't put the sort statements in the original request, just the WHERE statements. Hold the data when you get it back and do what you want to with it. Or do the BY's with a PRINT not a SUM, which ever gets you what you want.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
This is the first time I've heard that the DB2 server is slower than the reporting server, though this has given me something to think about in our environment.

As Ginny mentioned, I'd take a look at the Data Administration documentation: Customizing the DB2 Environment


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
Waz
Expert
posted Hide Post
If you realy want to have the GROUP BY done by WF, then do BY TOTAL field.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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     [CLOSED] SQL translator

Copyright © 1996-2020 Information Builders