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 a DB2 Universal 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
mqsideleteconfigurableservice MB8BROKER -c JDBCProviders -o SIMPLERROUTEDB mqsicreateconfigurableservice MB8BROKER -c JDBCProviders -o SIMPLERROUTEDB -n connectionUrlFormat,databaseName,description,jarsURL,portNumber,serverName, type4DatasourceClassName,type4DriverClassName -v "jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];, SROUTEDB,Simplified Database Routing Sample Database,<JARS URL>,<PORT NUMBER>, <SERVER NAME>,com.ibm.db2.jcc.DB2DataSource,com.ibm.db2.jcc.DB2Driver"Note: The above script consists of only two commands, each of which must start and finish on their own separate line. Therefore, when copying and pasting the script, the entire script must occupy only two lines, as follows:
mqsideleteconfigurableservice MB8BROKER -c JDBCProviders -o SIMPLERROUTEDB
mqsicreateconfigurableservice MB8BROKER -c JDBCProviders -o SIMPLERROUTEDB -n connectionUrlFormat,databaseName,description,jarsURL,portNumber,serverName,type4DatasourceClassName,type4DriverClassName -v "jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];,SROUTEDB,Simplified Database Routing Sample Database,<JARS URL>,<PORT NUMBER>,<SERVER NAME>,com.ibm.db2.jcc.DB2DataSource,com.ibm.db2.jcc.DB2Driver"If the script is not copied and pasted as two lines, attempts to run it will result in an error like " -n is not recognised as a valid program....". This is because the system wrongly identifies every new line as the start of a new command.
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:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];' connectionUrlFormatAttr1='' connectionUrlFormatAttr2='' connectionUrlFormatAttr3='' connectionUrlFormatAttr4='' connectionUrlFormatAttr5='' databaseName='SROUTEDB' databaseType='default_Database_Type' databaseVersion='default_Database_Version' description='Simplified Database Routing Sample Database' environmentParms='default_none' jarsURL='C:\Program Files\IBM\SQLLIB\java' portNumber='50000' securityIdentity='default_User@default_Server' serverName='localhost' type4DatasourceClassName='com.ibm.db2.jcc.DB2DataSource' type4DriverClassName='com.ibm.db2.jcc.DB2Driver' BIP8071I: Successful command completion.
To specify the user identifier and password to associate with the JDBC provider SIMPLERROUTEDB use the mqsisetdbparms and mqsichangeproperties commands. 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.