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 ../../com.ibm.swg.im.iis.conn.msole.doc/topics/sqlserverandmsole.html.
- 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:
- In the Microsoft SQL Server DSN configuration window, specify the connection information for the Microsoft SQL Server database.
- Click Finish.
- 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]#