MS SQL Server

You should install a number of Microsoft client libraries in the TDI_install_dir/jars directory to use MS SQL Server.

JDBC connection parameters

com.ibm.di.store.database=jdbc:Microsoft:sqlserver://localhost:1433;
     DatabaseName=master;selectMethod=cursor;
com.ibm.di.store.jdbc.driver=com.microsoft.jdbc.sqlserver.SQLServerDriver
com.ibm.di.store.jdbc.user=sa
com.ibm.di.store.jdbc.password=passw0rd

The above connection parameters are used with these Microsoft JDBC jars:

Msutil.jar
MsBase.jar
MSsqlserver.jar

Note: For Microsoft SQL Server 2008, the driver jar file to be placed in the TDI_install_dir/jars directory is sqljdbc.jar (only one file is required) and it can be obtained from your SQL Server 2008 installation at <Microsoft SQL Server 2005-Install-Dir>/sqljdbc_<version>/<language>/sqljdbc.jar; the JDBC connection parameters need to be specified as follows:

 com.ibm.di.store.database=jdbc:sqlserver://localhost:1433;DatabaseName=name;selectMethod=cursor;
 com.ibm.di.store.jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
 com.ibm.di.store.jdbc.user=sa
 com.ibm.di.store.jdbc.password=passw0rd

The selectMethod property is optional to the jdbc URL. When this property is set to "cursor", a database cursor is created. This is useful when reading very large result sets that cannot be contained in the clients memory.

The default behavior of selectMethod is not "cursor", but "direct", which keeps result sets in clients memory, thus providing much faster performance. So unless memory is a problem, it is better to use the default "direct" behavior. For more information: http://msdn.microsoft.com/en-us/library/ms378988(SQL.90).aspx.

JDBC connection parameters (for JSQLConnect driver)

com.ibm.di.store.database= jdbc:JSQLConnect://itdiderver/database=reqpro 
com.ibm.di.store.jdbc.driver= com.jnetdirect.jsql.JSQLDriver 
com.ibm.di.store.jdbc.urlprefix= jdbc:JSQLConnect: 
com.ibm.di.store.jdbc.user=administrator 
{protect}-com.ibm.di.store.jdbc.password=password

These connection parameters are used with JSQLConnect drivers. You must download the JSQLConnect.jar file and copy it into the TDI_install_dir/jars directory.

Create table statements

The DATA TYPE for MS SQL is IMAGE.

com.ibm.di.store.create.delta.systable=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL,
 SEQUENCEID int, VERSION int);
 ALTER TABLE {0} ADD CONSTRAINT IDI_MYCONSTRAINT_{UNIQUE} PRIMARY KEY (ID)  
com.ibm.di.store.create.delta.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL,
 SEQUENCEID int, ENTRY IMAGE );
 ALTER TABLE {0} ADD CONSTRAINT IDI_DS_{UNIQUE} Primary Key (ID)  
com.ibm.di.store.create.property.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL,
 ENTRY IMAGE );
 ALTER TABLE {0} ADD CONSTRAINT IDI_PS_{UNIQUE} Primary Key (ID)  
com.ibm.di.store.create.sandbox.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL,
 ENTRY IMAGE)  
com.ibm.di.store.create.recal.conops=CREATE TABLE {0} (METHOD varchar(VARCHAR_LENGTH),
 RESULT IMAGE, ERROR IMAGE)