Mainframe COBOL access to UDB server

Edward Kosek

Mainframe COBOL access to UDB server
List,

We are z/OS and DB2 version 8.

We are considering moving some DB2 data off the mainframe and onto a (UDB)
server.

If we go this direction, what are my options for getting a Mainframe COBOL
program to access DB2 data on the mainframe and server?

Suggestions / Recommendations?

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

John Lantz

Re: Mainframe COBOL access to UDB server
(in response to Edward Kosek)
It's been a while (previous job) since I've done it. But it is possible to access
data on LUW from a mainframe COBOL program. I think the program issues a
CONNECT to the remote location. Then a remote BIND needs to be done as
well (runs on the mainframe but points to the remote server). This implies
that you need an appropriate entry in SYSIBM.SYSLOCATIONS that points to
the remote server.


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Bruce Lightsey

Re: Mainframe COBOL access to UDB server
(in response to John Lantz)
You will need to put the needed entries into sysibm.locations,
sysibm.ipnames ( for tcp/ip ) or lunames ( for sna ), and sysibm.usernames
for the connectivity to the UDB box ( we talk to linux and AIX flavors).
Then you will need to bind the packages/plans - all calls to the remote are
3-part names ( location.database.tablename or
location.database.procedurename).
If you are using DPROP for replication, then you already have most of this
set up and just need to set up the user and bind the pieces/parts.





Ed Kosek
<[login to unmask email]>
Sent by: DB2 Data To
Base Discussion [login to unmask email]
List cc
<[login to unmask email]
ORG> Subject
[DB2-L] Mainframe COBOL access to
UDB server
01/20/2009 10:13
AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






List,

We are z/OS and DB2 version 8.

We are considering moving some DB2 data off the mainframe and onto a (UDB)
server.

If we go this direction, what are my options for getting a Mainframe COBOL
program to access DB2 data on the mainframe and server?

Suggestions / Recommendations?

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

john travis

Re: Mainframe COBOL access to UDB server
(in response to Bruce Lightsey)


You may also need to bind the packages at both the mainframe and LUW.

We use a stub program containing the 'CONNECT' logic that is bound at MF and
LUW which in turn calls a module to do actual work that is only bound at
LUW.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bruce Lightsey
Sent: 21 January 2009 15:24
To: [login to unmask email]
Subject: Re: [DB2-L] Mainframe COBOL access to UDB server

You will need to put the needed entries into sysibm.locations,
sysibm.ipnames ( for tcp/ip ) or lunames ( for sna ), and sysibm.usernames
for the connectivity to the UDB box ( we talk to linux and AIX flavors).
Then you will need to bind the packages/plans - all calls to the remote are
3-part names ( location.database.tablename or
location.database.procedurename).
If you are using DPROP for replication, then you already have most of this
set up and just need to set up the user and bind the pieces/parts.





Ed Kosek
<[login to unmask email]>
Sent by: DB2 Data To
Base Discussion [login to unmask email]
List cc
<[login to unmask email]
ORG> Subject
[DB2-L] Mainframe COBOL access to
UDB server
01/20/2009 10:13
AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






List,

We are z/OS and DB2 version 8.

We are considering moving some DB2 data off the mainframe and onto a (UDB)
server.

If we go this direction, what are my options for getting a Mainframe COBOL
program to access DB2 data on the mainframe and server?

Suggestions / Recommendations?

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html
Capgemini is a trading name used by the Capgemini Group of
companies which includes Capgemini UK plc, a company registered in
England and Wales (number 943935) whose registered office is at No.
1 Forge End, Woking, Surrey, GU21 6DB


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Catherine McBride

Re: Mainframe COBOL access to UDB server
(in response to john travis)
Another approach is to have the mainframe COBOL program, via TCPIP socket call, invoke a script residing on the server. The SQL is executed by the script rather than the COBOL code, and the resulting data is passed back to the COBOL program. We did not have DB2 on our mainframe, so this method saved us a lot of money.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Bruce Lightsey
Sent: Wednesday, January 21, 2009 9:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mainframe COBOL access to UDB server


You will need to put the needed entries into sysibm.locations,
sysibm.ipnames ( for tcp/ip ) or lunames ( for sna ), and sysibm.usernames
for the connectivity to the UDB box ( we talk to linux and AIX flavors).
Then you will need to bind the packages/plans - all calls to the remote are
3-part names ( location.database.tablename or
location.database.procedurename).
If you are using DPROP for replication, then you already have most of this
set up and just need to set up the user and bind the pieces/parts.





Ed Kosek
<[login to unmask email]>
Sent by: DB2 Data To
Base Discussion [login to unmask email]
List cc
<[login to unmask email]
ORG> Subject
[DB2-L] Mainframe COBOL access to
UDB server
01/20/2009 10:13
AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






List,

We are z/OS and DB2 version 8.

We are considering moving some DB2 data off the mainframe and onto a (UDB)
server.

If we go this direction, what are my options for getting a Mainframe COBOL
program to access DB2 data on the mainframe and server?

Suggestions / Recommendations?

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Fred Edgar

Re: Mainframe COBOL access to UDB server
(in response to Catherine McBride)
Ed,

It's been a long time since I installed DB2 so I don't know if you'll have all this setup
done or not. When DDF starts it spits out msg. DSNL004I that tells the location name
and TCPPORT. We use TCP/IP here and I recommend going that way if you have a choice.
As Bruce says, you'll need to put a row in SYSIBM.LOCATIONS and SYSIBM.IPNAMES
for each database you want to connect to. In the LOCATIONS table we just populate
columns: LOCATION, LINKNAME and PORT. And on the IPNAMES table we fill in:
LINKNAME, SECURITY_OUT and IPADDR. We normally use the database name in the
IPADDR column (DNS or whatever resolves to IP address), but you can hard-code the
ip address.

You can use 3-part names, but we usually use CONNECT statements. You'll have to bind
your DBRM on both databases and will have to use parm SQLERROR(CONTINUE) if you
have SQL for both databases. Bind both from the z/OS side. For the remote bind you
just specify the location(database) name of the DB2 LUW like:
BIND PACKAGE (location.collection)

Section 4 of chapter 4 of the "Application Programming and SQL Guide" has some good
information:
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnapj15/CCONTENTS

Let me know if you need more information. I would suggest writing a very simple COBOL
program that you can use to test with.

Fred

Bruce Lightsey <[login to unmask email]> wrote:
You will need to put the needed entries into sysibm.locations,
sysibm.ipnames ( for tcp/ip ) or lunames ( for sna ), and sysibm.usernames
for the connectivity to the UDB box ( we talk to linux and AIX flavors).
Then you will need to bind the packages/plans - all calls to the remote are
3-part names ( location.database.tablename or
location.database.procedurename).
If you are using DPROP for replication, then you already have most of this
set up and just need to set up the user and bind the pieces/parts.





Ed Kosek

Sent by: DB2 Data To
Base Discussion [login to unmask email]
List cc
ORG> Subject
[DB2-L] Mainframe COBOL access to
UDB server
01/20/2009 10:13
AM


Please respond to
DB2 Database
Discussion list
at IDUG
2-l.org>






List,

We are z/OS and DB2 version 8.

We are considering moving some DB2 data off the mainframe and onto a (UDB)
server.

If we go this direction, what are my options for getting a Mainframe COBOL
program to access DB2 data on the mainframe and server?

Suggestions / Recommendations?

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html




______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Fred Edgar

Re: Mainframe COBOL access to UDB server
(in response to Fred Edgar)
Good point John. We do the same. We bind this "connector" package on all databases
that might ever be accessed and then just pass a parm to it indicating if you're connecting,
or disconnecting and which database.
But I think it would be simpler to start with just binding
the one package at both places. Or if you will only access data on LUW, you can just bind
it there.

Fred

"Travis, John" <[login to unmask email]> wrote:


You may also need to bind the packages at both the mainframe and LUW.

We use a stub program containing the 'CONNECT' logic that is bound at MF and
LUW which in turn calls a module to do actual work that is only bound at
LUW.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bruce Lightsey
Sent: 21 January 2009 15:24
To: [login to unmask email]
Subject: Re: [DB2-L] Mainframe COBOL access to UDB server

You will need to put the needed entries into sysibm.locations,
sysibm.ipnames ( for tcp/ip ) or lunames ( for sna ), and sysibm.usernames
for the connectivity to the UDB box ( we talk to linux and AIX flavors).
Then you will need to bind the packages/plans - all calls to the remote are
3-part names ( location.database.tablename or
location.database.procedurename).
If you are using DPROP for replication, then you already have most of this
set up and just need to set up the user and bind the pieces/parts.





Ed Kosek

Sent by: DB2 Data To
Base Discussion [login to unmask email]
List cc
ORG> Subject
[DB2-L] Mainframe COBOL access to
UDB server
01/20/2009 10:13
AM


Please respond to
DB2 Database
Discussion list
at IDUG
2-l.org>






List,

We are z/OS and DB2 version 8.

We are considering moving some DB2 data off the mainframe and onto a (UDB)
server.

If we go this direction, what are my options for getting a Mainframe COBOL
program to access DB2 data on the mainframe and server?

Suggestions / Recommendations?

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html
Capgemini is a trading name used by the Capgemini Group of
companies which includes Capgemini UK plc, a company registered in
England and Wales (number 943935) whose registered office is at No.
1 Forge End, Woking, Surrey, GU21 6DB


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html




______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Barbara Koenen

Re: Mainframe COBOL access to UDB server
(in response to Fred Edgar)
Yup. It can be done this way. And if the LUW database is an Information Integrator (or whatever they are calling it these days) instance, you can connect through to Oracle and SQLServer too.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of John Lantz
Sent: Wednesday, January 21, 2009 8:09 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mainframe COBOL access to UDB server

It's been a while (previous job) since I've done it. But it is possible to access
data on LUW from a mainframe COBOL program. I think the program issues a
CONNECT to the remote location. Then a remote BIND needs to be done as
well (runs on the mainframe but points to the remote server). This implies
that you need an appropriate entry in SYSIBM.SYSLOCATIONS that points to
the remote server.


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Steve Whittaker

Re: Mainframe COBOL access to UDB server
(in response to Barbara Koenen)
We are doing this same thing using Dpropr on z/OS and DB2 Information
Integrator on AIX server. We are replicating large amounts of data
between z/OS and AIX and thus eliminating some MIPS from z/OS by going
after the AIX data.
We also have a couple COBOL applications combining DB2 z/OS & Oracle
data using our Integrator software and the app runs on z/OS..

--Steve....

Steve Whittaker
DB2-DBA @ Progress Energy
TPP 12 C2
[login to unmask email]
Raleigh, NC
919-546-7267



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Bruce Lightsey
Sent: Wednesday, January 21, 2009 10:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mainframe COBOL access to UDB server

You will need to put the needed entries into sysibm.locations,
sysibm.ipnames ( for tcp/ip ) or lunames ( for sna ), and
sysibm.usernames
for the connectivity to the UDB box ( we talk to linux and AIX flavors).
Then you will need to bind the packages/plans - all calls to the remote
are
3-part names ( location.database.tablename or
location.database.procedurename).
If you are using DPROP for replication, then you already have most of
this
set up and just need to set up the user and bind the pieces/parts.






Ed Kosek

<[login to unmask email]>

Sent by: DB2 Data
To
Base Discussion [login to unmask email]

List
cc
<[login to unmask email]

ORG>
Subject
[DB2-L] Mainframe COBOL access to

UDB server

01/20/2009 10:13

AM





Please respond to

DB2 Database

Discussion list

at IDUG

<[login to unmask email]

2-l.org>









List,

We are z/OS and DB2 version 8.

We are considering moving some DB2 data off the mainframe and onto a
(UDB)
server.

If we go this direction, what are my options for getting a Mainframe
COBOL
program to access DB2 data on the mainframe and server?

Suggestions / Recommendations?

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events
*
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password.
You
should have gotten an e-mail with the temporary password assigned to
your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html