Template Report Performance
Home Up Turning off Warning Messages in Walker Batch Processing Setting CICS terminal to mixed case Template Report Performance Reading non-IOSYS files SelfServe Object Replicator

 


Tips and Tools : Tamaris : Personalizers : Reportbuilder : Improving Template Report Performance

Improving Template Report Performance

Walker General Ledger clients running in a DB2 environment can improve the performance
of their template reports by moving much of the processing into SQL statements
within specialized FAMs.  (This is not nearly as difficult as it might sound.)


What are Template Reports?

The template reports are ones that have XXR115 (or SCR115) as the primary database.  Walker ships several template reports, including the Balance Sheet (IIGL/014), Income Statement (IIGL/017), Responsibility Balance Sheet (IIGL/051), and Responsibility Income Statement (IIGL/052) reports.  Your shop, however, probably has its own custom template reports, perhaps with names similar to the Walker standard ones.


Why Improve Their Performance?

Most template reports have the following characteristic in common: they sum values across many records in order to generate a relatively small set of result values to be printed.  This makes them ideal candidates for performance enhancement by pushing functionality down into DB2.

Depending on your data, these performance enhancements can potentially reduce the number of records returned to the Reportbuilder by several orders of magnitude, decreasing processing time by 25% to 75% or more.  Depending on your report customizations, moving filtering or other logic into DB2 can provide further dramatic improvements.

One client reduced monthly report processing time from more than a day to just a few hours by applying this simple technique to its template reports.


How do you do it?

First, find out which template reports use the most processing time each month.  (This will normally be the ones with the most "records selected for processing.")  Only address those reports that are taking "too much" processing time.  (How much is "too much"?  A minute of elapsed time certainly isn't too much, but 30 minutes probably is.  It depends on how much optimization you want to do.)

Analyze your reports to make sure that they use the standard Walker ACK / ACK Set data extraction logic, and to see what custom filtering or other processing they do.

Create a custom FAM for each template report or for several related reports.  Here are some hints for developing your FAM:

If possible, stick to the record layout of the XXG4000.  This makes everything easier.  (Don't bother trying to eliminate fields that your reports do not use, as the time to process these extra fields is not significant.)

Replace the ACK key field with one or more key fields allowing you to pass in the set name and any other appropriate filter criteria fields.  (Pad with FILLER out to 50 bytes.)

This FAM will return the sum of all the records in a set.  Change the SQL statements to join the SCR913 and the SCG4000.  Return the MIN() of all non-numeric fields, and the SUM() of all numeric fields.

If your FAM is to return values for a single year, you only need to implement the KEQ paragraph.  If your FAM needs to return values for multiple years (e.g., for year-to-year comparisons), implement the KGE paragraphs instead and use GROUP BY in your SQL statement.

Include data filtering or other processing in you SQL as appropriate.  The more processing you move into the SQL statement, the more performance improvement you will see.

If your custom template reports use a "column set" to define which records go in each column, move the second join into the FAM as well.  The report will perform better if you always use the smallest set as the outermost join.

Create FID and DDM entries and a TRT entry, and assemble, bind, and link your FAM.

Create a new version of the report that uses your FAM.  Since the join of the SCR913 and SCG4000 and the data filtering have been moved into the FAM, the report should become smaller and simpler.  Generally, your changes will be confined to the Extract phase.

Test to be sure that you get the same results.  Compare execution times.  Your new version should be a lot faster!

Can the same technique be used on any other reports?

Most reports that run for a long time but produce a small number of pages are excellent candidates for this kind of optimization.  It is almost always more efficient to let DB2 do the processing.


Contributed by JC Cunningham