|Tips and Tools :
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
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