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,

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

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

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.

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.

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.

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.

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...

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.