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     [WORKAROUND] Disable query "optimizer"?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[WORKAROUND] Disable query "optimizer"?
 Login/Join
 
Wep5622
Virtuoso
posted
I'm having an issue with what the query optimizer is doing to one of my TABLE-requests and I wonder whether there's some setting I can put in my procedure to turn it off (or at least temporarily)?

The problem in case is that I have a couple of conditional joins that significantly limit the size of the data-set. They are in my TABLE request (and with differing JOIN aliases too), but when I look at the trace output they are missing from the generated SQL.

Apparently the optimizer thinks it doesn't need those tables and conditions, but it doesn't even tell me why...

All I get are messages about 1:n joins not using the entire primary key of the foreign relation:
 FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: UVSCII
 FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: UVPP
 FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
 FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY AGGREGATION DONE ...


That looks pretty harmless to me and there doesn't appear to be a way around it.

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


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
quote:
How to Optimize Requests
SQL SQLORA SET {OPTIMIZATION|SQLJOIN} setting where:
SQLORA - Is the target RDBMS. You can omit this value if you previously issued the SET SQLENGINE command.

SQLJOIN - Is a synonym for OPTIMIZATION.

setting - Is the optimization setting. Valid values are as follows:

ON - instructs the adapter to create SQL statements that take advantage of RDBMS join,
sort, and aggregation capabilities. Note that the multiplicative effect may disable
optimization in some cases. However, misjoined unique segments and multiplied lines
in PRINT-based and LIST-based report requests do not disable optimization. This is the
default.

OFF - instructs the adapter to create SQL statements for simple data retrieval from each
table. The server handles all aggregation, sorting, and joining in your address space or
virtual machine to produce the report.


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
Wep5622
Virtuoso
posted Hide Post
That's not really what I'm looking for. The problem is that the WF-server already performs partially unoptimized joins by requesting far too much data and then joining that (on the reporting server) to the contents of a fairly large table.

What I'm looking for is a way to make it not try to be smart about the SQL it generates, but instead do a straight conversion from the TABLE request to SQL.

If not, I'm probably forced to use SQL passthru and do the type-conversion for each field by hand to make it match the master files again.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
njsden
Virtuoso
posted Hide Post
Wep5622, the reason why those 2 JOINS are missing from the generated SQL may not be related to the warnings you're seeing but likely to the fact the WebFOCUS excludes any tables when building the SQL statement if no fields from those tables are being used anywhere in the request.

Please try to adjust your TABLE FILE request and reference at least one field from each of the 2 tables you're joining to, either as a display field in your PRINT/SUM verb (you can use NOPRINT if you don't actually want to see the value being displayed), as part of a DEFINE/COMPUTE expression or perhaps in a WHERE condition. By doing so, the iWay translator would know to include a join to those tables when building the SQL statement as it would be the only way to access the fields specified in your request. Smiler


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
j.gross
Virtuoso
posted Hide Post
Sounds (from the FOC2524 message) like:
your join structure includes a "unique" join, but based on the primary key of the joined-to table (as determined from the keys= value in the acx file of its synonym) what you are joining to does not include the primary key field(s). This might happen if you are joining to an alternate key.

Is that the case?


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Wep5622
Virtuoso
posted Hide Post
Right, of course! Even though some of those columns were in the WHERE-clause...

I don't suppose there's an option to turn off that optimization?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Wep5622
Virtuoso
posted Hide Post
quote:
Originally posted by j.gross:
Sounds (from the FOC2524 message) like:
your join structure includes a "unique" join, but based on the primary key of the joined-to table (as determined from the keys= value in the acx file of its synonym) what you are joining to does not include the primary key field(s). This might happen if you are joining to an alternate key.

Is that the case?


No, I'm joining to the primary key.

The tables I'm joining have a 1:N relationship, so naturally the primary key on the right side of the join spans more columns than the one on the left side of the join.

It is a UNIQUE join however, since we constrain the (conditional) join on a single constant value at the right side, completing the PK there. Apparently WebFOCUS does not realise that[1].

The join is done like this:
JOIN
 FILE UVSC AT SC TAG left TO UNIQUE
 FILE UVSCIC AT SC TAG right AS Jn
 WHERE left.SC EQ right.SC;
 WHERE right.IC EQ 'foo';
END


With the PK of UVSC as (SC) and the PK of UVSCIC as (SC, IC).

The error message seems to indicate that WebFOCUS got things backwards, as it's claiming that the foreign key of "some unmentioned segment" is not a superset of the primary key for segment uvscii. That makes no sense.

In fact, uvscii is the foreign key segment, not the primary, so it's actually the other way around.
Is it just the error message that's got it reversed or is it the JOIN?

If that's not confusing enough for you, have a look at the output of 
? 2525
That's just a really complicated way to not explain anything at all...

Ad.1. I got a response on a case I opened on a related matter and it appears that WF server 7704M sometimes optimizes away relevant join conditions, where 7705M does not. I suspect that might be related, as it could make the join non-unique.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
j.gross
Virtuoso
posted Hide Post
quote:
It is a UNIQUE join however, since we constrain the (conditional) join on a single constant value at the right side, completing the PK there. Apparently WebFOCUS does not realise that.


Welcome to the club. I encountered the identical situation about a year ago at a former client. I tilted with the CSS windmill long and hard until they finally comprehended it and admitted it's a defect.

I wound up, as a temporary work-around, creating an alternate synonym that omitted the component from the declared primary key (rearranged the FIELDNAME lines and reduced KEYS= by one), so that, with no change to the JOIN declaration, WF believed me when I asserted it's a unique join. Basically "Who cares what WebFOCUS thinks so long as it generates the correct SQL." But make sure your workaround is documented well.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Wep5622
Virtuoso
posted Hide Post
Good to know I'm not the first one running into this issue. I suppose anyone reporting on an EAV-modelled database will run into this at some point.

The trick with the access-file makes sense, I'll keep that in mind.

I ended up replacing the table-request with pass-thru SQL, as I was running into several other issues with these queries.

I already mentioned the optimizer bug, but another issue was that Oracle (11g) did something peculiar to a comparison between a TIMESTAMP WITH LOCAL TIME ZONE field and a constant datetime value: for some reason it decided to convert both sides of the equation to UTC, while it is of course rather more efficient to just convert the constant to the correct time zone. Doing that explicitly by casting the constant value to the correct type fixed that.

For the archives, an example of a (SQL) conversion of such a constant datetime value (in this example '24 hours ago'):
CAST(CURRENT_TIMESTAMP - INTERVAL '24' HOUR AS TIMESTAMP(0) WITH LOCAL TIME ZONE)


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 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     [WORKAROUND] Disable query "optimizer"?

Copyright © 1996-2020 Information Builders