Oracle batch reporting.

[Steven E. Protter]

We have some long running Oracle application reports that we'd rather the users not run in from their desktop computers.

We can output the reports to pdf and submit them to a print queue.

I'd like your thoughts on how to kick off batch reports(cron) and how you limit the number of reports running background at one time.

Other issues:
Stopping report processing for backups,
Restarting reports stopped for backup,
Preserving sql populations.

If you have a script infrastructure, this can be big points.

Current product set.

Oracle 64 bit 8.1.7.4
Internet Application Server 1.0.2.2 Patch 12
Which includes
Apache
Oracle Forms
ORacle Reports

UI is web based, IE 5.5 browser.

Some points for effort. Big points for working solutions that include scripts.

SEP

[Chris Vail]

I'm going to hate myself for this, but not nearly so much as you're going to hate me for suggesting it. You need Tivoli Workload Scheduler (TWS) or its competing product from Computer Associates (I forget its name). These
are "uber cron" programs that work in a client/server environment. They allow you to have very elaborate rules about jobs, when/how/why they start, how they run, and what to do if/when they abend.

We use TWS here. It SUCKS as a program--it breaks all the time (its relatively stable in production, but thats only since we changed the password and won't let anyone mess with it). However, we just couldn't do our work here without it. We have hundreds of jobs that run in a given day. Some of these jobs have to run only after another is finished. Others need to run differently depening on what another job did with the data. Still others have to wait until the system gets and stays quiet for a while.

TWS lets you schedule all this, and more. It is a PITA to install and maintain, but once its up, running and debugged, its pretty forgettable.
I can't speak for the CA Unicenter people: the rumor is that their product is just as heinous as TWS.

I recommend the 7.0 version rather than the 8.0. The earlier version has an X-based tool not supported in the later. The X-based tool is far more efficient and user friendly than the other, but THATS MICROSOFT FOR YOU!. Maybe they'll put it back in version 9.

You can get a 30 day demo of the product for free. Check it out.

Chris

[James A. Donovan]

Setup one or more concurrent managers and assign special workshifts within Oracle Applications. Make it so that only these manager(s) can run the reports in question. This way, you can precisely control when these jobs can run.

You can use the CONCSUB, found in $FND_TOP/bin, utility in your backup scripts to start/stop the concurrent managers during your backup windows.

example:
${FND_TOP}/bin/CONCSUB apps/${PASSWD} SYSADMIN 'System Administrator' SYSADMIN
WAIT=N CONCURRENT FND ABORT

The "ABORT" will ensure that all managers are brought down immediately. It will reset any currently running jobs back to a "PENDING" status.

All this info and more can be found in the Oracle Applications Administrators guide....

[Brian Crabtree]

One option would be to activate dbms_jobs, and use this for scheduling and reporting. It should be pretty simple to write a report program that would use a table for scheduling, and keep the number of processes down for batch processing, or call a procedure with a parameter of the report name for jobs that need to be run.

Brian

[Steven E. Protter]

I won't hate you.

Keep the ideas coming. I am particularly interested in the last one. Quite intriguing.

I may have some of my Oracle consultants/geeks post in some questions, just in case you find the writing style curious on Monday.

On the TWS product. We aren't running client server. We run the application on the web, which is considered thin client mode. Will it work in that context?

SEP

[Yogeeraj_1]

hi,

We run a very similar configuration.

Batch reports: this is how we do it,

Method 1.
Using the reports queue manager (this is well documented and quite intuitive)

You should install the client/server version of the program which normally comes with the Forms/Reports builder.

Method 2.
Using cron.
We run it on the server, publish it and alternatively email it to the users.
(script is attached)

HTH
Yogeeraj

[Christian Gebhardt]

Hi

a comment to Chris Vail's state:
Tivoli Workload Scheduler (TWS) is formely known or better known as "MAESTRO".
We uses TWS in our company, but you have to think about the cost of thid tool.

Chris

[Yogeeraj_1]

hi again,

About the issues:
Stopping report processing for backups,
Restarting reports stopped for backup,

These can be cron'ed.

For example, the reports server script "reports60_server" found in "$IAS_HOME/6iserver/", can be executed as follows:

reports60_server stop

then

reports60_server start

Please find attached an extract from my crontab.

HTH
Yogeeraj

[Yogeeraj_1]

hi Mr. Protter,

"..limit the number of reports running background at one time"

I have been investigating about this issue too.

Normally, each report engine is a process handling a particular report generation request. It will block until the report output is complete, which is why you need multiple of them for parallelization. However, unlike Forms and Discoverer (etc.), Reports is a "stateless" architecture, so a single report engine will process requests for the entire user community.

Hence, if you want to limit the number of reports at any time, you can also reduce that parameter in the server.ora file.

Parameters which we can modify are:

Minengine
Is the minimum number of engines the Reports Multi-tier Server should have available. The server process will attempt to keep at least this many engines active. Ensure that you have sufficient memory and resources available to accommodate this many engines.

maxconnect
Is the maximum number of processes that can communicate with the server process at any one time. This is the sum of the number of engines and clients, and must be greater than two (at least one engine and one client).

Best Regards
Yogeeraj

[Steven E. Protter]

Most interesting every post. We're designing a system now and certain aspects of the posts here are already designed in.

The table structures for the report server for monitoring jobs is supported by oracle through 9i Release 2 database and reports server. At least.

I'm curious batch people, how many big jobs can you run simultaneously before your system slows down. Post system type and memory configuration.

We have rp5450/L2000 2 Gig of RAM 4 Gig RAM in production.

SEP