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

HTTP 500 error, probably the browser timed out.

Have you tried running the report deferred ?

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

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

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

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

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

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.

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

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

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

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

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?

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.

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

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

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