Use of RDL as reporting tool

[KatA]

Hi,

We use RDL as a reporting tool, and often get problems with reports "hanging". As reporting is done using our live instance this can also cause problems in live system performance. We've carried out work to make the RDL coding as efficient as possible, and asked users to run large reports out of core hours, however we are still having problems. Has anyone else had this problem, and how have you got around it?

Kat

 

[sb98]

Hi Kat,

Try check the index key in selection criteria for master file (MFILE) and index key in lookup file (LFILE), maybe this can help faster the report process.

Regards,

Sb

 

[Calator]

What exactly do you mean by "hanging"? what is the situation displayed by MSO083 and programs.log? Is the task executing, aborted, initiated but not executing, or not initiated? There are many possible situations.

 

[MayW]

Hi KAT,

I've had jobs "hangup" after being submitted also. I'm not sure what causes it. What usually worked for me was to delete the job (I believe it was in the I mode), initiated but not executing. Then resubmit it. Sometimes I would need to delete and resubmit it several times, but it usually worked.
Regards,
MayW

 

[payers]

RDL like any other database access tool will cause problems if the way in which they are written cause the program to scan entire tables looking for results. The problem is intensified when the tables have a large number of rows increasing the number of IO's on the database. Typical OLTP databases, of which MIMS should be configured for, are tuned for transaction processing and not reporting. Reporting databases have larger buffers set up so that larger amounts of data can be returned in one IO, OLTP databases are tuned to return the least amount of data for the transaction.
When writing database access programs, the initial search should always use a table / index combination which will direct it immediately to the required data. This makes the selection of the 'mfile' critical. Use of the stopif command also helps restrict the search through the table. The selection of information from 'afiles' is also critical and the careful formation of the where condition and use of the stopif command should be used to restrict the number of rows returned.
This has been a issue for us for a while on our site, and our investigations have identified a number of generic MIMS RDL's that provide the correct result, but take longer than necessary to retrieve the data. We have replaced the worst ones with our own versions to reduce the load.
We are also currently looking at other options for reporting, one is to replicate some of the data onto a database tuned for reporting and using third party reporting tools to access it.

 

[KMillar]

"Hanging" RDLs used to be a problem years ago, but unless you're on version 3.13 or less, I doubt the reports are stalled. I think its more likely they're simply running way too long.

While RDL is not perfect, its certainly a lot better than writing reports from scratch in COBOL, so persist with it if you can. As PAYERS mentioned (g'day Paul), the way table reads are set up and the method of joining the tables will have a significant impact on a given RDL's performance. We've had instances of reports being tuned with the result being run times are reduced from 12 hours down to 6 seconds!

Its not an easy thing to pin point exactly where you problem is without seeing your set up, but PAYERS' advice is worth careful consideration.

 

[gbell]

Something else worth a look is the $COBSW setting used in compilation. For those who don't know, all RDL's create sort files in the MIMS instance tmp directory of all records selected. It's from these sort files the output is produced, a two stage process. Tweaking the $COBSW setting for sorting can sometimes lead to significant reductions in runtime.

 

[dianne001]

Has your Batch table been updated? A program will sit in the initiated status because it cannot find it in the batch.tbl.

 

[oz1]

PAYERS, I'm very interested in the fact that you have been able to optimise generic MIMS RDL's that provide the correct result, but take longer than necessary. Can you give us an idea of which ones? We have been doing the same but may not have got them all. Were your changes feedback to Mincom and if so do you know if they then placed them into core code?

 

[payers]

oz1
One program in recent time thet we changed (MSR600 - Used to take hours to run but now runs in minutes) had the changes passed back to Mincom and they made the changes to the standard program as well. This is not the norm though.
There are others that are able to provide the requierd results with one combination of parameters, but the programs uses a more extensive set. By reducing the parameters and resetting the select statements, we were able to improve performance, but would not be picked up by Mincom for obvious reasons.
I cannot list the the programs involved as I have not been involved with all of them and I am not fully aware of all the details. I just know this is the process we use.
The effort to improve system performance here is ongoing. We use CSC to help support our system and they have been able to collect statistics and report on resource hungry programs. We use this to concentrate our effort in the areas that should give us the better outcome.