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

 
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

 
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) 

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.

The user id performing the bind should have been granted the appropriate authorization/privileges.

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

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

 
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) 

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.

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.

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.

Also most tables in iSeries are automatically journalled, but QCUSTCDT sample table is not, so make sure to journal it.

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

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

 
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) 

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.

The user id performing the bind should have been granted the appropriate authorization/privileges.

This may also be required to run the packages:

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.

Raul F. Chong Raul F. Chong is a database consultant from the IBM Toronto Laboratory and works primarily with IBM Business Partners. Raul has worked for five years in IBM, three of them in DB2 Technical Support, and two of them as a consultant specializing in database application development and migrations from other RDBMS to DB2. You can reach Raul at rfchong@ca.ibm.com.