I have created multiple generic reports so that some of my users can review the data that is in the report versus the data in JD Edwards files. I am not doing anything fancy, just pulling in the first 20 or so columns and less than 5,000 records in most reports.

If I ran the same query in JDE on all the fields, and the exact same parameters (in most cases, I let the user select the company and/or the year); it would come back almost immediately; however, in my WebFOCUS reports, it takes anywhere from a minute to ........ "kill the agent!" and has taken up to 70% of my resources on the AS/400.

Not good.

We have not gone live yet, and the powers that be have said that this is not acceptable.

Tech support suggested creating a hold file if I was using sorts, but for this, I'm not doing any manipulation - no sorts (which was even worse, because the read limit seems to be ignored), no defines or computes, no totals, nothing. Just parameters so they can get the most relevant data, and even then only one or two fields.

Any thoughts/ideas? Thanks in advance from this newbie...

Kimber

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

Check the SQL you are sending to JDE. Its probably not what you think it is. We have had several problems with our JDE adapter. If you are going hub-sub check the code the subserver is sending to JDE.

If your report o/p format is in HTML, try using SET STYLEMODE=PAGED for rendering.

N.Selph, I'm not sure I follow what you posted... we have a single WIN2k3 server that is hosting the WF client, the Web server, and MRE, WF Reporting Server and Report Caster. Then we have the WF Reporting Server on the AS400 where JDE data resides...

thangam, I tried that as well - doesn't appear to change the response time. Only changes that have made any difference has been if I want to sort, to bring in a record set to a hold file, then sort.

Thanks for the advice - will be watching for your responses! -Kimber

try running a request against a jde file using sql passthru.
sample fex:
SQL SQLMSS SET SERVER servername
SQL SQLMSS
SELECT MCMCU,
MCCO,
MCAN8
FROM F0006
WHERE MCAN8 = 12345
;
TABLE
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT MCMCU
MCCO
MCAN8
END

Thanks for your reply, Spence...

OK, I tried to open a report using painter and got a parsing error (new development - same file worked fine last week); anyway, went into text editor and pasted this fex. Changed servername to JDESERVE, which is our JDE server and got this response:

(FOC1671) SET SERVER JDESERVE COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
(FOC1671) SET SERVER JDESERVE COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
(FOC1400) SQLCODE IS 1 (HEX: 00000001)
L (FOC1405) SQL PREPARE ERROR.
0 ERROR AT OR NEAR LINE 14 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: MCMCU
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT


When I tried to open the SQL command in the procedure viewer, I get a message saying "This adapter is not configured. Please choose a different adapter or configure this adapter."

-Kimber

PS - running this from DevStudio -- going to try now from server console and see if there's any difference.

*update: from JDESERVE console - get same results.

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

So it seems you are going HUB-SUB.
Do you have

 
SET APT=OFF

in the edasprof.prf on in the AS400 WF Server Console? We found that we need that setting to properly report from JDE.

Also I would copy your fex code into a blank procedure page on the AS400 WF Server Console.
Put the these lines at the top:

 
SET TRACEUSER=CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
SET TRACEON=STMTRACE//CLIENT
SET XRETRIEVAL=OFF

You will see the SQL code generated. Cut that out, clean it up, and run it in JDE. You can also try this from the SUB (WIN2k3) Server console to see if you get the same thing.
This will rule out if WF is sending bad SQL to the AS400, or if you have another problem.

We have had both kinds of problem. Then you need to open a case with IBI Tech support.

I'm having this same exact issue while I'm trying to upgrade from 7.1.6 to 7.6.6.

I ran the report on my HUB(NT) server and the client timed out / crashed but if I ran my report on the SUB(AS400) server then it ran in 20 seconds.

Any updates or solutions?

as an aside to your issue i would suggest using recordlimit as well as readlimit- but you are probably already doing this...

we always use hold files or create data strucutures depending on complexity....

i would try holding the data as a focus database... but thats a 'thrashing' approach as opposed to a 'know exactly what im doing' approach

Are you aware of -REMOTE? It's the means of including local and remote sections in your fex: Unmarked sections run locally (on Windows), while sections of code delimited by -REMOTE BEGIN and -REMOTE END are sent to the remote (AS400) WF server for execution ("hub calling a sub"). PCHOLD in the remote section ships a Hold file back to the local server.

Typically it looks like this:

  
-* local preliminaries (to handle parameters, etc.) go here
 . . .
-* set up credentials for Remote execution
REMOTE DEST = remote_server_name
REMOTE USER = userid
REMOTE PASS = password
 
-REMOTE BEGIN
-* produce an extract:
 DEFINE ...
  . . . 
 END
 TABLE FILE xxxxx   
  . . .
  WHERE field EQ '&value';
  ON TABLE PCHOLD
 END
-REMOTE END

-* local code to produce a styled report  
TABLE FILE HOLD
. . . 
END

Of course, the remote code can involve SQL passthru, or EX ..., or any other Focus commands -- as long as it includes a final PCHOLD.

Note that all processing of DM &vars is local. The remote code can include references to dialog-manager &vars -- but those are resolved before the remote code-block is transmitted to the sub for execution -- what the remote server sees is pure Focus code.

If you want to do anything in the remote process that involves dialog manager (e.g., testing &LINES and braching), encapsulate it as a remote procedure (a fex stored on the remote server) and invoke it via "EX" in the remote section (Not via "-INCLUDE" -- that's a DM directive which would be expanded on the local side, from the local WF servers directories, and all the DM would be processed locally)


So, if you already have Focus or/or SQL code to pull your data efficiently on AS400, you should be able to wrap it in a fex to run on the local server, with an embedded section bracketed by -REMOTE BEGIN/END, to pull the data as a HOLD file and report from it locally with little overhead beyond the remotely processed data extraction.

For development purposes (i.e., to use Dev Studio to define and style the finished report) you should capture sample data, save the ftm and mas files (and the filedef parameters), and develop your formatted report against them; then repackage to run directly against a dynamically generated REMOTE extract.

I am just wondering, Do we have any solution for this slowness? We are running our report against the AS400 server and it takes more than an minute to load irrespective of data.