Database & WMB

Database node vs Compute node

The actual difference between the Database node and the Compute node is NOT the accessing of databases as both nodes can do this, which does mean their names can be slightly confusing. The actual (and only) difference is that a ComputeNode always creates a new message (even if you never use it), whereas a Database node never creates a new message. You can obviously do more in a Compute node than in a DB node (like create a changed OutputRoot message which you can not do in a DB node). But if all you want to do in the node is access and/or change the Envrionment or LocalEnvrionment trees then you can do this in the Database node which is why the patterns used the DB node where they do as they are simply setting a flag in the LE.

JDBC vs ODBC

i think JDBC may be faster than ODBC, our supplier's ODBC is not multi-threaded yet whereas their JDBC is, there are SQL limitations in the ODBC which are over-come by using JDBC, and ODBC may be phased out by Microsoft. i would propbaly offer JDBC as an alternative rather that a straight replacement to our customers.

As discussed below are the differences between ODBC and JDBC connections 

• OBDC is Microsoft drivers and uses dlls, JDBC is Java drivers and uses jar files. 
• ODBC is procedural oriented and JDBC is object oriented. 
• ODBC can't be directly used with Java because it uses a C interface. 
• ODBC makes use of pointers which have been removed totally from Java. 
• ODBC mixes simple and advanced features together and has complex options for simple queries. But JDBC is designed to keep things simple while allowing advanced capabilities when required. 
• ODBC requires manual installation of the ODBC driver manager and driver on all client machines. JDBC drivers are written in java and JDBC code is automatically installable, secure, and portable on all platforms. 
• JDBC API is a natural Java Interface and is built on ODBC. JDBC retains some of the basic feature of ODBC. 
• JDBC is Java Database Connectivity. It enables java programs to execute SQL statements. JDBC makes it possible to write a single database application that can run on different platforms and interact with different DBMS. JDBC is language dependent i.e it is used only to make connectivity with java. 
• ODBC is open database connectivity. The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data so it is language independent. 
• ODBC is language independent and JDBC is language dependent. 

·         If you are creating ODBC connections to your user databases, ensure that you correctly create 32-bit, 64-bit, or both, connections for each DSN. If you are creating JDBC connections, their use is independent of 32-bit or 64-bit mode.


There are no performance issues for JDBC connections as compared to ODBC connections, we need to configure it on the server to use it in InfoView , in the jar files, and the end users cannot modify it like ODBC connections. 

Yes JDBC libraries are faster than ODBC because it uses JAVA jars and not windows dlls, so there can be a slight better performance die to platform independency if using any non windows Server, but again as I told earlier it completely depends on the requirement and scenario . 

There are no major issues it’s just the configuration which is a bit complex as compared to ODBC , but it is more reliable and once done it works perfectly. 

Connecting to Database from message broker:

·         For one database ,one dsn ,one message flow,we need one thread to access.

·         When connection gets broker?

The broker makes the connections when it needs to use them, and they remain open until one of the following events occurs:

• The message flow has been idle for one minute
• The message flow is stopped
• The broker is stopped

ODBC.ini

ODBC .ini files that are supplied, and the information contained in these configuration topics, include all the connection parameters that are supported for connections to your databases.

ODBC connections –Creating DSNs

In Windows

Fro all databases like DB2,Mysql,… we just need to choose ODBC driver in User or System DSN and create a DSN.

But for ORACLE,we need to go to Windows registery REGEDIT ,then HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. Expand that location, and right-click your DSN entry.Then give value in string(refer http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fah14442_.htm)

In Linux/Unix (Using WMB ODBC Database extender)

Copy  the ODBC.ini and ODBCINST.ini  files from installation path(install_dir/ODBC/unixodbc/) and place in WMB /var/mqsi path.

Then point the environment variables ODBCINI  and ODBCSYSINI to this in

This copied path.

Then edit the ODBC.ini file with your required Database paramaters.

For DB2

The DB2 alias name configured during DB2 remote cataloguing for DB2 remote database in Client/server modal,should be used for DB2 alias name.

Enabling JDBC in WMB

First install the driver of the database if the driver is not in your broker machine.

Then mqsicreateconfigurable service to enable JDBC connection with that database.Include security identity.

Then we can use the JDBC only with DatabaseRetrieve node and DatabaseRoute nodes.

ODBC and JDBC drivers in Broker

Broker contains ODBC drivers for the Databases supported by Broker.

But the Broker will not contain the JDBC drivers for even the Databases supported by Broker.If any JDBC driver is required,it will has to be download and kept in BROKER lib folder and referred in jars url property of JDBCProvider.