![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
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
Table 1. DB2 UDB for iSeries client to DB2 UDB for Linux, UNIX, Windows Server
| Machine 1 ('torisc6') DB2 UDB for iSeries | Machine 2 ('aries') DB2 UDB for Linux, UNIX and Windows |
|---|---|
| Part I: Configuring iSeries | |
| Step 1 From OS/400 main menu type wrkrdbdireAfter pressing enter, this will bring up the Work with relational database directory entries panel. | |
From the Work with relational database directory
entriespanel:
| 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 directoryand 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:
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:
Notes:
| For this example:
To find out the port used, issue this command from the CLP: get dbm cfgThen 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: The
'services' file can be located at:
|
| 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: | |
| Part III: Testing the connection | |
| Step 4 Start the Interactive SQL tool from the OS/400 main menu by typing: strsqlFrom the 'Enter SQL Statements Panel type: Note: | db2admin = user id as defined on machine 2 mypsw = password as defined on machine 2 |
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
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
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
Figure 5. Step 4: Invoking Interactive SQL (strsql), testing the connection and running a query
Figure 6. 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. |
| |
| 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.
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
Table 3. DB2 UDB for iSeries client to DB2 UDB z/OS Server
| Machine 1 ('torisc6') DB2 UDB for iSeries | Machine 2 ('tlba22me') DB2 UDB for z/OS |
|---|---|
| Part I: Configuring iSeries | |
| Step 1 From OS/400 main menu type wrkrdbdireAfter pressing Enter, this will bring up the Work with relational database directory entries panel. | |
From the Work with relational database directory
entriespanel:
| 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:
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:
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:
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: | |
| Part III: Testing the connection | |
| Step 4 Start the Interactive SQL tool from the OS/400 main menu by typing: strsqlFrom the 'Enter SQL Statements Panel type: Notes: 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 |
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
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
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
Figure 11. 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:
| |
| 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'.
DB2 UDB for iSeries client to DB2 UDB iSeries server
Figure 12.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 iSeries | Machine 2 ('mytorisc') DB2 UDB for iSeries |
|---|---|
| Part I: Configuring iSeries | |
| Step 1 From OS/400 main menu type wrkrdbdireAfter pressing Enter, this will bring up the Work with relational database directory entries panel. | |
From the Work with relational database directory
entriespanel:
| 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:
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:
Note: *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:
*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: | |
| Part III: Testing the connection | |
| Step 4 Start the Interactive SQL tool from the OS/400 main menu by typing: strsqlFrom the 'Enter SQL Statements Panel type: Note: | johntest = user id as defined on machine 2 psw400 = password as defined on machine 2 |
Figure 13. Step 1: Invoking wrkrdbdire and adding the TORISC6B subsystem to the iSeries Relational Database Directory Entries
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
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
Figure 16. 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 |
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.
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.
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.
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.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
