Configuring data source names on Windows

To configure data source names on Windows systems, you must start ODBC Administrator and then map an ODBC driver to one or more relational databases that you will use for creating and storing OLAP models and metaoutlines. Run the ODBC Administrator utility from the Windows Control Panel.

The procedures in the following sections show you how to configure data source names to create connections to databases. The examples in the procedures use a database named TBC as the sample Data Source database and a database named TBC_MD as the sample OLAP Metadata Catalog database.

For each site-specific data source name that you configure, obtain the following information from your database administrator:

For clarity in presenting the sample application in Integration Services Console, the sample Data Source database and sample OLAP Metadata Catalog database are two separate databases that use two different data source names. You are not required to use two different databases for your Data Source database and OLAP Metadata Catalog database. Additionally, you are not required to use two different data source names to connect to your Data Source database and OLAP Metadata Catalog database if all the tables reside in the same database.

Note:
If you are using Oracle OCI direct connection, you do not need to configure data sources. You connect DB2 OLAP Integration Server to relational data sources when you access them in Integration Services Console. This process is described in Connecting to Integration Server and Connecting to data sources.

Accessing the ODBC Data Source Administrator

For any RDBMS, you must access the Windows ODBC Data Source Administrator to begin the data source name configuration process.

To access the Administrator:

  1. On the Windows desktop, select Start --> Settings --> Control Panel to open the Control Panel window.
  2. In the Control Panel window, double-click the Administrative Tools icon, and then double-click the Data Sources (ODBC) icon to open the ODBC Data Source Administrator dialog box.
  3. In the ODBC Data Source Administrator dialog box, click the System DSN tab.

Configuring DataDirect Wire Protocol drivers

Use the following procedures to configure a data source name for your Data Source database using a DataDirect Wire Protocol driver for DB2 Universal Database(TM), Informix, Oracle, or Sybase. Then repeat the procedure to configure a data source name for the relational database that you want to use as your OLAP Metadata Catalog database.

Before beginning the configuration procedure, perform the procedure in Accessing the ODBC Data Source Administrator.

Note:
You may repeat the configuration procedure to create any number of data source names to relational Data Source databases or OLAP Metadata Catalog databases.

To configure a data source name:

  1. Click Add to open the Create New Data Source dialog box.
  2. In the driver list box of the Create New Data Source dialog box, select the appropriate Wire Protocol driver for your RDBMS, and click Finish.

    The Wire Protocol Driver Setup dialog box for your RDBMS is displayed.

  3. In the Data Source Name text box, type the name that you want to use for this data source connection.

    In the sample application, TBC is the data source name used to connect to the Data Source database called TBC; TBC_MD is the data source name used to connect to the sample OLAP Metadata Catalog database called TBC_MD.

  4. Optional: If you want to record a description, in the Description text box, type a description that indicates how you use this data source name.

    For example, you might type the following to describe the My Business database:

    Customers, products, markets

    You might type the following to describe the sample application database:

    Sample relational data source 

    You might type the following to describe the sample OLAP Metadata Catalog database:

    Sample OLAP Metadata Catalog
  5. Make the necessary entries to configure data source names by completing the set of steps appropriate to your RDBMS:

    DB2 Universal Database or DB2 UDB for z/OS and OS/390:

    1. In the IP Address text box, type the address (either the computer name or the numeric IP address) of the computer where the database tables are stored.
    2. In the TCP Port text box, type the port number on which the DB2 database server listens.
      • If you are using DB2 UDB, the default port number is 50000.
      • If you are using DB2 UDB for z/OS and OS/390, the default port number is 446.
    3. If you are using DB2 UDB, in the Database Name text box, type the name of the database for which you want to create this data source name.
    4. If you are using DB2 UDB for z/OS and OS/390, perform the following actions:
      1. In the Location text box, type the DB2 location name as defined during the DB2 UDB for z/OS and OS/390 installation.
      2. In the Collection text box, type the name that identifies a logical group of database objects (the default is DATADIRECT00).
      Note:
      For DB2 UDB for z/OS and OS/390, you configure a data source name for the Data Source database only. You cannot configure a data source name for the OLAP Metadata Catalog database. OLAP Metadata Catalog functionality is not supported in DB2 UDB for z/OS and OS/390.

      To use DB2 OLAP Integration Server with DB2 UDB for z/OS and OS/390, you must add an entry to the eis.cfg file after you configure the data source name for the source database. See Adding a configuration file entry for DB2 UDB for z/OS and OS/390 for more information.

    5. Click the Bind tab.
    6. Accept the default in the Bind tab and click the Create Package button.

      For more information about the bind process, see DataDirect Connect for ODBC Reference.

    7. In the Login dialog box, type the user name and password associated with the database for which this ODBC data source name is being created, and click OK.

      A message is displayed informing you that the package was created successfully.

    Informix:

    1. In the Host Name text box, type the address (either the computer name or the numeric IP address) of the computer where the database tables are stored.
    2. In the Port Number text box, type the port number on which the Informix database server listens.

      The default port number for Informix is 1526.

    3. In the Server Name text box, type the address, either numerically or by name, of the Informix server as it appears in the sqlhosts file.

      If you do not know this information, contact your Informix database administrator.

    4. In the Database Name text box, type the name of the database for which you want to create this data source name.

    Oracle:

    1. In the Host text box, type the address (either the computer name or the numeric IP address) of the computer where the database tables are stored.
    2. In the Port Number text box, type the port number on which the Oracle database server listens.

      The default port number for Oracle is 1521.

    3. In the SID text box, type the name of the database for which you want to create this data source name.

    Sybase:

    1. In the Network Address text box, type the address (either the computer name or the numeric IP address) of the computer where the database tables are stored and the port number on which the Sybase database server listens. Separate the computer name or IP address and the port number with a comma as shown in the following examples:

      By name: sequoia,4100

      Numerically: 172.0.0.125,4100

    2. In the Database Name text box, type the name of the database for which you want to create this data source name.
  6. Test the connection by following the procedures in Testing DataDirect Wire Protocol drivers.

When you finish testing, repeat 2 through 5 to configure a data source name for an OLAP Metadata Catalog database.

Testing DataDirect Wire Protocol drivers

To test a data source name connection:

  1. Click Test Connect.

    The Login to Wire Protocol dialog box for your RDBMS is displayed.

  2. In the User Name and Password text boxes, enter a valid user name and password for this data source connection, and click OK.

    If an error message is displayed, make the necessary corrections, and click Test Connect again.

    When no errors occur, a dialog box displays the message, Connection established!

  3. In the message dialog box, click OK.

    You are returned to the ODBC Wire Protocol Driver Setup dialog box.

  4. In the ODBC Wire Protocol Driver Setup dialog box, click OK.

    You are returned to the System DSN tab of the ODBC Data Source Administrator dialog box. The data source name that you entered and the driver that you mapped to the data source name are displayed in the System Data Sources list box.

  5. Repeat steps 1 through 5 in Configuring DataDirect Wire Protocol drivers to configure a data source name for an OLAP Metadata Catalog database.
  6. When you have completed configuration of all needed data source names, click OK to close the ODBC Data Source Administrator dialog box.

Adding a configuration file entry for DB2 UDB for z/OS and OS/390

If you are using IBM DB2 UDB for z/OS and OS/390, you must add an entry to the eis.cfg file to support DB2 OLAP Integration Server functionality. You must add entries for each data source name for DB2 UDB for z/OS and OS/390 that you configured in Configuring DataDirect Wire Protocol drivers.

Note:
For DB2 UDB for z/OS and OS/390, you configure a data source name for the Data Source database only. You cannot configure a data source name for the OLAP Metadata Catalog database. OLAP Metadata Catalog functionality is not supported in DB2 UDB for z/OS and OS/390.

To add an entry for DB2 UDB for z/OS and OS/390 to the eis.cfg file:

  1. On the computer running Integration Server, use a text editor to open eis.cfg.
  2. Add a new line for DB2 UDB for z/OS and OS/390 using the data source name or names you configured in Configuring DataDirect Wire Protocol drivers.

    Add separate lines for each data source name configured for DB2 UDB for z/OS and OS/390. The syntax is:

    [DS:dsn:390]

    For example, using the sample application database, TBC, the following is the entry to the eis.cfg file:

    [DS:TBC:390]
  3. Save and close eis.cfg.

Configuring the SQL Server ODBC Driver for Microsoft SQL Server

Use the following procedure to configure a data source name for your Data Source database using the SQL Server driver. Then repeat the procedure to configure a data source name for your OLAP Metadata Catalog database.

Note:
You may repeat the configuration procedure to create any number of data source names to relational Data Source databases or OLAP Metadata Catalog databases.

The procedures that follow provide information on basic configuration. If you are using more advanced options, refer to the online help for each wizard screen for assistance in completing the wizards.

To use the SQL Server driver to configure a data source name:

  1. Access ODBC Data Source Administrator by following the procedure in Accessing the ODBC Data Source Administrator.
  2. Click Add to open the Create New Data Source dialog box.
  3. In the driver list box of the Create New Data Source dialog box, select the SQL Server driver, and click Finish.

    The Create a New Data Source to SQL Server dialog box is displayed.

  4. In the Name text box, type the data source name that you want to use for this data source connection.

    In the sample application, TBC is the data source name used to connect to the Data Source database called TBC; TBC_MD is the data source name used to connect to the sample OLAP Metadata Catalog database called TBC_MD.

  5. Optional: In the Description text box, type a description that indicates how you use this data source name.

    For example, you might type the following names to describe the My Business database:

    Customers, products, markets

    You might type the following statement to describe the sample application database:

    Sample relational data source

    You might type the following statement to describe the sample OLAP Metadata Catalog database:

    Sample OLAP Metadata Catalog
  6. In the Server text box, type the address (either the computer name or the numeric IP address) of the computer where the database tables are stored.
  7. Click Next.

    The second wizard screen is displayed.

  8. Select the appropriate option button for the method to use for verifying login IDs:
  9. Optional: If you are using a network library other than TCP/IP (the default) to connect to the database server, perform the following steps:
    1. Click the Client Configuration button.
    2. In the Network libraries list, select the appropriate option button for the library that you are using to connect to the database server computer, and click OK.

      You are returned to the second wizard screen.

  10. Optional: Select the Connect to SQL Server to obtain default settings for the additional configuration check box.

    When this check box is selected, the driver obtains default settings from SQL Server that it uses to complete additional setup screens in the wizard.

    When this check box is clear, the driver uses standard defaults to complete the additional setup screens in the wizard.

  11. Click Next.

    The third wizard screen is displayed.

  12. Select the Change the default database to check box and then type or select the name of the database for which you want to create this data source name.

    For example, in the sample application, TBC is the name of the Data Source database; TBC_MD is the name of the OLAP Metadata Catalog database.

  13. Click Next.

    The fourth wizard screen is displayed.

  14. Make any entries appropriate to your system configuration and click Finish.
  15. Repeat steps 2 through 14 to configure a data source name for an OLAP Metadata Catalog.
  16. When you have completed configuring all needed data sources, click OK to close the ODBC Data Source Administrator dialog box.

Configuring the Teradata ODBC driver for Teradata

Use the following procedure to configure a data source name for your Data Source database using the Teradata driver. Then repeat the procedure to configure a data source name for the relational database that you want to use as your OLAP Metadata Catalog database.

Note:
You may repeat the configuration procedure to create any number of data source names to Data Source databases or OLAP Metadata Catalog databases.

To configure a data source name:

  1. Access the ODBC Data Source Administrator by following the procedure in Accessing the ODBC Data Source Administrator.
  2. Click Add to open the Create New Data Source dialog box.
  3. In the driver list box of the Create New Data Source dialog box, select Teradata, and click Finish.

    The ODBC Driver Setup for Teradata RDBMS dialog box is displayed.

  4. In the Name text box, type the data source name that you want to use for this data source connection.

    In the sample application, TBC is the data source name used to connect to the Data Source database called TBC; TBC_MD is the data source name used to connect to the sample OLAP Metadata Catalog database called TBC_MD.

  5. Optional: In the Description text box, type an description that indicates how you use this data source name.

    For example, you might type the following to describe the My Business database:

    Customers, products, markets

    You might type the following to describe the sample application database:

    Sample relational data source

    You might type the following to describe the sample OLAP Metadata Catalog database:

    Sample OLAP Metadata Catalog
  6. In the Teradata Server Info text box, type the address (either the computer name or the numeric IP address) of the computer where the database tables are stored.
  7. In the Default Database text box, type the name of the database for which you want to create this data source name.

    For example, in the sample application, TBC is the name of the Data Source database; TBC_MD is the name of the OLAP Metadata Catalog database.

  8. Optional: Enable the X Views option for Teradata.

    You can limit the number of data sources displayed in the left frame of an OLAP Model main window based on the security of the Teradata user ID that was used to connect. Follow steps a through c to enable the X Views option:

    1. Click the Options button.
    2. Check the Use X Views check box.
    3. Click OK to return to the ODBC Driver Setup for Teradata RDBMS dialog box.
  9. Click OK to return to the System DSN tab of the ODBC Data Source Administrator dialog box.

    The data source name that you entered and the driver that you mapped to it are displayed in the System Data Sources list box.

  10. Repeat steps 2 through 9 above to configure a data source name for an OLAP Metadata Catalog.
    Note:
    You may repeat the above procedure to create any number of data source connections to relational data sources or OLAP Metadata Catalogs.
  11. When you have completed configuring all needed data sources, click OK to close the ODBC Data Source Administrator dialog box.

Editing a Data Source Name

To edit configuration information for a data source name:

  1. Access the ODBC Data Source Administrator by following the procedures in Accessing the ODBC Data Source Administrator.
  2. Select the data source name and click Configure to open the driver setup dialog box or wizard specific to your RDBMS.
  3. Correct any information that you want to change.