The first step that one must do to create a link to or export data from DB2 is to establish to proper connectivity.
This supplimental artical will show you how to configure StarSQL 3.0 by StarQuest. It is a good idea if you are the DB2 admin
when installing the product to run a custom installation and install StarAdmin also. StarAdmin will give you the access to easily bind the packages to DB2. If you are using
StarSQL to connect to DB2 for the purposes of DTS or linked servers, make sure that you install it on the server and configure it exactly as you did on your workstation. StarSQL will not work
with the Windows 95 or 98 operating system.
I choose StarSQL 3.0 for the following reasons :
- Ease of configuration.
- Supportibilty by Microsoft of DTS and Replication
- Fits seamlessly into DTS and Linked Server.
Another consideration you must have is the datatypes. You will need
to check the datatypes on DB2 and SQL Server to make sure that they will
support each other. The chart below covers covers the supportibilty of the datatypes (see books online for more information).
| SQL Server data type | IBM DB2 AS400 ODBC | IBM DB2 MVS ODBC | |||
|---|---|---|---|---|---|
| binary(n) | L | S | |||
| bit | S | S | |||
| char(n) | S | S | |||
| datetime | S** | S** | |||
| decimal | S | S | |||
| float | S | L | |||
| image | N | N | |||
| int | S | S | |||
| money | S | S | |||
| nchar(n) | S | S | |||
| ntext | N | N | |||
| numeric | S | S | |||
| nvarchar(n) | S | S | |||
| real | S | S | |||
| smalldatetime | S | S | |||
| smallint | S | S | |||
| smallmoney | S | S | |||
| text | S | N | |||
| timestamp | S | S | |||
| tinyint | S | S | |||
| uniqueidentifier | S | S | |||
| varbinary(n) | S | S | |||
| varchar(n) | S | S | |||
| S = supported | L = limited support | N = not supported | |||
| S** See DTS Data Conversion and Transformation Considerations | |||||
- Install StarSQL ver 3.x. Download at StarQuest.
- Register the software with either a temporary evaluation code or a purchased one. If you do not
register the software, you will receive an "INVALID NODE ID" error. This error will not occur until
you try to see the actual data in SQL Server. You will be able to see the schemas however.
- Go to the control panel, then ODBC Data Sources.
- Select the system DSN tab. When prompted to select a type of ODBC, select StarSQL.
- Give your data source a name and make a note of it. (see figure 1)
- Enter the server name of your DB2 server and leave the default package collection name as STARSQL (see figure 2).
- Select the method of connection you wish to use to link the SQL Server to DB2 for the Network Option (see figure 3). If using TCP/IP, select the IP Address for the host name and port (see your DB2 Admin for port information).
- Enter your DB2 or RACF user id and password
- Click on advanced (which replaced next). If you'd like to filter which DB2
database schemas will be visible to the user, check the databases you'd like to see in the Catalog Filters section. Hint : If you see schemas listed here, you know your conectivity is good to the DB2 server. Click summary after you've completed this option.
- Verify the settings on the summary screen and click OK.
FIGURE 1
FIGURE 2
FIGURE 3
FIGURE 4
FIGURE 5
