DB2 UDB connectivity cheat sheets: Connectivity Cheat Sheat Part 3, iSeries

Setting up connectivity for DB2 UDB for iSeries

Part of a series on DB2® Universal Database™ (DB2 UDB) connectivity, this article focuses on DB2 for iSeries™ connectivity. It covers three scenarios: connecting from DB2 UDB for iSeries clients to DB2 UDB for Linux, UNIX® and Windows® servers, connecting from DB2 UDB for iSeries clients to DB2 UDB for z/OS® subsystem servers, and connecting from DB2 UDB for iSeries clients to DB2 UDB for iSeries servers.

Raul Chong (rfchong@ca.ibm.com), DB2 UDB Consultant, IBM Toronto Lab

Raul F. Chong is a database consultant from the IBM Toronto Laboratory and works primarily with IBM Business Partners. Raul has worked for six years in IBM, three of them in DB2 Technical Support, and three of them as a consultant specializing in database application development and migrations from other RDBMS to DB2.


developerWorks Contributing author
        level

11 December 2003

Introduction

Part 1 of the DB2UDB Connectivity Cheat Sheet series gave you everything you need to know for connecting DB2 UDB Linux, UNIX and Windows clients to DB2 servers on various platforms. Part 2 continued covering more connectivity scenarios where the main focus was DB2 UDB for z/OS V81 as the client. Now in Part 3, we'll continue covering more connectivity scenarios, but this time the main focus is on DB2 UDB for iSeries 5.2 as the client. As in Part 2 of the series, we'll use the latest versions; however, the instructions you see may apply to other versions as well.

In this article I describe three connectivity scenarios:

  • Connecting from a DB2 UDB for iSeries client to a DB2 UDB for Linux,UNIX and Windows server
  • Connecting from a DB2 UDB for iSeries client to a DB2 UDB for z/OS subsystem server
  • Connecting from a DB2 UDB for iSeries client to a DB2 UDB for iSeries server

The only protocol I describe is the TCP/IP protocol, given that it is the one most often used, and we cover only basic setup information.

Note that the DB2 Connect product is not required in these scenarios. DB2 Connect works only in one direction, when a DB2 UDB for Linux, UNIX and Windows client connects to a DB2 UDB for z/OS, iSeries, or VM/VSE server. This connectivity was covered in Part 1 of this article series.


Scenario 1

DB2 UDB for iSeries client to DB2 UDB for Linux, UNIX and Windows server

From DB2 UDB for iSeries client (machine 1) to DB2 UDB for Linux, UNIX and Windows server (machine 2):

Figure 1. The Java Beans view
DB2 UDB for iSeries client to DB2 UDB for Linux, UNIX, Windows Server
Table 1. DB2 UDB for iSeries client to DB2 UDB for Linux, UNIX, Windows Server
Machine 1 ('torisc6') DB2 UDB for iSeriesMachine 2 ('aries') DB2 UDB for Linux, UNIX and Windows
Part I: Configuring iSeries
Step 1
From OS/400 main menu type wrkrdbdire
After pressing enter, this will bring up the Work with relational database directory entries panel.
From the Work with relational database directory entriespanel:
  • Choose Option 1 to Add a database.
  • type the database name: SAMPLE.
SAMPLE is the database in this machine 2 that you want to connect from the iSeries machine. If you don't remember the database name, you can issue from the CLP the command:
list db directory
and look for any entries with a Directory entry type of 'indirect'. These entries would correspond to local databases in your machine.
Step 2
In panel 'Add RDB directory entry', specify:
  • Name or address field: 9.23.190.24
  • Type: *IP

Note: The hostname could have been used instead of the IP address.

After specifying '*IP' for the TCPIP protocol and pressing <enter>, other choices will appear:

  • Port number or Service program: 50000

Notes:
  • The default port number is *DRDA which means port 446.
  • The service name as defined on this machine1 (client) could have been used instead of the port number.
  • The default authentication method is *ENCRYPTED which only works between iSeries machines; thus *USRIDPWD is used.
For this example:
  • 9.23.190.24 = IP address of machine 2
  • 50000 = The port used for DB2

To find out the port used, issue this command from the CLP: get dbm cfg
Then look for the parameter SVCENAME.

If the value of SVCENAME is not the port number but a string, then look in your system for the file 'services' and grep for this string, which is normally based on your DB2 instance name. For example, if you instance name is 'db2inst1', you will normally find a corresponding entry like this:
db2cdb2inst1 50000/tcp

The 'services' file can be located at:

  • /etc/services (in UNIX)
  • X:\WINNT\System32\drivers\etc\services (in Windows)
Part II: Configuring the iSeries user profile
Step 3
For this example we logged on to the iSeries machine with user id of 'jmascare'. We need to change the profile for this user so that the CCSID value is not the default of 65335 but something else like 37 (US English). There is no codepage conversion supported for CCSID 65335.

From the OS/400 main menu type: chqusrprf jmascare
This command will invoke panel 'Change user profile'. Then specify:
- Coded character set id: 37

Part III: Testing the connection
Step 4
Start the Interactive SQL tool from the OS/400 main menu by typing: strsql

From the 'Enter SQL Statements Panel type:
commit <enter>
connect to sample
user db2admin
using 'mypsw' <enter>
select * from db2admin.employee<enter>

Note:
The password need to be passed in single quotes in order to maintain its case. Using double quotes for the user id as shown below also works:
user "db2admin"
using 'mypsw'<enter>

db2admin = user id as defined on machine 2
mypsw = password as defined on machine 2

Scenario 1 setup screens

Figure 1 and Table 1 presented you with a quick overview of what you need for your connectivity configuration. In this section we show you the actual print screens so that you have a better idea of what is involved. Note that the figures match the steps described in Table 1.

Figure 2. Step 1: Invoking wrkrdbdire and adding the SAMPLE database to the iSeries Relational Database Directory entries
Invoking wrdrdbdire and adding the sample database to the RDD entries
Figure 3. Step 2: Adding the hostname/IP address of the machine where the SAMPLE database resides, the port and the authentication method to use
Adding hostname//IP address

Note that in the first window of Figure 3, the port and remote authentication method fields are not displayed until you choose *IP for the TCPIP protocol and press <enter>. The second window of the figure does show these other fields.

Figure 4. Step 3: Invoking chgusrprf (change the user profile) for user id 'jmascare' to change the CCSID to 37
Invoking chgursprf
Figure 5. Step 4: Invoking Interactive SQL (strsql), testing the connection and running a query
Invoking Interactive SQL
Figure 6. Output of the query
Output of the query
Table 2. What to check if you cannot connect
Client Machine 'torisc6'Database Server 'aries'
ping aries.xyz.com
(Assuming the hostname was used instead of the ip address itself in the 'Add RDB directory entry' panel.) This command can be performed from the OS/400 Main menu.
aries.xyz.com = Host name of database server
If cannot ping, there may be problems with the DNS. Try pinging the IP address.
ping 9.23.190.24
This command can be performed from the OS/400 Main menu.
9.23.190.24 = IP address of database server
This will confirm if there are problems or not with the network.
  • Is DB2 started? If not run db2start
  • Is DB2COMM set to TCPIP?
    Check by executing: db2set -all. If this registry variable is not set, you should execute: db2comm=tcpip and then issue a db2stop/db2start to make sure the change takes effect.
  • Is SVCENAME set to the port number or service name specified in the 'services' file of this server machine? Check this parameter from the CLP by issuing:
    get dbm cfg
If you used a service name instead of the port number in your 'Add RDB directory entry' panel, make sure the entry is correct in the 'services' file at the client machine.
netstat
Shows all connections and port numbers and their status. This command can be performed from the OS/400 Main menu, options 6 -> 5 -> 10 -> 7 -> 3
netstat -a -n
Shows all connections and port numbers and their status. Issue this from your command prompt.

Notes:

  • DB2COMM and SVCENAME are set up automatically during the installation of DB2 UDB for Linux, UNIX and Windows for the default instance. Any other new instance created after installation with the db2icrt command, will not have these parameters set up.
  • DB2 will check the 'services' file in the machine where the DB2 command is issued.

Scenario 2

From DB2 UDB for iSeries client (machine 1) to DB2 UDB for z/OS server (machine 2)

Figure 7. DB2 UDB for iSeries client to DB2 UDB for z/OS Server
DB2 UDB for iSeries client to DB2 UDB for z/OS Server
Table 3. DB2 UDB for iSeries client to DB2 UDB z/OS Server
Machine 1 ('torisc6') DB2 UDB for iSeriesMachine 2 ('tlba22me') DB2 UDB for z/OS
Part I: Configuring iSeries
Step 1
From OS/400 main menu type wrkrdbdire
After pressing Enter, this will bring up the Work with relational database directory entries panel.
From the Work with relational database directory entriespanel:
  • Choose Option 1 to Add a database.
  • type the database name: NEW_JERSEY.
NEW_JERSET = The LOCATION NAME of the DB2 UDB for z/OS subsystem you want to access on machine 2.

To find out the LOCATION NAME, contact your DB2 for z/OS DBA who can check the MVS syslog for message DSNL0041. "LOCATION" in that message contains the "LOCATION NAME" to use. Also, the 'DISPLAY DDF command provides this info.

Step 2
In panel 'Add RDB directory entry', specify:
  • Name or address field: tlba22me.torolab.ibm.com
  • Type: *IP

Note: The IP address could have been used instead of the hostname.

After specifying '*IP' for the TCPIP protocol and pressing <enter>, other choices will appear:

  • Port number or Service program: 448
  • Preferred method: *USRIDPWD

Note: The service name as defined on the machine 1 (client could have been used instead of the port number.

Versions other than the DB2 UDB for iSeries 5.2 may not have some of the above choices.

For this example:
  • tlba22me.torolab.ibm.com = Hostname of machine 2
  • 448 = The port used for DB2

Though the example shows port 448, port 446 is normally the default value. To find out the port used, contact your DB2 for OS/390 and z/OS DBA who chan check the MVS syslog for message DSNL0041. "TCPPORT" in that message contains the port to use. Also, the -DISPLAY DDF command provides this info.

Part II: Configuring the user profile
Step 3
For this example we logged on to the iSeries machine with user id of 'jmascare'. We need to change the profile for this user so that the CCSID value is not the default of 65335 but something else like 37 (US English). There is no codepage conversion supported for CCSID 65335.

From the OS/400 main menu type: chqusrprf jmascare
This command will invoke panel 'Change user profile'. Then specify:
- Coded character set id: 37

Part III: Testing the connection
Step 4
Start the Interactive SQL tool from the OS/400 main menu by typing: strsql

From the 'Enter SQL Statements Panel type:
commit <enter>
connect to NEW_JERSEY
user ts56692
using 'tsopsw' <enter>
select * from dsn8810.emp<enter>

Notes:
The password need to be passed in single quotes in order to maintain its case. Using double quotes for the user id as shown below also works:
connect to NEW_JERSEY user ts56692
using 'tsopsw'<enter>

The SELECT query is using the sample table dsn8810.emp for version 8. If connecting to a DB2 UDB for OS/390 and z/OS version 7 subsystem, use table dsn8710.emp.

ts56692 = user id as defined on machine 2
tsopsw = password as defined on machine 2

Scenario 2 setup screens

Figure 7 and Table 3 presented you with a quick overview of what is needed for your connectivity configuration. In this section we show you the actual print screens so that you have a better idea of what is involved. Note that the figures match the steps described in Table 3.

Figure 8. Step 1: Invoking wrkrdbdire and adding the NEW_JERSEY subsystem to the iSeries Relational Database Directory Entries
Invoking wrkrdbdire
Figure 9. Step 2: Adding the hostname/IP address of the machine where the NEW_JERSEY subsystem resides, the port and the authentication method to use
Adding hostname/IP address

Note that in the first window of Figure 9, the port and remote authentication method fields are not displayed until you choose *IP for the TCPIP protocol and press <enter>. The second window of the figure does show these other fields.

Step 3 to configure the iSeries user profile to use a CCSID of 37 is exactly the same as in Scenario 1; therefore we will not show the corresponding figures in this section.

Figure 10. Step 4: Invoking Interactive SQL (strsql), testing the connection and running a query
Invoking Interactive SQL
Figure 11. Output of the query
Output of the query
Table 4. What to check if you cannot connect
Client Machine 'torisc6'Database Server 'tlba22me'
ping tlba22me.torolab.ibm.com
(Assuming the hostname was used instead of the ip address itself in the 'Add RDB directory entry' panel.) This command can be performed from the OS/400 Main menu.
tlba22me.torolab.ibm.com = Host name of database server
If cannot ping, there may be problems with the DNS. Try pinging the IP address.
ping 158.228.50.34
This command can be performed from the OS/400 Main menu.
158.228.50.34 = IP address of database server
This will confirm if there are problems or not with the network.
Since the database server is DB2 UDB for OS/390 and z/OS, check:
  • Is DB2 started? If not, execute -start db2t
  • Is DDF started? If not, execute -start ddf
If you used a service name instead of the port number in your 'Add RDB directory entry' panel, make sure the entry is correct in the 'services' file at the client machine.
netstat
Shows all connections and port numbers and their status. This command can be performed from the OS/400 Main menu, options 6 -> 5 -> 10 -> 7 -> 3
netstat -a -n
Shows all connections and port numbers and their status. Issue this commane from 'option 6 Command' in the main ISPF menu.

Note:

In DB2 for OS/390 and z/OS you always connect to the subsystem, not to a specific database, thus, in Figure 7 we do not use the database name 'blue42d1' in any of the commands, but the DB2 for z/OS LOCATION NAME 'NEW_JERSEY'.


Scenario 3

DB2 UDB for iSeries client to DB2 UDB iSeries server

Figure 12.DB2 UDB for iSeries client to DB2 UDB for iSeries server
DB2 UDB for iSeries client to DB2 UDB for iSeries server
Table 5. DB2 UDB for iSeries client to DB2 UDB for iSeries server
Machine 1 ('torisc6') DB2 UDB for iSeriesMachine 2 ('mytorisc') DB2 UDB for iSeries
Part I: Configuring iSeries
Step 1
From OS/400 main menu type wrkrdbdire
After pressing Enter, this will bring up the Work with relational database directory entries panel.
From the Work with relational database directory entriespanel:
  • Choose Option 1 to Add a database.
  • type the database name: TORISC6B.
TORISC6B = The local RDB name you want to access on machine 2. In order to determine the local RDB name , contact your iSeries administrator who can issue the command WRKRDBDIRE. When the 'Work with Relational Database Directory Entries' panel appears, he can find the desired value in column 'Relational Database' that maps to the column 'Remote Location' with a value of '*LOCAL'.
Step 2
In panel 'Add RDB directory entry', specify:
  • Name or address field: 9.23.168.62
  • Type: *IP

Note: The hostname address could have been used instead of the IP address.

After specifying '*IP' for the TCPIP protocol and pressing <enter>, other choices will appear:

  • Port number or Service program: *DRDA
  • Preferred method: *ENCRYPTED

Note:
*DRDA is equivalent to entering port 446. This value is very unlikely to be changed.

*ENCRYPTED authetication works between iSeries machines.

Versions other than DB2 UDB for iSeries 5.2 may not have some of the above choices.

For this example:
  • 9.23.168.62 = IP address of machine 2
  • *DRDA = The port used for DB2

*DRDA is equivalent to port 446 and is the default value.

Part II: Testing the connection
Step 3
Start the Interactive SQL on to the iSeries machine with user id of 'jmascare'. We need to change the profile for this user so that the CCSID value is not the default of 65335 but something else like 37 (US English). There is no codepage conversion supported for CCSID 65335.

From the OS/400 main menu type: chqusrprf jmascare
This command will invoke panel 'Change user profile'. Then specify:
- Coded character set id: 37

Part III: Testing the connection
Step 4
Start the Interactive SQL tool from the OS/400 main menu by typing: strsql

From the 'Enter SQL Statements Panel type:
commit <enter>
connect to TORISC6B
user johntest
using 'psw400' <enter>
select * from QIWS.QCUSTCDT<enter>

Note:
The password need to be passed in single quotes in order to maintain its case. Using double quotes for the user id as shown below also works:
connect to TORISC6B user johntest
using 'psw400'<enter>

johntest = user id as defined on machine 2
psw400 = password as defined on machine 2

Scenario 3 setup screens

Figure 13. Step 1: Invoking wrkrdbdire and adding the TORISC6B subsystem to the iSeries Relational Database Directory Entries
Invoking workrdbdire
Figure 14.Step 2: Adding the hostname/IP address of the machine where the TORISC6B database resides, the port and the authentication method to use
The Java Beans view

Note that in the first window of figure 14, the port and remote authentication method fields are not displayed until you choose *IP for the TCPIP protocol and press <enter>. The second window of the figure does show these other fields.

Figure 15. Step 3: Invoking Interactive SQL (strsql), testing the connection and running a query
Invoking Interactive SQL
Figure 16. Output of the query
Output of the query
Table 6. What to check if you cannot connect
Client Machine 'torisc6'Database Server 'mytorisc'
ping mytorisc.mkm.ibm.com
(Assuming the hostname was used instead of the ip address itself in the 'Add RDB directory entry' panel.) This command can be performed from the OS/400 Main menu.
mytorisc.mkm.ibm.com = Host name of database server
If cannot ping, there may be problems with the DNS. Try pinging the IP address.
ping 9.23.168.62
This command can be performed from the OS/400 Main menu.
9.12.168.62 = IP address of database server
This will confirm if there are problems or not with the network.
Since the database server is DB2 UDB for iSeries, check:
Is DDM started? If not, execute:
STRTTCPSVR SERVER (*DDM)
If you used a service name instead of the port number in your 'Add RDB directory entry' panel, make sure the entry is correct in the 'services' file at the client machine.
netstat
Shows all connections and port numbers and their status. This command can be performed from the OS/400 Main menu, options 6 -> 5 -> 10 -> 7 -> 3
netstat
Shows all connections and port numbers and their status. This command can be performed from the OS/400 Main menu, options 6 -> 5 -> 10 -> 7 -> 3

What's next?

Part 4 of this series describes connectivity scenarios from an application perspective using CLI, ODBC and JDBC. Part 5 of the series covers connectivity scenarios for applications written with ADO and ADO.NET.


Footnote

1At the time this article was written, DB2 UDB for z/OS version 8 had not yet been released for general availability. DB2 for OS/390 and z/OS v7 and a beta version of V8 were used to test the scenarios.


Acknowledgment

I am very grateful of the time and input provided by John Mascarenhas of the IBM DB2 UDB for iSeries Application Enabling Support Systems team. Without his help, it would have been a lot more difficult to write this article.

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, IBM i
ArticleID=90237
ArticleTitle=DB2 UDB connectivity cheat sheets: Connectivity Cheat Sheat Part 3, iSeries
publish-date=12112003