SSRS Issue with Microsoft OLE DB for DB2 v3.0

I ran into a perplexing problem on one of my SSRS servers.  This server handled the reporting for internal users, and had a lot of reports that read from our source system, on iSeries DB2 (V6R1), and some reporting from our SQL Server data mart.

The issue was that at random times, SSRS would fully utilize one processor, and a lot of memory.

When looking at the active jobs, it was never the same report, so we tried to find the common thread in each report that was killing the server. We found nothing that was able to stop this from happening. I adjusted the maximum memory setting in SSRS, trying to keep the server from using all memory and causing a restart of the SSRS service. This had no effect as well.

Finally, one of my team members stumbled across the commonality in all of the offending reports: They all used DB2 as the data source.

Well, the first thing I thought of was the Microsoft OLE DB for DB2. We had v3.0 installed on this server. It has been working fine for over two years, but there are regular OS updates to both the iSeries and the SSRS server, one of which could have started this issue.

So I upgraded this server to v4.0, tested the reports to make sure we didn't break anything, and turned the users loose on it.

After two days I can call this a success! The server rarely gets above 10% processor, and very little memory is being used. Reports are faster, and the users are happier!

Update:  The server is running MUCH better, but we have run into an issue with some of the queries causing DB2 errors because the parameters are defined as Text, however, the database field is numeric.  I am still looking for a global way of fixing this, hopefully with the connection string to the DB2.

Update #2: After running v4.0 for 4 months, the problems have definitely gone away.  The only two side effects of this upgrade are:

• A Text type parameter in an SSRS report will throw an error if used on a numeric data type on the DB2. To rectify this, I just did a cast on the numeric field when comparing to the text parameter.
• In v3, field names were limited to a size smaller than on DB2.  If longer field names were used for reports, v3 just truncated the field name and used the limit. v4.0 did away with this limitation, so field names were their full size, causing field name issues. Example:
• AReallyLongFieldNameForTesting on DB2 might show up in v3.0 as AReallyLongFieldNameFo. Once you upgrade to v4.0, the full field name comes in, causing a name mismatch in your reports.

peace