Contents

Connectivity Cheat Sheet for DB2 Universal Database Part 2 - DB2 for z/OS

Raul Chong
Published on October 09, 2003/Updated: February 19, 2009

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
The CDB Tables for a TCP/IP connections

View image at full size

The CDB Tables for a TCP/IP connections

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):

First row

View image at full size

First row

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):

Second row

View image at full size

Second row

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
Figure 2

View image at full size

Figure 2
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 clientMachine 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.
A value of 'A' for the security_out column implies that authentication has already been verified at this machine.

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:
MYUDBLNK 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.

  1. SAMPLE is the database in this machine 2 that you want to connect from the z/OS client 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 server machine.
  2. For this example:
    9.26.93.234 = 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 your 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 (UNIX)
    X:\WINNT\System32\drivers\etc\services (Windows)

  3. dbm cfg AUTHENTICATION should be set to CLIENT for option 1, when column 'security_out' is set to 'A'. It should be set to SERVER for option 2, when this column is set to 'P'.
  4. db2admin = user id as defined on machine 2
    mypsw = password as defined on machine 2
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 3

View image at full size

Figure 3
Figure 4. Perform the insert statements from SPUFI
Figure 4

View image at full size

Figure 4

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
Figure 5

View image at full size

Figure 5

Note that the library specified in the command below is specific to your system.

Figure 6. Bind SPUFI - Binding the package (output)
Figure 6

View image at full size

Figure 6
Figure 7. Bind SPUFI - Binding the plan
Figure 7

View image at full size

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

View image at full size

Figure 8

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
Figure 9

View image at full size

Figure 9

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)
Figure 10

View image at full size

Figure 10
Table 2. Typical errors
Error MessageResolution
SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME SAMPLE..DSNESM68.149EEA901A79FE48 NOT FOUND IN PLAN DSNESPCS. REASON 02You 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.EMPLOYEEFor 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
  • 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.26.93.234
This command can be performed from the TSO Command Processor
  • 9.26.93.234 = 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:
    db2set 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 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
DB2 UDB for z/OS client to DB2 UDB for iSeries server

View image at full size

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/OSMachine 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.

  1. TORISC6 is the local RDB Name.
    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'.
  2. For this example:
    9.89.168.6 = IP address of machine 2.
    446= The port used for DB2.

    Port 446 is the default value for the DRDA service. It is very unlikely this port is changed.

  3. john01 = user id as defined on machine 2
    psw400 = password as defined on machine 2
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:
CREATE COLLECTION DSNESPCS
CREATE COLLECTION DSNESPRR

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
Perform the insert statements from SPUFI

View image at full size

Perform the insert statements from SPUFI
Figure 13. Bind SPUFI - Binding the package
Bind SPUFI - Binding the package

View image at full size

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
Bind SPUFI - Binding the plan

View image at full size

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
Testing the connection using SPUFI

View image at full size

Testing the connection using SPUFI
Figure 16. Testing the connection using SPUFI (continued)
Testing the connection using SPUFI (continued)

View image at full size

Testing the connection using SPUFI (continued)
Table 5. Typical errors (in addition to the ones for table 2
Error MessageResolution
SQLCODE = -204, SQLSTATE = 42704, SQLERRMT = QSYS.DSNESPRR: COLLECTIONIf 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 QIWSTable 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
  • big400.ca.ibm.com = Host name of Database Server
    If cannot ping, there may be problems with the DNS. Try pinging the IP address.
ping 9.89.168.6
  • 9.89.168.6 = 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 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
UDB for z/OS client to DB2 UDB for z/OS server

View image at full size

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/OSMachine 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.

  1. 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.
  2. For this example:
    158.228.20.3 = IP address of machine 2.
    447= The port used for DB2.

    To find out the port used, contact your DB2 for OS/390 and z/OS DBA who can check the MVS syslog for message DSNL004I. "TCPPORT" in that message contains the port to use. Also, the -DISPLAY DDF command provides this info.

  3. tso1234 = user id as defined on machine 2
    tsopsw = password as defined on machine 2
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
DSNESPCS.DSNESM68 to <user id>
GRANT all on package
DSNESPCS.DSNESM68 to <user id>
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
Perform the insert statements from SPUFI at the DB2 UDB for z/OS client

View image at full size

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 19. Bind SPUFI - Binding the package

View image at full size

Figure 19. Bind SPUFI - Binding the package
Figure 20. Bind SPUFI - Binding the plan
Figure 20. Bind SPUFI - Binding the plan

View image at full size

Figure 20. Bind SPUFI - Binding the plan
Figure 21. Testing the Connection using SPUFI
Figure 21. Testing the Connection using SPUFI

View image at full size

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)
Figure 22. Testing the connection using SPUFI (continued)

View image at full size

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
  • tlba23me.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.20.3
This command can be performed from the TSO Command Processor
  • 158.228.20.3 = IP address of Database Server
    This will confirm if there are problems or not with the network.
  • Is DB2 started? If not execute -start db2
  • Is DDF started? If not, execute -start ddf
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

Related topic

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13257
ArticleTitle=Connectivity Cheat Sheet for DB2 Universal Database Part 2 - DB2 for z/OS
publish-date=02192009