Wednesday, March 12, 2008

ITM6 Warehouse S&P SQL 2005 Driver Update

There is a knowledge base article regarding updating the S&P agent with the SQL Server 2005 Driver. It is also included in FP03. While they detail the instructions for updating the driver, seems like one oversight in the instructions will cause the agent to not fire at all...

According to KB 1237586, the SQL Server 2005 JDBC Driver should replace the SQLServer 2000 drivers in the Warehouse S&P configuration. We went through the process of moving to the new driver to alleviate the out of memory and java errors we were seeing in the sy_java log files. However, after changing the configuration to use only the 2005 jar driver, the S&P agent never seemed to kick off.

The instructions basically outline how to update the drivers per the gui configuration
JDBC Drivers: Remove the SQL Server 2000 drivers
JDBC Drivers: Add the SQL Server 2005 Drivers
Driver: C:\Program Files\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.1\enu\sqljdbc.jar
Warehouse URL: jdbc:sqlserver://localhost:1433;databaseName=WAREHOUS
Warehouse Driver:

But what they forget to mention is that the CLASSPATH variable needs to be cleaned up.

On the Warehouse Proxy Agent Server..
Edit the file: /TMAITM6/KSYENV
and change the KSZ_CLASSPATH variable by removing the paths to previous JDBC Drivers'?path (e.g. C:/Program Files/SQL Server 2000 Drivers)

And finally, of course, restart the S&P agent.

This was our resolution experience in the TEMS environements we were working with, so this is by no means conclusive, and your results may differ, but I figured it is worth noting and sharing.

No comments: