A complete connectivity guide from InfoSphere Information Server to DB2 for i

Step-by-step instructions

IBM® Information Server supports extracting from and writing to DB2 for System i®. To help you overcome any challenges in setting up the connection from Information Server to DB2 for i, this article provides clear, step-by-step instructions, from checking prerequisite information and components to connecting to DB2 for i and defining DataStage jobs.

Share:

Simon Yao (simon.yao@au1.ibm.com), Advisory Software Engineer, IBM

Photo of Simon YaoSimon Yao has been working with IBM Information Server Suite products (formerly called DataStage and QualityStage, MetaStage and ProfileStage) since 2000. He currently works as an ATS Support Engineer and trainer for Information Server in the IBM Australia Development Lab. Simon also has a strong background in database including Oracle, DB2, MS SQL Server, and Sybase.



Kevin Cao (kevincao@au1.ibm.com), Advisory Software Engineer, IBM

Photo of Kevin CaoKevin Cao has 12 years of experience for IBM System i (formerly named iSeries, AS/400). Currently, he is a technical support engineer for IBM InfoSphere products, including InfoSphere Information Server and InfoSphere Master Data Management Collaboration Edition. Additionally, Kevin has strong skills and knowledge of IBM Enterprise Content Management products and ERP.



30 May 2013

Also available in Vietnamese

Introduction

DB2 for i and DB2 for Linux, UNIX and Windows have some key differences, for example, common terms, data storage architecture, and national language support (NLS) mapping. These differences often result in conceptual confusion when using DataStage to set up a connection to DB2 for i from InfoSphere Information Server.

Connectivity problems are often easily sorted out. However, issues involving Information Server connecting to System i can sometimes be challenging. Some users conclude that the connectivity issues are a defect with the functions of Information Server. But more often it turns out to be an understanding gap.

With Information Server being widely adopted as a primary ETL tool in recent years, the number of users connecting to DB2 for i using Information Server is also increasing. This article aims to overcome the understanding gap by providing a simple, step-by-step guide for connecting Information Server to DB2 for i for Information Server administrators, developers, technical architects, system administrators, and all others users of Information Server and System i.

Understanding Information Server and DB2 for System i

Introduction to DB2 for System i

DB2 for i is a relational database management system that is fully integrated in the IBM System i (formerly called iSeries® and AS/400®) operating system. This is the key difference between DB2 for i and DB2 for Linux, UNIX and Windows. DB2 for Linux, UNIX and Windows is an application that is hosted by the operating system. The fact that DB2 for i is incorporated into the operating system makes it extensible, high performing, and scalable. It adheres to many industry standards while maintaining a low total cost of ownership.

To allow access to DB2 for System i remotely, the distributed relational database architecture (DRDA) capability must be enabled in System i, and the local relational database also needs to be defined using the ADDRDBDIRE command. The following sections describe how to perform these tasks.

Introducing InfoSphere Information Server architecture

IBM InfoSphere Information Server is an information management product suite that consists of Business Glossary, Business Glossary Anywhere, FastTrack, Information Analyzer, Metadata Workbench, DataStage, QualityStage, Information Services Director, and more. A key advantage of InfoSphere Information Server is its shared infrastructure. This allows metadata sharing between individual modules and makes data integration easy. InfoSphere Information Server consists of four tiers:

  • Repository tier (A database, such as DB2, Oracle, or SQL Server)
  • Services tier (WebSphere Application Server)
  • Engine tier (DSEngine, PXEngine)
  • Client tier

From a high-level view, InfoSphere Information Server architecture is shown in Figure 1.

Figure 1. InfoSphere Information Server architecture
Diagram shows services tier connected to repository tier and engine tier. It is also connected to the various machines of the client tier

Connecting to DB2 for System i from Information Server

From Information Server, there are two approaches that can be used to access DB2 for System i:

  • DataDirect ODBC: DataDirect ODBC drivers are built into the Information Server Engine tier with a successful installation. DB2 Wire Protocol Driver is designated for accessing DB2 for System i from DataStage jobs.
  • DB2 client on Information Server Engine tier: DB2 Connect must be installed on the Information Server Engine. To use this access method, the DB2 UDB API stage should be used in the DataStage job.

Getting the required information from DB2 for System i

The following information is required for accessing DB2 for System i:

  • IP address: The IP address of DB2 for System i.
  • User name: The user name on System i to access DB2 for System i.
  • Password: Password of the user.
  • TCP port: The DRDA service port. Generally, the port number is 446. To get the port, run the WRKSRVTBLE command from your System i command interface and find the line with keyword "drda" for service name and "tcp" for protocol, as shown in Figure 2.
    Figure 2. WRKSRVTBLE command output
    Screen shot shows drda service and tcp protocol with port 446
  • Location: The name of the relational database on System i that you want to access. To get the location name, use the DSPRDBDIRE command from the System i command interface and then find the line with keyword "*LOCAL." The entry name is the location. In the example in Figure 3, DBONB01 is the location name.
    Figure 3. DSPRDBDIRE command output
    Screen shot shows *LOCAL as remote location and DBONB01 as entry

Connecting to DB2 for i using ODBC

This is the most commonly used method to access DB2 for System i from Information Server DataStage because it doesn't require the extra expense for DB2 Connect and is relatively simple to set up.

Setting up the data source in a Windows environment

Because the ODBC drivers bundled with Information Server are 32-bit, you need to use the 32-bit ODBC Administration Tool to set up the System Data Source. If you are on a 64-bit Windows environment, refer to the Microsoft Support document 942976 (see Resources.

  1. Open the Control Panel and select System and Security > Administrative Tools > Data Sources(ODBC). Choose the System DSN tab and click the Add button.
    Figure 4. ODBC Data Source Administrator on Windows
    Screen cap showing DB2SAMPLE selected as the data source
  2. Select IBM DB2 Wire Protocol.
    Figure 5. DB2 Wire Protocol driver
    screen cap shows DB2 Wire Protocol selected
  3. Fill in the Data Source Name, IP Address, TCP Port, and Location Name information obtained in the previous section. Click Test Connect to make sure the connection can be established successfully.
    Figure 6. ODBC DB2 Wire Protocol Driver Setup
    screen cap shows input areas for data source, ip address, tcp port, and location name.

Setting up the data source in a UNIX or Linux environment

In UNIX and Linux environments, the ODBC connection inherits its environment from dsenv and looks for the .odbc.ini file for its runtime environment. The steps for setting up on UNIX or Linux are:

  1. Make sure the information in dsenv is correct.

    The environment setting in dsenv for the ODBC connection is set during installation by default. If dsenv has not been modified, your dsenv file should look like Listing 1.

    Listing 1. Sample environment variable list in dsenv file
    ODBCINI=$DSHOME/.odbc.ini; export ODBCINI
    …
    LD_LIBRARY_PATH=`dirname $DSHOME`/branded_odbc/lib
    …

    Note that the library path environment variable is different across different UNIX platforms:

    • Solaris: Linux – LD_LIBRARY_PATH
    • HP-UX: SHLIB_PATH
    • AIX: LIBPATH
  2. Create an access entry in the .odbc.ini file.

    The .odbc.ini file is under the $DSHOME directory by default (or refer to $ODBCINI in dsenv as discussed in step 1). Back up the .odbc.ini file, then open it with a text editor. There is an example in this file for the DB2 Wire Protocol. Copy and paste this example to create a new entry and fill in information for Location, LogonID, Password, and TcpPort. Listing 2 shows an example.

    Listing 2. Sample DataDirect ODBC setting up for System i
    [TORI6B01]
    Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb225.so
    Description=DataDirect DB2 Wire Protocol Driver
    AddStringToCreateTable=
    AlternateID=
    Collection=
    Database=
    DynamicSections=100
    GrantAuthid=PUBLIC
    GrantExecute=1
    IpAddress=9.26.103.178
    IsolationLevel=CURSOR_STABILITY
    Location=Location_name
    LogonID=User_name
    Password=Password
    Package=DB2 package name
    PackageOwner=
    TcpPort=446
    WithHold=1

    The entry name between square brackets can be any name. However, we recommend that you stay with the Location name for easy identification.

  3. Modify the uvodbc.config file.

    After the entry in the .odbc.ini file has been created, you need to add an entry for the data source name in the uvodbc.config file under each project directory for each project that requires accessing the data source. Listing 3 shows an example.

    Listing 3. Sample entry in uvodbc.config
    DBMSTYPE = UNIVERSE
    network = TCP/IP
    service = uvserver
    host = localhost
    <TORI6B01>
    DBMSTYPE = ODBC

    Note that the data source name between angle brackets must be identical to the data source name in the .odbc.ini file between the square brackets. In the previous examples, TORI6B01 is used.

  4. Create a bind package on DB2 for i.

    For DB2 Wire Protocol to work properly, the DB2 bind package must be created on DB2 for System i. The bind utility is located in the ../branded_odbc/bin directory. The syntax is:

    Bindxx data_source_name

    Note: xx is the bind utility version. Navigate to the ../branded_odbc/bin/ directory and you should be able to find the correct version of bind that comes with the bundled ODBC drivers. Listing 4 gives an example.

    Listing 4. Sample to bind package
    [root@rhel55iis87 bin]# ./bind25 TORI6B01
    User Name: TEST
    Password: ********
    SecurityMechanism: ''
    Creating packages ...Packages created and bound.

You can use the sample program to test the connection for the data source. The example program is under the ./branded_odbc/sample directory. Listing 5 gives an example.

Listing 5. Sample to test connection using ODBC driver
[root@rhel55iis87 example]# pwd
/opt/IBM/InformationServer/Server/branded_odbc/samples/example
[root@rhel55iis87 example]# ./example
./example DataDirect Technologies, Inc. ODBC Example Application.

Enter the data source name : TORI6B01

Enter the user name        : TEST

Enter the password         : PASSWORD

Enter SQL statements (Press ENTER to QUIT)
SQL>

When you see the "Enter SQL statements (Press ENTER to QUIT)" message displayed and the SQL interface appears, the connection to data source using ODBC has been made successfully.

Importing ODBC table definitions

  1. After an ODBC connection has been made, log on to the Information Server Designer client. From Import > Table Definitions > ODBC Table Definitions…, select DSN from the drop-down list and enter the user name and password as shown in Figure 7.
    Figure 7. Import ODBC table definitions - 1
    Import Meta Data (ODBC) screen
    Click OK.
  2. From the next window, select the table you want to import.
    Figure 8. Import ODBC table definitions - 2
    Screen shows list of tables

Using ODBC Connector, ODBC Enterprise, or DRS stage to access DB2 for i

You can use the imported table definition to design a DataStage job to read or write to a table on DB2 for i using ODBC Connector, ODBC Enterprise, or DRS stage. The following steps use ODBC Connector stage.

  1. Start with the ODBC Connector. Click Load.
    Figure 9. ODBC Connector
    screen shows ODBC_Connector_0-ODBC Connector
  2. Load the ODBC table definitions. From the Table Definitions explorer view, select ODBC, select the data source name, and select the table name.
    Figure 10. Table definitions
    Shows explorer view of table definitions
  3. Select the columns you want to use, moving them to the Selected columns pane.
    Figure 11. Select columns
    shows list of columns
  4. The next screen (Figure 12) shows column details.
    Figure 12. Column details
    Shows SQL type, extended attributes, length, scale, and nullable for each column
  5. Define the communication detail. From the ODBC Connector screen, define the details on the Properties tab. Under Connection, define your data source, user name, and password.
    Figure 13. ODBC Connector details
    Shows data source MYTEST1, username, and password
  6. Run the DataStage job from the InfoSphere DataStage Designer.
    Figure 14. Complete job running
    Shows ODBC job running

The previous example is for reading from a table in DB2 for i. Check to confirm that you have received the correct output. Writing to DB2 for i is very similar. The important thing is to make sure the metadata (schema) is the same as the target table from the input.


Connecting to DB2 for i using DB2 connect

DB2 Connect or DB2 Enterprise Server Edition is a requirement on the Information Server Engine tier to access DB2 for System i. The installation of DB2 Connect or DB2 Enterprise Server is not in the scope of this article. However, you can refer to the IBM DB2 Information Center for details (see Resources for a link).

Checking to see if DB2 Connect or DB2 Enterprise Server is installed on the Information Server Engine Tier

To check if DB2 Connect or DB2 Enterprise Server is installed:

  • Windows platform: Open a Windows command prompt, change to the DB2 BIN directory under the DB2 installation directory (usually SQLLIB), then execute the db2licm -l command. Figure 15 gives an example.
    Figure 15. Check DB2 product on Windows platform
    Shows db2licm command with the output of product name, license type, expiry date, product identifier, and version information
  • UNIX/Linux platform: Check with your DB2 DBA for the DB2 instance name that is installed in the Information Server Engine tier machine. Log on to the Information Server Engine tier machine as the db2 instance owner and run db2licm to check if the DB2 instance is either DB2 Connect or DB2 Enterprise. Listing 6 gives an example.
    Listing 6. Sample to check DB2 product on UNIX/Linux
    # db2licm -l
    Product name:                     "DB2 Enterprise Server Edition"
    License type:                     "Restricted"
    Expiry date:                      "Permanent"
    Product identifier:               "db2ese"
    Version information:              "9.7"

Creating a DB2 node for System i

After the installation of the prerequisite DB2 Connect component is confirmed, you can catalog System i to create a DB2 node for System i to establish the connection from DB2 Connect to System i. Listing 7 shows the syntax.

Listing 7. Syntax to create the DB2 node
db2 catalog tcpip node Node_Name remote IP_Address server Port

Command parameters:

  • Node_Name: The local alias for the node to be cataloged. It can be an arbitrary name on the Information Server Engine tier machine and is used to identify the node.
  • IP_Address: The IP address of the i Series machine where DB2 for System i is located.
  • Port: The DB2 for System i DRDA service port, generally 446, or the port number obtained in "Getting the required information from DB2 for System i."

For example:

Listing 8. Example to create DB2 node
$db2 catalog tcpip node TORI6B01 remote 9.26.103.178 server 446
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.

Catalog to target database on System i

The next step is to catalog the database on DB2 for i for access. Listing 9 shows the syntax.

Listing 9. Syntax to catalog database
db2 catalog db DB_Name_on_System_i as DB_Alias_on_Local at node Node_Name 
authentication Authentication_Mode

Command parameters:

  • DB_Name_on_System_i: The database name on System i, which is the Location Name obtained in "Getting the required information from DB2 for System i."
  • DB_Alias_on_Local: The database alias on the local system. Note that this alias name is used for database connection after catalog to the target database (Location) on DB2 for System i. In case you are using an alias name other than Location name, you need to use this alias name instead of Location name when connecting from DB2 Connect in Information Server to the target database (Location) on DB2 for System i.
  • Node_Name: This must be the same Node_Name as in "Creating a DB2 node for System i" when creating a DB2 node for System i;
  • Authentication_Mode: The default authentication mode is server.

For example:

Listing 10. Example to catalog DB2 for i database
$db2 catalog db DBONB01 as DBONB01 at node TORI6B01 authentication server
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.

Connect to DB2 for i

After the catalog to the target database is made successfully, you should be able to connect to the target database. Listing 11 shows the syntax.

Listing 11. Syntax to connect database
db2 connect to DB_Alias_on_Local user System_i_user_name using Password_for_System_i_user

Command parameters:

  • DB_Alias_on_Local: The same as DB_Alias_on_Local created in "Catalog to target daatabase on System i."
  • System_i_user_name: The user name for accessing the Location on DB2 for System i. This user must have read and write permission on the target Location.
  • Password_for_System_i_user: The password for user for System i.

For example:

Listing 12. Example to connect to DB2 for i database
$db2 connect to DBONB01 user TEST using PASSWORD

Database Connection Information

 Database server        = OS/400 6.1.0
 SQL authorization ID   = TEST
 Local database alias   = DBONB01

Environment setup for Information Server

Information Server connects to the DB2 database on System i using DB2 Connect in this scenario. Thus, the DB2 environment must be readable by Information Server.

In a Windows environment, Information Server gains the DB2 environment information from the system environment. When DB2 Connect or DB2 Enterprise server is installed, the installation creates entries in Windows System Environment Variables > System Variables. However, you can double check to verify that the DB2 library path is set correctly.

Figure 16. Check Windows System Variable
shows variable name LIB and variable_value C:\IBM\SQLLIB\LIB

In a UNIX or Linux environment, Information Server inherits the environment from dsenv, the project environment, and the user login environment. It is strongly recommended to set the DB2 Connect environment from dsenv to avoid unnecessary confusion.

The Library path for DB2 Connect must be set in dsenv. Listing 13 gives an example.

Listing 13. Example for UNIX/Linux Environment Variable
…
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/IBM/db2/V9/lib64
…

Listing 13 is from a Linux environment. Please note that the library path environment variable is different across different UNIX platforms:

  • Solaris and Linux: LD_LIBRARY_PATH
  • HP-UX: SHLIB_PATH
  • AIX: LIBPATH

The Information Server DataStage engine must be restarted after dsenv is modified in order for the environment change to take effect.

Import Plug-in Meta Data table definitions

Now you should be able to connect to the database on DB2 for i. Log on to the Information Server Designer client. From Import > Table Definitions > Plug-in Meta Data Table Definitions…, choose DSDB2.

Figure 17. Import Meta Data table definitions - 1
shows DSDB2 plug-in

Click OK. From the next window, select Server Name from the drop-down list, enter the User Name and Password, and check Tables and Fully Qualified Table Names.

Figure 18. Import Meta Data table definitions - 2
shows DBONB01 as server name, TEST as User name

Click Next and select the table you want to import.

Figure 19. Import Meta Data table definitions - 3
Shows ISTEST.CUST selected

Use DB2 API stage to read and write to database on DB2 for i from DataStage job

You can use the imported table definition to read or write to the table on DB2 for System i through DB2 API stage from a DataStage job.

Figure 20. DB2 UDB API stage - Stage General
Stage tab shows DBONB01 server name, TEST userid
Figure 21. DB2 UDB API stage - Output General
Output tab shows Query type as Generate SELECT Clause from column list; enter other clauses
Figure 22. DB2 UDB API stage - Load Columns
Shows columns tab selected, click on Load
Figure 23. DB2 UDB API stage - Select Columns
Select the columns desired
Figure 24. DB2 UDB API stage - Output Columns
shows the columns with their attributes

Note: You might see the following error when you try to view data from the table. This error can be resolved by removing all derivations from Output > Columns Tab as shown in Figure 24.

Figure 25. Error when viewing data
SQLExecDirect error message
Figure 26. Removing all derivations
From output columns screen, remove all the values in the Derivation column

Run the job.

Figure 27. Complete job running
shwos explorer veiw of jobs on left pane, job running in parallel on the right pane

The previous example is for the read operation from the DB2 API stage. However, writing to DB2 for i is very similar. The only thing that must be kept in mind is that the metadata (schema) is the same as the target table from the input.


Conclusion

Connectivity from Information Server to DB2 for i should not be a complex task. As discussed in this article, the most important things to get through the connection issues are to have the prerequisite components installed and to properly set up the environment.

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, or use a product in a cloud environment.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=931609
ArticleTitle=A complete connectivity guide from InfoSphere Information Server to DB2 for i
publish-date=05302013