Configuring ETL client/server connectivity

For an overview of client/server connectivity for Sybase IQ, see “Configuring client connectivity,” in Chapter 5, “Configuring Sybase IQ,” in the Sybase IQ Installation and Configuration Guide. You must define Open Client server definitions and ODBC data source names (DSNs) for ETL projects or jobs on the appropriate machine. Server definitions and DSNs must be appropriate for the platform and operating system where the ETL project or job runs.

For example, if you build a project or job in the Windows ETL Development client that you plan to run on a remote GridNode engine on Solaris, you need to configure the connectivity on that remote machine with the GridNode. The ODBC DSN or Open Client server name must be defined in the Solaris .odbc.ini file or interfaces file.

Follow these steps to configure connectivity for Sybase IQ ETL Server on Windows.

1. Install Sybase IQ ETL Development from the Extended Enterprise Edition Client CD.

2. Open the ODBC Data Source Administrator (Start > Programs > Sybase > Data Access > ODBC Data Source Administrator.)

3. In the ODBC Data Source Administrator application, choose the System DSN tab.

4. Click Add and select the Adaptive Server IQ driver when prompted.

5. Name the data source (DSN) and complete the connection information.

   See “Creating ODBC data sources,” in Chapter 5, “Configuring Sybase IQ,” in the Sybase IQ Installation and Configuration Guide.

6. Within the ETL client, create a project and add a DB component. In the component properties, select ODBC from the Interface drop-down menu and then specify the ODBC DSN in the Host name field, user id and password to connect.

Follow these steps to configure IQ ETL to be an Open Client Sybase IQ client on Windows.

1. Install the Sybase IQ Server from the Extended Enterprise Edition.

2. Install Sybase IQ SDK (Open Client).

3. Create a sql.ini file entry for Sybase IQ using the dsedit utility (Start > Sybase > Connectivity > Open Client Directory Services Editor).

   For more details, see “Connecting using Open Client,” in Chapter 5, “Configuring Sybase IQ,” in the Sybase IQ Installation and Configuration Guide.

4. Click Add to define a server, name it, and provide the connectivity information.

5. Use the Ping utility to verify it.

6. Within the ETL client, create a project and add a DB component. In the component properties, select Sybase from the Interface drop-down menu and then specify the Open Client server name defined in the sql.ini file in the Host name field, user id and password to connect.

Follow these steps to configure connectivity for Sybase IQ ETL Server on UNIX or Linux.

1. Choose the Sybase IQ Server option from the Extended Enterprise Edition installation.

2. Install Sybase IQ Server.

3. Create a .odbc.ini file with any text editor and define a Data Source Name there. For details, see “Using ODBC data sources on UNIX,” in the Sybase IQ System Administration Guide.

4. Set the $ODBCINI variable to point to the file by typing the following C shell command:

   setenv ODBCINI /<Sybase_iq_installation_directory>/.odbc.ini

   For other shells, use the sh /export syntax.

5. Run ASIQ-12_7.csh or ASIQ-12_7.sh script from the root installation directory to set the variables.

6. Verify the ODBC configuration using the Sybase IQ component Interactive SQL. Change to the $ASDIR/bin directory and type:

   ./dbisql -c "uid=<user_id>;pwd=<password>"
   -ODBC -dataSource
   <ODBC_data_source_name_from_.odbc.ini> -nogui. 
   

   On connection, a user name displays. Type select @@version to return the Sybase IQ versions string to verify the connection. If an error indicates that no driver and no valid data source are found, the $ODBCINI variable is not set properly. Check the $ODBCINI variable setting and the .odbc.ini file for any possible mistakes or omissions. Type exit to exit dbisql.

   For more information, see “Storing connection information,” in Chapter 5, “Configuring Sybase IQ,” in the Sybase IQ Installation and Configuration Guide.

7. In the same console session where the ASIQ and $ODBCINI variables are set, change to the ETL server installation directory and run the GridNode.sh script.

8. Within the ETL client, create a project and add a DB component. In the component properties, select ODBC from the Interface drop-down menu and then specify the ODBC DSN from the .odbc.ini file in the Host name field to connect.

Follow these steps to configure the ETL server to be an Open Client to Sybase IQ on UNIX or Linux.

1. Choose the Sybase IQ Server option from the Sybase IQ Extended Edition Server installation media.

2. Choose to install Open Client from the Sybase IQ Server installation.

3. Run the sybase environment variable script in the root $SYBASE installation directory. For example, for the C shell, type: ./source SYBASE.csh

4. Use dsedit to create an interfaces file in the root $SYBASE directory. Change to the $SYBASE/OCS-15_0/bin directory and type: ./dsedit

   Since dsedit is a GUI application, you may need to set the $DISPLAY variable to the machine where you want dsedit to display. For example, in the C shell, type: setenv DISPLAY mypc:0.0

5. Specify the interfaces file. Use the default file in the root $SYBASE installation directory or create your own. Click OK.

6. When the Directory Service Session panel appears, click the Add new server entry button to define a new server.

7. In the Server Entry Editor panel, enter the server and click the Add new network transport button.

8. In the Network Transport Editor panel, provide the following information:

   • Transport type: Select tcp from the drop-down menu.

   • Host name: Enter the machine name of the IQ host.

   • Port number: Enter the port on which the IQ server is running. For example, the default port for the Sybase IQ demo server is 2638. in the interfaces file.

   Click OK.

9. Click Cancel as necessary and Exit to close the dsedit.

10. In the same console with the Sybase variables set, and within the $SYBASE/OCS-15_0/bin directory, use the isql application to verify the Open Client server definition. Type the following command:

    ./isql -U<user_name>  -P<password>
    -S<server_name_defined_in_interfaces>
    

    For example, for the Sybase IQ demo server, use: ./isql -Udba -Psql -Sasiqdemo. The application connects and displays a 1> prompt.

11. To return the Sybase IQ version string for verification, type: select @@version, press return and then enter go. If the connection fails, use any messages to troubleshoot the problem and verify the dsedit entry

12. Type exit at the command prompt to close dbisql and the connection.

13. In the same session where the $SYBASE variables are set, change to the ETL server installation directory and run the configure.sh file to update the ETL environment with the Sybase environment information.

14. Run the GridNode.sh file.

15. Within the ETL client, create a project and add a DB component. In the component properties, select Sybase from the Interface drop-down menu and then specify the Open Client server name defined in the sql.ini file in the Host name field to connect.