Part I: Setting Up Database Connectivity for DB2 UDB for zOS

Previous page
Table of content
Next page

Part I: Setting Up Database Connectivity for DB2 UDB for z/OS

In this section, you will learn to set up these three connectivity scenarios:

  • From a DB2 for z/OS client to a DB2 for Linux, UNIX, and Windows server

  • From a DB2 for z/OS client to a DB2 for iSeries server

  • From a DB2 for z/OS client to a DB2 for z/OS server

The DB2 Connect software is not required in these scenarios.

NOTE

In Chapter 6, Configuring Client and Server Connectivity, we mentioned that APPC is supported when connecting from a DB2 for Linux, UNIX, and Windows client to a host server. However, inbound connections using APPC from a host client into a DB2 for Linux, UNIX, and Windows server is not supported. TCP/IP is assumed for all the scenarios described in this appendix.


The Communications Database

Before describing the different connectivity scenarios, we need to explain what the Communications Database (CDB) is. The CDB consists of several updatable system tables where connectivity information is stored in the host. In previous versions of DB2 UDB for z/OS, it used to be a separate database; currently, it is part of the Catalog. The CDB tables are only used by DB2 UDB for z/OS when it behaves as a client (Application Requester). Figure E.1 shows the relevant CDB tables used for a TCP/IP connection with several rows inserted.

Figure E.1. The CDB tables for a TCP/IP connection


In Figure E.1, we have highlighted a few related cells to explain two common cases that will help you understand how to populate the CDB tables.

In the first case, let's assume the DB2 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 (with value MYUDBLNK). This is shown next.

SYSIBM.LOCATIONS

SYSIBM.IPNAMES

LOCATION

LINKNAME

IBMREQD

PORT

TPN

SECURITY_OUT

USERNAMES

IBMREQD

IPADDR

SAMPLE

MYUDBLNK

 

50000

 

A

  

9.26.93.234


Thus, the DB2 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, and it means there is no need to look into the SYSIBM.USERNAMES table. At the DB2 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, let's assume the DB2 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 (with value MYUDBL2). This is shown next.

[View Full Width]

Thus, the DB2 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 because 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, and it means there is a need to look into the SYSIBM.USERNAMES table for the user ID and password. The columns AUTHID, NEWAUTHID, and PASSWORD show the mapping between the TSO ID and the ID and password combination that will be passed to the server. At the DB2 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 example, we assumed the second DB2 for Linux, UNIX, and Windows server had a database called SAMPLE as well (not SAMPLE2). However, because the LOCATION column of the SYSIBM.LOCATIONS table is a primary key, and because there was already an entry for SAMPLE for the first row of SYSIBM.LOCATIONS, the only way to put an entry in the table is to first create an alias to the database in the DB2 for Linux, UNIX, and Windows server as follows:

 db2 catalog db sample as sample2

Then we could add an entry in SYSIBM.LOCATIONS for SAMPLE2.

Now you have learned how to read and populate the CDB tables. Let's discuss the three different connectivity scenarios.

Scenario 1: DB2 for z/OS Client to DB2 for Linux, UNIX, and Windows Server

Figure E.2 shows the overview for the scenario described in this section.

Figure E.2. DB2 for z/OS client to DB2 for Linux, UNIX, and Windows server


Table E.1 presents the list of commands required to set up this connection.

Table E.1. DB2 for z/OS Client to DB2 for Linux, UNIX, and Windows Server

Commands to Run on Machine 1 (tlba22me) DB2 for z/OS

Information You Need to Obtain from Machine 2 (aries) DB2 for Linux, UNIX, and Windows to Perform the Commands on Machine 1

Step 1: Configure 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' )

Note:

MYUDBLNK is an arbitrary name used to link tables SYSIBM.LOCATIONS and SYSIBM.IPNAMES.

When SECURITY_OUT = A, authentication has already been verified at this machine.

  1. SAMPLE is the database in machine 2 that you want to connect from the z/OS 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.

  2. For this example:

    9.26.93.234 = the 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, 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

    You can find the services file at /etc/services in Linux/UNIX and at X:\WINNT\System32\drivers\etc\services in Windows.

  3. The DBM configuration parameter 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. For this example:

    db2admin = user ID as defined on machine 2

    mypsw = password as defined on machine 2

Option 2:

[View full width]

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

Note:

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

 

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

Note:

DSNESPCS is the package to bind for the SPUFI application with isolation Cursor Stability. DSNESPRR would be for isolation Repeatable Read.

The library specified contains DBRM member DSNESM68 (for the SPUFI application) and is dependent on how DB2 was set up in your system.

After the packages have been bound against 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 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.

Step 3: Test the Connection from DB2I Using SPUFI

Make sure to specify the connect location field as SAMPLE. Then issue the following command:

 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 user ID and password are stored in the CDB.

When configuring the CDB using option 1 (when column SECURITY_OUT = A), you would be passing the TSO ID to the DB2 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


Table E.2 provides troubleshooting hints for connectivity problems.

Table E.2. What to Check If You Cannot Connect

Client Machine tlba22me

Database Server aries

 ping aries.xyz.com

This command can be performed from the TSO Command Processor (assuming the hostname was used instead of the IP address itself in the SYSIBM.IPNAMES table).

aries.xyz.com = the host name of the database server

If you 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 = the IP address of the database server

This command will confirm whether or not there are problems with the network.

No corresponding information required on this machine.

  1. Is DB2 started? If not, run db2start.

  2. Is DB2COMM set to TCPIP?

    Check by executing the following command:

     db2set all

    If this registry variable is not set, you should execute:

     db2set db2comm=tcpip

    Then issue a db2stop/db2start command to make sure the change takes effect.

  3. 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 this command:

     get dbm cfg

If you used a service name instead of the port number in your SYSIBM.LOCATIONS table, make sure the entry is correct in the services file at the client machine.

No corresponding information required on this machine.

 netstat

This command shows all connections and port numbers and their statuses. It can be performed from the TSO Command Processor.

 
 netstat -a -n

This command shows all connections and port numbers and their statuses. Issue it from your command prompt.


NOTE

DB2COMM and SVCENAME are set up automatically during the installation of DB2 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.


NOTE

DB2 will check the services file on the machine where the DB2 command is issued.


Scenario 2: DB2 for z/OS Client to DB2 for iSeries Server

Figure E.3 shows the overview for the scenario described in this section.

Figure E.3. DB2 for z/OS client to DB2 for iSeries Server


Table E.3 presents the list of commands required to set up this connection.

Table E.3. DB2 for z/OS Client to DB2 for iSeries Server

Commands to Run on Machine 1 (tlba22me) DB2 for z/OS

Information You Need to Obtain from Machine 2 (big400) DB2 for iSeries to Perform the Commands on Machine 1

Step 1: Configure the CDB(Communications Database)

[View full width]

 INSERT INTO SYSIBM.LOCATIONS (location, linkname,port) VALUES ('TORISC6','MY400LNK', '446') INSERT INTO SYSIBM.IPNAMES (linkname, security_out, usernames, ipaddr ) VALUES   ('MY400LNK', 'P', 'O', '91.89.168.6' ) INSERT INTO SYSIBM.USERNAMES (type, authid,  linkname, newauthid, password) VALUES ('O',  'TS56692', 'MY400LNK', 'john01', 'psw400')

Note:

MY400LNK is an arbitrary name used to link tables SYSIBM.LOCATIONS, SYSIBM.IPNAMES, and SYSIBM.USERNAMES.

When SECURITY_OUT = P authentication will be performed at 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, the administrator 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:

    91.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. For this example:

    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.

No corresponding information required on this machine.

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

Note:

DSNESPCS is the package to bind for the SPUFI application with isolation Cursor Stability. DSNESPRR would be for isolation Repeatable Read.

The library specified contains DBRM member DSNESM68 (for the SPUFI application) and is dependent on how DB2 was set up in your system.

After the packages have been bound against 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

Then grant iSeries user john01 the appropriate authorization/privileges against the collection.

Step 3: Test the Connection from DB2I Using SPUFI

Make sure to specify the connect location field as TORISC6. Then issue the following command:

 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 user ID and password 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 the appropriate SELECT privilege to the user:

 GRANT select ON QIWS.QCUSTCDT       TO USER john01

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


Table E.4 provides troubleshooting hints for connectivity problems.

Table E.4. What to Check If You Cannot Connect

Client Machine torisc6

Database Server tlba22me

 ping big400.youracme.com

This command can be performed from the TSO Command Processor (assuming the hostname was used instead of the IP address itself in the SYSIBM.IPNAMES table).

big400.youracme.com = the host name of the database server

If you cannot ping, there may be problems with the DNS. Try pinging the IP address.

 ping 91.89.168.6

91.89.168.6 = the IP address of the database server

This will confirm whether or not there are problems with the network.

No corresponding information required on this machine.

Since the database server is DB2 UDB for iSeries, check:

Is DDM started? If not, execute the following:

 STRTTCPSVR SERVER(*DDM)

If you used a service name instead of the port number in your SYSIBM.LOCATIONS table, make sure the entry is correct in the services file at the client machine.

No corresponding information required on this machine.

 netstat

This command shows all connections and port numbers and their statuses. It can be performed from the TSO Command Processor.

 
 netstat

This command shows all connections and port numbers and their statuses. It can be performed from the OS/400 Main menu,

options 6 -> 5 -> 10 -> 7 -> 3.


Scenario 3: DB2 for z/OS Client to DB2 for z/OS Server

Figure E.4 shows the overview for the scenario described in this section.

Figure E.4. DB2 for z/OS client to DB2 for z/OS server


Table E.5 presents the list of commands required to set up this connection are given in the next table.

Table E.5. DB2 for z/OS Client to DB2 for z/OS Server

Commands to Run on Machine 1 (tlba22me) DB2 for z/OS

Information You Need to Obtain from Machine 2 (tlba23me) DB2 for z/OS to Perform the Commands on Machine 1

Step 1: Configure 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  ('MY390LNK', 'P', 'O', '10.228.20.3' ) INSERT INTO SYSIBM.USERNAMES (type, authid, linkname,  newauthid, password) VALUES ('O', 'TS56692', 'MY390LNK', 'tso1234','tsopsw')

Note:

MY390LNK is an arbitrary name used to link tables SYSIBM.LOCATIONS, SYSIBM.IPNAMES, and SYSIBM.USERNAMES.

When SECURITY_OUT = P authentication will be performed at server machine 2.

TS56692 is the TSO ID on this mainframe machine 1 client.

  1. MEXICO is the location name for the DB2 for z/OS subsystem in this machine 2 that you want to connect from the other DB2 for z/OS client.

  2. For this example:

    10.228.20.3 = the 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 administrator, 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 information.

  3. For this example:

    tso1234 = the user ID as defined on machine 2

    tsopsw = the 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.

No corresponding information required on this machine.

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

Note:

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

Step 3: Test the Connection from DB2I Using SPUFI

Make sure to specify the connect location field as MEXICO. Then issue the following command:

 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 user ID and password 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 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, for example:

 GRANT select ON dsn8810.emp TO USER tso1234


Table E.6 provides troubleshooting hints for connectivity problems.

Table E.6. What to Check If You Cannot Connect

Client Machine tlba22me

Server Machine tlba23me

 ping tlba23me.myacme.com

This command can be performed from the TSO Command Processor (assuming the hostname was used instead of the IP address itself in the SYSIBM.IPNAMES table).

tlba23me.myacme.com = the host name of the database server

If you cannot ping, there may be problems with the DNS. Try pinging the IP address.

 ping 10.228.20.3

This command can be performed from the TSO Command Processor.

10.228.20.3 = IP address of Database Server

This will confirm if there are problems or not with the network.

No corresponding information required on this machine.

  1. Is DB2 started? If not, execute -start db2.

  2. Is DDF started? If not, execute -start ddf.

If you used a service name instead of the port number in your SYSIBM.LOCATIONS table, make sure the entry is correct in the services file at the client machine.

No corresponding information required on this machine.

 netstat

This command shows all connections and port numbers and their statuses. It can be performed from the TSO Command Processor.

 
 netstat

This command shows all connections and port numbers and their statuses. It can be performed from the TSO Command Processor.



Previous page
Table of content
Next page
Understanding DB2(R. Learning Visually with Examples)
Understanding DB2: Learning Visually with Examples (2nd Edition)
ISBN: 0131580183
EAN: 2147483647
Year: 2004
Pages: 313
Authors: Raul Chong, Xiaomei Wang, Michael Dang, Dwaine Snow
BUY ON AMAZON
Project Management JumpStart
The CISSP and CAP Prep Guide: Platinum Edition
Cisco IP Telephony (CIPT) (Authorized Self-Study) (2nd Edition)
Introduction to 80x86 Assembly Language and Computer Architecture
Ruby Cookbook (Cookbooks (OReilly))
InDesign Type: Professional Typography with Adobe InDesign CS2
flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net
Privacy policy