Preparing the Microsoft SQL Server Environment

Topics:

How to:

In order to take full advantage of the features available through the Adapter for Microsoft SQL Server 2012/2014, we strongly recommend using the same or higher version of the Microsot SQL Server Client. To determine which version of MS SQL Server you are using, please refer to the following article: http://support.microsoft.com/kb/321185.

You can set up the Microsoft SQL Server Environment on Windows and UNIX. On Windows, for Microsoft SQL Server versions 2017/2016/2014/2012, you can choose between the ODBC version of the adapter (please refer to Using the Adapter for Microsoft SQL Server ODBC) or the OLE DB version of the adapter (described in this chapter), depending on required features. Microsoft had deprecated the OLE DB version of Microsoft SQL Server, but has now un-deprecated it with the release of a third generation driver, MSOLEDBSQL driver version 18. To use the OLE DB version of the adapter for these releases, you must download and install MSOLEDBSQL driver version 18. Instructions for downloading and installing the driver can be accessed from the configuration page for the adapter by clicking the help link titled Prerequisites.

For version 2008, use the OLE DB version of the adapter (described in this chapter).

Procedure: How to Set Up the Environment on Windows (OLE DB)

The Microsoft SQL Server environment is set up during the installation of the Microsoft SQL Server, Client, and MDAC software.

No additional setup steps are required.

Procedure: How to Set Up the Environment on UNIX

Identify the location of the Microsoft SQL Server JDBC Driver files using the environment variable $CLASSPATH. For example, to set the location of the JDBC Driver files, specify:

CLASSPATH=/qas/mss/sqljdbc_4.0/enu/sqljdbc4.jar
export CLASSPATH

The driver sqljdbc4.jar file is within the "Microsoft JDBC Driver 4.0 for SQL Server" downloadable *.tar.gz version of the Microsoft package. Optionally, the CLASSPATH or IBI_CLASSPATH may be set up as part of the adapter configure step or in the Java Services properties (and exporting skipped, but the directory location must be known).

Identify the installation directory of the Java Development Kit using the environment variable $JDK_HOME. Java version 1.6 or higher is required. For example, if you want to set the location of the Java Development Kit to /usr/java, specify:

JDK_HOME=/usr/java 
export JDK_HOME

Identify the installation directory of the Java Virtual Machine using the environment variable $LD_LIBRARY_PATH. For example, if you want to set the location of the Java Virtual Machine, specify:

LD_LIBRARY_PATH=/usr/java/jdk1.6.0_38/jre/lib/amd64/server 
export LD_LIBRARY_PATH

Note: If the server is running with security on, the LD_LIBRARY_PATH variable is ignored. In this case, you must use IBI_LIBPATH.

Procedure: How to Set Up the Environment on IBM i

Identify the location of the Microsoft SQL Server JDBC Driver files using the environment variable $CLASSPATH. For example, to set the location of the JDBC Driver files, specify:

CLASSPATH=/qas/mss/sqljdbc_4.0/enu/sqljdbc4.jar
export CLASSPATH

The driver file sqljdbc4.jar file is within the "Microsoft JDBC Driver 4.0 for SQL Server" downloadable *.tar.gz version of the Microsoft package. Optionally, the CLASSPATH or IBI_CLASSPATH may be set up as part of the adapter configure step or in Java Services property (and exporting skipped, but the directory location must be known).

JVM access is built-in on IBM i and no further environment set up is needed. Java version 1.6 or higher is required.

Accessing Microsoft SQL Server Remotely

You can access Microsoft SQL Server on a remote node. To access Microsoft SQL Server remotely, you must:

  • Locally install the latest version of Microsoft SQL Server Native Client.
  • Know the name of the remote Microsoft SQL Server instance.

All Microsoft SQL Servers installed are defined by using a unique NetBEUI name. The server can access any Microsoft SQL Server on the network, provided you define a valid user ID and password, as well as the name of the Microsoft SQL Server instance. You can define these parameters in either the server global profile or a user profile.

XA Support

Read/write applications accessing Microsoft SQL Server data sources are able to execute transactions managed in XA-compliant mode.

To activate the XA Transaction Management feature, the server has to be configured in Transaction Coordination Mode, using the Web Console configuration functions. Using Transaction Coordination Mode guarantees the integrity of data modification on all of the involved DBMSs and protects part of the data modifications from being committed on one DBMS and terminated on another.

For complete documentation on XA compliance, see XA Support.

WebFOCUS

Feedback