TDW 2.1/ITM 6.1 Microsoft SQL Server Connection String for SPA

I can't take all the credit for this one, David Weir from the TME10 list posted a problem he was having with the Summarization and Pruning Agent running out of memory during its nightly processing. His server had plenty of memory, but MSSQL server ran out of memory everytime the SPA was processing.Here is the fix that worked for him.

David was receiving message like the following in his xxxxx_cms_datestamp.log file.

== 1144 t=work1 com.microsoft.sqlserver.jdbc.SQLServerException: Thesystem is out of memory. Use server side cursors for large result sets:.Result set size:1,630,338,861. JVM total memory size:679,541,248.

He told me that he configured the SPA according to the IBM Support article #1237586. This article tells you to use the following for the connection string:

jdbc:sqlserver://hostname;databaseName=WAREHOUS
This is OK and will work, but as David learned when the database grows in size - it stops working.Here is the fix, modify the jdbc connection string for the SPA to look like the following:

jdbc:sqlserver://SERVERNAME;databasename=WAREHOUS;SelectMethod=cursor;
There are two select methods - DIRECT and CURSOR. Depending on the database and how it was designed - CURSOR may be required. If you never need multiple-statement-execution transactions, or you never have more than one open statement at a time, you can use direct. Otherwise you need cursor mode. There is tuning available that can make it perform better - such as creating indexes on the most commonly quereied fields or normalizing your data structure. Row by row cursor selects are what appear to be required by the TDW 2.1 given it's flat design.