StarQuest Technical Documents

Using StarSQL with IBM DataStage

Last Update: 2 November 2016
Product: StarSQL
Version: 6.x
Article ID: SQV00SQ062

Abstract

IBM® InfoSphere® DataStage (a component of the InfoSphere Information Server) is an ETL (Extract, Transform, Load) tool, typically used in data warehousing applications.

This technical document demonstrates how to use StarSQL to connect DataStage running on Linux to an external DB2 database using ODBC. A similar procedure applies if DataStage is running on other UNIX platforms. If DataStage is running on Windows, the procedure is much simpler.

This technical document was developed using DataStage version 11.5.

DataStage includes a branded copy of the DataDirect ODBC driver manager in /opt/IBM/InformationServer/Server/branded_odbc. For additional information about using StarSQL/UNIX with the DataDirect ODBC driver manager, see Using StarSQL for UNIX with the DataDirect ODBC Driver Manager.

Solution

The examples below assume that DataStage was installed to its default location of /opt/IBM/InformationServer, and StarSQL to its default location of /usr/share/starsql64. Except for installing StarSQL and DataStage, most of the procedure below was performed with the userID dsadm.

1. During installation of DataStage using the installer for InfoSphere Information Server, the installer created an initial project dstage1.
   
2. Install StarSQL 6.x and configure licensing.
3. If necessary, bind StarSQL packages on the DB2 system in the default package collection name of STARSQL.
4. Edit /opt/IBM/InformationServer/Server/DSEngine/dsenv:

• Verify the value for ODBCINI; you will need this information when creating the StarSQL ODBC data source below. The default value is /opt/IBM/InformationServer/Server/DSEngine/.odbc.ini.
• If ODBCINI is pointing to a file other than the default .odbc.ini file supplied with DataStage, confirm that the odbc.ini file defines IANAAppCodePage and InstallDir in the [ODBC] block.
  
• Verify that LD_LIBRARY_PATH will include the path to the DataDirect Driver Manager (/opt/IBM/InformationServer/Server/branded_odbc/lib).
• If ODBCINST is not defined in dsenv, configure it, using a copy of odbcinst.ini or .odbcinst.ini that contains the driver description for StarSQL64.
  e.g.
  export ODBCINST=/usr/local/etc/odbcinst.ini
• If ODBCINST is already defined, edit that copy of odbcinst.ini and add the driver description for StarSQL64, using /usr/local/etc/odbcinst.ini as a guide.
  

5. Create a StarSQL ODBC data source in /opt/IBM/InformationServer/Server/DSEngine/.odbc.ini (or whichever file is defined by ODBCINI in Server/DSEngine/dsenv). The DriverUnicodeType keyword notifies the DataDirect Driver Manager that StarSQL is a UTF-16 ODBC driver. QryBufSiz is optional and may enhance read performance.

[MYDSN]
Server = MYRDB
PkgColId = STARSQL
Driver = /usr/share/starsql64/lib/libSWODBC.so
HostName = myhost
port = 446
DriverUnicodeType=1
QryBufSiz=1024000

6. Test connectivity (StarSQL + DataDirect ODBC Driver Manager) outside of DataStage using the sample query tool example. Note that the test tool ddtestlib does not work with StarSQL.
   

$ . /opt/IBM/InformationServer/Server/DSEngine/dsenv
$ cd /opt/IBM/InformationServer/Server/branded_odbc/samples/example
$ ./example

This program will prompt you for a DSN, a user and a password; enter the DSN name (e.g. MYDSN) and credentials for the DB2 host computer. If example establishes a successful connection it will show the following prompt, at which you can enter ad-hoc SQL queries.

Enter SQL statements (Press ENTER to QUIT)
SQL>

7. Edit uvodbc.config in the project - e.g. /opt/IBM/InformationServer/Server/Projects/dstage1/uvodbc.config. Note: the spaces around the " = " signs are required.

uvodbc.config
<MYDSN>
DBMSTYPE = ODBC

8. Restart the server so that it picks up the ODBCINST change in dsenv:

as user dsadm:
$ cd /opt/IBM/InformationServer/Server/DSEngine/bin
$ ./uv -admin -stop
$ ./uv -admin -start

$ cd /opt/IBM/InformationServer/ASBNode/bin
$ . ./NodeAgents_env_DS.sh
$ ./NodeAgents.sh stopAgent
$ rm -f ../logs/Agent.out
$ ./NodeAgents.sh start

9. Test with dssh (DataStage engine shell). Note: the LOGTO and DS_CONNECT commands in dssh are case-sensitive:

$ cd /opt/IBM/InformationServer/Server/DSEngine
$ . /dsenv
(unless this has already been sourced in step 6 above)
$ ./bin/dssh
> LOGTO dstage1
> DS_CONNECT MYDSN
Enter username for connecting to 'MYDSN' DBMS [dsadm]: myuser
Enter password for myuser:
MYDSN> select * from mytab;

10. Test with InfoSphere DataStage and QualityStage Designer:

1. Start Designer and connect to the Project (dstage1)
2. From the Import menu, select Table definitions and Import ODBC Table Definitions
   
   
   
   
3. Select the DSN from the dropdown, enter the host user & password
   Select Filter if you want to limit the table list (alternatively, you can use the CatalogFilter keyword in the StarSQL DSN)
   
   
   
   
4. You will see a list of tables on the source DB2 system.
   
   
   
   
5. Select a table and choose Details to get column names and other information about the table.
6. Select a table and choose View Data.. to perform a SELECT from the table.
   

References

IBM Technote 1434177: How to troubleshoot ODBC connectivity errors in DataStage