We are long-time WebFOCUS users. We have never put indexes on any of our focus hold files. We recently migrated from WF 5.3.x to WF 7.6.5 and started having problems with a few of our programs that join back to a hold file. For example:

DEFINE FILE TBRMEMO
CURR_MEMO/P9.2 = IF (TBRMEMO_DETAIL_CODE EQ 'BOOK') THEN TBRMEMO_AMOUNT ELSE 0;
CURR_OVER/P9.2 = IF (TBRMEMO_DETAIL_CODE EQ 'BKSV') THEN TBRMEMO_AMOUNT ELSE 0;
END
-*
TABLE FILE TBRMEMO
SUM CURR_MEMO CURR_OVER TBRMEMO_DETAIL_CODE
WHERE TBRMEMO_TERM_CODE EQ '&TERM'
WHERE TBRMEMO_DETAIL_CODE EQ 'BOOK' OR 'BKSV'
IF TBRMEMO_EXPIRATION_DATE GT &CURRDT
BY TBRMEMO_PIDM
ON TABLE HOLD AS TEMP14 FORMAT FOCUS INDEX TBRMEMO_PIDM
END
-*
JOIN CLEAR *
JOIN SPRIDEN_PIDM IN TEMP13 TO TBRMEMO_PIDM IN TEMP14 AS J9

In this example we added the FORMAT FOCUS INDEX and this resolved our problem. TBRMEMO is an Oracle table.
My question is – why are most of our programs working perfectly fine without an index under this same scenario? I’ve tested quite a few and get the same result AFTER adding the index. We have thousands of programs and are now faced with the task of reviewing each one. And if changes are made we’ll also need to test it.

Any insight into this is greatly appreciated.

You did the same thing we did, made a double release jump. There was lots of code tightening in the 7.x releases.

I'm not sure why your old code is working. The requirement for joining to a fixed-format hold file (not a FOCUS file) is that the data in the source be sorted in the same order as the data in the target, in this case the hold file. That has been true for a long time.

Could the Oracle table not be in order by TBRMEMO_PIDM?

Over and above all of that, joining Oracle to non-Oracle is a heterogenious join. The join is done by WebFOCUS. This is not considered a best practice. Depending on how much data you have in your sources, the performance can be very bad. An option to use would be WHERE IN FILE to select the data out of the Oracle table, hold it and then join to the other file.

DW,

I have been using Focus and WebFocus since 1983 and I have never seen a JOIN to a FOCUS file that doesn't have an index. But, in the world of FOCUS I have seen stranger things. So I ran the following:

  
TABLE FILE CAR
WRITE FST.WARRANTY
BY COUNTRY
ON TABLE HOLD AS CW FORMAT FOCUS
END
TABLE FILE CAR
WRITE FST.STANDARD
BY COUNTRY
ON TABLE HOLD AS CS FORMAT FOCUS
END
JOIN COUNTRY IN CW TO COUNTRY IN CS AS C_
TABLE FILE CW
PRINT *
END
? 238

And I got the following output:

  
 0 NUMBER OF RECORDS IN TABLE=        8  LINES=      5
 0 NUMBER OF RECORDS IN TABLE=       25  LINES=      5
 (FOC238) LINKED FIELD IS NOT INDEXED: COUNTRY
 BYPASSING TO END OF COMMAND
 0(FOC238) LINKED FIELD IS NOT INDEXED:
 The cross-referenced field in the cross-referenced file does not
 have FIELDTYPE=I.  Or, if the segment has been re-described in the
 referencing file the FIELDTYPE=I attribute is missing.

I even ran the same in DOS PC/FOCUS 6.01 (Yes I still have a copy!) and got the same reaction.

Could you run my code in 5.3? I really would like to see what you get.

Daniel is totally correct.
You have never been allowed to join to a non-indexed field in a Focus DB.

If it worked then it was a serious bug.

In the original post, he added the FORMAT FOCUS INDEX syntax. Prior to that, it was a fixed format hold file. The hold file must be in the same order as the data you are joining to it. It may be that the sort order of the source changed.

It may reflect the nature of the source file in the join (TEMP13), and the form of its MFD. One thing that changed not too long ago was that Focus pays more attention to the declared order of flat files.

Suppose TEMP13 is a flat file (the post does not specify, but TEMP sounds like a temporary HOLD file), and the MFD does not specify that it is sorted on SPRIDEN_PIDM. (If TEMP13's master is in a library and dates from before, it may lack a SEGTYPE declaration, and Focus now assumes S0. If it is created from a correctly sorted source by TABLEF ... HOLD without coding BY SPRIDEN_PIDM, Focus 7 will generate S0.)

If so, perhaps Focus 7 rejects the Join because the (presumed or explicit) non-ordered nature of the source file does no match the ordering requirement of a Join to a flat file; whereas Focus 5 essentially ignored the SUFFIX and let the Join roll.

Nope. Playing with Danny's code I tried a FIX S0 to FIX S1 Join...

TABLE FILE CAR
WRITE FST.WARRANTY
BY COUNTRY
ON TABLE HOLD AS CW FORMAT FOCUS
END
TABLE FILE CAR
WRITE FST.STANDARD
BY COUNTRY
ON TABLE HOLD AS CS FORMAT FOCUS
END
JOIN COUNTRY IN CW TO COUNTRY IN CS AS C_
TABLE FILE CW
PRINT *
END

This succeeded in producing the report; so at least in the case of explicit S0 7.6.5 respects the Join.

Thanks for all of your responses - I do appreciate it. Danny - I tried your sample code and I am getting the (FOC238) LINKED FIELD IS NOT INDEXED error. This is the first time that I've ever seen this error message. We no longer have our 5.3 version of WebFOCUS so I can't test it there.

We did discover one thing that will resolve the problem - add a SET HOLDFORMAT = ALPHA. I'd like to understand why! Also, if I hold the file again in another HOLD file but use a PRINT statement instead of a SUM statement it works fine.

I'm working with IBI to try and determine why most of the programs that I test (with no index) are perfectly fine and just a few give incorrect results (with no error).

According to IBI ... "If these are regular HOLD files -- then no INDEX option is needed.
As far the JOIN is concerned, the only rule is that the JOIN field have a common format and length, and that both HOLF filed be sorted in ascending order based on the JOIN-TO field.
"

Thanks again for answering.

quote:

JOIN SPRIDEN_PIDM IN TEMP13 TO TBRMEMO_PIDM IN TEMP14 AS J9

The original post doesn't talk about error messages, just "problems" with the report -- so I take it that the subsequent TABLE using the join just failed to pick up data from TEMP14 for some rows.

I'd say the prime suspect here is whether the underlying retrieval from Oracle is presenting rows in key order. Could be something changed with the upgrade (fresh synonyms, or the orignals operating under the new release's adapter, or something coincidental on the database side) to cause Oracle to return rows in physical-sweep order, rather than primary key order. If so, that would lead to the observed symptoms.

Run a TABLEF request to see whether Oracle presents the rows in order of SPRIDEN_PIDM:

TABLEF FILE TEMP13 
  PRINT SPRIDEN_PIDM
  AND COMPUTE ?/A1=IF SPRIDEN_PIDM LT LAST SPRIDEN_PIDM THEN '?' ELSE ' '; AS ' '
END

If any ?'s show up, there's your culprit.

If that's the issue, one solution is to report from a sorted View. That may entail a performance hit -- but it might lead Oracle to retrieve via the index to avoid a sort step, so the performance hit may be negligible.

What does the generated SQL look like? Apparently the sort is not being passed through.

The setting of AUTOTABLEF might influence whether TABLE explicitly requests Oracle to sort the rows.

After some additional thought...

A flat-file to flat-file join is basically a merge of two presorted sources. The merge succeeds -- regardless of the sort order of the report -- because Focus is constrained to scan through each file sequentially.

But joining an Oracle table (or any server-based data source) to a flat file is perilous at best: It’s still a merge operation, rather than a random lookup, because of the sequential nature of the flat file -- so the efficacy of the join depends on the order in which rows will be returned to Focus by the remote server, which in turn depends on several factors:

* Whether the TABLE request specifies sorting; and if so, whether the sort order of the report is the join key or something else (none of that matters in a true flat-to-flat join)

* If the report is sorted, whether Focus “passes through” the ordering in the SELECT request it issues (for better or for worse), or leaves the order of unspecified and assumes responsibility for sorting.

* When Focus does not specify ordering in the SELECT, what is the order of rows returned by the server (and is it predictable)

So, to address your inventory of “thousands of programs” you will need to classify the report programs involving JOINs, based on the nature of the join and the subsequent TABLE request. Here’s my attempt at taxonomy:

A. The first file in the JOIN is a flat file (plain HOLD or HOLD ALPHA): No problem.

If the first file in the JOIN is an Oracle table: run the report with a SQL statement trace to see what ordering Focus includes in the SELECT.

B. The (primary) ordering in the SELECT is the JOIN key: No problem (The rows of the answer set from Oracle will merge properly with the rows of the HOLD file)

C. The primary ordering is something else: Unlikely (why would it have worked under rel 5?), and definitely a candidate for conversion to an Index-based join (change HOLD to HOLD FORMAT FOCUS INDEX in forming the second file in the JOIN)

D. No ordering specified in SELECT, and Oracle table’s primary key is the join key: That places you at the mercy of Oracle’s optimizer. If you can determine that Oracle returns rows in primary key order, then no repair is needed -- but review with DBA to verify that the behavior is reliable. Otherwise: If the report sorts on the join key, you can try to revise the program (or global SETtings) to induce Focus to pass through the sort requirement, or else convert to an Index-based join. If the report sorts on something else, no choice but to convert to an Index-based join.

E. No ordering specified in SELECT, and Oracle table’s primary key is not the join key. Unlikely (why would it have worked under rel 5?). If the report sorts on the join key, try to revise to ensure Focus passes through the sort requirement, or else convert to Index-based join. If the report sorts on something else, no choice but to convert to an Index-based join.

Let us know how it goes.

We worked with IBI and determined that an index is not needed for our hold files. The final solution was to change our setting for HOLDFORMAT to ALPHA instead of using the BINARY default. The problem was basically a rounding issue. There were significant changes from the version 5 to version 7 release including the use of a different compiler by IBI. Changing the global setting to HOLDFORMAT=ALPHA should not impact our performance. Thanks to all.