We currently have a FEX that joins some master files to get at a dataset. When you look at the query that WebFocus builds it is dead on. When you pull it out and run that in Toad/SQL Plus/etc. the result set is also 100% correct.

The issue is when WebFocus runs that query and returns results it only puts some of them into the HOLD file which breaks the processes downstream for that report.

How is it that WebFocus can create the correct query but not store all of the rows? The kicker is it works sometimes and not for others given the exact same parameters for the report.

Has anyone else seen this? Am I missing something simple? FYI we are on WebFocus 5.3

- john

John

Unless you are holding then appending or using APP HOLD to create the file in an application directory then I think its the downstream processing that might be the culprit.

After you do
ON TABLE HOLD
END

Then
-RUN
JOIN CLEAR *
TABLE FILE HOLD
PRINT *
END

If you get different results then, I think you genuinely have a problem with holds otherwise its a short path join or unexpected where test that is causing the shortfall.

Regards

John

Are you doing a SUM or a PRINT? If you're doing a SUM based on a DEFINEd field, then the generated SQL will most likely retrieve more rows than will be output.

We are doing some SUM and Grouping in the query. The SQL that is built is identical to what we want and run outside of Webfocus returns all of the right rows (data quality and counts).

If we take the statment and replace it with one that just displays the rows to the screen it still only displays some of the data that the result set contains. However, there is no further groupings that take place so it should be a 1-to-1 match for rowcounts.

Hammo1j, if we do that basic code and print it out then it is still incorrect. It seems to be either lying to me about what query it is running or some additional processing is happening on the way to the hold file that I can't see.

We have a few people here just absolutely stumped. I am just glad this isn't a financial report because the problem appears to show itself (the classic) "sometimes" and not always.

Have you also turned SQL messages on to see if there's any messages from the SQL translator?

This may help debug the problem?

-*-- Activate tracing --------------------------------------
SET TRACEOFF = ALL
SET TRACEON = SQLTRANS
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
-RUN

John

I think I was going on the wrong tack on this one.

I think I have had similar probs and this was due to the nature of wf and the database sharing the processing between them.

Sometimes, in particular with missing data, wf will try to double check a result set returned from an RDB. Thus it passes IS NULL to the database and checks for -9998998 (wf internal representation of null) itself.

ENGINE SQLORA SET OPTIFTHENELSE OFF

Might cure your probs.

I'm guessing but is your code something like:

CAT_COST/P12.2 MISSING ON = IF CAT EQ 'CAT1' THEN COST ELSE MISSING ;

A work around would be to just SQL PASSTHRU the code, but you justs gotta know what's going on.

Can you post your code or something sim. so we can take a look?

Regards

John

Francis - I will go over and get the exact code. We have some tracing turned on but some of the commands in your post I have never used.

Hammo1j - I'll have to look into this further but when I checked the adapter in our QC vs. Prod environment ENGINE SQLORA SET OPTIFTHENELSE OFF
was we to OFF already and both adapters were consistent.

Did I mention that this works in our Local/Test/QC environments but just not in prod.

Johnny5, I don't think you mentioned that this works correctly in your local, test and QC environment. Are you pointing to the same database as the prod environment?

If not, could there be some database server setting that's different?

Local - Usually will point to a Dev DB (boxA)
Dev - Points to Dev DB (boxA)
Test - Poinsts to Test DB (boxA)
QC - Points to a Test DB (boxA)
Prod - Points to a Prod DB (boxB)

I don't get it though, the SQL that WF tells me it is going to run returns the right answer on either of the DB's on either box.

Very odd.

I too, at this very moment, am experiencing something weird as well.

When run in a DB2 client, the SQL generated by WebFOCUS retrieves 3525 rows with client names from 'A' to "Z'.

When running in WebFOCUS, the request gives me this as a result

quote:

"NUMBER OF RECORDS IN TABLE= 7050 LINES= 0"

but there are actually 6228 rows, from 'A' to 'S'.

This has never happened before - I can't explain it!!!

I hope it isn't contagious. I had the lead developer put those other trace statements in and there wasn't much else discovered.

We do get a statement saying that Summarization could not occur and then it takes 1011 rows which it should stick into the hold file and does something else to get it down to 259 rows.

We are still digging through it. I am somewhat concerned with the rest of the production reports now that I see this.

The problem I have is occuring since I added a join. I remove the join and all reference to columns in the joined table and the results are what I expect them to be. I get no warning or error messages with or without the join, but the results are completly wrong when the join is in place.

I will try to join the tables in a different sequence. You may wish to try that as well.

Now I'm even more confused. I thought that because of the "could not aggregate" warning that my .ACX files may be pointing to an incorrect database.

After going to the production console and doing an "Edit Access File as Text" the whole thing starts working as if by magic. (I didn't actually have to change any of them)
Does this even sound like it could be related? This isn't magic in my book and seems more like black magic.

I believe I ended up doing this as well, now that I think about it, in Test/QC where the reports were running correctly. Does the file structure change or something just because you look at it??

Can't explain that!

Nah, Magic is a completely different programming language!

T

One thing this could be was somebody changing the APP PATH in EDASPROF eg correcting a typo.

I had this happen and was extremely perplexed for a time. It does cause you to suspend belief in rational explanations!

John
it could be where your HOLD file is going.
To be absolutely sure you aren't reading some errant HOLD file somewhere else on your path, give your file a unique name when you hold it.
ON TABLE HOLD AS H12345 or something.
Under normal circumstances, the HOLD file in your current running agent is first on your path, but sometimes, you might be reading some other HOLD file or some other HOLD.MAS , maybe your have some TEMPDIR set, or maybe just for unfathomable (but always totally logical )reasons. That's the sort of thing that clears up by "magic". So in a production job, i never use HOLD , i always use HOLD AS something.. In case something breaks and i would be reading the previously-made HOLD. Make sense?

-*These set of joins get the count of answer weight for each answer code
 JOIN
 SRV_ATTRIBUTE_INFORMATION.SRV_ATTRIBUTE_INFORMATION.RESPONDENT_ID IN
 SRV_ATTRIBUTE_INFORMATION TO ALL SRV_FACTS_HTML.SRV_FACTS_HTML.RESPONDENT_ID
 IN SRV_FACTS_HTML AS J3
 END
 JOIN
 SRV_FACTS_HTML.SRV_FACTS_HTML.QUESTION_ID
 AND SRV_FACTS_HTML.SRV_FACTS_HTML.ANSWER_WEIGHT IN SRV_ATTRIBUTE_INFORMATION
 TO ALL SRV_ANSWER_MAP.SRV_ANSWER_MAP.QUESTION_ID
 AND SRV_ANSWER_MAP.SRV_ANSWER_MAP.ANSWER_NBR IN SRV_ANSWER_MAP AS J4
 END
 JOIN
 SRV_ANSWER_MAP.SRV_ANSWER_MAP.QUESTION_CALC_ID IN SRV_ATTRIBUTE_INFORMATION TO
 SRV_QUESTION_CALC_MAP.SRV_QUESTION_CALC_MAP.QUESTION_CALC_ID
 IN SRV_QUESTION_CALC_MAP AS J5
 END


FILEDEF DATAHLD DISK datahld.ftm (append
 DEFINE FILE SRV_ATTRIBUTE_INFORMATION
 COMBINED/A10='Combined';
 ALL_XNAC/A10='ALL';
 DUMMY/A1='';
 END
 TABLE FILE SRV_ATTRIBUTE_INFORMATION
 SUM
 CNT.ANSWER_WEIGHT AS 'QUESTION_CNT'
 BY RESPONDENT_ID
 BY SRV_QUESTION_CALC_MAP.QUESTION_DISPLAY_SHORT
 BY QUESTION_ID
 BY SVC_NATION_NAME
 BY SVC_NATION_ID
 BY ALL_XNAC
 BY ALL_XNAC
 BY SVC_NATION_NAME
 BY SVC_NATION_ID
 BY EFFECTIVE_DATE
 BY ANSWER_GROUP_CD
 -SET &frMDYY = EDIT(20061001,'$$$$9999')|EDIT(20061001,'9999');
 -SET &toMDYY = EDIT(20061031,'$$$$9999')|EDIT(20061031,'9999');
 WHERE EFFECTIVE_DAY_DATE GE '10012006';
 WHERE EFFECTIVE_DAY_DATE LE '10312006';
 -*FILTER ON SURVEY ID
 WHERE SURVEY_ID EQ 1;
 WHERE REPORT_ID EQ 223;
 WHERE SVC_NATION_ID IN ( 10183)
 WHERE QUESTION_CALC_ID IN ( 667)
 ON TABLE HOLD AS DATAHLD
 END
 -RUN
 12.44.53 BR  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 12.44.53 BR  (FOC2610) CANNOT AGGREGATE BY CONSTANT SORT KEY  : ALL_XNAC
 12.44.53 AE    SELECT T1."SURVEY_ID",T1."RESPONDENT_ID",T1."EFFECTIVE_DATE",
 12.44.53 AE   T1."EFFECTIVE_DAY_DATE",T1."SVC_NATION_ID",T1."SVC_NATION_NAME",
 12.44.53 AE   T2."QUESTION_ID",T2."ANSWER_WEIGHT",T3."QUESTION_CALC_ID",
 12.44.53 AE   T3."ANSWER_GROUP_CD",T4."REPORT_ID",T4."QUESTION_DISPLAY_SHORT"
 12.44.53 AE   FROM SRVADMIN.SRV_ATTRIBUTE_INFORMATION T1,
 12.44.53 AE   SRVADMIN.SRV_FACTS_HTML T2,SRVADMIN.SRV_ANSWER_MAP T3,
 12.44.53 AE   SRVADMIN.SRV_REPORT_QUESTION_MAP_VW T4 WHERE
 12.44.53 AE   (T2."RESPONDENT_ID" = T1."RESPONDENT_ID") AND (T3."QUESTION_ID"
 12.44.53 AE   = T2."QUESTION_ID") AND (T3."ANSWER_NBR" = T2."ANSWER_WEIGHT")
 12.44.53 AE   AND (T4."QUESTION_ID" = T3."QUESTION_CALC_ID") AND
 12.44.53 AE   (T1."SVC_NATION_ID" = 10183) AND (T1."SURVEY_ID" = 1) AND
 12.44.53 AE   (T1."EFFECTIVE_DAY_DATE" BETWEEN TO_DATE('01-10-2006',
 12.44.53 AE   'DD-MM-YYYY') AND TO_DATE('31-10-2006','DD-MM-YYYY')) AND
 12.44.53 AE   (T3."QUESTION_CALC_ID" = 667) AND (T4."REPORT_ID" = 223);
 0 NUMBER OF RECORDS IN TABLE=      325  LINES=     76
  

This is the piece that is failing. If you run that query in Toad/SQL*Plus etc it returns 419 rows. This query run through WebFocus to the DB apparently returns less. The fact that the aggregation isn't done on the query is what we expect also.

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

It is very important to show us your JOINs as well.

The only thing I can think of is that its to do with there being oracle dates 31-OCT-2005 10:00:00 which will fail the wf test and that the toad code is not exactly the same and subtlely takes this factor into account by issuing

trunc(T1."EFFECTIVE_DAY_DATE") BETWEEN TO_DATE('01-10-2006','DD-MM-YYYY') AND TO_DATE('31-10-
2006','DD-MM-YYYY'))

or

T1."EFFECTIVE_DAY_DATE" BETWEEN TO_DATE('01-10-2006','DD-MM-YYYY') AND TO_DATE('31-10-2006 23:59:59','DD-MM-YYYY hh:mm:ss'))

My hunch is it something to do with the dates. Have you compared answer sets to find the difference?

Regards and have a good w/e!!

I have included the joins in the above post.

The query that I run in Toad is the one generated from WebFocus. That query does work fine against the database. It seems that when WebFocus runs that query something bizzare happens sometimes. I don't see how the dates are causing anything at this point but I guess I can't be 100% sure on that.

I don't think this will make any difference, but I would change the DEFINEs to COMPUTEs and not sort on them.

DEFINE FILE SRV_ATTRIBUTE_INFORMATION
 COMBINED/A10='Combined';
 ALL_XNAC/A10='ALL';
 DUMMY/A1='';
END

Change to

TABLE FILE SRV_ATTRIBUTE_INFORMATION
SUM
...
COMPUTE COMBINED/A10='Combined';
COMPUTE ALL_XNAC/A10='ALL';
COMPUTE DUMMY/A1='';
BY ...

This will resolve the aggregation message and perhaps something else will pop up.

Francis,

Along your idea of something else may pop up, One of the trusted methods that has saved me since I began programming is to compare the records, that are not falling through one method, and review why these records passed the other test. This should highlight the difference.

Do you get a different number of rows extracted if you PRINT vs SUM?

Actually the counts have to be the same. We are returning the rows at the lowest level so there really isn't anything to SUM. It may as well say PRINT in the focexec.

We have run the SQL generated (via the tracing) and when run as its own procedure it works every time and returns all the rows requested.

According to my ticket contact with IBI there are some FOCSORT warnings in the actual trace file.

It still is a coin toss every time you hit run it will either work or not work which makes this tougher.

John,

Without wishing to sound repetitious, why are you not using SQL passthru for this? The overhead on the FOCSORT will be reduced as the majority of the sort can be attained by the RDBMS.

SQL
  SELECT T1.RESPONDENT_ID
       , T4.QUESTION_DISPLAY_SHORT
       , T2.QUESTION_ID
       , T1.SVC_NATION_NAME
       , T1.SVC_NATION_ID
       , 'ALL       ' AS ALL_XNAC
       , T1.EFFECTIVE_DATE
       , T3.ANSWER_GROUP_CD
       , CNT(T2.ANSWER_WEIGHT) AS QUESTION_CNT
    FROM SRVADMIN.SRV_ATTRIBUTE_INFORMATION T1
       , SRVADMIN.SRV_FACTS_HTML T2
       , SRVADMIN.SRV_ANSWER_MAP T3
       , SRVADMIN.SRV_REPORT_QUESTION_MAP_VW T4
   WHERE T2.RESPONDENT_ID     = T1.RESPONDENT_ID
     AND T3.QUESTION_ID       = T2.QUESTION_ID
     AND T3.ANSWER_NBR        = T2.ANSWER_WEIGHT
     AND T4.QUESTION_ID       = T3.QUESTION_CALC_ID
     AND T1.SVC_NATION_ID    IN (10183)
     AND T1.SURVEY_ID         = 1
     AND T1.EFFECTIVE_DAY_DATE BETWEEN TO_DATE('01-10-2006','DD-MM-YYYY')
                                   AND TO_DATE('31-10-2006','DD-MM-YYYY')
     AND T3.QUESTION_CALC_ID IN (667)
     AND T4.REPORT_ID         = 223
GROUP BY T1.RESPONDENT_ID
       , T4.QUESTION_DISPLAY_SHORT
       , T2.QUESTION_ID
       , T1.SVC_NATION_NAME
       , T1.SVC_NATION_ID
       , T1.EFFECTIVE_DATE
       , T3.ANSWER_GROUP_CD
ORDER BY T1.RESPONDENT_ID
       , T4.QUESTION_DISPLAY_SHORT
       , T2.QUESTION_ID
       , T1.SVC_NATION_NAME
       , T1.SVC_NATION_ID
       , T1.EFFECTIVE_DATE
       , T3.ANSWER_GROUP_CD
;
TABLEF FILE SQLOUT
PRINT *
END

T

Tony,

Are you suggesting that by using the SQL generated by the SQL translator instead of the FOCUS code, one could make the program more efficient?

If you can get the RDBMS to do the aggregation and sorting of the recordset you want then yes.

One of the main items I try to get over to my Clients when they are learning WebFOCUS is get the right product to do the work that it's best at and handle the minimum number of records in WebFOCUS.

Because the CNT.ANSWER_WEIGHT AS 'QUESTION_CNT' in John's TABLE request is not being aggregated, all of the fields from the joined tables that are required to perform the aggregation are being passed back to FOCUS. These then have to be sorted and aggregated there. If the average value for the count is, say, 500 then you are handling 500 times the amount of records in FOCUS than you really require in the end HOLD file.

In John's situation the numbers might appear to be relatively small (419 against possible 76) but I am guessing that this is a small piece of the overall puzzle and is combining with the other TABLE requests to give rise to FOCSORT problems.

Bottom line - if you can get the RDBMS to provide the exact recordset that you need then use SQL passthru.

If one has a problem determining what the SQL should be, then use Francis' tip and retrieve the SQL by using a trace, but use IF RECORDLIMIT EQ 1 as you only want the SQL and not the recordset on this occasion. Examine the SQL and build your SQL passthru from it.

You will notice that this is what I achieved in the above posting. BUT I added GROUP BY and ORDER BY clauses to minimise the recordset returned to WebFOCUS.

T

One other thing on SQL passtrhu, if you are going to add DEFINEs to the recordset once it is in WebFOCUS then help yourself further by getting the RDBMS to add the columns for you.

You will notice that I added

 , 'ALL       ' AS ALL_XNAC

in the SQL so that it is already contained within the recordset being returned and therefore negates the need for the DEFINE FILE.

T

And how does the GUI help in all this, hmmm?