Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] retrieving large amounts of JDE data results in SLOW response
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] retrieving large amounts of JDE data results in SLOW response
 Login/Join
 
Member
posted
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,


Prod: WebFOCUS 7.1.6 - on Win 2K3/Tomcat - MRE/BID/RCaster/VisDis and AS400 (JDEWorld)
 
Posts: 23 | Location: North Carolina | Registered: May 16, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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.


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
If your report o/p format is in HTML, try using SET STYLEMODE=PAGED for rendering.
 
Posts: 77 | Registered: December 22, 2004Reply With QuoteReport This Post
Member
posted Hide Post
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! Smiler -Kimber


Prod: WebFOCUS 7.1.6 - on Win 2K3/Tomcat - MRE/BID/RCaster/VisDis and AS400 (JDEWorld)
 
Posts: 23 | Location: North Carolina | Registered: May 16, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
Member
posted Hide Post
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,


Prod: WebFOCUS 7.1.6 - on Win 2K3/Tomcat - MRE/BID/RCaster/VisDis and AS400 (JDEWorld)
 
Posts: 23 | Location: North Carolina | Registered: May 16, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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.


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Reply With QuoteReport This Post
Member
posted Hide Post
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?
 
Posts: 13 | Registered: May 23, 2008Reply With QuoteReport This Post
Guru
posted Hide Post
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 Big Grin


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Master
posted Hide Post
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.


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] retrieving large amounts of JDE data results in SLOW response

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.