| Author |
Message
|
| kayhansefat |
 Posted: Tue Mar 27, 2007 12:57 am Post subject: MbSQLStatement problems with DB2 access from JavaCompute |
 |
|
Acolyte
Joined: 18 Oct 2006
Posts: 65
|
I am having problems connecting to a DB2 database on the same maching as the broker. I have readup on the help:
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/index.jsp
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/index.jsp?topic=/com.ibm.etools.mft.doc/ac30494_.htm
I am confused as to whether or not I have to setup the database as an ODBC datasource. I have used the following code:
| Code: |
MbMessage newMsg = new MbMessage(assembly.getMessage());
MbMessageAssembly newAssembly = new MbMessageAssembly(assembly, newMsg);
MbSQLStatement state = createSQLStatement("LETTERDB","SET OutputRoot.XML.integer[] = PASSTHRU
('SELECT * FROM GPSENT_LETTERS');");
state.setThrowExceptionOnDatabaseError(false);
state.setTreatWarningsAsErrors(true);
state.select(assembly, newAssembly);
int sqlCode = state.getSQLCode();
if (sqlCode != 0)
{
}
else
{
addToLog("Test", "sqlCode = 0, all ok?");
}
|
The "assembly" that I am using here is what is passed into the JavaCompute Node from an MQInput which is part of the XMLNSC domain. But this causes an exception when the state.select(assembly, newAssembly) method is run. The exception reads:
| Quote: |
| <com.ibm.broker.plugin.MbDatabaseException class:JNI method:ImbOdbcHandle::checkRcInner source:BIPv600 key:2321 > |
Is there anything that I need to do DB2 side to prepare this? Thanks |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Tue Mar 27, 2007 3:08 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Yes you need to set up the Database with the ODBC access....
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| kayhansefat |
| Posted: Tue Mar 27, 2007 3:49 am Post subject: |
|
|
Acolyte
Joined: 18 Oct 2006
Posts: 65
|
I have followed the manual in the DB2 Info Center titled "Setting up the UNIX ODBC environment". So I have the file /var/mqsi/odbc/.odbc.ini which includes:
| Code: |
LETTERDB=IBM DB2 ODBC Driver
[ODBC]
Trace=0
TraceFile=/var/mqsi/odbc/odbctrace.out
TraceDll=/opt/mqsi/merant/lib/odbctrac.so
InstallDir=/opt/mqsi/merant
UseCursorLib=0
IANAAppCodePage=4
[LETTERDB]
Driver=/opt/IBM/db2/v8.1/lib/libdb2.so
Description=LETTERDB Broker Database
Database=LETTERDB |
The user that runs the toolkit etc has the db2profile run on login so thats not the problem.
So I think I am not doing something correct in db2 to setup the database for ODBC access. In the Control Center, under the Configuration Assistant I can see that the database is "ODBC Registered = No". If I then double click on the DB it takes me into the Change Database Wizard, section 4 Data Source, Register this db for ODBC is ticked as a System Data Source with the name LETTERDB and then click Finish. However it is still set to "No" under the ODBC Registered field in the list view.
Any ideas...... |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Mar 27, 2007 3:56 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I've never had to do anything within DB2 on Unix to enable ODBC access.
You might need to bind the db2 database against the client libraries.
You can try
| Quote: |
| db2 bind ~/sqllib/bnd/@db2cli.lst blocking all sqlerror continue messages cli.msg grant public |
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kayhansefat |
| Posted: Tue Mar 27, 2007 4:14 am Post subject: |
|
|
Acolyte
Joined: 18 Oct 2006
Posts: 65
|
Running that command returns:
| Quote: |
| SQL1024N A database connection does not exist. SQLSTATE=08003 |
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Mar 27, 2007 4:25 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
well, db2 connect LETTERDB first.
And I don't know that this will solve your problem, as I don't know what your problem is.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kayhansefat |
| Posted: Tue Mar 27, 2007 4:47 am Post subject: |
|
|
Acolyte
Joined: 18 Oct 2006
Posts: 65
|
Doing a "db2 connect LETTERDB" returns:
| Quote: |
| SQL0104N An unexpected token "LETTERDB" was found following "CONNECT". Expected tokens may include: "END-OR-STATEMENT". SQLSTATE=42601 |
My problem is that I am unable to connect to the ODBC DataSource when issuing the select() method on the MbSQLStatement class variable. The code in my first post causes an MbException. The details for the exception state that the ODBC return code was 'null'. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Mar 27, 2007 7:01 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Sorry, I guess it's "db2 connect to LETTERDB".
Do you have a DBA available? You should probably start working with someone local who knows DB2 better than you do.
The DB2 information center for version 8.2 is at
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp
But there's a lot lot lot of information in there, most of which won't be relevant.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Mar 28, 2007 1:55 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
And if you are using the broker you should still use the merant driver for db2 and not the db2 driver...
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Mar 28, 2007 2:16 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| fjb_saper wrote: |
| And if you are using the broker you should still use the merant driver for db2 and not the db2 driver... |
FJ... I'm a little confused. If one is using MbSQLStatement, then one is using the ODBC datasource, which of course has been properly configured to use the right driver.
If one is using Type 4 JDBC, then one shouldn't be using the Merant driver, but the proper Type 4 JDBC Driver for the database.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Mar 28, 2007 2:26 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| kayhansefat wrote: |
I have followed the manual in the DB2 Info Center titled "Setting up the UNIX ODBC environment". So I have the file /var/mqsi/odbc/.odbc.ini which includes:
| Code: |
LETTERDB=IBM DB2 ODBC Driver
[ODBC]
Trace=0
TraceFile=/var/mqsi/odbc/odbctrace.out
TraceDll=/opt/mqsi/merant/lib/odbctrac.so
InstallDir=/opt/mqsi/merant
UseCursorLib=0
IANAAppCodePage=4
[LETTERDB]
Driver=/opt/IBM/db2/v8.1/lib/libdb2.so
Description=LETTERDB Broker Database
Database=LETTERDB |
|
I hope this clears your confusion Jeff.
It appears that he is using the db2 driver for the db and the merant driver for the trace ???
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Mar 28, 2007 2:45 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Mar 28, 2007 2:51 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Thanks for setting me straight ...
I learn every day with you...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Mar 28, 2007 2:58 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| fjb_saper wrote: |
| I learn every day with you... |
I promise it's mutual.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kayhansefat |
| Posted: Fri Mar 30, 2007 2:37 am Post subject: |
|
|
Acolyte
Joined: 18 Oct 2006
Posts: 65
|
| If I use the default database that the broker uses, not LETTERDB, then it seems to work fine. Seems like I have to register the new Database with the broker somehow. |
|
| Back to top |
|
|
|
|
