Creating a JDBC provider entry for a DB2 database

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.

  1. Create a JDBC provider entry for the database:

    1. Start the broker and ensure that an execution group has been deployed to the broker. For example, to start a broker, open a command console window in which you can run WebSphere Message Broker commands, and enter the following command:
      mqsistart MB8BROKER
    2. Open a new document in a text editor, copy and paste the following script, which creates the JDBC provider entry SIMPLERROUTEDB to provide JDBC database access for the Simplified Database Routing sample, into the document. You must replace the following place holders in the script with values appropriate to your environment.
      • <JARS URL> The local directory path on the system on which the broker is running, where the JAR file that contains the type 4 driver class is located. The following locations are the default locations:
        • On Windows: C:\Program Files\IBM\SQLLIB\java
        • On Linux: sqllib/java, for example, /home/db2inst1/sqllib/java/
      • <PORT NUMBER> The port number on which the database server is listening. The DB2 default is 50000.
      • <SERVER NAME> The name of the host on which DB2 is running; for example localhost.
      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.

      The flow contains a DatabaseRetrieve node, therefore use the com.ibm.db2.jcc.DB2DataSource class instead of com.ibm.db2.jcc.DB2XADataSource.

    3. Save the file. The following instructions assume that you have called the file simplifieddbrouting.bat
    4. In the command console, navigate to the folder that contains simplifieddbrouting.bat, and enter the following command to run the script:
      simplifieddbrouting.bat

      When you run the script, it performs the following tasks:

      • Deletes any existing JDBCProvider entry named SIMPLERROUTEDB.
      • Creates a new JDBCProvider entry named SIMPLERROUTEDB for use by the Simplified Database Routing sample. The default settings target a DB2 DBMS by using this vendor's JDBC type 4 driver class.
    5. Wait for the script to finish running. If you are running the script for the first time, the following message is displayed:
      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.
    6. You can enter the following command to check that the default broker registry has been correctly updated with the sample SIMPLERROUTEDB JDBCProvider entry:
      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.
  2. 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.

    1. Enter the following command to associate the user identifier and password with a security identity:
      mqsisetdbparms MB8BROKER -n jdbc::mySecurityIdentity -u <user ID> -p <password>
    2. Enter the following command to associate the security identity, which you defined in the previous step, with the securityIdentity property of the JDBC provider SIMPLERROUTEDB:
      mqsichangeproperties MB8BROKER -c JDBCProviders -o SIMPLERROUTEDB -n securityIdentity -v mySecurityIdentity
    3. You must stop and restart the broker for the changes to the JDBC provider to become available to a message flow that is already deployed. If you have already deployed the sample, stop and restart the broker.

    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.

Back to Setting up DB2

Back to Setting up the database

Back to sample home