| Author |
Message
|
| DSPS |
 Posted: Thu May 16, 2013 2:32 am Post subject: Can run iSeries Access ODBC queries, but broker cvp fails |
 |
|
Apprentice
Joined: 11 Sep 2008
Posts: 44
Location: Southern England
|
I have set up an ODBC data source, which I can use with iSeries Access
cwbrunsql /DSN:S65D /I:/home/testapp/test2.sql
works and returns rows.
I've tried to introduce this to the broker with this command: -
mqsisetdbparms poc_brkr -n odbc::S65D -u <userId> -p <password>
But when I test the connection with the following: -
mqsicvp poc_brkr -n odbc::S65D
I get: -
BIP2393E: Database error: ODBC return code '-1' from data source ''odbc::S65D'' using ODBC driver manager ''/opt/ibm/IE02/2.0.0/lib/libodbcinterface.so''.
The broker received an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database about this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect data source, or table names. Correct either the database or the broker configuration.
BIP2322E: Database error: SQL State ''IM002''; Native Error Code '0'; Error Text ''[unixODBC][Driver Manager]Data source name not found, and no default driver specified''.
The error has the following diagnostic information: SQL State ''IM002'' SQL Native Error Code '0' SQL Error Text ''[unixODBC][Driver Manager]Data source name not found, and no default driver specified''
echo $ODBCINI
/opt/ibm/IE02/2.0.0/etc
echo $ODBCSYSINI
/opt/ibm/IE02/2.0.0/etc
And that directory contains the odbc.ini and odbcinst.ini files that I believe will also be used by the iSeries Access. I have tried pointing those variables to the files themselves, rather than the directory, and that prevents the cwbrunsql working.
I did have problems with permissions on the files before, which gave me a similar error with cwbrunqsl, but that is fixed, and the broker is running with the same user ID as I use to run cwbrunsql.
What am I doing wrong this time please?
RHEL Linux 6.4 64-bit, Broker 8, MQ 7.5 talking to iSeries 6
_________________
Distributed Systems Professional Services
WebSphere and MQ Consulting and Training |
|
| Back to top |
|
 |
| DSPS |
| Posted: Thu May 16, 2013 2:55 am Post subject: |
|
|
Apprentice
Joined: 11 Sep 2008
Posts: 44
Location: Southern England
|
Oh, and setting Trace on the ODBC just seems to put a lot of hex around the same IM002 error.
[ODBC][10798][3095131936][2013-05-16 11:52:57.518][SQLConnect.c][3621]
Entry:
Connection = 0x6e4e00
Server Name = [odbc::S65D][length = 14 (SQL_NTS)]
User Name = [****][length = 4 (SQL_NTS)]
Authentication = [******][length = 6 (SQL_NTS)]
[ODBC][10798][3095131936][2013-05-16 11:52:57.518][SQLConnect.c][3830]Error: IM002
_________________
Distributed Systems Professional Services
WebSphere and MQ Consulting and Training |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu May 16, 2013 4:21 am Post subject: Re: Can run iSeries Access ODBC queries, but broker cvp fail |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| DSPS wrote: |
| RHEL Linux 6.4 64-bit, Broker 8, MQ 7.5 talking to iSeries 6 |
Broker 8.x.x.x what? If your not at 8.0.0.2 already, then that would be my first shot. Also, what is the effective level of your runtime?
Not all drivers and databases are supported by WMB. Have you verified yours is supported?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu May 16, 2013 5:22 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Unless it has changed in the last couple of years, DB2 in iSeries is not supported ( although I managed to access it from windows, from what I remember it was not straight forward ) |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu May 16, 2013 5:44 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
| dogorsy wrote: |
| Unless it has changed in the last couple of years, DB2 in iSeries is not supported ( although I managed to access it from windows, from what I remember it was not straight forward ) |
Where did you get that impression from. AFAIK it is supported but like with zOS you need to use "DB2 Connect" to access it...(from a distributed system) 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu May 16, 2013 5:57 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| fjb_saper wrote: |
| dogorsy wrote: |
| Unless it has changed in the last couple of years, DB2 in iSeries is not supported ( although I managed to access it from windows, from what I remember it was not straight forward ) |
Where did you get that impression from. AFAIK it is supported but like with zOS you need to use "DB2 Connect" to access it...(from a distributed system) |
yes, sorry, you are right, memory failing, not supported for XA |
|
| Back to top |
|
|
| DSPS |
| Posted: Thu May 16, 2013 6:18 am Post subject: |
|
|
Apprentice
Joined: 11 Sep 2008
Posts: 44
Location: Southern England
|
This is indeed unpatched v8. Just fetching 8.0.0.2 now.
This is trying to access DB2 / iSeries via a passthrough in a Java compute node within a broker express edition instance. If this goes well, we'll get an enterprise edition taking to it and one other express.
I believe that the iSeries Access and IE02 should be sufficient to connect to this environment, but I could be wrong, it has happened. I might try toggling XA - not sure if the broker is going to be doing the whole two phase commit thing, but it might be worth trying.
_________________
Distributed Systems Professional Services
WebSphere and MQ Consulting and Training |
|
| Back to top |
|
|
| DSPS |
| Posted: Thu May 16, 2013 11:59 am Post subject: Now at 8.0.0.2 - but it still fails. |
|
|
Apprentice
Joined: 11 Sep 2008
Posts: 44
Location: Southern England
|
I've patched the broker to 8.0.0.2 - but the mqsicvp still fails.
_________________
Distributed Systems Professional Services
WebSphere and MQ Consulting and Training |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu May 16, 2013 12:08 pm Post subject: Re: Now at 8.0.0.2 - but it still fails. |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| DSPS wrote: |
I've patched the broker to 8.0.0.2 - but the mqsicvp still fails.
|
Perhaps some of the hints that other posts in this thread have given are in fact True.
What dos IBM Officially say about this?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| DSPS |
| Posted: Thu May 16, 2013 12:57 pm Post subject: Supported.... |
|
|
Apprentice
Joined: 11 Sep 2008
Posts: 44
Location: Southern England
|
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu May 16, 2013 2:27 pm Post subject: Re: Can run iSeries Access ODBC queries, but broker cvp fail |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| DSPS wrote: |
| Code: |
$ echo $ODBCINI
/opt/ibm/IE02/2.0.0/etc
$ echo $ODBCSYSINI
/opt/ibm/IE02/2.0.0/etc |
|
$ODBCINI should specify the odbc.ini file, not the directory.
$ODBCSYSINI should specify the directory, not the odbcinst.ini file.
These files are typically copied from <install_dir>/ODBC*/unixodbc/.
IE02 is for ODBC data sources that are not supported through the DataDirect ODBC drivers.
| DSPS wrote: |
But when I test the connection with the following: -
| Code: |
| mqsicvp poc_brkr -n odbc::S65D |
|
Try it without the "odbc::"
| Code: |
| mqsicvp poc_brkr -n S65D |
If that doesn't work ...
| DSPS wrote: |
I've tried to introduce this to the broker with this command: -
| Code: |
| mqsisetdbparms poc_brkr -n odbc::S65D -u <userId> -p <password> |
|
... try "dsn::S65D" instead:
| Code: |
| mqsisetdbparms poc_brkr -n dsn::S65D -u <userId> -p <password> |
... and then try the mqsicvp command again. |
|
| Back to top |
|
|
| DSPS |
| Posted: Thu May 16, 2013 11:15 pm Post subject: |
|
|
Apprentice
Joined: 11 Sep 2008
Posts: 44
Location: Southern England
|
I thought it was odd to have two related variables both pointing to a directory. I've updated ODBCINI
ODBCSYSINI=/opt/ibm/IE02/2.0.0/etc
ODBCINI=/opt/ibm/IE02/2.0.0/etc/odbc.ini
Then restarted the broker.
When I try the mqsicvp command without the odbc:: prefix, I just get told that the datasource has not been associated with the broker.
mqsicvp poc_brkr -n S65D
BIP8272E: Datasource 'S65D' has not been associated with broker 'poc_brkr'.
mqsisetdbparms poc_brkr -n dsn::S65D -u <UID> -p <password>
Then all combinations of no prefix, ODBC:: or DSN:: prefixes give me the IM002 error message.
_________________
Distributed Systems Professional Services
WebSphere and MQ Consulting and Training |
|
| Back to top |
|
|
| DSPS |
| Posted: Fri May 17, 2013 12:09 am Post subject: |
|
|
Apprentice
Joined: 11 Sep 2008
Posts: 44
Location: Southern England
|
mqsireportproperties poc_brkr -o S65D -e poc_eg -a
BIP2212E: Invalid configuration message containing action 'Report' which is not valid for target object ''.
This is becoming less funny, and more urgent. I think my first weekend off in a month is in doubt.
_________________
Distributed Systems Professional Services
WebSphere and MQ Consulting and Training |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Fri May 17, 2013 12:33 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| DSPS wrote: |
| I've updated ODBCINI ... Then restarted the broker. |
Also make sure the user environment is updated too, by either re-sourcing the profile, or logging out and in again before restarting the broker.
| DSPS wrote: |
When I try the mqsicvp command without the odbc:: prefix, I just get told that the datasource has not been associated with the broker.
| Code: |
mqsicvp poc_brkr -n S65D
BIP8272E: Datasource 'S65D' has not been associated with broker 'poc_brkr'. |
|
What does the S65D datasource look like in the $ODBCINI file?
| DSPS wrote: |
| This is becoming less funny, and more urgent. |
Have you opened up a PMR yet? |
|
| Back to top |
|
|
| DSPS |
| Posted: Fri May 17, 2013 12:52 am Post subject: |
|
|
Apprentice
Joined: 11 Sep 2008
Posts: 44
Location: Southern England
|
"Also make sure the user environment is updated too, by either re-sourcing the profile, or logging out and in again before restarting the broker. "
- Yep - did that, but thank you very much indeed for the sensible reminder.
"What does the S65D datasource look like in the $ODBCINI file? "
[S65D]
Description = iSeries Access ODBC Driver DSN for BPCS on iSeries
Driver = iSeries Access ODBC Driver
System = S65D
UserID = XXXX
Password = YYYYYY
Naming = 0
DefaultLibraries = BMOSF610D
Database = S65D
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = BMOSF610D
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 1
The driver referenced is there in the odbcinst.ini file which is in the ${ODBCSYSINI} directory.
The library names are those which work from other clients, and seem to work for the cwbrunsql command just fine. I've cheated and used the same name for the database and for the hostname in /etc/hosts
_________________
Distributed Systems Professional Services
WebSphere and MQ Consulting and Training |
|
| Back to top |
|
|
|
|
