ITM 6.1 Troubleshooting MSSQL/TDW Tips

On a few occasions as a team we have had to troubleshoot WPA/SPA problems with MSQQL/TDW implementations. I decided to put together a tip sheet for resources related to these problems.

Tip 1

Here are some IBM FAQ's

How to Set up MS SQL Server for Warehousing
(search for 1228154)

S&P Agent receiving KSYException from SQL Server driver
(search 1233511))

Summarization and Pruning agent not creating aggregation tables
(search 1230920))

ITM 6.1: SQL drivers for the S&P Agent
(1236263))

ITM 6.1: download the SQL drivers for the S&P Agent
(1236261))

Tip 2.

When using SQL db with ITM 6, this is where the installation guide can mess you up some. With one of our classes, the SPA failed to connect when we clicked "test connection" from a reconfigure of SPA from MTEMS.
I found that the ITM installation had not created a database login for
the user that SPA was using. I created the login in SQL Enterprise Manager manually and all worked fine.

I've gone on to find that letting the ITM installation create this login in SQL Enterprise Manager works better than creating it manually since it seems to give it all of the permissions. However, the ITM install guide will steer you wrong with SQL server, telling you that you need to create things that you don't need to create because the ITM installation will do it for you.

I also had problems with the SPA not creating the summary tables in my lab and I had not given the OS account for the SPA user Administrator permissions in Windows.

Tip 3.

You can try adding DatabaseName=, where
is your warehouse db (e.g. DatabaseName=Warehous) in the db url setting for SPA in MTEMS and see if that helps. Put it after the 'server:port;' in the url.

I've also seen the SPA wake up but not do any work because the database
tables in the warehouse db don't show as owned by the SPA user. It appears the SPA actually looks for tables by what user owns them before starting any work. I had a case where the tables created by the WPA were owned by 'dbo', and the SPA didn't look at them until they were owned by the SPA user.