| Author |
Message
|
| gteplits |
 Posted: Tue Jul 17, 2001 1:51 pm Post subject: |
 |
|
Apprentice
Joined: 16 Jul 2001
Posts: 29
|
I posted the same question on listserver, sorry for duplicating....
Is it possible to access remote DB2 database , located on mainframe host from MQSIv2.0.1 compute node?
We make use of ESQL to lookup application tables in compute nodes (AIX/DB2/MQSIv2.0.1).
In production application tables will reside on DB2 mainframe.
The plan is to use DB2 connect to access mainframe tables from AIX MQSI run-time box.
While there is no problem in accessing local UDB instance on AIX, it is not clear how to configure such access to the mainframe. It was my understanding, that all I had to do is to properly configure ODBC data source.
DBA insist however, that both userID and password must be supplied. I can not find place in MQSIv2.0.1for such configuration... When broker is created, userID and password is provided. Are those parameters passed to DB2 connect behind the scene?Does anyone accessing mainframe DB2 from MQSIv2.0.1? Please, share you ideas and experienses.
Thank you for your help |
|
| Back to top |
|
 |
| Cliff |
| Posted: Thu Jul 19, 2001 4:51 am Post subject: |
|
|
Centurion
Joined: 27 Jun 2001
Posts: 145
Location: Wiltshire
|
You are right that the UserId and Password specified when the broker is created are the ones used by the broker when it connects to an ODBC source. This is how it works on non-OS/390 platforms, so unless anyone knows otherwise, that's how I would expect it to operate on OS/390.
Anyone else care to comment? |
|
| Back to top |
|
|
| Yongdeuk |
| Posted: Thu Jul 26, 2001 7:07 am Post subject: |
|
|
Newbie
Joined: 25 Jun 2001
Posts: 2
Location: IBM Korea
|
I tried this with the proper userid/passwd
for DB2 390.. but unfortunately, it was
unsuccessful.
I am not sure connection from MQSI node to DB2 390 is possible or not.. but my experience is that I failed.
I'd like to listen others say.. |
|
| Back to top |
|
|
| kolban |
| Posted: Thu Jul 26, 2001 8:22 am Post subject: |
|
|
Grand Master
Joined: 22 May 2001
Posts: 1072
Location: Fort Worth, TX, USA
|
| I believe this is possible by installing/configuring the DB2 Client Connect portion on the same machine as the MQSI Broker. |
|
| Back to top |
|
|
| adkessell |
| Posted: Thu Aug 30, 2001 9:41 am Post subject: |
|
|
Novice
Joined: 29 Aug 2001
Posts: 10
|
The other option is to find out what the Schema Name is of the Database and Table you are trying to access from MQSI. Then put this in you select statement as follows:
SELECT ..... FROM Database.Schema Name.Table Name ..... |
|
| Back to top |
|
|
| NickB |
| Posted: Fri Oct 12, 2001 6:22 am Post subject: |
|
|
Centurion
Joined: 20 May 2001
Posts: 107
Location: Zurich Financial Services
|
We also would like to query OS/390 DB2 from a message flow. I've not tried it yet but I think you need:
a) to define DRDA correctly on OS/390. This basically involves defining DB2 to TCP/IP such that DB2 listens to requests on a certain port
b) to define an ODBC data source on the node where yur broker is running pointing to your mainframe DB2 service |
|
| Back to top |
|
|
| bower5932 |
| Posted: Fri Oct 12, 2001 6:36 am Post subject: |
|
|
Jedi Knight
Joined: 27 Aug 2001
Posts: 3023
Location: Dallas, TX, USA
|
| I didn't think that using a 390 database was supported with MQSI 2.0.1. There is a table in the front of the Installation Guide that doesn't list 390 as an option. |
|
| Back to top |
|
|
| gteplits |
| Posted: Fri Oct 12, 2001 11:40 am Post subject: |
|
|
Apprentice
Joined: 16 Jul 2001
Posts: 29
|
Nick, you are absolutely correct. Those two components needed to be configured: DRDA and ODBC.
We managed to access application mainframe DB2 tables from AIX broker after couple tries.
The biggest thing to keep in mind: broker accesses application DB with service userID and password. Those should be identicaly defined on host (mainframe).
Regards, George |
|
| Back to top |
|
|
| doggy |
| Posted: Fri Oct 12, 2001 11:47 am Post subject: |
|
|
Newbie
Joined: 03 Oct 2001
Posts: 8
|
In the compute node, you can access external DB using ODBC. In here you must specify "Database.yourschema.table" in your SELECT clause.
ODBC will first attempt to use login/pwd you specified on ODBC to your MQSI Broker DB. And if that was not specified, then it will use your broker login/pwd.
I tried connecting to JDE on AS400, IBM DB2 driver works but return data in EBCDIC.. weird, the driver does not support conversion. |
|
| Back to top |
|
|
| Outdesign |
| Posted: Tue Oct 16, 2001 6:11 am Post subject: |
|
|
Apprentice
Joined: 16 Sep 2001
Posts: 38
Location: Hampshire, UK
|
To explain and summerise this thread ... 
1. UserId and Password ?
************************
mqsicreatebroker <brokername> -i <ServiceUserID> -a <ServicePassword> -q <QueueManagerName> -n <DataSourceName> -u <DataSourceUserID> -p <DataSourcePassword>
User database access is controlled via the values that you specify for the
DataSourceUserID and DataSourcePassword on the mqsicreatebroker command.
If you allow these parameters to default,
user database access is controlled via the values that you specify for the
ServiceUserID and ServicePassword on the mqsicreatebroker command.
2. What is formally supported ?
*******************************
The formally supported combination of DBMS and platform are only those
listed in the Administration Guide (as per your product version).
In summary, for MQSIv2.0.2, MQSI can access local and remote DB2 user databases
on Windows NT, Windows 2000, Unix (AIX, Solaris and HP-UX).
To connect to a remote user database a local client must be installed and the
connection to the remote server setup.
The datasource the MQSI node references resolves to a database table on the
remote server via the local client.
There is nothing new or specific to MQSI here.
Check your product version specifics in your Admin Guide or via
http://www-4.ibm.com/software/ts/mqseries/platforms/supported.html
3. What people have found ?
***************************
In principle, people have found that, for example, connecting to a remote DB2 server
on AIX is the same as connecting to a remote DB2 server on OS/390.
The only difference is the specifics of setting up the local DB2 client connection
to that remote server.
It also needs to be noted that the userid (and password) needs to be defined on the remote
systems as having access to those database tables for the type of SQL operation, be
that query only (SELECT) or modification (INSERT, UPDATE and DELETE).
I have tried this using a v2.0.2 broker on Windows 2000 accessing a remote DB2v7.1
user database on OS/390 via a local DB2v7.1 client.
On NT, you can start the client configuration tool by typing 'db2cca' from a dbcmd prompt.
4. Where to from here ?
***********************
It is my understanding that IBM will support this on a 'best can do basis' for customers
using MQSIv2.0.1 and v2.0.2 and accessing remote DB2 user databases on OS/390.
[ This Message was edited by: Outdesign on 2001-10-16 07:12 ] |
|
| Back to top |
|
|
| adkessell |
| Posted: Mon Oct 22, 2001 2:18 pm Post subject: |
|
|
Novice
Joined: 29 Aug 2001
Posts: 10
|
| Apparently to resolve this issue, you need to contact IBM as there is a fix patch that resolve the problem. A colleage of mine was trying to access DB2 on OS390 from a AIX box using MQSI V2.0.2 and it wouldn't work. He applied the patch from IBM and it worked straight away. I'm not sure if it has been included in the CSD01. |
|
| Back to top |
|
|
| NickB |
| Posted: Mon Oct 29, 2001 7:46 am Post subject: |
|
|
Centurion
Joined: 20 May 2001
Posts: 107
Location: Zurich Financial Services
|
We have:
- MQSI v2.0.2 installed on AIX
- UDB v7.1 installed on AIX
- UDB v6.1 on OS/390
I have performed the following steps in order to establish a link between my AIX node and the mainframe:
a) catalog tcpip node UK remote a.b.c.d server nnnn
b) catalog database DBGNNKD as UKMF at node UK authentication dcs
c) catalog dcs database DBGNNKD as DBGNNKD
d) connect to UKMF user nkd using my_passwd
When doing this, db2 on aix states that
SQL30061N The database alias or database name "DBGNNKD " was not found at the remote node. SQLSTATE=08004
even though I know that there is a database called "DBGNNKD" resident on our mainframe.
Note that I haven't even got as far as setting up the MQSI stuff! I'm still clearly struggling with getting remote db config working. Can you let me know where I'm going wrong?
I have successfully set up remote UDB access to another AIX node and have included this within a message flow so I know how to do all the .odbc.ini changes. However, I cannot make that bridge through to the mainframe yet.
All contributions gratefully accepted!
|
|
| Back to top |
|
|
| NickB |
| Posted: Tue Oct 30, 2001 3:38 am Post subject: |
|
|
Centurion
Joined: 20 May 2001
Posts: 107
Location: Zurich Financial Services
|
Yet again, I answer one of my own posts!
I have now successfully connected AIX as a client to mainframe DB2. The thing I got wrong in the previous post was on the "catalog dcs" command where I should have put
catalog dcs database DBGNNKD as DB2D
where "DB2D" is the name of the mainframe DB2 subsystem I needed to connect to.
All I need to do know is to get the mainframe ACF-2 guys to create me an id on the mainframe to match the id which was used to create the broker on AIX. I've tried overriding this by specifying UID and PWD in the .odbc.ini file but this seems to have no effect. |
|
| Back to top |
|
|
| Outdesign |
| Posted: Tue Oct 30, 2001 7:09 am Post subject: |
|
|
Apprentice
Joined: 16 Sep 2001
Posts: 38
Location: Hampshire, UK
|
Nick,
QUOTE:
"I've tried overriding this by specifying UID and PWD in the .odbc.ini file but this seems to have no effect."
REPLY:
The userid and and password specified in the .odbc.ini file are *NOT* used by MQSI !!!
I refer back to an extract from a previous post of mine in this thread ...
1. UserId and Password used to access a user database ?
*******************************************************
mqsicreatebroker
<brokername> -i <ServiceUserID> -a <ServicePassword>
-q <QueueManagerName>
-n <DataSourceName> -u <DataSourceUserID> -p <DataSourcePassword>
User database access is controlled via the values that you specify for the
DataSourceUserID and DataSourcePassword on the mqsicreatebroker command.
If you allow these parameters to default,
user database access is controlled via the values that you specify for the
ServiceUserID and ServicePassword on the mqsicreatebroker command.
|
|
| Back to top |
|
|
| NickB |
| Posted: Wed Oct 31, 2001 1:19 am Post subject: |
|
|
Centurion
Joined: 20 May 2001
Posts: 107
Location: Zurich Financial Services
|
You're right about the .odbc.ini file!
I now have an id created on the mainframe to match the broker id and everything is working perfectly. I've written a message flow on AIX to query mainframe DB2 data and output it as XML.
Its been a long hard slog. |
|
| Back to top |
|
|
|
|
