Configuring access to Microsoft SQL Server databases

To configure access to Microsoft SQL Server, you must set the ODBCINI environment variable. You must also ensure that the Microsoft SQL Server database is accessible from the Microsoft SQL Server client and test connectivity between the Microsoft SQL Server client and the Microsoft SQL Server databases.

Before you begin

Install client libraries.

About this task

When you are connecting to a remote database, ensure that the database server is configured to allow remote connections over the TCP/IP protocol.

The Microsoft SQL Server Client cannot be installed on UNIX. Because of this, the InfoSphere® DataStage® Dynamic RDBMS plug-in stage on UNIX cannot use the Bulk Insert mode operation when the stage is configured for Microsoft SQL Server database type. The DRS plug-in stage on Windows uses the Microsoft OLE DB API for Bulk Load operations and the API is not available on UNIX. When the DRS plug-in stage is configured for Microsoft SQL Server database on UNIX, the database type for the stage is automatically switched to ODBC.

For more information about configuring access to SQL Server InfoSphere DataStage, see the IBM® InfoSphere DataStage and QualityStage Connectivity Guide for Microsoft SQL Server and OLE DB Data.

Procedure

On UNIX or Linux, set the ODBCINI environment variable to point to the .odbc.ini file in which the Microsoft SQL Server connection definitions are created.
From the Microsoft SQL Server driver on Windows, test if the Microsoft SQL Server database is ready to receive incoming connections:
1. In the Microsoft SQL Server DSN configuration window, specify the connection information for the Microsoft SQL Server database.
2. Click Finish.
3. Click Test Data Source.

On UNIX, to test if the SQL server database is ready to receive incoming connections:

Open the UNIX command-line utility.

Run the DataDirect ODBC example application from the installation_directory/example directory, where installation_directory is the path to the DataDirect ODBC example application installation directory.

[root@RH2011 example]# ./example
./example DataDirect Technologies, Inc. ODBC Example Application.

Enter the data source name : mysqlserver

Enter the user name        : username

Enter the password         : passoword

Enter SQL statements (Press ENTER to QUIT)
SQL>     
Exiting from the Example ODBC program
[root@RH2011 example]# pwd
installation_directory/example
[root@RH2011 example]#