DDF connecting to DB2 UDB on Z/os

Dee Reins

DDF connecting to DB2 UDB on Z/os
I am a real newbie about this.
I have some oracle programmers, that want to connect their Oracle database system, to our Mainframe DB2 database. They have given me the Oracle Transparent Gateway for DRDA manual, and they think that has the answers.

What I think I need to do is to help them connect to DB2's DDF, with a valid signon, and bind some packages and plans so they can access the DB2 databases and tables they want to read.
Where would I find more documentation, or where would I start with this project.
This is the results from -Display DDF

DSNL080I <DB3U DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STOPDQ
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I USCTFN01DB2U01 -NONE.DB3UAPPL -NONE.DB2U
DSNL084I IPADDR TCPPORT RESPORT
DSNL085I -NONE 449 32449
DSNL086I SQL DOMAIN=-NONE
DSNL086I RESYNC DOMAIN=-NONE
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE

Thanks
Dee


______________________________________________________________________

* 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

Tony Saul

Re: DDF connecting to DB2 UDB on Z/os
(in response to Dee Reins)
Dee,
 
You should get something like this in the MSTR (sorry I had to remove company sensitive values) -
 
DB2D DSNLILNR TCP/IP SERVICES AVAILABLE   
FOR DOMAIN xxxxxxxx.xxxxxxx.com AND PORT pppp
DB2D DDF START COMPLETE                     
LOCATION  DB2D                                  
LU        yyyyyyyy.yyyyyyyy                        
GENERICLU -NONE                                 
DOMAIN    xxxxxxx.xxxxxxx.com               
TCPPORT   pppp                                  
RESPORT   pppp (usually +1 of TCPPORT)                                 

 
Regards, Tony

--- On Fri, 16/1/09, Dee Reins <[login to unmask email]> wrote:


From: Dee Reins <[login to unmask email]>
Subject: [DB2-L] DDF connecting to DB2 UDB on Z/os
To: [login to unmask email]
Received: Friday, 16 January, 2009, 6:05 AM


I am a real newbie about this.
I have some oracle  programmers, that want to connect their Oracle database system,  to our Mainframe DB2 database.    They have given me the Oracle Transparent Gateway for DRDA manual, and they think that has the answers.   

What I think I need to do is to help them connect to DB2's  DDF, with a valid signon, and bind some packages and plans so they can access the DB2 databases and tables they want to read. 
Where would I find more documentation, or where would I start with this project.
This is the results from  -Display DDF

DSNL080I  <DB3U DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STOPDQ
DSNL082I LOCATION           LUNAME            GENERICLU
DSNL083I USCTFN01DB2U01     -NONE.DB3UAPPL    -NONE.DB2U
DSNL084I IPADDR          TCPPORT RESPORT
DSNL085I -NONE           449     32449
DSNL086I SQL    DOMAIN=-NONE
DSNL086I RESYNC DOMAIN=-NONE
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE

Thanks
Dee


______________________________________________________________________

* 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



Stay connected to the people that matter most with a smarter inbox. Take a look http://au.docs.yahoo.com/mail/smarterinbox

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * 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

Kirk Hampton

Re: DDF connecting to DB2 UDB on Z/os
(in response to Tony Saul)
Hello Dee,
Do your Oracle people have the Transparent Gateway for DRDA already
installed on another platform ? If so, you would just need to help them
complete the connection.

We have been running an older version of OTG for DRDA here for a number
of years. The package runs on the Oracle servers, and basically acts
just like DB2 Connect, talking to DDF through TCP/IP. I had to run a
DDL script they sent me, that created 5 or 6 tables that never seem to
have any data in them, and bound a couple of packages. That's pretty
much all there was to it, other than providing them with our host name
and port number, and you seem to have that info already. What it does on
the Oracle side, I'm told, is handle the translation necessary to make
your z/OS DB2 tables appear to an Oracle program as if they were Oracle
tables on a remote server.

It should not be a big deal to you, unless they run some queries that
you need to control the priority on in DDF, via Workload Manager, but
that's a whole other discussion, and hopefully you already have a
default set that is something other than wide open. Just like DB2
Connect, they cannot access any tables that they don't already have
SELECT authority on.


_________________________________________________________________
J Kirk Hampton / Capgemini / Dallas

Senior Infrastructure Engineer / Capgemini Energy
Office: +1 972 216 3119 / Mobile: +1 817 228 6083 / www.us.capgemini.com
Fax: +1 972 216 3185
Mesquite Data Center, 2947 Executive Blvd, Mesquite, TX, 75149
Email: [login to unmask email]

Join the Collaborative Business Experience
_________________________________________________________________



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Dee Reins
Sent: Thursday, January 15, 2009 4:05 PM
To: [login to unmask email]
Subject: [DB2-L] DDF connecting to DB2 UDB on Z/os

I am a real newbie about this.
I have some oracle programmers, that want to connect their Oracle
database system, to our Mainframe DB2 database. They have given me
the Oracle Transparent Gateway for DRDA manual, and they think that has
the answers.

What I think I need to do is to help them connect to DB2's DDF, with a
valid signon, and bind some packages and plans so they can access the
DB2 databases and tables they want to read.
Where would I find more documentation, or where would I start with this
project.
This is the results from -Display DDF

DSNL080I <DB3U DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STOPDQ
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I USCTFN01DB2U01 -NONE.DB3UAPPL -NONE.DB2U
DSNL084I IPADDR TCPPORT RESPORT
DSNL085I -NONE 449 32449
DSNL086I SQL DOMAIN=-NONE
DSNL086I RESYNC DOMAIN=-NONE
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE

Thanks
Dee


______________________________________________________________________

* 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






This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is
intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to
read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message
in error, please notify the sender immediately and delete all copies of this message.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * 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

Dee Reins

Re: DDF connecting to DB2 UDB on Z/os
(in response to Kirk Hampton)
What you say makes sense. They were giving me the wrong instructions.
I finally received the correct instructions, and it is a bunch of views on the system catalog, to make the DB2 catalog look like oracle system tables.

Did you create a user named OTGDB2?

The views look like this.

CREATE VIEW OTGDB2.ALL_CATALOG1
(OWNER, TABLE_NAME,
TABLE_TYPE) AS
SELECT T.CREATOR, T.NAME,
CAST(CASE T.TYPE
WHEN 'T' THEN 'TABLE'
WHEN 'V' THEN 'VIEW'
WHEN 'A' THEN 'SYNONYM'
WHEN 'G' THEN 'TABLE'
WHEN 'M' THEN 'TABLE'
WHEN 'X' THEN 'TABLE'
WHEN 'P' THEN 'TABLE'
WHEN 'L' THEN 'TABLE'
ELSE 'TABLE'
END AS VARCHAR(11))
FROM SYSIBM.SYSTABLES T ;


-------------- Original message ----------------------
From: "Hampton, James K" <[login to unmask email]>
> Hello Dee,
> Do your Oracle people have the Transparent Gateway for DRDA already
> installed on another platform ? If so, you would just need to help them
> complete the connection.
>

>
> _________________________________________________________________
> J Kirk Hampton / Capgemini / Dallas
>
> Senior Infrastructure Engineer / Capgemini Energy
> Office: +1 972 216 3119 / Mobile: +1 817 228 6083 / www.us.capgemini.com
> Fax: +1 972 216 3185
> Mesquite Data Center, 2947 Executive Blvd, Mesquite, TX, 75149
> Email: [login to unmask email]
>
> Join the Collaborative Business Experience
> _________________________________________________________________
>
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Dee Reins
> Sent: Thursday, January 15, 2009 4:05 PM
> To: [login to unmask email]
> Subject: [DB2-L] DDF connecting to DB2 UDB on Z/os
>
> I am a real newbie about this.
> I have some oracle programmers, that want to connect their Oracle
> database system, to our Mainframe DB2 database. They have given me
> the Oracle Transparent Gateway for DRDA manual, and they think that has
> the answers.
>
> What I think I need to do is to help them connect to DB2's DDF, with a
> valid signon, and bind some packages and plans so they can access the
> DB2 databases and tables they want to read.
> Where would I find more documentation, or where would I start with this
> project.
> This is the results from -Display DDF
>
> DSNL080I <DB3U DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
> DSNL081I STATUS=STOPDQ
> DSNL082I LOCATION LUNAME GENERICLU
> DSNL083I USCTFN01DB2U01 -NONE.DB3UAPPL -NONE.DB2U
> DSNL084I IPADDR TCPPORT RESPORT
> DSNL085I -NONE 449 32449
> DSNL086I SQL DOMAIN=-NONE
> DSNL086I RESYNC DOMAIN=-NONE
> DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
>
> Thanks
> Dee
>
>
> ______________________________________________________________________
>
> * 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
>
>
>
>
>
>
> This message contains information that may be privileged or confidential and is
> the property of the Capgemini Group. It is
> intended only for the person to whom it is addressed. If you are not the
> intended recipient, you are not authorized to
> read, print, retain, copy, disseminate, distribute, or use this message or any
> part thereof. If you receive this message
> in error, please notify the sender immediately and delete all copies of this
> message.
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * 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 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

Kirk Hampton

Re: DDF connecting to DB2 UDB on Z/os
(in response to Dee Reins)
Hello Dee,
I had to do a bit of research. Our solution has been in place since
1997. There is no user named OTGDB2 in RACF, but it does show up as the
owner of these views. Looks like you are on the right track now.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Dee Reins
Sent: Tuesday, January 20, 2009 1:31 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DDF connecting to DB2 UDB on Z/os

What you say makes sense. They were giving me the wrong instructions.

I finally received the correct instructions, and it is a bunch of views
on the system catalog, to make the DB2 catalog look like oracle system
tables.

Did you create a user named OTGDB2?

The views look like this.

CREATE VIEW OTGDB2.ALL_CATALOG1
(OWNER, TABLE_NAME,
TABLE_TYPE) AS
SELECT T.CREATOR, T.NAME,
CAST(CASE T.TYPE
WHEN 'T' THEN 'TABLE'
WHEN 'V' THEN 'VIEW'
WHEN 'A' THEN 'SYNONYM'
WHEN 'G' THEN 'TABLE'
WHEN 'M' THEN 'TABLE'
WHEN 'X' THEN 'TABLE'
WHEN 'P' THEN 'TABLE'
WHEN 'L' THEN 'TABLE'
ELSE 'TABLE'
END AS VARCHAR(11))
FROM SYSIBM.SYSTABLES T ;


-------------- Original message ----------------------
From: "Hampton, James K" <[login to unmask email]>
> Hello Dee,
> Do your Oracle people have the Transparent Gateway for DRDA already
> installed on another platform ? If so, you would just need to help
them
> complete the connection.
>

>
> _________________________________________________________________
> J Kirk Hampton / Capgemini / Dallas
>
> Senior Infrastructure Engineer / Capgemini Energy
> Office: +1 972 216 3119 / Mobile: +1 817 228 6083 /
www.us.capgemini.com
> Fax: +1 972 216 3185
> Mesquite Data Center, 2947 Executive Blvd, Mesquite, TX, 75149
> Email: [login to unmask email]
>
> Join the Collaborative Business Experience
> _________________________________________________________________
>
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Dee Reins
> Sent: Thursday, January 15, 2009 4:05 PM
> To: [login to unmask email]
> Subject: [DB2-L] DDF connecting to DB2 UDB on Z/os
>
> I am a real newbie about this.
> I have some oracle programmers, that want to connect their Oracle
> database system, to our Mainframe DB2 database. They have given me
> the Oracle Transparent Gateway for DRDA manual, and they think that
has
> the answers.
>
> What I think I need to do is to help them connect to DB2's DDF, with
a
> valid signon, and bind some packages and plans so they can access the
> DB2 databases and tables they want to read.
> Where would I find more documentation, or where would I start with
this
> project.
> This is the results from -Display DDF
>
> DSNL080I <DB3U DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
> DSNL081I STATUS=STOPDQ
> DSNL082I LOCATION LUNAME GENERICLU
> DSNL083I USCTFN01DB2U01 -NONE.DB3UAPPL -NONE.DB2U
> DSNL084I IPADDR TCPPORT RESPORT
> DSNL085I -NONE 449 32449
> DSNL086I SQL DOMAIN=-NONE
> DSNL086I RESYNC DOMAIN=-NONE
> DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
>
> Thanks
> Dee
>
>
> ______________________________________________________________________
>
> * 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
>
>
>
>
>
>
> This message contains information that may be privileged or
confidential and is
> the property of the Capgemini Group. It is
> intended only for the person to whom it is addressed. If you are not
the
> intended recipient, you are not authorized to
> read, print, retain, copy, disseminate, distribute, or use this
message or any
> part thereof. If you receive this message
> in error, please notify the sender immediately and delete all copies
of this
> message.
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * 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 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






This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is
intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to
read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message
in error, please notify the sender immediately and delete all copies of this message.


______________________________________________________________________

* 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