Querying data without ODBC access

Currently the best approach to accessing non-Sybase data on 64-bit systems is to do so indirectly, as follows:

1. Configure ASE/CIS with a remote server and proxy to connect via DirectConnect. For example, use DirectConnect for Oracle to the Oracle server.

2. Configure IQ with a remote server using the ASEJDBC class to the ASE server. (The ASEODBC class is unavailable because there is no 64-bit Unix ODBC driver for ASE.)

3. Use the CREATE EXISTING TABLE statement to create proxy tables pointing to the proxy tables in ASE which in turn point to Oracle.

This example shows how to access MS SQL Server data. For this example, assume the following:

• A Sybase IQ server on host sunbeam, port 7594.

• An Adaptive Server Enterprise server on host jones, port 4101.

• An Enterprise Connect Data Access (ECDA) server exists named mssql on host monroe, port 12530.

• The data is to be retrieved from an MS SQL server named 2000 on host hardscrabble, port 1433.

1. Set up ASE and Component Integration Services (CIS) to MS SQL Server through DirectConnect. For example, assume that the server name is jones_1207.

2. Add an entry to the ASE interfaces file to connect to mssql:

   mssql
   

   master tcp ether monroe 12530
   

   query tcp ether monroe 12530
   

3. Enable CIS and remote procedure call handling from the ASE server. For example, if CIS is already enabled as the default,

   sp_configure ‘enable cis’
   

   Parameter Name Default Memory Used Config Value Run Value
   

   enable cis 						1 		0 				1 			1
   

   (1 row affected)
   (return status=0)
   

   sp_configure ‘cis rpc handling’, 1
   

   Parameter Name Default Memory Used Config Value Run Value
   

   enable cis 						0 		0 				0 			1
   

   (1 row affected) 
   Configuation option changed. The SQL Server need not be rebooted since the option is dynamic.
   

   You may need to reboot Adaptive Server Enterprise server after enabling CIS remote procedure call handling in older versions such as Sybase IQ 12.5.

4. Add the DirectConnect server to the ASE server’s SYSSERVERS system table.

   sp_addserver mssql, direct_connect, mssql
   

   Adding server ‘mssql’, physical name ‘mssql’
   Server added. 
   (Return status=0)
   

5. Create the user in Adaptive Server Enterprise that will be used in Sybase IQ to connect to ASE.

   sp_addlogin tst, tsttst
   

   Password correctly set. 
   Account unlocked. New login created. 
   (return status = 0)
   

   grant role sa_role to tst
   use tst_db
   sp_adduser tst
   

   New user added.
   (return status = 0)
   

6. Add an external login from the master database:

   use master
   sp_addexternlogin mssql, tst, chill, chill
   

   User ‘tst’ will be known as ‘chill’ in remote server ‘mssql’.
   (return status = 0)
   

7. Create an ASE proxy table as the added user from the desired database:

   isql -Utst -Ttsttst
   use test_db
   create proxy_table billing_tst at ‘mssql.pubs..billing’
   select * from billing_tst
   

   status				name				telno
   ------				-----------				-----
   D				BOTANICALLY				1
   B				BOTANICALL				2
   (2 rows affected)
   

1. Add an entry to the IQ interfaces file:

   jones_1207
   master tcp ether jones 4101
   query tcp ether jones 4101
   

2. Create the user to connect to ASE:

   grant connect to tst identified by tsttst
   grant dba to tst
   

3. Log in as the added user to create the ‘asejdbc’ server class and add external login:

   isql -Utst -Ptsttst -Stst_asiqdemo
   create SERVER jones_1207 CLASEE 'asejdbc' USING 'jones:4101/tst_db'
   create existing table billing_iq at 'jones_4101.tst_db..billing_txt' 
   select * from billing_iq
   

   status				name				telno
   ------				-----------				-----
   D				BOTANICALLY				1
   B				BOTANICALL				2
   (2 rows affected)