Hi everyone,

I have this mysterious time out problem when running MRE reports that take longer than 5 minutes to complete. I have already consulted with IBM and IBI technical support to increase all relevant timeout parameters found in websphere 6.0.2.12, Apache webserver 2.47, webserver plugin and I even increased the timeout for Internet explorer to 1 hour, but the mysterious timeout problem still persists.

The MRE reports would run for 1 hour then the IE browser would display the error message "Page can not be displayed"

MRE reports that take less than 5 minutes to complete will return to the IE browser fine.

We are running websphere 6.0.2.12 and MRE/ReportCaster 7.1.4 on Windows 2003, SP2 server. The backend DB2 database and the webfocus reporting server 7.1.5 run on AIX 5.2 server. There are firewalls set up in front of the Windows 2003 and AIX 5.2 servers

Thank you all for your help on this problem

Regards,

Thang

Thang,

You didn't mention whether you checked the DB2 query governer time. That could be the issue?

Kevin

what happens if you run the same fex in mre in deferred mode?
same question - in report caster?
i would try both of those things to help me narrow down the problem's location.

Hi Kevin/Susan,

The webfocus query completed successfully in DB2, even when it took longer than 5 minutes. We don't have DB2 governor set up for this particular DB2 database.

The same long running webfocus reports would execute fine in deferred mode and in reportcaster. I even tried to execute the same long running reports in webfocus reporting web console, it they ran successfully there too.

The problem is our business client doesn't want to execute the reports in deferred mode, even when the reports take longer than 5 minutes

Thanks,
Thang

In IIS (our Webserver) there's a connect time out that's set to about 2mins and we upped it to 20mins.
You will find similar in Websphere (your Webserver) since its very sensible to have time out on long running queries for an internet rather than intranet based system since in general an internet user will have given up after 7 seconds so I am told.

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

As a practical matter, web browsing should be interactive. 5 minute wait times are not acceptable. Nor are they practical considering the nature to TCPIP and the various timouts that are introduced at the various levels. Hence, you need to convince your user community to submit their queries in deferred mode.

We also ran into this obstacle a long time ago with WebFocus. The best solution we found to force users into submitting their queries deferred is to code the connection_limit on the reporting server service block. Setting connection_limit=90 will automatically kill any query that runs longer than 90 seconds.

People learn fast. After having a few of their queries terminated after waiting 90 seconds they will quickly learn to submit their queries in deferred mode.

i agree with John, it may well be that setting in your webserver, especially since the job runs fine in all non web modes.
and i also agree with John that 7 seconds is the attn span for humans, except for my kids

what query are you running that takes 5 minutes?

If you're not familiar with performance tuning your query, I'd look into IBM's redbook on performance tuning.

Not attempting to insult, this is a commonly overlooked area that drives BI performance. If you haven't heard of IBM's "materialized Query table" technology, that may turn your longest running queries into very efficient impressive reports.

We also have a gigantic report that takes up a lot of time to end.
We have found that when we do ANALYZE statements on the tables the performance is better and the report will run and return succesfully.
However, if we don't run the analyze, the user gets a "CGI Timeout" screen, the session in WebFocus is killed, but the session in Oracle stays.
Is there anyway to kill the session in Oracle (8) as well? (Like adjusting some configuration setting or something)

There's a facility in Oracle that allows you to place each user in a resource pool, so you could put the wf user into a pool that had a cpu limit.

If you are writing reports in Oracle dont go beyond 4 Oracle managed joins because the Oracle optimizer is very poor. Use wf MATCH facility to relate retrievals of 4 or less joins.

Tim

One thing that intrigues me is you say you might forget to do the analyse before you run the report.

Oracle stats are not transient and should stay in place until you reload the database or re-run the analyse.

Are you reloading the database eachtime?

Regards

John

We are using the normal JOIN syntax of webfocus (not the SQLORA syntax stuff)
We are not reloading the database that I'm aware of, but we have a strange issue that seems to point in that direction though.
We run our ETL jobs with iWay's ETL Manager, they are configured to run at night; some of them have "dependencies"; and the general "loading" setting is "truncate table". Furthermore, practically each job is set-up like this: we have a "pre-extract RPC" that is a .fex, which is in fact just a report that generate a hold file and this hold file is then copied to an Oracle table.

This thread might be split to the iWay forum.

Tim

1. Dont think it needs to go IWAY since to do with long running jobs.

2. It looks like your ETL is altering the contents of the db enough that a new ANALYSE needs to be run - simply tack this on the end of your iway jobstream.

3. Long running reports

Even though you are issuing joins in wf the query gets translated to ORACLE sql. Use this to view the created SQL and look for reports where you have more than 4 tables related. NB Oracle joins dont use new SQL syntax but use the old style WHERE file1.field = file2.field or the (+) is appended for an outer join.

-* File sqltrace.fex
-* check on includes translation
SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE/1/CLIENT
SET TRACEON=SQLAGGR/1/CLIENT
-*SET TRACEON=WHOPT/1/CLIENT
SET TRACEON=SQLTRANS/3/CLIENT
SET TRACEON=?
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF

NB Sometimes a report may be so big you can't speed it up beyond a certain point, but with Oracle you can use careful design - I had an instance of going from 9hrs scheduled job to a 15 minutes by using the wf match facility in place of Oracle join!

Hope this is of use...

John

Other thing that is worth doing is if you are an Oracle DW try getting rid of bitmap indexes and replace with btree as they work very badly under 9.2 and 10i.