Once again the evil webfocus error 6000 is cropping up. I still think it has something to do with the number of rows returned or perhaps some sort of timeout. I am hoping someone can help...though last time I gave up.

This time, if I can’t get it working I have to switch to Tableau or simply run sql queries to txt file. Booo!

The idea is to use Webfocus to extract a subset of data from a large (5M= rows) sql table into a FOCUS file (less than 1M rows) and finally report (via Excel) off the focus file. Essentially we are creating a data dump.

It works well except when until extractions that approach somewhere around 500K rows. I suspect something times out ---not sure. I just know the Error 6000 pops up.

WF 8008
Reading SQL Database
Threshold of problem seems to be about 500K rows
No Joins – trying to create a focus file which is a subset of the SQL table that contains 5M+ rows
SET &ECHO = ALL shows no errors....it aborts with WEBFOCUS Error (6000) Unregistered Error.
No Computes or Defines.
No Groupings.
No summations.
Report format FOCUS.
User criteria via drop down boxes to select out a subset of data.

This message has been edited. Last edited by: FP Mod Chuck,

Does the request work with the HOLD line commented out? Will the report run error free to your browser?
What happens if you HOLD FORMAT BINARY instead of FOCUS?
In the ECHO, do you see HOLDING... and the number of Records: ?
Can you generate the SQL trace and execute the SQL within the DBMS?
Do you have enough disk space for the resulting hold file?

Also you might test your code with these two lines in the beginning to see if it makes it past the errors:

  
SET WARNING = OFF

SET EMGSRV=OFF

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

Robert

Another thing to consider is that the maximum size of a focus database is 2GB so you may be exceeding that. I agree with Babak's suggestion to hold format binary and see if the issue goes away.

Thanks all..here is what I have found:

First I was unaware of the 2G limit.

We are on: WF 8008.

When I change the Report Type From FOCUS to HTML OR if I comment out the ON TABLE statement altogether and run a small data set, it runs fine..data pops up.


When I change the Report Type From FOCUS to HTML OR if I comment out the ON TABLE statement altogether and run large data set it runs forever..never seems to finish. Perhaps the 2G limit is coming into effect...?


BUT....
If I change the command to:
ON TABLE HOLD AS HOLD_CPTDTL2 FORMAT BINARY

I can see in the ECHO that the say 1.4M rows are written!

However when I go to do a report off: HOLD_CPTDTL2 (new fex, Report, look for HOLD_CPTDTL2 in list, grab it, drag columns onto report, click run) I get an error:

0 ERROR AT OR NEAR LINE 12 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC226) FILEDEF OR ALLOC MISSING FOR EXTERNAL FILE: HOLD_CPTDTL2

I never knew there was a BINARY option – it does not appear in the GUI so I am a bit ignorant as to how/when to use it – perhaps I am doing something wrong? Looks promising except I can not seem to be able to tap it for a subsequent report....

It seems like you should start thinking in terms of ETL. In general, a reporting tool isn't meant to be used for staging large data sets. HOLD is a convenient way of capturing data for future processing but it's not a substitute for App Dev that requires data manipulation and staging. BTW, for what it's worth, you won't be able to do any of this in Tableau. Just saying

Confused..this is exactly what Webfocus is supposed to do--connect to a large data set, let users select a subset of data and report it. We do it all the time. It just so happens this table is large...it needs to be.

Can you elaborate on why the BINARY file type does not seem to allow reports against it? I thought I was home free---it loaded the binary table real fast---why can I not report off said table.

Tableau...I agree..not designed for this however I can sit it on top the sql data source it allows me to access the data---not an ideal solution for end user who wants a report - I AGREE.

You can't just HOLD it without placing it in a folder on your APP PATH, OR, into FOCCACHE...

ON TABLE HOLD AS foccache/HOLD_CPTDTL2 FORMAT BINARY
ON TABLE HOLD AS baseapp/HOLD_CPTDTL2 FORMAT BINARY
etc...

again.sorry .. never heard of the BINARY option. All our fexes have this syntax...I assume specifying foccache this is something special with BINARY files...?

This format is in most of our fexes; works fine:
ON TABLE HOLD AS MYHOLDDATA FORMAT FOCUS

Robert

foccache is allowing you to hold the file for the life of a user session so the result set is specific to a user. The binary output format is actually the default if all you do is say ON TABLE HOLD without the FORMAT component. If you are really staging the data for all users to share then you want to use the APP HOLD appfoldername command at the beginning of the fex that creates it. If you use BINARY then you will have to go back and modify the .mas file and add the DATASET attribute so the master file can find the actual file created. In my example I used APP HOLD baseapp...


FILENAME=MYHOLDDATA, SUFFIX=FIX , IOTYPE=BINARY, $
DATASET=baseapp/myholddata.ftm , $


I will look at this..

initial tests seem to indicate things are now working. I may want to look at the number of lines written to the binary file before attempting an excel export...but so far its now working....at least for files with 500K rows.

And then there is also the XFOCUS format that can hold up to 32GB.

The plus side on (X)FOCUS files is that you can put an index on fields, which AFAIK you can't do with BINARY (or alpha-based) formats. That can help a lot if you JOIN to these files.

I recall there is something that FOCUS can do that XFOCUS can't, but I can't remember what it was.

I was just going to suggest XFOCUS. We have an extract file we report from with data back to 2013. It is well over 2GB. I've never found anything that can be done in a FOCUS file that can't be done in an XFOCUS file and we use them a lot. On the other hand XFOCUS files can have multi-field indexes where FOCUS files can't. We have other applications where we build FOCUS files by State Fiscal and/or Federal Fiscal year with separate files for each year. Then if we need to use multiple years in one report, we just "concatenate" them using the USE command:

USE
  FFY2015.FOC AS FFYFILE
  FFY2016.FOC AS FFYFILE
  FFY2017.FOC AS FFYFILE
  FFY2018.FOC AS FFYFILE
  FFY2019.FOC AS FFYFILE
END

Now we can access all 5 years in one report. I will add that to do it right does take some planning but it is well worth it. Most of our on-demand reports that user run, run in less than a few seconds. It takes the client longer to assemble the report and download it than it does to run it.

Is XFOCUS still a licensed add-on?

My first reaction was to output to XFOCUS also.

Michael

Not positive but I don't think it is a licensed add-on because it shows up as a output format under database formats when creating a report

IBI may very well have given up on licensing XFOCUS. It’s been one of our Dev/App Studio output options for a long time, but in 2008 it was definitely in the price book as an add-on. You could use it, but you were supposed to license it. Truth be told, I am not sure how many people did (or do).