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
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,
WRKSRVTBLEcommand 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
- Location: The name of the relational database on System i that you
want to access. To get the location name, use the
DSPRDBDIREcommand 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
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.
- 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
- Select IBM DB2 Wire Protocol.
Figure 5. DB2 Wire Protocol driver
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
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:
- 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
- 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=126.96.36.199 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.
- 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.
- 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:
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
- 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
- From the next window, select the table you want to import.
Figure 8. Import ODBC table definitions - 2
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.
- Start with the ODBC Connector. Click Load.
Figure 9. ODBC Connector
- 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
- Select the columns you want to use, moving them to the Selected
Figure 11. Select columns
- The next screen (Figure 12) shows column details.
Figure 12. Column details
- 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
- Run the DataStage job from the InfoSphere DataStage Designer.
Figure 14. Complete 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 -lcommand. Figure 15 gives an example.
Figure 15. Check DB2 product on Windows platform
- 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
db2licmto 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
- 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."
Listing 8. Example to create DB2 node
$db2 catalog tcpip node TORI6B01 remote 188.8.131.52 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
- 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.
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
- 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.
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
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
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
Click Next and select the table you want to import.
Figure 19. Import Meta Data table definitions - 3
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
Figure 21. DB2 UDB API stage - Output General
Figure 22. DB2 UDB API stage - Load Columns
Figure 23. DB2 UDB API stage - Select Columns
Figure 24. DB2 UDB API stage - Output Columns
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
Figure 26. Removing all derivations
Run the job.
Figure 27. Complete job running
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.
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.
- If you are on a 64-bit Windows environment, refer to the Microsoft Support document "942976".
- Refer to the IBM DB2 Information Center for details on installing DB2 Connect or DB2 Enterprise Server.
- Read the IBM Redbook "IBM InfoSphere Information Server Deployment Architectures.
- Read more developerWorks articles about InfoSphere Information Server.
- Follow developerWorks on Twitter.
- Watch developerWorks on-demand demos ranging from product installation and set-up demos for beginners to advanced functionality for experienced developers.
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.
- Participate in the discussion forum.
- Discuss DB2 on the System i platform at the DB2 for i forum.
- Get involved in the developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.