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] No optimization due to no indexes in views

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] No optimization due to no indexes in views
 Login/Join
 
Silver Member
posted
I am working with a Teradata data warehouse. I am only allowed to access views that have been created by the DBAs. When I join these views, as I invariably must, optimization gets disabled because WebFOCUS cannot find keys or indexes to use to create its queries:

09.52.40 BT (FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
09.52.40 BT (FOC2505) A SEGMENT IN THE STRUCTURE IS NON-KEYED: T_TDPR_MDSE_ITE
09.52.40 BT (FOC2517) FST. OR LST. WHERE SORT FIELDS DO NOT COVER KEY
09.52.40 BT (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
09.52.40 BT (FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED

The synonym editor will show a KEYS=(non-zero value), occasionally, but these views will often have many foreign key fields that connect them to other views. Often, there might not be a primary key involved.

When I create synonyms for DB2 data sources (they are, almost always, tables), the .acx files will have very detailed information about the indexes. An example:

INDEX_NAME=IPUDC141, INDEX_UNIQUE=Y,
INDEX_COLUMN=CHLD_REQ_I/DEPT_I/CLAS_I/ITEM_I/FROM_LOC_I/TO_LOC_I,
INDEX_ORDER=ASC/ASC/ASC/ASC/ASC/ASC, $

In the past, I have “manufactured” a set of Key fields by moving a unique identifier to the top of the field list. Is there any technical reason not to do this? (could it cause WebFOCUS to create bad queries?)

Beyond that, can I manually specify a non-key “index” for a view, to “help” the WebFOCUS optimizer create proper and valid joins? If so, what would that look like? The example above specifies a real index, which I couldn’t do with a view.

Thanks!

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


Jeff
WebFOCUS 8.0.09, Unix-Win-z/OS
FOCUS 7.3.1 on z/OS
 
Posts: 34 | Location: Minneapolis, MN | Registered: June 10, 2003Report This Post
Guru
posted Hide Post
Hello Anonoymouse,

The following links are from the IBI TechSupport Knowledge base which contains information related to Teradata (and other RDBMSs) and JOINs


Optimization Settings


Aggregate Awareness in an RDBMS

As far as your question:
In the past, I have “manufactured” a set of Key fields by moving a unique identifier to the top of the field list. Is there any technical reason not to do this? (could it cause WebFOCUS to create bad queries?)

Are you referring to the basic master file description (synonym)? Then the fields in here should not be moved since the field order reflects the layout of the data record.

You may find additional help with searching the Form as well or on the IBI TechSupport Knowledge Base.

Here is the link to the Teradata Adapter . You will find an addition link to Optimization Settings.

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator - Information Builders Inc.
* Summit 2016 – June


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report This Post
Guru
posted Hide Post
Hi Anonymouse,

Here is an update from the product manager :

You can "move fields in the MFD and manufacture indexes" -this has been done successfully. If you know that a particular field in a view that is actually a key index column in the underlying table, you can move that field to the top of the MFD and then set KEYS=1 (or whatever) in the ACX file.

The downside to doing this is that if you run a CREATE SYNONYM statement on the source it will over-write any changes you have made in the MFD & ACX files.

Also - I believe that a new feature is being added into the next server release that will allow you to explicitly name the key fields - so you would not even have to move the fields around in the MFD. The same caveat about CREATE SYNONYM still applies here since that will wipe out any changes made to the ACX file.

I hope the above provides you with the information you were looking for to answer your question about moving fields around within the MFD.

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator - Information Builders Inc.
* Summit 2016 – June


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report This Post
Silver Member
posted Hide Post
Tamra,
Thanks for your replies! RE-yesterday's reply, I had looked at the Teradata adapter references, but didn't find what I was looking for, so I posted to the Forum. My understanding of the field order in a synonym for a RDBMS data source is that it shouldn't matter, since the adapter uses the alias attribute to identify the field. The optimization settings text confirmed my basic understanding of what WebFOCUS describes as "optimization". The big disconnect was that WebFOCUS generated synonyms with KEYS=0 for a RDBMS view (at least, in Teradata), so it couldn't find a way to optimize, and resorted to managing joins itself, which took a lot of time (and, probably a lot of disk space, too).


Today's info from the product manager helps a ton! It confirms that I can, at least, define a likely join path (via a made-up "key"), that WebFOCUS can use to send a single query to the DBMS. And, it would be great to have the ability to tell WebFOCUS that there are non-key indexes available for use in joining. I just have to remember to save a backup of my customizations, in case somebody comes along and blows them away... (maybe the synonym creator could recognize attributes like TITLE and DESCRIPTION, and carry them forward to the newly-generated MFD/ACX files?)

Thanks again.


Jeff
WebFOCUS 8.0.09, Unix-Win-z/OS
FOCUS 7.3.1 on z/OS
 
Posts: 34 | Location: Minneapolis, MN | Registered: June 10, 2003Report This Post
Platinum Member
posted Hide Post
Hey Anonymouse -

Looking at the warning messages FOCUS is sending back, it appears that you are doing either a FST. or an LST. in your actual query. FST. and LST. do not easily translate into SQL. If possible, recommend that you use a MIN. or a MAX.
 
Posts: 164 | Registered: March 26, 2003Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by EricH:
Hey Anonymouse -

Looking at the warning messages FOCUS is sending back, it appears that you are doing either a FST. or an LST. in your actual query. FST. and LST. do not easily translate into SQL. If possible, recommend that you use a MIN. or a MAX.

Good spotting, Eric! I noticed that, too, and it confused the heck out of me, because I had no such code in the fex! I'm thinking that the adapter might have generated one or more of them as it tried to join entities that had no keys. Now that I've added key fields to the MFDs, I've gone from three queries to one. No more question marks! Thanks for your interest and willingness to help.


Jeff
WebFOCUS 8.0.09, Unix-Win-z/OS
FOCUS 7.3.1 on z/OS
 
Posts: 34 | Location: Minneapolis, MN | Registered: June 10, 2003Report This Post
Master
posted Hide Post
Anonymouse,

Also try setting SHORTPATH=SQL. This will force WF to translate to SQL even though there aren't any indexes. Since the adapter doesn't actually care about indexes, this works well. I have this set in my environment at the Reporting server. But you can do it individually by fex or in the site_profile if you prefer.

Also as for the FST. / LST., If you have a non numeric field in the SUM verb without it being in a BY Field, WF automatically attributes either FST. or LST. (I Can't remember which one). So when going against SQL databases, just like with SQL, you need to make sure there is an operator on all non-numeric fields outside of your group by. I usually use Max, but just make sure that your BY fields are written in a way where there can only be 1 value returned. That is unless you really want the Max value of course.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Virtuoso
posted Hide Post
I'm not sure what this depends on, so can't tell whether your 8.0.0.9 server or your Teradata support this, but in our 8.1.03 SAP access files we've witnessed a new way of defining which fields make up keys.

For example:
 SEGNAME=BKPF,
   TABLENAME=EDADBA.bkpf,
   CONNECTION=INDIA,
   KEY=BKPF_MANDT/BKPF_BUKRS/BKPF_BELNR/BKPF_GJAHR, $


Since you're using views created from JOINs, it wouldn't surprise me if it were possible to create your masters with multiple segments (one for each table in the underlying join), with an index on each segment. Now if I only could find an example of that...


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
Silver Member
posted Hide Post
Eric,
Thanks for the suggestion. I'll keep the SHORTPATH=SQL option in mind the next time I run into a view with KEYS=0.

Also, you are correct about there being non-numeric data getting SUMmed in my fex. Of course that's where the FST./LST. came from! Just me being lazy, since all the non-numeric values in each sort group are the same.

Wep5622,
I have seen that syntax in the access files of synonyms for DB2 tables. Since the entities I'm working with are views, not tables, and the views are created inside Teradata, FOCUS only sees what Teradata allows it to see; in my case, a single segment with no index information provided. While the code you show might be helpful in that I might not have to manually move fields within the master file, it would still probably get wiped out if the synonym were re-created. But, if you can find an example of a synonym describing a view that has this, I'd be interested...

Thanks to you both for your suggestions.


Jeff
WebFOCUS 8.0.09, Unix-Win-z/OS
FOCUS 7.3.1 on z/OS
 
Posts: 34 | Location: Minneapolis, MN | Registered: June 10, 2003Report 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] No optimization due to no indexes in views

Copyright © 1996-2020 Information Builders