Learning Visually with Examples [Electronic resources]

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 

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.

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

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.

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 

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.