Creating a JDBC provider entry for an Oracle 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 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.

  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 a text editor.

      You must replace the following place holders in the script with values appropriate to your environment.

      • <SID> The Oracle System identifier that identifies the database containing the tables used by this sample.
      • <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.
        • On Windows: <ORACLE HOME>\jdbc\lib
        • On Linux: <ORACLE HOME>/jdbc/lib/
      • <PORT NUMBER> The port number on which the database server is listening. The Oracle default is 1521.
      • <SERVER NAME> The name of the host on which Oracle is running; for example localhost.
      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" 
      
    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:

      1. Deletes any existing JDBCProvider entry named SIMPLERROUTEDB
      2. Creates a new JDBCProvider entry named SIMPLERROUTEDB for use by the Simplified Database Routing sample
    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 was 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: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.
  2. 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.

    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 preceding 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 Oracle

Back to Setting up the database

Back to sample home