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     [SHARE] Need to run MR over a big file with 1,500,000 records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SHARE] Need to run MR over a big file with 1,500,000 records
 Login/Join
 
Member
posted
Hello All,

I need to create a Sales History Report. Data has been extracted on AS/400 and populated in a work file with 1,560,060 records. It took me almost an hour to run a report over the file, and finally I got an error saying "An HTTP 500 error has been returned while processing the web components....."

Is there a limitation on the size of a file in Webfocus to generate a report?

Any idea and help will be appreciated.

Guang
 
Posts: 29 | Registered: April 16, 2012Report This Post
Expert
posted Hide Post
HTTP 500 error, probably the browser timed out.

Have you tried running the report deferred ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
@Gaung

Like Waz says : it's the browser that gives up waiting after 30min. of no response from the server.

It's not a limitation. I did the same with over 2,500,000 records. Not recommendable, but it works.

Although we found that the issue was NOT trying to query the big HOLD file. Most time was spend creating ( and filling ) the HOLD file.

TABLE FILE CAR
  SUM SALES
  BY  CAR
ON TABLE HOLD AS H_ONE FORMAT FOCUS
END

TABLE FILE CAR
  SUM SALES
  BY  CAR
ON TABLE HOLD AS H_TWO
END


Turned out that H_ONE enables you to quicker access the data, but takes a lot more time to create in comparison with H_TWO.


And yes, try defered.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Member
posted Hide Post
Hi Waz and Dave,

What do you mean "Try defered"? Do you mean to run the report in batch mode from Report Caster?

Many thanks for help.

Guang
 
Posts: 29 | Registered: April 16, 2012Report This Post
Member
posted Hide Post
Hi Dave,

And also, I am not trying to create a HOLD file, but just need to pull the data and output to Excel.

I searched the HTTP status code 500 in Google. It says:
"Internal Error 500
The server encountered an unexpected condition which prevented it from fulfilling the request."

Does this mean that Webfocus server has a problem to fulfill the request?

Thanks,

Guang
 
Posts: 29 | Registered: April 16, 2012Report This Post
Master
posted Hide Post
Guang,

- Please look up "Defered" in the help.

- Error 500 just means the server didn't 'answer' the browser's call within a certain time-out period. After this time-out the browser just assumes something wrong with the server. In this case it means the server didn't finish querying before the browser got tired of waiting. That's why 'defered' might be an option because the browser isn't waiting.


G'luck
Dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Member
posted Hide Post
Hi Dave,

I tried running the report defered. For one month of data (58,100 records), I can save and view the report without any problems. But when I extract 3 months of data (179,084 records), I can not open the report. When I click on the View button, I got error:
FOC35804) BAD STATUS CODE (-17) RECEIVED FROM SERVER EDASERVE
(FOC35867) EDA STATUS (-17): INVALID USERID OR PASSWORD ACCESS DENIED
(FOC1400) SQLCODE IS -17 (HEX: FFFFFFEF)
L (FOC1406) SQL OPEN CURSOR ERROR.

Any ideas?

Many thanks for help.

Guang
 
Posts: 29 | Registered: April 16, 2012Report This Post
Virtuoso
posted Hide Post
Guang,
quote:
And also, I am not trying to create a HOLD file, but just need to pull the data and output to Excel.

Are you trying to output to XL on your computer using ON TABLE PCHOLD FORMAT EXL2K?
If so, bringing 1.5M records is not advisable.
I would use ON TABLE HOLD FORMAT EXL2K which would save the records on the server and then, if you can access your server from your computer simply copy it, if not have the file sent via FTP, for example.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Hi Danny,

Yes, I am trying to output to Excel on my computer using ON TABLE PCHOLD FORMAT EXL2k. If I run for a whole year of data (which actually preferred by my user), the size of the data file will reach 1.7 million records, so if possible, I would like to try your method: creating a HOLD file on the server using ON TABLE HOLD FORMAT EXL2K. I think I can access the sever through WebFOCUS Administration Console. But could you please tell me where is the place I can find the HOLD file just created. I can not see it on the server.

Many thanks for help.

Guang
 
Posts: 29 | Registered: April 16, 2012Report This Post
Member
posted Hide Post
Hi Danny,

After I changed the code to
ON TABLE HOLD FORMAT EXL2K and run the report, I got:

No HTML Output!

--------------------------------------------------------------------------------


0 NUMBER OF RECORDS IN TABLE= 179084 LINES= 179084
0 EXL2K FILE SAVED ...

I am not sure if a XL file created on the server. Do you see? If yes, where can I see the file on the server?

Thanks,

Guang
 
Posts: 29 | Registered: April 16, 2012Report This Post
Expert
posted Hide Post
By default, the HOLD file gets created in a temporary directory, which gets cleaned up on a regular schedule.

You can use FILEDEF to assign a specific directory and file name.

FILEDEF FILE1 DISK BASEAPP/MYEXCELFILE.XLS

TABLE FILE CAR
SUM
SALES
BY COUNTRY
ON TABLE HOLD AS FILE1 FORMAT EXL2K
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Hi Francis,

I did what you said, but I got an error:

--------------------------------------------------------------------------------

No HTML Output!

--------------------------------------------------------------------------------


0 NUMBER OF RECORDS IN TABLE= 2394 LINES= 2394
0 ERROR AT OR NEAR LINE 47 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC350) ERROR WRITING OUTPUT FILE: sales_hist


Line 47 is the statement END, the last line of my code.

Below is my code"

-*
-*
-* APP HOLD SALES_HISTCO
FILEDEF SALES_HIST DISK SALES_HIST/SALES_HIST.XLS

DEFINE FILE F3IHSTCO
INVAMT/P15.6 = (QTYSHP*RELPRC) + (QTY*UPRICE);
END

TABLE FILE F3IHSTCO
PRINT
'F3IHSTCO.F3IHSTCO.COMPNY' AS 'Affiliate,Code'
'F3IHSTCO.F3IHSTCO.CABBV' AS 'Customer,Abbr.'
'F3IHSTCO.F3IHSTCO.DABBV' AS 'Destination,Abbr.'
'F3IHSTCO.F3IHSTCO.DOCK' AS 'Dock,Number'
'F3IHSTCO.F3IHSTCO.SHPINV' AS 'Shipper,Number'
'F3IHSTCO.F3IHSTCO.INVNO' AS 'Invoice,Number'
'F3IHSTCO.F3IHSTCO.SHPDTE' AS 'Shipping,Date'
'F3IHSTCO.F3IHSTCO.PRCDTE' AS 'Invoice,Date'
'F3IHSTCO.F3IHSTCO.SOLDTO' AS 'JDE,Customer'
'F3IHSTCO.F3IHSTCO.HASN#' AS 'Rep,Number'
'F3IHSTCO.F3IHSTCO.CUSNM' AS 'Customer,Name'
'F3IHSTCO.F3IHSTCO.CSCTRY' AS 'Customer,Country'
'F3IHSTCO.F3IHSTCO.SHIPTO' AS 'Customer,Ship To'
'F3IHSTCO.F3IHSTCO.DCNTRY' AS 'Destination,Country'
'F3IHSTCO.F3IHSTCO.OEM' AS 'OEM,Code'
'F3IHSTCO.F3IHSTCO.OEMDIV' AS 'OEM,Division'
'F3IHSTCO.F3IHSTCO.SUPPID' AS 'Supplier,Id'
'F3IHSTCO.F3IHSTCO.CHRBOL' AS 'BOL,Number'
'F3IHSTCO.F3IHSTCO.PARTNO' AS 'Denso,Part'
'F3IHSTCO.F3IHSTCO.CPART' AS 'Customer,Part'
'F3IHSTCO.F3IHSTCO.PDESC' AS 'Part,Description'
'F3IHSTCO.F3IHSTCO.IMSRP6' AS 'Assembly,Code'
'F3IHSTCO.F3IHSTCO.QTYSHP' AS 'Qty,Shipped'
'F3IHSTCO.F3IHSTCO.RELPRC' AS 'Release,Price'
'F3IHSTCO.F3IHSTCO.UOFMM' AS 'Unit,of Measure'
'F3IHSTCO.F3IHSTCO.PONO' AS 'PO,Number'
'F3IHSTCO.F3IHSTCO.QTY' AS 'Misc.,Qty'
'F3IHSTCO.F3IHSTCO.UPRICE' AS 'Misc.,Price'
'F3IHSTCO.F3IHSTCO.INVAMT' AS 'Invoice,Amount'
BY 'F3IHSTCO.F3IHSTCO.COMPNY' NOPRINT
BY 'F3IHSTCO.F3IHSTCO.INVNO' NOPRINT
BY 'F3IHSTCO.F3IHSTCO.PRCDTE' NOPRINT

ON TABLE HOLD AS SALES_HIST FORMAT EXL2K

END

Could you please tell what's wrong?

Thanks,

Guang
 
Posts: 29 | Registered: April 16, 2012Report This Post
Expert
posted Hide Post
quote:
FILEDEF SALES_HIST DISK SALES_HIST/SALES_HIST.XLS

Is SALES_HIST a valid application folder on your server and does your user id have permission to write to it?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Guang,
quote:
0 NUMBER OF RECORDS IN TABLE= 179084 LINES= 179084
0 EXL2K FILE SAVED ...

This shows that you retrieved your records and saved them in XL format on your server in the temporary directory created by WebFOCUS. This is the default.

When you used
quote:
FILEDEF SALES_HIST DISK SALES_HIST/SALES_HIST.XLS

WebFOCUS tried to save the XL file in the SALES_HIST application directory. Does this application exist? If not, use one that exists. If so, this might mean that you do not have permission or enough space for all the records of the file.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Hi Francis and Danny,

I do have 3 month of data (179,084 records) saved on the server. But when I tried a whole year of data (716,348 records), I got the HTTP 500 error again. However, I do have a file (618MB) created on the server. It appears that it was time out on my browser, but the backgroud job was still running until the job is done.

I tried to open the big Excel file, but the connection to the server was terminated. It's definitely not the issue with WebFocus. I believe my issue has been resolved.

Many thanks for your help.

Guang
 
Posts: 29 | Registered: April 16, 2012Report This Post
Member
posted Hide Post
Hi Francis and Danny,

One more question:

quote:
if you can access your server from your computer simply copy it, if not have the file sent via FTP.

As I can not copy the file to my PC (time out), can you please tell how to FTP the file? Do I need to do this within WebFocus?

Thanks again,

Guang
 
Posts: 29 | Registered: April 16, 2012Report This Post
Member
posted Hide Post
Hi Francis and Danny,

Just so you know, I download the file directly from AS400 through MS Access using ODBC connection. I am all set.

Thank you guys for help.

Guang
 
Posts: 29 | Registered: April 16, 2012Report 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     [SHARE] Need to run MR over a big file with 1,500,000 records

Copyright © 1996-2020 Information Builders