The DatabaseRoute and DatabaseRetrieve nodes construct their JDBC connections by using connection details that are stored in the registry of the broker, which contain standard DBMS JDBC provider settings.
Use the following instructions to create a JDBC provider entry if you are using an Oracle database. You can use the mqsicreateconfigurableservice command, which is provided in the following script, to create a new JDBC provider entry called SIMPLERROUTEDB. This entry is referenced by the Data Source Name property field in the database nodes that are used in sample. The following instructions also configure the JDBC provider entry with a user identifier and password for the broker to use when accessing the database.
Create a JDBC provider entry for the database:
mqsistart MB8BROKER
You must replace the following place holders in the script with values appropriate to your environment.
mqsideleteconfigurableservice MB8BROKER -c JDBCProviders -o SIMPLERROUTEDB mqsicreateconfigurableservice MB8BROKER -c JDBCProviders -o SIMPLERROUTEDB -n connectionUrlFormat,connectionUrlFormatAttr1,description,jarsURL,portNumber, serverName,type4DatasourceClassName,type4DriverClassName -v "jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1], <SID>,Simplified Database Routing Sample Database,<JARS URL>,<PORT NUMBER>, <SERVER NAME>,oracle.jdbc.xa.client.OracleXADataSource,oracle.jdbc.OracleDriver"
simplifieddbrouting.bat
When you run the script, it performs the following tasks:
BIP8984W: Configurable service object name 'JDBCProviders : SIMPLERROUTEDB' was not found. The user has tried to delete or modify a configurable service object name that does not exist. Check the names of the existing configurable services using the mqsireportproperties command. Then modify the external resource object name and reissue the command. BIP2087E: Broker MB8BROKER was unable to process the internal configuration message. The entire internal configuration message failed to be processed successfully. The internal configuration message failed to be processed, use the messages following this message to determine the reasons for the failure. BIP8036E: Negative response received. This command sends an internal configuration message to the broker, the response received indicated that the internal configuration message was unsuccessful. Check that the WebSphere MQ transport is available. Check the system log for further information.Ignore this message. The message is displayed because the script attempts to delete an existing JDBCProvider registry entry called SIMPLERROUTEDB before it re-creates the entry. If you have not run the script before, the entry is not found in the registry.
mqsireportproperties MB8BROKER -o SIMPLERROUTEDB -c JDBCProviders -r
If the update was successful, the reported property output from the command matches the following example:
JDBCProviders SIMPLERROUTEDB connectionUrlFormat='jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1]' connectionUrlFormatAttr1='orc1' connectionUrlFormatAttr2='' connectionUrlFormatAttr3='' connectionUrlFormatAttr4='' connectionUrlFormatAttr5='' databaseName='default_Database_Name' databaseType='default_Database_Type' databaseVersion='default_Database_Version' description='Simplified Database Routing Sample Database' environmentParms='default_none' jarsURL='C:\oracle\oraxx\jdbc\lib' portNumber='1521' securityIdentity='default_User@default_Server' serverName='localhost' type4DatasourceClassName='oracle.jdbc.xa.client.OracleXADataSource' type4DriverClassName='oracle.jdbc.OracleDriver' BIP8071I: Successful command completion.
Specify the user identifier and password to associate with the JDBC provider SIMPLERROUTEDB:
Use the mqsisetdbparms and mqsichangeproperties commands to specify a user identifier and password for the broker to use with JDBC provider SIMPLERROUTEDB. This user identifier must be the same user identifier that you used when you created the database.
mqsisetdbparms MB8BROKER -n jdbc::mySecurityIdentity -u <user ID> -p <password>
mqsichangeproperties MB8BROKER -c JDBCProviders -o SIMPLERROUTEDB -n securityIdentity -v mySecurityIdentity
For further information, see Enabling JDBC connections to the databases in the WebSphere Message Broker documentation and the "Making the JDBC provider service available to the DatabaseRoute node" section in DatabaseRoute node in the WebSphere Message Broker documentation.