Contents
- Introduction
- The Communications Database (CDB)
- Scenario 1 - DB2 UDB for z/OS client to DB2 UDB for Linux, UNIX and Windows server
- Scenario 2 - DB2 UDB for z/OS client to DB2 UDB for iSeries server
- Scenario 3 - DB2 UDB for z/OS client to DB2 UDB for z/OS server
- Acknowledgments
- Footnotes
- Downloadable resources
- Related topic
- Comments
Connectivity Cheat Sheet for DB2 Universal Database Part 2 - DB2 for z/OS
Part 1 of the DB2 UDB Connectivity Cheat Sheet gave you everything you need to know for connecting to DB2® Universal DatabaseTM for Linux, UNIX® and Windows® clients to DB2 servers on various platforms. This article continues covering more connectivity scenarios involving DB2 UDB for Linux, UNIX and Windows version 8, DB2 UDB for iSeries® 5.2 and DB2 for z/OS® V8*. As in the first part of the article, the latest versions are being used; however, the instructions shown may apply to other versions as well. The focus of this article will be on DB2 UDB for OS/390© and z/OS. In this article we will describe three connectivity scenarios:
- From DB2 UDB for z/OS client to DB2 UDB for Linux, UNIX and Windows server
- From DB2 UDB for z/OS client to DB2 UDB for iSeries server
- From DB2 UDB for z/OS client to DB2 UDB for z/OS server
The only protocol described is the TCP/IP protocol given that it is the one most often used. Only basic setup information will be shown.
Note that the DB2 ConnectTM software is not required in these scenarios. This software is only required 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.
The Communications Database (CDB)
Before describing the different connectivity scenarios, we need to explain what the Communications Database is. The CDB consists of several updatable system tables where connectivity information is stored. In previous versions, it used to be a separate database; currently, it is part of the DB2 UDB for z/OS Catalog. The CDB tables are only used by DB2 UDB for z/OS when it behaves as a client (Application Requester). Figure 1 shows the relevant CDB tables that are used for a TCP/IP connection with several rows inserted.
Figure 1. The CDB Tables for a TCP/IP connection

In Figure 1, I have highlighted in color a few rows to briefly explain two common cases which will help you understand how to populate the CDB tables.
Let's make this explanation as simple as possible. In the first case, the DB2 UDB for z/OS client is trying to connect to the SAMPLE database. DB2 for z/OS will first join the first row of SYSIBM.LOCATIONS with the first row of SYSIBM.IPNAMES given that the LINKNAME column is the same (cell in yellow):

Thus, DB2 UDB for z/OS client will use IP address 9.26.93.234 and port 50000. Also, because SECURITY_OUT = 'A' (already verified), it will only pass the user id to the server given that authentication has already been performed at the client when logging on to TSO. Note as well that the USERNAMES column is blank. This should normally be the case when SECURITY_OUT = 'A' (therefore we have colored both cells in green), and it means there is no need to look into the SYSIBM.USERNAMES table. At the DB2 UDB for Linux, UNIX and Windows server, the database manager configuration parameter AUTHENTICATION must be set to CLIENT in order for these settings to work.
For the second case, when the DB2 UDB for z/OS client is trying to connect to the SAMPLE2 database, DB2 for z/OS will join the second row of SYSIBM.LOCATIONS, the second row of SYSIBM.IPNAMES and the first row of SYSIBM.USERNAMES given that the LINKNAME column is the same (cell in orange):

Thus, DB2 UDB for z/OS client will use IP address 9.23.190.25 and port 50000. Also, because SECURITY_OUT = 'P', it will pass a user id and a password to the server since authentication will be performed at the server. Note as well that the USERNAMES column has a value of 'O'. This should normally be the case when SECURITY_OUT = 'P' (therefore we have colored both cells in purple) and it means there is a need to look into the SYSIBM.USERNAMES table for the user id and password. Shown in blue you can see the mapping between the TSO id and the id and password combination that will be passed to the server. At the DB2 UDB for Linux, UNIX and Windows server, the database manager configuration parameter AUTHENTICATION must be set to SERVER in order for these settings to work.
For this second example, we assumed the DB2 UDB for Linux, UNIX and Windows server had a database called 'SAMPLE' as well (not SAMPLE2); however, since the LOCATION column of the SYSIBM.LOCATIONS table is a primary key, and since there was already an entry for 'SAMPLE' for the first row of SYSIBM.LOCATIONS (used in the first example), then the only way to put an entry in the table is by first creating an alias to the database in the DB2 UDB for Linux, Unix and Windows server as follows:
db2 catalog db sample as sample2
and then, you could add an entry in SYSIBM.LOCATIONS for 'SAMPLE2'.
Thus far we have presented a simplified version of how to read and populate the CDB tables. If you need further details, please refer to the DB2 UDB for OS/390 and z/OS manuals. Now let's discuss three different connectivity scenarios.
Scenario 1 - DB2 UDB for z/OS client to DB2 UDB for Linux, UNIX and Windows server
From DB2 UDB for z/OS client (machine 1) to DB2 UDB for Linux, UNIX and Windows server (machine 2)
Figure 2. DB2 UDB for z/OS client to DB2 UDB for Linux, UNIX and Windows Server

Table 1. DB2 UDB for z/OS client to DB2 UDB for Linux, UNIX and Windows Server
Machine 1 ('tlba22me') DB2 UDB for z/OS client | Machine 2 ('aries') DB2 UDB for Linux, UNIX and Windows server | ||||
---|---|---|---|---|---|
Commands to run on this machine: | Information you need to obtain from this machine, to perform the commands on machine 1: | ||||
Part I: Configuring the CDB (Communications Database) | |||||
Option 1:insert into SYSIBM.LOCATIONS (location, linkname,port) values ('SAMPLE',''MYUDBLNK', '50000') insert into SYSIBM.IPNAMES (linkname, security_out, ipaddr ) values (''MYUDBLNK','A', '9.26.93.234' ) Notes: MYUDBLNK is an arbitrary name used to link table
SYSIBM.LOCATIONS with table SYSIBM.IPNAMES. Option 2: insert into SYSIBM.LOCATIONS (location, linkname,port) values ('SAMPLE','MYUDBLNK', '50000') insert into SYSIBM.IPNAMES (linkname, security_out, usernames, ipaddr ) values ('MYUDBLNK', 'P', 'O ', '9.26.93.234' ) insert into SYSIBM.USERNAMES (type, authid, linkname, newauthid, password) values ('O', 'TS56692',''MYUDBLNK', 'db2admin','mypsw') Notes: A value of 'P' for the security_out column implies that authentication will be performed at the server machine 2. TS56692 is the TSO id on this mainframe machine 1 client. |
| ||||
To make sure the changes to the CDB take effect, restart DDF
(-stop ddf -start ddf ).This may not be necessary if you have entered a new entry in the CDB. | |||||
Part II: Bind SPUFI | |||||
BIND PACKAGE (SAMPLE.DSNESPCS) MEMBER(DSNESM68) LIBRARY ('SHARE.DSN710.PROD.SDSNDBRM') ACTION(REPLACE) ISOLATION (CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND PACKAGE (SAMPLE.DSNESPRR) MEMBER(DSNESM68) LIBRARY ('SHARE.DSN710.PROD.SDSNDBRM') ACTION(REPLACE) ISOLATION (CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND PLAN (DSNESPCS) PKLIST (*.DSNESPCS.DSNESM68) ISOLATION(CS) ACTION(REPLACE) BIND PLAN (DSNESPRR) PKLIST (*.DSNESPRR.DSNESM68) ISOLATION(CS) ACTION(REPLACE) Notes: DSNESPCS is the package to bind for the SPUFI application with isolation Cursor Stability. DSNESPRR would be for isolation Repeatable Read. The library specified in the bind package command, contains DBRM member DSNESM68 (for the SPUFI application). This library location will vary depending on how DB2 was set up in your system. After the packages have been bound against the server machine 2, the PLAN has to be bound. Using * in the package list guarantees the plan is bound in all locations. | SAMPLE is the database in this server machine 2 that
you want to connect from the z/OS client machine. The user id performing the bind should have been granted the appropriate authorization/privileges. | ||||
Part III: Testing the connection from SPUFI | |||||
Make sure to specify: - Connect Location field: SAMPLE Then issue: select * from db2admin.employee Note: There is no connect statement issued from SPUFI, but there is a specific field where you put the location you want to connect to. Note as well that the userid and psw are stored in the CDB. | When configuring the CDB using option 1 (when column
'security_out' is set to 'A'), you would be passing the TSO id
to the DB2 UDB for Linux, Unix and Windows server. For this
example the TSO id is TS56692. Thus in order to access a table
for 'select', you would need to do this:GRANT select on db2admin.employee to user TS56692 |
Connectivity configuration setup print screens
Figure 2and Table 1 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. We will only show the print screens for the CDB 'option 1' .
Figure 3. Setting AUTHENTICATION = CLIENT on the DB2 UDB for Linux, UNIX and Windows server

Figure 4. Perform the insert statements from SPUFI

Note: If you had a connection from the workstation to the mainframe, the statements could have been executed from the CLP too.
Figure 5. - Bind SPUFI - Binding the package

Note that the library specified in the command below is specific to your system.
Figure 6. Bind SPUFI - Binding the package (output)

Figure 7. Bind SPUFI - Binding the plan

Figure 8. Bind SPUFI - Binding the plan (output)

Figure 5 through 8 only display the steps required for binding package/plan DSNESPCS. The same process must be followed for package/plan DSNESPRR.
Figure 9. Testing the connection using SPUFI

Note in this figure the CONNECT LOCATION field. This is where you input the database/location you want to connect to.
Figure 10. Testing the connection using SPUFI (continued)

Table 2. Typical errors
Error Message | Resolution |
---|---|
SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME SAMPLE..DSNESM68.149EEA901A79FE48 NOT FOUND IN PLAN DSNESPCS. REASON 02 | You need to bind the package and the plan for SPUFI as explained in table 1. |
SQLCODE = -551, SQLSTATE = 42501, SYNTAX ERROR OR ACCESS RULE VIOLATION FROM OS/2 TOKENS TS56692 SELECT DB2ADMIN.EMPLOYEE | For option 1 of the CDB setup, you are passing only the user id
(TSO id), which for this example is TS56692. You need to grant
authorization to this user to query the db2admin.employee table.
From the CLP issue:grant select on db2admin.employee to user ts56692 |
Besides the typical errors shown above, other things to check are mentioned in table 3.
Table 3. What to check if you cannot connect:
Client Machine 'tlba22me' | Database Server 'aries' |
---|---|
ping aries.xyz.com (Assuming the hostname was used instead of the ip address itself in the SYSIBM.IPNAMES table. This command can be performed from the TSO Command Processor |
|
ping 9.26.93.234 This command can be performed from the TSO Command Processor |
|
| |
If you used a service name instead of the port number in your table SYSIBM.LOCATIONS, 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 TSO Command Processor. | 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 - DB2 UDB for z/OS client to DB2 UDB for iSeries server
From DB2 UDB for z/OS client (machine 1) to DB2 UDB for iSeries server (machine 2). For this scenario, we only tested with one configuration for the CDB; however you are encouraged to try other setups.
Figure 11. DB2 UDB for z/OS client to DB2 UDB for iSeries server

Table 4. DB2 UDB for z/OS client to DB2 UDB for iSeries server
Machine 1 ('tlba22me') DB2 UDB for z/OS | Machine 2 ('big400') DB2 UDB for iSeries | |||||
---|---|---|---|---|---|---|
Commands to run on this machine: | Information you need to obtain from this machine, to perform the commands on machine 1: | |||||
Part I: Configuring the CDB (Communications Database) | ||||||
insert into SYSIBM.LOCATIONS (location, linkname,port) values ('TORISC6',''MY400LNK', '446') insert into SYSIBM.IPNAMES (linkname, security_out, usernames, ipaddr) values (''MY400LNK','P',' 9.26.93.234' ) insert into SYSIBM.USERNAMES (type, authid, linkname, newauthid, password) values ('O', 'TS56692','MY400LNK', 'john01','psw400') Notes: MY400LNK is an arbitrary name used to link tables SYSIBM.LOCATIONS, SYSIBM.IPNAMES and SYSIBM.USERNAMES. A value of 'P' for the security_out column implies that authentication at the server machine 2. TS56692 is the TSO id on this mainframe machine 1 client. |
| |||||
To make sure the changes to the CDB take effect, restart DDF
(-stop ddf -start ddf ).This may not be necessary if you have entered a new entry in the CDB. | ||||||
Part II: Bind SPUFI | ||||||
BIND PACKAGE ( TORISC6.DSNESPCS) MEMBER(DSNESM68) LIBRARY ('SHARE.DSN710.PROD.SDSNDBRM') ACTION(REPLACE) ISOLATION (CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND PACKAGE (TORISC6.DSNESPRR) MEMBER(DSNESM68) LIBRARY ('SHARE.DSN710.PROD.SDSNDBRM') ACTION(REPLACE) ISOLATION (CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND PLAN (DSNESPCS) PKLIST (*.DSNESPCS.DSNESM68) ISOLATION(CS) ACTION(REPLACE) BIND PLAN (DSNESPRR) PKLIST (*.DSNESPRR.DSNESM68) ISOLATION(CS) ACTION(REPLACE) Notes: DSNESPCS is the package to bind for the SPUFI application with isolation Cursor Stability. DSNESPRR would be for isolation Repeatable Read. The library specified in the bind package command, contains DBRM member DSNESM68 (for the SPUFI application). This library location will vary depending on how DB2 was set up in your system. After the packages have been bound against the server machine 2, the PLAN has to be bound. Using * in the package list guarantees the plan is bound in all locations. | TORISC6 = The local RDB Name. In order to bind the packages, you first need to create the collections: And grant iSeries user john01 the appropriate authorization/privileges against the collection. | |||||
Part III: Testing the connection from SPUFI | ||||||
Make sure to specify: - Connect Location field: TORISC6 Then issue: SELECT * FROM QIWS.QCUSTCDT Note: There is no connect statement issued from SPUFI, but there is a specific field where you put the location you want to connect to. Note as well that the userid and psw are stored in the CDB. Issue this query for testing purposes. The sample table QIWS.QCUSTCDT is normally available after installation of iSeries unless it was removed or not set up by your iSeries administrator. | GRANT select on QIWS.QCUSTCDT to user john01 Also most tables in iSeries are automatically journalled, but QCUSTCDT sample table is not, so make sure to journal it. |
Connectivity configuration setup print screens.
Figure 11 and Table 4 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.
Figure 12. Perform the insert statements from SPUFI

Figure 13. Bind SPUFI - Binding the package

Figure 13 only shows the command for DSNESPRR, but it would be similar for DSNESPCS.
Figure 14. Bind SPUFI - Binding the plan

Figure 14 only shows the command for DSNESPCS, but it would be similar for DSNESPRR.
Figure 15. Testing the connection using SPUFI

Figure 16. Testing the connection using SPUFI (continued)

Table 5. Typical errors (in addition to the ones for table 2
Error Message | Resolution |
---|---|
SQLCODE = -204, SQLSTATE = 42704, SQLERRMT = QSYS.DSNESPRR: COLLECTION | If you get this error when binding the package, you first need to
create the collection in iSeries. From STRSQL issue:CREATE COLLECTION DSNESPRR and CREATE COLLECTION DSNESPCS (for the
other package) |
SQLCODE = -7008, SQLSTATE = 55019, OBJECT NOT IN PREREQUISITE STATE FROM OS/400 TOKENS QCUSTCDT QIWS | Table QCUSTCDT needs to be journalled. |
Table 6. What to check if you cannot connect
Client Machine 'tlba22me' | Database Server 'big400' |
---|---|
ping big400.ca.ibm.com (Assuming the hostname was used instead of the ip address itself in the SYSIBM.IPNAMES table. This command can be performed from the TSO Command Processor |
|
ping 9.89.168.6 |
|
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 table SYSIBM.LOCATIONS, 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 TSO Command Processor. | 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 |
Scenario 3 - DB2 UDB for z/OS client to DB2 UDB for z/OS server
From DB2 UDB for z/OS client (machine 1) to DB2 UDB for z/OS server (machine 2). For this scenario, we only tested with one configuration for the CDB, however you are encouraged to try other setups.
Figure 17. UDB for z/OS client to DB2 UDB for z/OS server

Table 7. DB2 UDB for z/OS client to DB2 UDB for z/OS Server
Machine 1 ('tlba22me') DB2 UDB for z/OS | Machine 2 ('tlba23me') DB2 UDB for z/OS | ||||
---|---|---|---|---|---|
Commands to run on this machine: | Information you need to obtain from this machine, to perform the commands on machine 1: | ||||
Part I: Configuring the CDB (Communications Database) | |||||
insert into SYSIBM.LOCATIONS (location, linkname,port) values ('MEXICO',''MY390LNK', '447') insert into SYSIBM.IPNAMES (linkname, security_out, usernames, ipaddr) values (''MYUDBLNK','P','O', '158.228.20.3' ) insert into SYSIBM.USERNAMES (type, authid, linkname, newauthid, password) values ('O', 'TS56692',''MY390LNK', 'tso1234','tsopsw') Notes: MY390LNK is an arbitrary name used to link tables SYSIBM.LOCATIONS, SYSIBM.IPNAMES and SYSIBM.USERNAMES. A value of 'P' for the security_out column implies that authentication will be performed at the server machine 2. TS56692 is the TSO id on this mainframe machine 1 client. |
| ||||
To make sure the changes to the CDB take effect, restart DDF
(-stop ddf -start ddf ).This may not be necessary if you have entered a new entry in the CDB. | |||||
Part II: Bind SPUFI | |||||
BIND PACKAGE (MEXICO.DSNESPCS) MEMBER(DSNESM68) LIBRARY ('SHARE.DSN710.PROD.SDSNDBRM') ACTION(REPLACE) ISOLATION (CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND PACKAGE (MEXICO.DSNESPRR) MEMBER(DSNESM68) LIBRARY ('SHARE.DSN710.PROD.SDSNDBRM') ACTION(REPLACE) ISOLATION (CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND PLAN (DSNESPCS) PKLIST (*.DSNESPCS.DSNESM68) ISOLATION(CS) ACTION(REPLACE) BIND PLAN (DSNESPRR) PKLIST (*.DSNESPRR.DSNESM68) ISOLATION(CS) ACTION(REPLACE) Notes: DSNESPCS is the package to bind for the SPUFI application with isolation Cursor Stability. DSNESPRR would be for isolation Repeatable Read. The library specified in the bind package command, contains DBRM member DSNESM68 (for the SPUFI application). This library location will vary depending on how DB2 was set up in your system. After the packages have been bound against the server machine 2, the PLAN has to be bound. Using * in the package list guarantees the plan is bound in all locations. | MEXICO is the location name for the DB2 UDB for z/OS
subsystem in this machine 2 that you want to connect from the
other DB2 UDB for z/OS client. The user id performing the bind should have been granted the appropriate authorization/privileges. This may also be required to run the packages: GRANT all on package | ||||
Part III: Testing the connection from SPUFI | |||||
Make sure to specify: - Connect Location field: MEXICO Then issue: select * from dsn8810.emp Note: There is no connect statement issued from SPUFI, but there is a specific field where you put the location you want to connect to. Note as well that the userid and psw are stored in the CDB. Issue this query for testing purposes. The sample table dsn8810.emp is normally available after installation of DB2 for OS/390 and z/OS unless it was removed or not set up by your mainframe DBA. The example uses version 8 'emp' table. If connecting to a DB2 UDB for OS/390 and z/OS version 7 subsystem, use table dsn8710.emp instead. | Make sure the user executing the query has the appropriate
authorization/privilege. Eg:GRANT select on dsn8810.emp to
user tso1234 |
Connectivity configuration setup print screens.
Figure 17 and Table 7 presented you with a quick overview of what is needed for your connectivity configuration. In this section we will show you the actual print screens so that you have a better idea of what is involved.
Figure 18. Perform the insert statements from SPUFI at the DB2 UDB for z/OS client

Note: If you had a connection from the workstation to the mainframe, the statements could have been executed from the CLP as well).
Figure 19. Bind SPUFI - Binding the package

Figure 20. Bind SPUFI - Binding the plan

Figure 21. Testing the Connection using SPUFI

Note in figure 21 the CONNECT LOCATION field. This is where you input the database/location you want to connect to.
Figure 22. Testing the connection using SPUFI (continued)

The query shown in figure 22 will work assuming 'TS56692' id has authorization to run the SPUFI package DSNESPCS.DSNESM68. Otherwise, you need to run this SQL statement:
GRANT all on package DSNESPCS.DSNESM68 to ts56692
Table 8. What to check if you cannot connect
Client Machine 'tlba22me' | Database Server 'tlba23me' |
---|---|
ping tlba23me.torolab.ibm.com (Assuming the hostname was used instead of the ip address itself in the SYSIBM.IPNAMES table. This command can be performed from the TSO Command Processor |
|
ping 158.228.20.3 This command can be performed from the TSO Command Processor |
|
| |
If you used a service name instead of the port number in your table SYSIBM.LOCATIONS, 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 TSO Command Processor. | netstat Shows all connections and port numbers and their status. This command can be performed from the TSO Command Processor. |
Note:
DB2 for OS/390 and z/OS supports two protocols:
- DRDA: This is the recommended protocol, and the one for which the instructions of this article apply. The application uses a CONNECT statement, a three-part name, or an alias (if bound with DBPROTOCOL (DRDA) to access the server.
- DB2 private protocol: This protocol is being phased out and can only be used among DB2 UDB for OS/390 and z/OS client and servers. The application must connect using an alias or three-part name to direct the SQL statement to a given location.
Acknowledgments
Special thanks to John Mascarenhas of the DB2 UDB for iSeries Application Enabling Support Systems team for his time and feedback about this document.
Footnotes
* At 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.
Downloadable resources
- PDF of this content
- Connectiviity cheatsheet for DB2 for z/OS (connectivity_part2_cheatsheet.pdf | 254 KB)
Related topic
- Download file connectivity_part2_cheatsheet.pdf (pdf, 254 KB).